CODEWITHSUNDEEP
sql-serverservicestackormlite-servicestack
user2661045
user2661045

Reputation: 1

Bug in ServiceStack.OrmLite.SqlServer and GetLastInsertId when using InsertParam?

To exemplify the problem, I have a simple table with a PK that is AUTOINCREMENT. When I use the Insert, GetLastInsertId works as it should, ie returns the key value of the inserted row, but not when I use InsertParam. Then the key value is 0. When I look in the database, everything looks good.

My installed version is https://www.nuget.org/packages/ServiceStack.OrmLite.SqlServer/3.9.56.

Example code

public class Foo
{
   public Foo() {}

   [AutoIncrement]
   public int Id { get; set; }

   public string SomeText { get; set; }
}

Insert - GetLastInsertId is working correct

using (IDbConnection db = dbFactory.OpenDbConnection())
{
    db.Insert(new Foo { SomeText = "Bla bla" } );
    string sql = db.GetLastSql();      
    // sql = "INSERT INTO \"Foo\" (\"SomeText\") VALUES (N'Bla bla')"

    int id = (int)db.GetLastInsertId();
    // id = 1
    sql = db.GetLastSql();
    // sql = SELECT SCOPE_IDENTITY()
}

InsertParam - GetLastInsertId is NOT working correct (always 0!!!)

using (IDbConnection db = dbFactory.OpenDbConnection())
{
    db.InsertParam(new Foo { SomeText = "Bla bla" } );
    string sql = db.GetLastSql();
    // sql = "INSERT INTO \"Foo\" (\"SomeText\") VALUES (@SomeText)"

    int id = (int)db.GetLastInsertId();
    // id = 0 (always 0)
    sql = db.GetLastSql();
    // sql = "SELECT SCOPE_IDENTITY()"
}

Has anyone else seen the same problem with ORMLite and SQL Server?

When I view the source code for ORMLite and SQL server and project ServiceStack.OrmLite.SqlServerTests I see the class InsertParam_GetLastInsertId with test case Can_GetLastInsertedId_using_InsertParam. Should give an assert, I think?

I've just verified the error by running NUnit and I got the expected error on line 31 

ServiceStack.OrmLite.SqlServerTests.
InsertParam_GetLastInsertId.
Can_GetLastInsertedId_using_InsertParam:
   with InsertParam
   Expected: greater than 0
   But was:  0

Upvotes: 0

Views: 639

Answers (1)

Wim
Wim

Reputation: 1985

I have investigated this failed unit test and it is related to this question Why SCOPE_IDENTITY returns NULL?. I have made a pull request on OrmLite to fix this. I will keep you informed if it gets accepted.

Demis Bellot altered my solution and merged a fix in the main branch. InsertParam can now directly return the lastinsertedid. 

public static long InsertParam<T>(this IDbConnection dbConn, T obj, bool selectIdentity = false)

This is now available in v3.9.58

Upvotes: 3

Related Questions