Dapper + MSAccess: How to get identifier of inserted row

Question

I am using Dapper with C# and back end is MS Access. My DAL method inserts record in database. I want to return unique identifier (or updated POCO with unique identifier) of the inserted row. I am expecting my function something like follows (I know this does not work; just to explain what I want): -

public MyPoco Insert(MyPoco myPoco)
{
    sql = @"INSERT INTO MyTable (Field1, Field2) VALUES (@Field1, @Field2)";
    var param = GetMappedParams(myPoco);//ID property here is null.
    var result = _connection.Query<MyPoco>(sql, param, null, false, null, CommandType.Text);.Single();
    return result;//This result now contains ID that is created by database.
}

I am from NHibernate world and POCO updates automatically with NH. If not; we can call Refresh method and it updates the ID. I am not aware how to achieve this with Dapper.

I read this question on SO which is not relevant as it talks about SQL Server.

Another this question does not have accepted answer.

I read this question where accepted answer explains pit-falls of using @@Identity.

I think it`s your answer http://stackoverflow.com/questions/2230719/scope-identity-in-microsoft-access — progpow, May 23 '16 at 13:11
@progpow: You are suggesting **SELECT @@Identity**. I am not sure if this is reliable. My application is multi-user application where multiple users will insert records in same table at same time. In my understanding, this is not recommended. Correct me if I am wrong. — Amit Joshi, May 23 '16 at 13:19
You can test, I wrote an answer on this but cannot find it. Use an isolated instance (set db=currentdb), pause before the sql and insert a record manually. I think you will find that it runs true. — Fionnuala, May 23 '16 at 13:33
The Access Database Engine does in fact handle `SELECT @@IDENTITY` correctly for multiple concurrent users. It returns the last created AutoNumber value *for that particular connection*. What it doesn't handle is the case where an event-driven Data Macro performs another insert that also creates an AutoNumber value. Unfortunately, Access SQL doesn't (currently) have an equivalent to `SCOPE_IDENTITY`. — Gord Thompson, May 23 '16 at 13:42
Sorry; that was my mistake. `@@Identity` was always returning zero because I was never opening (and closing) connection explicitly. In that case, Dapper opens (and closes) connection internally. As new connection was being opened for executing `@@Identity` query, it was returning zero. — Amit Joshi, May 24 '16 at 08:16
@GordThompson when you say "an event-driven Data Macro performs another insert" does that specifically refer only to inserts emanating from macros built by the macro building bits of Access, i.e. not inserts triggered from VBA code or inserts triggered by a databound Access form? In other words, does your comment mean "it's safe if you don't have macros"? — tomRedox, Mar 16 '17 at 18:09
@tomRedox - It refers specifically to [event-driven Data Macros](https://support.office.com/en-us/article/Create-a-data-macro-b1b94bca-4f17-47ad-a66d-f296ef834200) which are a special kind of macro that behave like a [trigger](https://en.wikipedia.org/wiki/Database_trigger). Other inserts, such as those performed from VBA code or bound forms, are unlikely to cause problems with using `SELECT @@IDENTITY`. — Gord Thompson, Mar 17 '17 at 16:51

Gord Thompson · Accepted Answer · 2016-05-24T12:05:42.780

This is what works for me:

static MyPoco Insert(MyPoco myPoco)
{
    string sql = "INSERT INTO MyTable (Field1, Field2) VALUES (@Field1, @Field2)";
    _connection.Execute(sql, new {myPoco.Field1, myPoco.Field2});
    myPoco.ID = _connection.Query<int>("SELECT @@IDENTITY").Single();
    return myPoco;  // This result now contains ID that is created by database.
}

Note that this will work with an OleDbConnection to the Access database, but it will not work with an OdbcConnection.

Edit re: comment

To ensure that the Connection remains open between the INSERT and the SELECT calls, we could do this:

static void Insert(MyPoco myPoco)
{
    string sql = "INSERT INTO MyTable (Field1, Field2) VALUES (@Field1, @Field2)";
    bool connAlreadyOpen = (_connection.State == System.Data.ConnectionState.Open);
    if (!connAlreadyOpen)
    {
        _connection.Open();
    }
    _connection.Execute(sql, new {myPoco.Field1, myPoco.Field2});
    myPoco.ID = _connection.Query<int>("SELECT @@IDENTITY").Single();
    if (!connAlreadyOpen)
    {
        _connection.Close();
    }
    return;  // (myPoco now contains ID that is created by database.)
}

This works as long as `_connection` is opened explicitly **BEFORE** `INSERT INTO` and closed **AFTER** `SELECT @@IDENTITY` call. I was not handling the connection properly. Also, one must understand pit-falls of using `@@Identity`; I have provided link in my question. — Amit Joshi, May 24 '16 at 08:39

score 0 · Answer 2 · edited May 23 '17 at 12:32

Just a couple of extra thoughts: If the @@Identity pitfalls are an issue then another option would be to create a new GUID ahead of time in code and then insert that GUID with the rest of the data, rather than letting Access create the identity value when it creates the new record.

I appreciate that will only work if your particular situation allows for a GUID primary key for the table, but it does guarantee you that you know the true value of the key for the record you just inserted.

Alternatively, if you don't want a GUID key you could create a table with a single row that holds the current seed value for any manually managed keys in your application. You can then manually increment the particular seed's value each time you want to insert a new record. As with the GUID approach, you'd then manually insert the ID with the record, this time the ID would be the newly incremented seed you just retrieved.

Again, that should guarantee you a unique key for each insert, although now you are doing a read and two writes for each insert.

Guid is good alternative but many will move away due to its size and performance penelty. Its primary objective was to make data replication complaint. May be that is why Microsoft named it `Autonumber` - `Replication ID`. I guess there are very less developers using this data type with MS Access. — Amit Joshi, Jan 13 '17 at 04:33
Other alternative you suggested (to generate id in code) looks better. Drawback as you said is additional database calls. Those could be easily avoided using algorithm that does not use DB. — Amit Joshi, Jan 13 '17 at 04:35

Dapper + MSAccess: How to get identifier of inserted row

2 Answers2

Linked