Adding row to DataTable which has Sql Server identity column type

[Edward Diener]

If I call NewRow() for a DataTable and add a row with an Sql Server
identity column, whose value I set to null before calling
DataTable.Rows.Add, does the identity value get created immediately in
the new row of the DataTable ? If so, how do I determine the value of
the identity column after the row has been added ? Is it in the DataRow
object I pass to the DataTable.Rows.Add method after the call completes ?

The identity column is my unique key for the table and I need to use it
to find rows in the DataTable.

 

[W.G. Ryan - MVP]

Set Autoincrement on the column to true, seed it so that it counts
backwards. When you go to the db to updated it, SQL Server will assign a
value to it and you can retrieve that value. Check out Bill Vaughn's
article Managing An @@Identity crisis , he walks you through the process.

--
Cordially,

W.G. Ryan - MVP
Windows Embedded

Author - MCTS Self-Paced Training Kit (Exam 70-536)
http://www.amazon.com/gp/product/0735622779/ref=pd_ts_b_6/103-6803568-2524652?n=5&s=books&v=glance
http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?z=y&isbn=0735622779&itm=1

 

[Edward Diener]

Set Autoincrement on the column to true, seed it so that it counts
backwards. When you go to the db to updated it, SQL Server will assign a
value to it and you can retrieve that value. Check out Bill Vaughn's
article Managing An @@Identity crisis , he walks you through the process.

I am talking about adding rows with Identity columns to a DataTable, not
INSERTing them immediately to a table in an Sql Server database. When
one adds a row to a DataTable, one first creates a row with DataTable's
NewRow, then one adds the row to DataTable's Rows with Add. At this
point in my DataTable I need to have the Identity column updated, from
the null value I set for the column before I add the new row, to an
identity value, so that I can later find the row in my DataTable, since
the Identity is the primary key for the table. Since I am passing in a
DataRow I am hoping that after calling myTable.Rows.Add(myNewRow) the
column value for the Identity column of myNewRow now has a unique
identity value after the Add call. I have not tried this out in the
debugger to see if it is the case or not.

If it is not, I do not know how I can identity the row I have added to
the DataTable, since the Identity column provides the primary key, so I
am hoping very much it is so.

The column definition for the identity column in the actual table is set
to Identity = Yes, Identity Seed = 1, Identity Increment = 1 in Server
Explorer for the column in the table. Is this setting AutoIncrement for
the Identity column to true ? I think so. But why you suggest that I
should set a high seed and increment backwards is beyond me.

I will look at Bill Vaughan's article but a preliminary glance suggested
that he was talking about immediately INSERTing a row with an Identity
column into a table via an SqlCommand and SQL statement rather than
dealing with DataTable. Eventually, of course, the updates to my
DataTable get written to the database using SqlDataAdaptwer's Update()
method, but by that time I should already know the Identity column's
value for each row in the DataTable so I can deal with it
programatically ( it's a primary key/foreign key connection to other
tables so I can find rows from the other non-identity table's foreign key).

 

[Matt Noonan]

I will look at Bill Vaughan's article but a preliminary glance
suggested that he was talking about immediately INSERTing a row with
an Identity column into a table via an SqlCommand and SQL statement
rather than dealing with DataTable. Eventually, of course, the
updates to my DataTable get written to the database using
SqlDataAdaptwer's Update()
method, but by that time I should already know the Identity column's
value for each row in the DataTable so I can deal with it
programatically ( it's a primary key/foreign key connection to other
tables so I can find rows from the other non-identity table's foreign
key).

The only way you can get the final IDENTITY value to use for foreign keys,
is to actually INSERT the record into the database (the DB is the only
authority for the IDENTITY value).

If you absolutely need a primary key value before INSERTing the record into
the database, then you should use a GUID (uniqueidentifier in SQL). You can
populate the GUID column on the client side, use the column in foreign key
relationships, and then INSERT the record into the database.

 

[Edward Diener]

The only way you can get the final IDENTITY value to use for foreign keys,
is to actually INSERT the record into the database (the DB is the only
authority for the IDENTITY value).

If you absolutely need a primary key value before INSERTing the record into
the database, then you should use a GUID (uniqueidentifier in SQL). You can
populate the GUID column on the client side, use the column in foreign key
relationships, and then INSERT the record into the database.

Yes, I realized this after reading the article. Thanks !