Get IDENTITY primary key value to a datatable

[hugobooze]

I have a table with an automatic increasing primary key. I insert, update and delete rows in this table from my vb.net application by using a Datatable.

The problem is: When i insert a new row, the Datatable doesn't get the primary key (is null today). Is there any way to solve this? (I have to use a Datatable)

 

Here is my vb-code i use now:

 

'updateDataTable is executed first:
Public Sub updateDataTable(strSql_SelectedData as String)
  Dim da As SqlClient.SqlDataAdapter
  da = New SqlClient.SqlDataAdapter(strSql_SelectedData, connectionString)
  DataTable = New DataTable()
  da.Fill(DataTable)
End Sub

'Then a lot of code that manipulates data in the datatable is executed
' (for example: inserts rows)

'At last, updateDatabase is executed. 
'I want to change this code, so all inserted row's IDENTITY primary keys 
'  is fetched from the database
Public Sub updateDatabase()
  Dim da As SqlClient.SqlDataAdapter
  da = New SqlClient.SqlDataAdapter(strSql_DbUpdate, connectionString)
  da.MissingSchemaAction = MissingSchemaAction.AddWithKey
  sqlCommBuild = New SqlClient.SqlCommandBuilder(da)
  da.Update(DataTable)
  DataTable.AcceptChanges()
End Sub

[Mehyar]

From what I know, you need not worry about inserting the primary key in SQL Server if it is an identity, it will insert it by itself..

[RappyFood]

There is a good article that covers this topic in the MSDN library at http://msdn.microsoft.com/library/en-us/dnadonet/html/manidcrisis.asp?frame=true

[hugobooze]

Ok, so it seems that you have to use the DataAdapter Configuration Wizard, or write a lot of code manually.

 

I'm afraid this will not work in my solution, because i'm using the same dataadapter to update all tables in my database. The subs "updateDataTable" and "updateDatabase" (see above) is contained in a class that is inherited by one class for each table.

 

However, I've solved it in a little bit dirty way: I've created shared functions to insert new rows. The function returns the primary key value. I only use this code when I insert rows, and the table has an identity primary key. In all other cases I use my old code. See example of one of my shared insert-functions below:

 

Public Shared Function createItem_Shared(ByVal Name As String, ByVal isTemporary As Boolean, _
ByVal ChargeCodeSearchStr As String) As Int32

       Dim id As Int32
       Dim strSQL As String
       Dim connection As New SqlConnection(dbSK_Global.connectionString)
       strSQL = "INSERT INTO tblArticleGroup(Name, IsTemporary, ChargeCodeSearchStr) " & _
	"VALUES(@Name, @IsTemporary, @ChargeCodeSearchStr); Select @@Identity as 'ArticleGroupID' ;"

       Dim cmd As New SqlCommand(strSQL, connection)
       cmd.Parameters.Add("@Name", Name)
       cmd.Parameters.Add("@IsTemporary", isTemporary)
       cmd.Parameters.Add("@ChargeCodeSearchStr", ChargeCodeSearchStr)
       Dim param As New SqlParameter()
       param.Direction = ParameterDirection.Output
       param.ParameterName = "@ArticleGroupID"
       param.SqlDbType = SqlDbType.Int
       cmd.Parameters.Add(param)

       connection.Open()
       Dim dr As SqlDataReader = cmd.ExecuteReader()
       dr.Read()
       id = dr.Item("ArticleGroupID")
       connection.Close()

       Return id
End Function

 

As you see: I use SqlParameters and create an output parameter (ArticleGroupID), where the identity primary key will appear after the ExecuteReader-command.