Well, here's my challenge, which I'm sure has been run into umpteen times by every developer who's used MS Access. I have auto-number primary keys in all of my tables, and when I add a record, I want to get that Id back. So, in my research I found this solution (from ADO.net Cookbook by Bill Hamilton, O'Reilly),
First, create data adapter
da = new OleDbDataAdapter(sqlSelect, connectionString);
where sqlSelect is the select statement that retrieves rows from your table. Next, add your insert statement and parameters. You then attach an event handler to the RowUpdated event,
da.RowUpdated += new OleDbRowUpdatedEventHandler(OnRowUpdated);
which will fire for each insert. Lastly, add the event handler.
private void OnRowUpdated(object Sender, OleDbRowUpdatedEventArgs args)
{
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();
}
}
So, this looks like a lot of work to me, I cut out a lot of code in the examples above too. I looked at this example and thought "why don't I just call that select @@identity statement after my insert?". So I cut out the event stuff (and I felt like I was losing some automatic protection and handling, but the feeling soon left) and the code seems to work. But I'm still hitting the database twice for an insert!
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.
