Λοιπόν, το κοίταξα το πρόβλημα κι έγραψα λίγο κώδικα για να δοκιμάσω στην πράξη αυτά που θεωρητικά είπαμε. Ανακεφαλαιώνοντας, έχουμε και λέμε:
Υπάρχουν τρεις τρόποι για να κάνεις αυτό που θέλεις. Ας ξεκινήσουμε από τον πρώτο που είναι να χρησιμοποιήσεις το RowUpdated event του DataAdapter και την στιγμή που γράφεται κάθε εγγραφή να στέλνεις ένα Query που να ρωτάς το νέο ID.
Σε όλα τα παραδείγματα, υποτίθεται ότι δουλεύουμε σε έναν πίνακα στη Northwind που τον έχω φτιάξει με αυτήν την εντολή
CREATE TABLE DataBuilderDemo(ColumnID int identity NOT NULL PRIMARY KEY, Column1 nvarchar(16) NOT NULL)
O κώδικας στην φόρμα έχει ως εξής:
Dim ConnectionNorthwind As New SqlConnection("Data Source=.;Initial Catalog=Northwind;Integrated Security=True")
Dim DataAdapterCategories As New SqlDataAdapter("SELECT ColumnID, Column1 FROM DataBuilderDemo", ConnectionNorthwind)
Dim CommandBuilderCategories As New SqlCommandBuilder(DataAdapterCategories)
Dim DataTableCategories As New DataTable
Dim CommandRefresh As New SqlCommand("SELECT @@IDENTITY", ConnectionNorthwind)
Private Sub CreateInsertCommandButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CreateInsertCommandButton.Click
DataAdapterCategories.InsertCommand = CommandBuilderCategories.GetInsertCommand
AddHandler DataAdapterCategories.RowUpdated, AddressOf RowUpdatedHandler
End Sub
Private Sub FillTableButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FillTableButton.Click
DataAdapterCategories.Fill(DataTableCategories)
End Sub
Private Sub AddNewRowButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddNewRowButton.Click
Dim RowNew As DataRow
RowNew = DataTableCategories.NewRow
RowNew.Item(0) = 0
RowNew.Item(1) = "New Row"
DataTableCategories.Rows.Add(RowNew)
PrintRows("Rows before update:")
End Sub
Private Sub UpdateButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UpdateButton.Click
Try
DataAdapterCategories.Update(DataTableCategories)
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
PrintRows("Rows after update:")
End Sub
Private Sub PrintRows(ByVal message As String)
Console.WriteLine(message)
For Each row As DataRow In DataTableCategories.Rows
Console.WriteLine(String.Format(" {0}, {1}", row.Item(0), row.Item(1)))
Next row
Console.WriteLine()
End Sub
Private Sub RowUpdatedHandler(ByVal sender As Object, ByVal e As SqlRowUpdatedEventArgs)
If e.Status = UpdateStatus.[Continue] AndAlso (e.StatementType = StatementType.Insert) Then
e.Row("ColumnID") = CInt(CommandRefresh.ExecuteScalar)
e.Row.AcceptChanges()
End If
End Sub
Προσοχή χρειάζεται στα εξής σημεία: Δεν μπορούμε να χρησιμοποιήσουμε SCOPE_IDENTITY() αντί για @@IDENTITY γιατί στη RowUpdatedHandler, κάθε φορά που τρέχουμε το CommandRefresh, δημιουργείται νέο connection και το SCOPE_IDENTITY() επιστρέφει null. Επίσης, αυτή η τεχνική δουλεύει και σε Access.
Η δεύτερη τεχνική είναι να αλλάξεις το InsertCommand που σου δίνει ο CommandBuilder έτσι ώστε να έχεις ένα Batched Query. Στη νέα έκδοση των παραπάνω, καταργείται η ρουτίνα RowUpdatedHandler, και το περιεχόμενο της CreateInsertCommandButton_Click αλλάζει σε:
Private Sub CreateInsertCommandButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CreateInsertCommandButton.Click
DataAdapterCategories.InsertCommand = CommandBuilderCategories.GetInsertCommand
DataAdapterCategories.InsertCommand.CommandText &= "; SELECT @@IDENTITY AS ColumnID"
DataAdapterCategories.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord
End Sub
Όμως φρούδες οι ελπίδες... Το παραπάνω δεν δουλεύει αν και θα έπρεπε! Λέω "αν και θα έπρεπε" γιατί αν αντί γι αυτό, γράψουμε
Private Sub CreateInsertCommandButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CreateInsertCommandButton.Click
Dim InsertCommand As SqlCommand
Dim strSQL As String
strSQL = "INSERT INTO DataBuilderDemo (Column1) VALUES (@Column1); SELECT SCOPE_IDENTITY() AS ColumnID"
InsertCommand = New SqlCommand(strSQL, ConnectionNorthwind)
InsertCommand.Parameters.Add("@Column1", SqlDbType.NVarChar, 16, "Column1")
InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord
DataAdapterCategories.InsertCommand = InsertCommand
End Sub
δηλαδή κατασκευάσουμε χειροκίνητα την InsertCommand, τότε παίζει! Απλά δεν του κάνει η InsertCommand που γυρίζει ο CommandBuilder, ακόμα κι αν προσθέσει κανείς στο τέλος το "; SELECT SCOPE_IDENTITY() AS ColumnID". Βασικά, έκανα πολλές δοκιμές αλλά δεν βρήκα καμιά ειδοποιό διαφορά ανάμεσα στα δύο Commands ώστε να καταλάβω τι δεν πάει καλά με το command που παράγει το CommandBuilder. Όποιος έχει όρεξη ας το ψάξει περισσότερο να μας πει.
Υπάρχει και μία τρίτη τεχνική που είναι να χρησιμοποιήσεις stored procedure η οποία θα επιστρέφει ως output το νέο ID.
CREATE PROCEDURE spInsertDataBuilderDemo
(@ColumnID int OUTPUT,
@Column1 nvarchar(16))
AS
INSERT INTO tblAutoIncrementTest(Column1) VALUES (@Column1)
SELECT @ColumnID = SCOPE_IDENTITY()
Και αντίστοιχα, η CreateInsertCommandButton_Click γίνεται κάπως έτσι:
Private Sub CreateInsertCommandButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CreateInsertCommandButton.Click
Dim InsertCommand As SqlCommand
Dim strSQL As String
Dim param As SqlParameter
strSQL = "spAutoIncrementTestInsert"
InsertCommand = New SqlCommand(strSQL, ConnectionNorthwind)
param = InsertCommand.Parameters.Add("@ColumnID", SqlDbType.Int, 0, "ColumnID")
param.Direction = ParameterDirection.Output
InsertCommand.Parameters.Add("@Column1", SqlDbType.NVarChar, 16, "Column1")
InsertCommand.CommandType = CommandType.StoredProcedure
InsertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters
DataAdapterCategories.InsertCommand = InsertCommand
End Sub
Από τις τρεις λύσεις, εφόσον θέλεις απαραιτήτως να χρησιμοποιήσεις CommandBuilder ουσιαστικά σου κάνει μόνο η πρώτη. Θα σου πρότεινα να επανεξετάσεις το κατά πόσο είναι εφικτό να μην χρησιμοποιήσεις τον CommandBuilder και να περάσεις στην δεύτερη ή ακόμα καλύτερα την τρίτη λύση που έχει καλύτερο performance. Προσωπικά, ποτέ δεν τον έχω χρησιμοποιήσει και τώρα βλέπω πόσο δίκιο έχουν όλοι αυτοί που έγραφαν στα blogs και τα βιβλία τους ότι είναι μόνο για quick & dirty και όχι production κώδικα.
Vir prudens non contra ventum mingit