how do I get the ID of a row I just added using OleDbDataAdapter.Update() ?

[Bennett Haselton]

Suppose I add a new row to a table in a dataset, and then I use an
OleDbDataAdapter to add that new row to a SQL Server database using
OleDbDataAdapter.Update(), as in the following code:

dsLocalDataSet.user_postRow newRow =
dsLocalDataSet1.user_post.Newuser_postRow();
newRow.post_text = this.lblHiddenMessageStorage.Text;
newRow.post_datetime = System.DateTime.Now;
dsLocalDataSet1.user_post.Adduser_postRow(newRow);
this.oleDbUserPostAdapter.Update(dsLocalDataSet1.user_post);

How do I get the ID in the underlying database, of the new row that I
just added?

It might not be the same as the ID of the row that was just added to
the DataSet table. Even if new IDs are generated sequentially in both
the underlying database table and in the DataSet table, and say the
records already in the table had IDs 1, 2, 3, 4, and 5, when I add the
new row to the DataSet table it will get ID 6. However, in the
meantime, some other process might have added a new row to the table
in the underlying database, so that when the row actually gets added
through adapter's Update() method, in the database it will get ID 7.
So I can't just look at the ID of the row in the DataSet.

In MySQL you can do SELECT LAST_INSERT_ID() to get the last
automatically generated value that was inserted into an AUTO_INCREMENT
column by your current connection. Is there an equivalent for SQL
Server? (Or even better, a way to get the value using the built-in
functions of the typed DataSet, so you don't have to get it
inelegantly passing a raw command to SQL Server, which is error-prone
in case you ever want to switch to a different data source type, etc.)

-Bennett

 

[Bennett Haselton]

Suppose I add a new row to a table in a dataset, and then I use an
OleDbDataAdapter to add that new row to a SQL Server database using
OleDbDataAdapter.Update(), as in the following code:

dsLocalDataSet.user_postRow newRow =
dsLocalDataSet1.user_post.Newuser_postRow();
newRow.post_text = this.lblHiddenMessageStorage.Text;
newRow.post_datetime = System.DateTime.Now;
dsLocalDataSet1.user_post.Adduser_postRow(newRow);
this.oleDbUserPostAdapter.Update(dsLocalDataSet1.user_post);

How do I get the ID in the underlying database, of the new row that I
just added?

It might not be the same as the ID of the row that was just added to
the DataSet table. Even if new IDs are generated sequentially in both
the underlying database table and in the DataSet table, and say the
records already in the table had IDs 1, 2, 3, 4, and 5, when I add the
new row to the DataSet table it will get ID 6. However, in the
meantime, some other process might have added a new row to the table
in the underlying database, so that when the row actually gets added
through adapter's Update() method, in the database it will get ID 7.
So I can't just look at the ID of the row in the DataSet.

In MySQL you can do SELECT LAST_INSERT_ID() to get the last
automatically generated value that was inserted into an AUTO_INCREMENT
column by your current connection. Is there an equivalent for SQL
Server? (Or even better, a way to get the value using the built-in
functions of the typed DataSet, so you don't have to get it
inelegantly passing a raw command to SQL Server, which is error-prone
in case you ever want to switch to a different data source type, etc.)

-Bennett

Well I found how to do this -- in SQL Server, you call SELECT
@@IDENTITY to get the last automatically inserted ID, however it's
only valid if the connection has not been closed since the update
occurred for which you're trying to get the last inserted ID. So if
you just call a data adapter's Update() method, and the connection was
in the closed state before you called it, the connection will be
closed again when Update returns, and you'll lose the information
about the last inserted ID. So you have to put the adapter's
connection object in the Open state first, then call Update() and then
do a SELECT @@IDENTITY query.

Here's a utility function that does it:

public static int GetLastInsertID(OleDbConnection conn)
{
/*
* The connection object must be open at the time this method
* is called, and the connection cannot have been closed since
* the insertion occurred for which you are trying to get the
* last inserted ID. Otherwise, an exception will be thrown.
*/
OleDbDataAdapter objAdapter = new OleDbDataAdapter(
"SELECT @@IDENTITY AS 'Identity';", conn
);
DataSet ds = new DataSet();
objAdapter.Fill(ds, "tablename");
string strID = ds.Tables["tablename"].Rows[0]["Identity"].ToString();
if (strID == "")
{
throw new Exception("GetLastInsertID called but @@IDENTITY returned
nothing");
}
int nID = System.Int32.Parse(strID);
return nID;
}

and the code that calls the function:
dsLocalDataSet.wbuserRow newRow =
this.dsLocalDataSet1.wbuser.NewwbuserRow();
newRow.username = "abc";
newRow.password_hash = "def";
newRow.email_address = "ghi";
this.dsLocalDataSet1.wbuser.AddwbuserRow(newRow);

// must open connection first or GetLastInsertID won't work
this.oleDbLocalConnection.Open();

this.oleDbWbuserAdapter.Update(dsLocalDataSet1.wbuser);
Response.Write("last insert: " +
DatabaseUtils.GetLastInsertID(this.oleDbLocalConnection).ToString() +