After upgrade from 4.0.60 to 4.5.8 no longer possible to retrieve SCOPE_IDENTITY() after InsertOnly

ServiceStack.OrmLite
1

Just upgraded from 4.0.60 to 4.5.8

The following code worked in 4.0.60, but nog longer in 4.5.8

 db.InsertOnly(contactEntity, diffs.ToArray());
          var command = db.CreateCommand();
          command.Transaction = trans.ToDbTransaction();
          var idContactInserted = Convert.ToInt32(db.GetDialectProvider().GetLastInsertId(command));

Reason:
4.0.60 would generate the following statements:

INSERT INTO "Contact" ("IDCampagne",LastName)
VALUES (224,'Smith', '3002240593176')

SELECT SCOPE_IDENTITY()

4.5.8 generates the following statements:

exec sp_executesql N'INSERT INTO "Contact" ("IDCampagne","LastName")
VALUES (@IDCampagne,@IDActContact)',N'@IDCampagne int,@LastName varchar(200)', 
@IDCampagne=224,@LastName='Smith' go
SELECT SCOPE_IDENTITY()

In the last statement “SELECT SCOPE_IDENTITY()” returns Null.

I would like to be able to select the last inserted ID.

2

OrmLite has been changed to use parameterized queries which need to be retrieved in the same query, e.g:

var idContactInserted = db.Insert(contactEntity, selectIdentity:true);
3

For db.Insert there is no problem. I am talking about db.InsertOnly

We want to use InsertOnly because we have a legacy table with 200+ fields and we are only inserting 50 or so fields

4

I’ve just added selectIdentity option to all InsertOnly APIs in this commit.

This change is available from v4.5.9 that’s now available on MyGet.

2 Likes