Dapper getting "Specified cast is not valid." for ReturnValue parameter value

c# dapper stored-procedures

Question

I am trying to use SCOPE_IDENTITY to return a long primary key back to c# using the ReturnValue option for DynamicParameter.

Here is sample code from the Dapper website:

var p = new DynamicParameters();
p.Add("@a", 11);
p.Add("@b", dbType: DbType.Int32, direction: ParameterDirection.Output);
p.Add("@c", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

cnn.Execute("spMagicProc", p, commandType: commandType.StoredProcedure); 

int b = p.Get<int>("@b");
int c = p.Get<int>("@c");

Instead of returning int, I would prefer to do the following as my primary key field should be bigint

var p = new DynamicParameters();
p.Add("@a", 11);
p.Add("@b", dbType: DbType.Int32, direction: ParameterDirection.Output);
p.Add("@c", dbType: DbType.Int64, direction: ParameterDirection.ReturnValue);

cnn.Execute("spMagicProc", p, commandType: commandType.StoredProcedure); 

int b = p.Get<int>("@b");
int c = p.Get<long>("@c");

In my proc, I am using "RETURN SCOPE_IDENTITY()".

However doing this seems to result in a "Specified cast is not valid." exception.

Accepted Answer

The return value from a stored procedure is always implicitly an integer, i.e. int. As such, you can only treat it as an integer. If you try to unbox it as a long, it will fail.

Options:

  • if the value will fit, just treat it as int in the .NET side
  • otherwise use an out parameter of type bigint, and treat it as long on the .NET side
  • or use select and Query<long>(...).Single()

Popular Answer

If i remember correctly, SCOPE_IDENTITY() returns a decimal (http://msdn.microsoft.com/en-us/library/ms190315.aspx), hence the invalid cast exception.

You need to cast it to bigint (in the SQL) for it to work as you want it.




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