How to pass a null parameter with Dapper

Question

I have a stored procedure that has a parameter with no default value, but it can be null. But I can't figure out how to pass null with Dapper. I can do it just fine in ADO.

connection.Execute("spLMS_UpdateLMSLCarrier", new { **RouteId = DBNull.Value**, CarrierId = carrierID, UserId = userID, TotalRouteRateCarrierId = totalRouteRateCarrierId },
                                        commandType: CommandType.StoredProcedure);

Exception:

System.NotSupportedException was caught
  Message=The member RouteId of type System.DBNull cannot be used as a parameter value
  Source=Dapper
  StackTrace:
       at Dapper.SqlMapper.LookupDbType(Type type, String name) in C:\Dev\dapper-git\Dapper\SqlMapper.cs:line 348
       at Dapper.SqlMapper.CreateParamInfoGenerator(Identity identity) in C:\Dev\dapper-git\Dapper\SqlMapper.cs:line 1251
       at Dapper.SqlMapper.GetCacheInfo(Identity identity) in C:\Dev\dapper-git\Dapper\SqlMapper.cs:line 908
       at Dapper.SqlMapper.Execute(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable`1 commandTimeout, Nullable`1 commandType) in C:\Dev\dapper-git\Dapper\SqlMapper.cs:line 532
       at Rating.Domain.Services.OrderRatingQueueService.UpdateLMSLCarrier(Int32 totalRouteRateCarrierId, Int32 carrierID, Int32 userID) in C:\DevProjects\Component\Main\Source\Rating\Source\Rating.Domain\Services\OrderRatingQueueService.cs:line 52
       at Benchmarking.Program.OrderRatingQueue() in C:\DevProjects\Component\Main\Source\Benchmarking\Source\Benchmarking\Program.cs:line 81
       at Benchmarking.Program.Main(String[] args) in C:\DevProjects\Component\Main\Source\Benchmarking\Source\Benchmarking\Program.cs:line 28
  InnerException: 

Can't leave the RouteId off, gives me an error that says it's required. Can't use regular null either gives me another error. Can't change the stored procedure, it does not belong to me.

Secure

It's an int. And before you say anything, remember, I can do this just fine in ADO with no exceptions. – Secure

Answer 1

I think you should be able to do it by casting null to the appropiate type. Let's assume RouteId is an integer:

connection.Execute("spLMS_UpdateLMSLCarrier", new { RouteId = (int?)null, CarrierId = carrierID, UserId = userID, TotalRouteRateCarrierId = totalRouteRateCarrierId }, commandType: CommandType.StoredProcedure);

The problem you are encountering when using regular null is likely that the compiler cannot infer the type of RouteId in the anonymous type when just using null without the cast.

Smug

I'm trying to do the same thing, but my underlying type is String. I get a compiler error when I try (String?)null. What's the answer to this one? Thanks. – Vanover

@BarryDysert - It would be (string)null - string is a reference type, so doesn't need or support the ? suffix, which is used to indicate a nullable value type. – Strategy

@Strategy This is an old one and I may be wrong but (string)null ends as an empty string not a null value – Mandell

In my testing using SQL Profiler (string)null was translated to null and not empty string. Dapper 2.0.78. – Widely

Answer 2

I have a slightly different approach using a parameter container, which in my opinion is cleaner and more practical than the accepted solution. Here is the code I use to provide NULL values for nvarchar, int and guid columns:

var exampleSql = "UPDATE TableName SET SomeColumn = @paramName1, SomeOtherColumn = @paramName2 WHERE SomeCondition";

DynamicParameters parameters = new DynamicParameters();
parameters.Add(name: "paramName1", dbType: DbType.String, value: DBNull.Value); // for int and nvarchar columns. yes, DbType.String does work for int columns too.
parameters.Add(name: "paramName2", dbType: DbType.Guid, value: DBNull.Value); // if it was a guid column, you would have to set DbType accordingly like this.

// then, execute query with the parameters
connection.Open();
int rowsAffected = connection.Execute(exampleSql, parameters);
connection.Close();

One slight annoyance is that you have to declare the DbType for null parameters. This is exactly why you got that error, you did not specify DbType.

Cowey