I recently ran into an interesting problem. I had created a data access method that used generic typing to execute a scalar SQL Server stored procedure (below), and in the stored procedure was returning SCOPE_IDENTITY() for an integer identity column. I was passing in a type of "int" to the method (as <T>) since that's what I was expecting back, but I kept getting back null. After some frustration and bewilderment I discovered that SCOPE_IDENTITY() always returns a decimal, and C# doesn't want to convert the decimal to an integer automatically. I worked around it by using CAST in the stored procedure so that I didn't have to modify the method.
private static T? ExecuteScalarStoredProcedure (string sprocName, SqlParameter[] parameters) where T : struct {
SqlConnection conn = new SqlConnection(GetConnectionString());
conn.Open();
object retValue;
try {
SqlCommand command = new SqlCommand(sprocName, conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(parameters);
retValue = command.ExecuteScalar();
}
finally {
try {
conn.Close();
}
catch (Exception) {
//Ignore close exception
}
}
return retValue as T?;
}