Retrieving Identity field after inserting datarows into SQL Server using ADO.NET

[Wokawidget]

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:

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:

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:

SELECT @@SCOPE_IDENTITY AS UserID

Is tagged to the end of the insert statement.

I modified the above code so that it is like:

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:

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:

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