How can I send a string as NULL to SQLServer using Dapper?

Question

I've got a scenario where a string in C# can be null. I need it to be NULL on SQLServer.

I'm sending it to SQLServer using Dapper with a query like:

connection.Query<MyObject>("[dbo].[sp_MyStoredProcedure]"), new
{
    StartDate: startDate
}, commandType: CommandType.StoredProcedure);

Where startDate is the string that can sometimes be equal to null.

The stored procedure's parameter is

@StartDate varchar(10) = NULL

When it's is NULL it returns all records. I've confirmed this behavior works via SSMS.

I read this post by Marc Gravell that states:

The null vs DBNull issue is a constant cause of confusion; however, generally if people say null in C# they intend null in SQL. This is the approach that dapper adopts.

This leads me to believe that when the string is set to null, it should send DBNull.Value to SQLServer.

However, this doesn't appear to be the case. I get back 0 records from SQLServer when sending a null string. This seems indicative of sending an empty string, rather than a DBNull.Value.

Also, I can't send DBNull.Value directly:

connection.Query<MyObject>("[dbo].[sp_MyStoredProcedure]"), new
{
    StartDate: DBNull.Value
}, commandType: CommandType.StoredProcedure);

This produces an exception within Dapper:

The member StartDate of type System.DBNull cannot be used as a parameter value

Question

How can I send NULL to SQLServer, using Dapper, when I have a string in C# that can be null?

---

Important

Dapper does indeed send NULL when a string is null. This assumption was a mistake on my part based on faulty information. Nonetheless, this question may serve to help someone else who makes an equally faulty assumption.

Furthermore, the accepted answer provides a good mechanism for dealing with optional or conditional parameters.

Answer 1

Yes, dapper knows to replace reference-null with DBNull.Value whenever it sees it. Because DBNull is my arch -nemesis, and if I never see it again in my application code (library code is different) I will die a little happier.

See also: https://stackoverflow.com/a/9632050/23354

Answer 2

You can choose not to send StartDate.

Example:

dynamic parameters = new {

};

if (!string.IsNullOrWhiteSpace(startDate)) 
{
   parameters.StartDate = startDate;
}

connection.Query<MyObject>("[dbo].[sp_MyStoredProcedure]"), parameters, commandType: CommandType.StoredProcedure);

Edit:

Also, your stored procedure must accept nulls. Here's an example:

CREATE PROCEDURE [ExampleProc]
   @StartDate datetime = null
AS
   Select @StartDate