First, create data adapter
which will fire for each insert. Lastly, add the event handler.
if(args.StatementType == StatementType.Insert)
// Retrieve the identity value
OleDbCommand cmd = new OleDbCommand("SELECT @@IDENTITY", da.SelectCommand.Connection);
// Store the id
args.Row[ID_FIELD_NAME] = (int)cmdExecuteScalar();
In SQL Server I can pass the id in as an out parameter and hit the DB once. My suspicion is that I can't use auto-number ids and have one-hit inserts. My next option is to use the Identity Field pattern (Patterns of Application Enterprise Architecture, Martin Fowler et al. Addison Wesley).
I haven't implemented this solution yet, and I am nervous about taking identity control out of the database and implementing it manually, as I will probably do it wrong and spend my remaining years debugging Ids.
Another option is to use GUIDs as my ids, however I suspect GUIDs cause slow retrievals from Access.
Here are some results from some preliminary tests. I created an MS Access 2000 database with 2 tables. One using a text(64) field to stored GUIDs as the primary key and a text(50) datafield. The other with an autonumber primary key and a text(50) datafield. I created a test to add 1000 records to each table and retrieve a record using the primary key. The timings came out as follows,
|Key Technique||1000 Inserts||Select by key|
|GUID||46922 MSec||62 MSec|
|Autonumber||38469 Msec||16 MSec|
So, the Auto-number technique was faster for inserts and retrieves. I was surprised to see the insert numbers were better since every autonumber insert also comes with an identity retrieve using "SELECT @@IDENTITY". I suppose sorting the text strings is much less efficient, due probably to conversions and compares. The retrive was 4 times faster! So, while the GUID technique is very convenient for development, it is very slow. I suspect I can even improve on the Autonumber technique by using the Identity Field pattern.
Well, I did a test with an Identity Singleton and got about the same result as the GUID tests at 46906 MSecs and the retrieve took 47 MSecs. I can't explain these results, but it looks like the autonumber technique is the way to go.