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.

It's an int. And before you say anything, remember, I can do this just fine in ADO with no exceptions. — , Jun 19 '12 at 18:15

score 92 · Accepted Answer · answered Jun 19 '12 at 18:16

92

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.

answered Jun 19 '12 at 18:16

driis

161,458
45
265
341

1

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. – Barry Dysert Jul 25 '13 at 20:13
4

@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. – Joe Oct 14 '13 at 14:27
@Joe This is an old one and I may be wrong but (string)null ends as an empty string not a null value – Nov 05 '20 at 13:44
In my testing using SQL Profiler `(string)null` was translated to `null` and not empty string. Dapper 2.0.78. – Ben Osborne Feb 16 '21 at 21:56

How to pass a null parameter with Dapper

1 Answers1

Linked