My SQLParameter isn't passing NULL correctly

Question

I have this C# webform that has has a date picker box. If the date is set to nothing (the default) I want it to pass NULL to the database. This happens inside my parametrized query.

SqlParameter CMActionDate = new SqlParameter();
CMActionDate.ParameterName = "@ActionDate";
if (ActionDate.Equals(""))
  {
      CMActionDate.Value = System.Data.SqlTypes.SqlDateTime.Null;
  }
  else
  {
      CMActionDate.Value = ActionDate;
  }

When I turn on debugging I see that the date is indeed "" so it goes into the IF statement and sets the actiondate.value to {Null} like I think it should.

However.

When it then goes to execute the nonquery, I click the magnifying glass and see this:

UPDATE table SET [action_date] = '' WHERE [id] = 2488

What I would like to see is this:

UPDATE table SET [action_date] = 'Null' WHERE [id] = 2488

Since the action_date never really gets set to NULL, then the value in the datetime field reverts to "01/01/1900 12:00:00AM" and that's a pain in itself.

I have tried setting CMActionDate.Value to the following values to no avail (I get the same result as above.):

• DBNull.Value;
• "NULL";
• SqlDateTime.Null;
• null;

Yes, of course the parametrized query looks like this:

"UPDATE CM_Codebase SET [action_date] = '" + @ActionDate + "' WHERE [id] = " + @CM_id + "";

But when I am debugging this thing in VS, I put a breakpoint right before ExecuteNonQuery(); so I can see the SQL it's trying to run. It's there that I see the actual SQL and see the bit where action_date=''.

Answer 1

You shouldn't see either '' or 'Null'. If you're using parameterized queries correctly it should look like this:

UPDATE table SET [action_date] = @ActionDate WHERE [id] = @ID

The whole point of a parameterized query is that the actual parameter value is never substituted directly into the query string.

Your query code should look something like this:

string sql = "UPDATE table SET [action_date]= @ActionDate WHERE [id]= @CM_id";

using (var cn = new SqlConnection("your connection string here."))
using (var cmd = new SqlCommand(sql, cn))
{
    cmd.Parameters.Add("@ActionDate", SqlDbTypes.DateTime).Value = 
         ActionDate.Equals("")? DBNull.Value : DateTime.Parse(ActionDate);
    cmd.Parameters.Add("@CM_id", SqlDbTypes.Int).Value = 2488;

    cn.Open();
    cmd.ExecuteNonQuery();
}

The result of this code is that your query parameters are sent to the server as data. At no point in your C# code will you ever be able to view the query string with your data substituted in: it's sent to the server separately.

This prevents any possibility of the server executing a parameter value as code because of an error in sanitizing your parameter value. The data is completely separate, and doesn't need to be sanitized for that context in the first place. It also allows the server to cache and reuse the execution plan for the query, resulting in a (small) performance boost.