Updating Auto-Numbered Primary Keys in a DataTable (MSAccess Databases)

It is common to configure back-end database tables to contain a column with an auto-generated ID unique to each row. When a row is added, the application layer just makes up an ID for each newly added row (-1, -2 etc.).

When the newly added row is added back to the database (think TableAdapter.Update) the database auto-generates the actual ID. But the application still skips blithely along with its made-up ID. When subsequent operations are performed on the database (like deleting the newly added row), this situation can result in a Concurrency exception. Yes, a Concurrency exception. Even with a single-user, single-threaded application. Threw me for a loop.

Apparently this is not such a big deal for SQL users, because you can bundle multiple commands into a single transaction. SO they just bundle a command that retrieves the ID from the database after the row has been added and update the DataTable accordingly. I don't know much about SQL, so please comment if you can shed more light on this mechanism.

For us poor schlubs stuck with Access, it seems that we can't bundle commands into a single transaction. So what's a guy/gal to do? An article[^]from the talented Beth Massi describes a solution for VB .NET, and my tip is closely modeled on her article. I did have to make some changes for C# though.

So here is a walkthrough:

1. Include your database in your project. This will auto-generate a xxDataset.xsd (xx is your database name).
   
2. Double-click xxDataset.xsd to bring up the Database Designer.
   
3. Right click any table and choose View Code. This auto-generates a xxDataSet.cs file that EXTENDS the auto-generated code that Visual Studio created for you.
   
4. Delete the contents of xxDataSet.cs and replace it with the following:
   
   C#

   namespace DatabaseTest.App_Data.{dbName}DataSetTableAdapters
   {
       /// /// This class extends the one that is auto-generated by VS when including 
       /// the database file.
       /// 
       public partial class {tableName}TableAdapter
       {
           public void HookUpHandlers()
           {
               this.Adapter.RowUpdated += 
                   new OleDbRowUpdatedEventHandler(Adapter_RowUpdated);
           }
   
           public void Adapter_RowUpdated(object sender, OleDbRowUpdatedEventArgs e)
           {
               // Every time a row is added populate the ID column with the auto-
               // generated value from the database.
               TableAdapterHelper.PopulateKey(e, Connection, Transaction);
           }
       }
   
       /// 
       /// This helper class contains a generic method that updates the primary key 
       ///   column with the auto-generated on from the database.
       /// NOTE: Only operates on new (inserted) rows.
       /// NOTE: Does nothing is the table does not contain an auto-generated
       ///       primary key.
       /// 
       public static class TableAdapterHelper
       {
           public static void PopulateKey(
               OleDbRowUpdatedEventArgs e, 
               OleDbConnection connection, 
               OleDbTransaction transaction)
           {
               // Only look for Add statements
               if (e.StatementType == StatementType.Insert)
               {
                   // Primary key exists?
                   DataColumn[] pk = e.Row.Table.PrimaryKey;
                   if (pk != null && pk.Length > 0)
                   {
                       DataColumn primaryKeyColumn = pk[0];
   
                       // Get the auto-geerated primary key
                       OleDbCommand cmd = new OleDbCommand(
                           "SELECT @@IDENTITY", connection, transaction);
                       int id = (int)cmd.ExecuteScalar();
   
                       // IN the DataTable change the C# default key (-1, -2 etc.) 
                       // into the real key generated by the database
                       e.Row[primaryKeyColumn] = id;
                   }
               }
           }
       }
   }

   
   Then call HookUpHandlers() from wherever the tableadapter is initialized:
   
   C#

   MyTableAdapter _myTableAdapter = new MyTableAdapter();
   _myTableAdapter.HookUpHandlers();

So what does this do? Every time the TableAdapter is used to update a row of the database TableAdapterHelper.PopulateKey is called. If the update is an Insert (Add), then we need to worry about the database generating an ID different from that in the DataTable. We check if a primary key was defined for this table. If so, we execute a nifty little query (SELECT @@IDENTITY) that returns the unique ID last generated by the database. Then we update the DataTable with the correct ID. Easy as pie. Which is an odd expression, given that pie is one of the more difficult pastries to make. But that's OK - my goal is communication, not accuracy! Wait, that's not right. Maybe I'll write an article about this...

So anyway, why does this work? Because the code is stuck right inside the TableAdapter.Update RowUpdated callback - IN THE MIDDLE OF THE TRANSACTION. That's the key. The SELECT @@IDENTITY will only work if called when the transaction is still active, which is why this is so much easier with the command-bundling provided by SQL.

Other fun stuff - what if multiple rows are added before TableAdapter.Update is called? No problem. RowUpdated callback gets fired for each row, one at a time, and everything works fine.

What about multiple tables? This example code is only for one table, it's true. My application has quite a few more tables, and the code got somewhat ugly. I would appreciate any better ideas, but I just added one TableAdapter extension (partial class) per table. And then I called HookUpHandlers for each TableAdapter. Not very extensible, I know. Maybe something nifty with reflection?