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

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.

Answer 1

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