Dapper getting “Specified cast is not valid.” for

问题:

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.

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

回答2:

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.