Retrieving the value of an identity column from a record in a database table after inserting it may sound ridiculously simple (and it is), however if you google for it you will find that people often have problems with it when using ADO.NET, specifically if they are not using SQL Server.
First of all, let me say that adding a row to a datatable, updating it using a table adapter and then refreshing the table is not a solution to this problem. This is a very bad idea:
[sourcecode language=”csharp”]
BooksDataset booksDataSet = new BooksDataset();
BooksDatasetTableAdapters.BooksTableAdapter booksTableAdapter = new BooksDatasetTableAdapters.BooksTableAdapter();
booksTableAdapter.Fill(booksDataSet.Books);
var newBookRow = booksDataSet.Books.NewBooksRow();
newBookRow.Author = “Some Author Name”;
newBookRow.Name = “Some Book Name”;
booksDataSet.Books.AddBooksRow(newBookRow);
booksTableAdapter.Update(booksDataSet.Books);
booksTableAdapter.Fill(booksDataSet.Books);
Console.WriteLine(booksDataSet.Books.Last().ID);
[/sourcecode]
This might seem to work, but given enough time it will bring you problems because between the Update, the Fill and the retrieving of the ID of the last record many things might happen in a concurrent environment, namely another row might get inserted or the row you’ve inserted might have been deleted.
If you are using SQL Server everything works great you’ll probably never have to worry about this. However, if you are not, and my personal experience is with MySQL, specifically using MySQL with the ODBC connector, the wizards of Visual Studio do not help you as much as if you were using SQL Server. If you are not interested in resorting to stored procedures to solve this problem, read on.
But before we go into the solution, let’s just look at how what gave this post it’s title. The auto-generated ID columns whose values we so desperately want to get.
First let’s look at the ways you can get the generated ID for an inserted row in SQL Server. The need to understand this next part will become clear at the end. Trust me.
SCOPE_IDENTITY()
From the msdn documentation: “Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.”
@@IDENTITY
The difference between @@IDENTITY and SCOPE_IDENTITY is that @@IDENTITY will “return the last identity values that are generated in any table in the current session”
“For example, there are two tables, T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 by the trigger.
Assuming that both T1 and T2 have identity columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1. @@IDENTITY will return the last identity column value inserted across any scope in the current session. This is the value inserted in T2. SCOPE_IDENTITY() will return the IDENTITY value inserted in T1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function will return the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.”
This is all from the msdn documentation.
Lastly, for SQL Server, there’s the IDENT_CURRENT(‘table_name’) that gives you the “last identity value generated for a specified table or view. The last identity value generated can be for any session and any scope.”
For MySQL you can use LAST_INSERT_ID(), and to ensure you that this is what we really want have a look at this from MySQL’s documentation:
“The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.”
Now that we have got that out of the way, let’s talk about the reason why you can’t immediately retrieve the ID when you add a record to a database through a table adapter update. Specifically, why this will get you in trouble:
[sourcecode language=”csharp”]
BooksDataset booksDataSet = new BooksDataset();
BooksDatasetTableAdapters.BooksTableAdapter booksTableAdapter = new BooksDatasetTableAdapters.BooksTableAdapter();
booksTableAdapter.Fill(booksDataSet.Books);
var newBookRow = booksDataSet.Books.NewBooksRow();
newBookRow.Author = “Some Author Name”;
newBookRow.Name = “Some Book Name”;
booksDataSet.Books.AddBooksRow(newBookRow);
booksTableAdapter.Update(booksDataSet.Books);
Console.WriteLine(newBookRow.ID);
[/sourcecode]
Assuming that you are using a strongly typed dataset, in this case that’s BooksDataSet, and you used the wizzard to generate that strongly-typed dataset and the corresponding table adapters. If you are using SQL server as your database that will print the new ID value into the console. If you are using another database, chances are that won’t be the case.
For me it was MySQL, and what I got was –1. There are a couple of reasons why this happened, let’s go through them:
First, the Visual Studio 2010 wizard does not generate an Insert command for the table adapter that refreshes the data:
That is what you will get if you poke in your dataset’s configuration (just go to the designer, right-click the dataset, configure). The good news is that you can edit the commands yourself, and it is very very easy, trust me. I’ll just show you how to do the Insert one:
See the highlighted part, that’s what we are going to change. (By the way, that’s what you’d get if you’re using MySQL, the “?”s give it away, there are no named parameters in MySQL). You actually have to type a version of this for your particular table:
INSERT INTO books
(Name, Author)
VALUES (?, ?);
SELECT ID, Name, Author FROM books
WHERE ID = LAST_INSERT_ID()
And when you press OK you’ll get a warning stating “Unable to parse query text.”. Just click ignore.
But wait! Don’t go try this just yet. You need to be able to perform multiple statements. This is very important, and this is why you see so many people complaining that they can’t get this to work. Search for the FLAG_MULTI_STATEMENTS here. This is for the ODBC connector for MySQL. Just so you can understand how that can be hard to find here’s how you turn that on for the MySQL’s ODBC connector:
There you go.
If you did not TL;DRed by now, here’s why it’s important to be aware of the intricacies of IDENTITY, SCOPE_IDENTITY (SQL Server) and LAST_INSERT_ID (for MySql). For this particular scenario it should be clear that you should use SCOPE_IDENTITY (and this is assuming that you need to fiddle with the auto generated commands for some reason) because if you use @@IDENTITY you can get an autogenerated ID for some other table where a record has been inserted, and this might very well happen given sufficient concurrent writes happening at your database.
LAST_INSERT_ID is the only alternative I know for MySql.
Relevant Links:
Connector ODBC: http://dev.mysql.com/downloads/connector/odbc/
Adding Commands “by hand” to a table adapter: http://www.vbmysql.com/articles/vbnet-mysql/myodbc-connector
SQL Server Identity Column values (meaning of @@identity, Scope_identity()): http://msdn.microsoft.com/en-us/library/ks9f57t0.aspx
Managing an @@IDENTITY Crisis: http://msdn.microsoft.com/en-us/library/ms971502.aspx
“How Microsoft Does It ”: http://www.davidhayden.com/blog/dave/archive/2006/02/16/2803.aspx
How to Get the Unique ID for the Last Inserted Row: http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html