Dapper NullReferenceException

c# dapper sql

Question

I'm getting a NullReferenceException when I execute this code:

var insertTransaction = @"INSERT INTO [192.168.1.55].databaseName.dbo.tableName
(Date, Desc) 
VALUES(GETDATE(), 
@Description)
SELECT Scope_identity()";

var result = _sqlMapper.Query<int>(insertTransaction,
             new
             {                           
                 Description = "some description"
             });

where _sqlMapper is an instance of Dapper.SqlMapper

If I remove SELECT Scope_identity() I don't get the exception.

The exception stack trace says the exception is thrown here:

at Dapper.SqlMapper.d__11`1.MoveNext() in d:\Dev\dapper-dot-net\Dapper NET40\SqlMapper.cs:line 1583

  1. Why does SELECT Scope_identity() create a null object and how can I fix it?
  2. Why does the stack trace display a file path which does not exist on my local machine?

UPDATE: Dapper version = 1.40.0.0, Runtime Version = v4.0.30319 DLL = C:\src\packages\Dapper.1.40\lib\net45\Dapper.dll

UPDATE: If I execute the query in Management Studio, the row gets inserted however the scope_identity returned is null.

Accepted Answer

Scope_identity will not work on remote/linked servers. See the following answer for a solution: Best way to get identity of inserted row in Linked server?

My favorite method:

 SELECT *
 FROM OPENQUERY(server, '
     INSERT INTO database.schema.table (columns) VALUES (values);
     SELECT SCOPE_IDENTITY() AS ID');

Also note that SCOPE_IDENTITY() gives a decimal not a int. And Dapper.SqlMapper.Query will return an IEnumerable, there is an ExecuteScalar methode since 1.28.

Remember to upvote the answer in the link if this helped you.


Popular Answer

you get the exception using dapper and Management Studio that because both are in the same scope. when you SELECT SCOPE_IDENTITY() , there are two statement that in same scope, therefore you always get NullReferenceException

if you separate the SQL statement with the semi-colon, and it should work

var insertTransaction = @"INSERT INTO [192.168.1.55].databaseName.dbo.tableName
(Date, Desc) 
VALUES(GETDATE(), 
@Description);
SELECT Scope_identity()";


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why