Getting the value of an identity column after an insert

[UncleJoe]

Hi all. I am trying to resolve this seemingly very simple and not to
mention well documented problem but just couldn't seem to get it to
work. I have a datatable that uses an identity column as the primary
key. I need to get the value of the identity column back after
performing an insert to the SQL Server 2005 (beta) database. From all
the information that I have gathered, the following code should work:

1 SqlConnection conn = new SqlConnection(connStr);
2 conn.Open();
3 SqlCommand cmd = new SqlCommand(string.Format("SELECT * FROM {0}",
dt.TableName), conn);
4 SqlDataAdapter da = new SqlDataAdapter(cmd);
5 SqlCommandBuilder CB = new SqlCommandBuilder(da);
6 da.InsertCommand = CB.GetInsertCommand();
7 da.InsertCommand.CommandText += string.Format("; SELECT * FROM {0}
WHERE {1} = @@IDENTITY", dt.TableName, "PKeyID");
8 da.InsertCommand.UpdatedRowSource = UpdateRowSource.Both;
9 da.Update(dt);
10 dt.AcceptChanges();

* dt is the database containing the rows that need to be inserted.
* I had the FillSchema operated on dt so the identity column's
AutoIncrement property is set to true.
* I set the AutoIncrementSeed and AutoIncrementStep properties of the
identity column set to -1.
* I have already tried setting the UpdatedRowSource property to "Both"
and "FirstReturnedRecord".

But after running the above code, the value of the identity column
remains -1. What am I missing???

In addition, if I need to wrap the update within a transaction, should
I invoke the AcceptChanges call inside or outside the transaction? Will
the row state reverse along with a rollback call?

Any help will be greatly appreciated.

 

[Bart Mermuys]

Hi,

Hi all. I am trying to resolve this seemingly very simple and not to
mention well documented problem but just couldn't seem to get it to
work. I have a datatable that uses an identity column as the primary
key. I need to get the value of the identity column back after
performing an insert to the SQL Server 2005 (beta) database. From all
the information that I have gathered, the following code should work:

1 SqlConnection conn = new SqlConnection(connStr);
2 conn.Open();
3 SqlCommand cmd = new SqlCommand(string.Format("SELECT * FROM {0}",
dt.TableName), conn);
4 SqlDataAdapter da = new SqlDataAdapter(cmd);
5 SqlCommandBuilder CB = new SqlCommandBuilder(da);
6 da.InsertCommand = CB.GetInsertCommand();

The problem is that a CommandBuilder hooks up to DataAdapter.RowUpdating
event and sets an insert/update/delete Command there. This way it can use
different Commands depending on the row, this is mostly usefull for updates,
because then only the values of the fields that have really changed are
sent, instead of all fields.

When you assign your own Command to the DataAdapter.InsertCommand then it
won't change the InsertCommand, but it doesn't consider the Command from
GetInsertCommand() as your own Command and so it will change it during
RowUpdating.

You could use the Clone method to get a copy of the Command which
(obviously) won't have the same reference and therefore is no longer
recognized by the CommandBuilder as its own and it won't be changed during
RowUpdating.

da.InsertCommand = ((IClonable)CB.GetInsertCommand()).Clone();
da.InsertCommand.CommandText += string.Format("; SELECT * FROM {0}
WHERE {1} = @@IDENTITY", dt.TableName, "PKeyID");

HTH,
Greetings

 

[Sahil Malik [MVP]]

Joe,

I see many problems with the code below.

-- you should try and use named parameters rather than {0} et. al.
-- use scope_identity() instead of @@identity
-- no need to do batched sql, use sqlparameter instead, and specify output
direction
-- tie sqlparameter to the datatable using the "sourcecolumn" property

and finally let the dataadapter do it's magic - you can see a working
example of what I just said in Exercise 9.4 of my ADO.NET 2.0 book in
Chapter 9 (See Signature)

To answer your other 2 questions --

In addition, if I need to wrap the update within a transaction, should
I invoke the AcceptChanges call inside or outside the transaction?

This depends on your logic and what you need - again there is more info on
this in Chap 9,10, and 11 of my book.

Will
the row state reverse along with a rollback call?

No it won't, and you have to either do AcceptChangesonUpdate = false (.NET
2.0 only), or you have to extract changes - update - and merge back into the
original DS.

HTH

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

 

[UncleJoe]

Thanks so much for your help. It worked beautifully.