Dapper NullReferenceException

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

Why does SELECT Scope_identity() create a null object and how can I fix it?
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.

Can you show us the line 1583 of your dapper source? I just tried the exact same query and it worked for me. Are you on the latest Dapper release? — Rob, Sep 25 '15 at 04:57
Do you get better luck using `.Query` instead? (Which is apparently what `Scope_identity` is returning to Dapper, after debugging a bit) — Rob, Sep 25 '15 at 05:04
I tried using .Query but it didn't work. I updated the question with my dapper version. I'll have to decompile the dll to see line 1583. — David Klempfner, Sep 25 '15 at 05:17
@Backwards_Dave I've rolled back further than 1.40 locally, and I can still run your query with an issue. What database are you using? Are you able to execute that query directly against the DB without dapper? What value does it give for `Scope_Identity`? — Rob, Sep 25 '15 at 05:21
Failing that, I'd suggest cloning the Dapper source: `https://github.com/StackExchange/dapper-dot-net` and using this instead of the NuGet packager. This way we'd be able to get a better idea about *why* it's failing in your particular setup. — Rob, Sep 25 '15 at 05:22
@Backwards_Dave Wait.. are you sure your table's primary key is defined as an `IDENTITY`? And that there's actually a primary key (and is it an integer?) That's the only thing I can think of causing `Scope_Identity` to return `null`. What happens when you use `@@IDENTITY` ? — Rob, Sep 25 '15 at 05:30
possible duplicate of [Best way to get identity of inserted row in Linked server?](http://stackoverflow.com/questions/5708996/best-way-to-get-identity-of-inserted-row-in-linked-server) — Lukasz Szozda, Sep 25 '15 at 06:55
@lad2025 I would say, no. Since he did not know that the problem was caused by the linked server. His question was how to solve the null pointer when using dapper. — Edwin Stoteler, Sep 25 '15 at 10:21
Possible duplicate of [What is a NullReferenceException and how do I fix it?](http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) — Nasreddine, Oct 04 '15 at 09:08

score 6 · Accepted Answer · edited May 25 '23 at 16:01

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.

score 0 · Answer 2 · answered Sep 25 '15 at 07:55

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()";

score 0 · Answer 3 · answered Apr 22 '19 at 18:53

You might get a NullReferenceException in case your connection string is null or empty.

It was happening to me, because after an await call the connection string was being cleared before I reuse the same connection used before the await. So in this case I had to create another connection right on the point where I needed to query the database.

Dapper NullReferenceException

3 Answers3