Results 1 to 20 of 20

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

Threaded View

  1. #1

    Thread Starter
    Super Moderator Wokawidget's Avatar
    Join Date
    Nov 2001
    Location
    Headingly Occupation: Classified
    Posts
    9,632

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

    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:
    1. Public Sub Update(ByVal SQL As String, ByRef Data As DataTable)
    2.         Dim Comm As New SqlCommand(SQL, Connection)
    3.         Dim da As New SqlDataAdapter(Comm)
    4.         Dim db As New SqlCommandBuilder(da)
    5.  
    6.         da.Update(Data)
    7.         CloseConnection()
    8.     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:
    1. Public Sub Update(ByRef Data As DataTable)
    2.         Dim da As New SqlDataAdapter
    3.         da.InsertCommand = BuildInsertCommand(Data)
    4.         da.UpdateCommand = BuildUpdateCommand(Data)
    5.         da.Update(Data)
    6.         CloseConnection()
    7.     End Sub
    8.  
    9.     Private Function BuildUpdateCommand(ByRef Data As DataTable) As SqlCommand
    10.         Dim NewCmd As New SqlCommand
    11.         NewCmd.Connection = Connection
    12.  
    13.         Dim SetString As String
    14.         Dim Identity As String
    15.         For Each Col As DataColumn In Data.Columns
    16.             If Col.AutoIncrement Then
    17.                 Identity = Col.ColumnName & " = @" & Col.ColumnName
    18.             Else
    19.                 If Not (SetString = String.Empty) Then
    20.                     SetString &= ", "
    21.                 End If
    22.                 SetString &= Col.ColumnName & " = @" & Col.ColumnName
    23.             End If
    24.  
    25.             Dim Param As New SqlParameter
    26.             Param.ParameterName = "@" & Col.ColumnName
    27.             Param.SourceColumn = Col.ColumnName
    28.             NewCmd.Parameters.Add(Param)
    29.         Next Col
    30.  
    31.         Dim CmdText As String = "UPDATE " & Data.TableName & " SET " & SetString & " WHERE " & Identity
    32.         NewCmd.CommandText = CmdText
    33.         Return NewCmd
    34.     End Function
    35.  
    36.     Private Function BuildInsertCommand(ByRef Data As DataTable) As SqlCommand
    37.         Dim NewCmd As New SqlCommand
    38.         NewCmd.Connection = Connection
    39.  
    40.         Dim Fields As String
    41.         Dim Values As String
    42.         Dim Identity As String
    43.         For Each Col As DataColumn In Data.Columns
    44.             If Col.AutoIncrement Then
    45.                 Identity = "SELECT @@SCOPE_IDENTITY AS " & Col.ColumnName
    46.             Else
    47.                 If Not (Fields = String.Empty) Then
    48.                     Fields &= ", "
    49.                 End If
    50.                 Fields &= Col.ColumnName
    51.                 If Not (Values = String.Empty) Then
    52.                     Values &= ", "
    53.                 End If
    54.                 Values &= "@" & Col.ColumnName
    55.  
    56.                 Dim Param As New SqlParameter
    57.                 Param.ParameterName = "@" & Col.ColumnName
    58.                 Param.SourceColumn = Col.ColumnName
    59.                 NewCmd.Parameters.Add(Param)
    60.             End If
    61.         Next Col
    62.  
    63.         Dim CmdText As String = "INSERT INTO " & Data.TableName & "(" & Fields & ") VALUES(" & Values & ")"
    64.         If Not (Identity Is Nothing) Then
    65.             CmdText &= "; " & Identity
    66.         End If
    67.         NewCmd.CommandText = CmdText
    68.         Return NewCmd
    69.     End Function
    When saving my Users datatable this produces an insert command text of:
    VB Code:
    1. 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:
    1. Dim Param As New SqlParameter
    2. Param.ParameterName = "@" & Col.ColumnName
    3. Param.SourceColumn = Col.ColumnName
    4. 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width