If you have a datatable, and lets say this has a field UserID, which is an Identity column in the DB, then when you update this using a dataadapter the new UserID value is not inserted into the rows in the datatable.
For updating a datatable you can simpley use:
VB Code:
Public Sub Update(ByVal SQL As String, ByRef Data As DataTable)
Dim Comm As New SqlCommand(SQL, Connection)
Dim da As New SqlDataAdapter(Comm)
Dim db As New SqlCommandBuilder(da)
da.Update(Data)
CloseConnection()
End Sub
Where SQL is like:
Code:
SELECT UserID, Username, Password FROM Users
and where Connection is a function to get and open a connection to the SQL Server DB.
Using the above function it's quite easy to update, insert and delete rows as the SQLCommandBuilder does most of the hard work for you and creates the Update, insert and delete command objects.
This however is not good enough, as I want to see the userID field values. No idea why ADO.NET doesn't do this automatically for you.
What I decided to do is to scrap the SQL param.
I can manually build up the Update and Insert statements by looping through the columns in the datatable.
While looping, I check for an identity column. If there is one then:
Code:
SELECT @@SCOPE_IDENTITY AS UserID
Is tagged to the end of the insert statement.
I modified the above code so that it is like:
VB Code:
Public Sub Update(ByRef Data As DataTable)
Dim da As New SqlDataAdapter
da.InsertCommand = BuildInsertCommand(Data)
da.UpdateCommand = BuildUpdateCommand(Data)
da.Update(Data)
CloseConnection()
End Sub
Private Function BuildUpdateCommand(ByRef Data As DataTable) As SqlCommand
Dim NewCmd As New SqlCommand
NewCmd.Connection = Connection
Dim SetString As String
Dim Identity As String
For Each Col As DataColumn In Data.Columns
If Col.AutoIncrement Then
Identity = Col.ColumnName & " = @" & Col.ColumnName
Else
If Not (SetString = String.Empty) Then
SetString &= ", "
End If
SetString &= Col.ColumnName & " = @" & Col.ColumnName
End If
Dim Param As New SqlParameter
Param.ParameterName = "@" & Col.ColumnName
Param.SourceColumn = Col.ColumnName
NewCmd.Parameters.Add(Param)
Next Col
Dim CmdText As String = "UPDATE " & Data.TableName & " SET " & SetString & " WHERE " & Identity
NewCmd.CommandText = CmdText
Return NewCmd
End Function
Private Function BuildInsertCommand(ByRef Data As DataTable) As SqlCommand
Dim NewCmd As New SqlCommand
NewCmd.Connection = Connection
Dim Fields As String
Dim Values As String
Dim Identity As String
For Each Col As DataColumn In Data.Columns
If Col.AutoIncrement Then
Identity = "SELECT @@SCOPE_IDENTITY AS " & Col.ColumnName
Else
If Not (Fields = String.Empty) Then
Fields &= ", "
End If
Fields &= Col.ColumnName
If Not (Values = String.Empty) Then
Values &= ", "
End If
Values &= "@" & Col.ColumnName
Dim Param As New SqlParameter
Param.ParameterName = "@" & Col.ColumnName
Param.SourceColumn = Col.ColumnName
NewCmd.Parameters.Add(Param)
End If
Next Col
Dim CmdText As String = "INSERT INTO " & Data.TableName & "(" & Fields & ") VALUES(" & Values & ")"
If Not (Identity Is Nothing) Then
CmdText &= "; " & Identity
End If
NewCmd.CommandText = CmdText
Return NewCmd
End Function
When saving my Users datatable this produces an insert command text of:
VB Code:
INSERT INTO Users(Username, Password) Values(@Username, @Password); SELECT @@SCOPE_IDENTITY As UserID
The command param object allows you to bind rows to an @Varible by doing:
VB Code:
Dim Param As New SqlParameter
Param.ParameterName = "@" & Col.ColumnName
Param.SourceColumn = Col.ColumnName
NewCmd.Parameters.Add(Param)
Now when I save my DB the new identity value is added to datatable.
Perfect.
I am howveer making some assumptions here:
In both functions I am assuming that the tablename in the DB is the same as the tablename of the datatable.
In the update command function I am using the Identity column to specify which rows to update. This is used to create the WHERE clause of my update statement. I should however change this to use unique columns to build up the WHERE clause. Not figured that out yet.
WOka