Monday, January 15, 2018

Passing Nullable Value for a DbCommand Parameter

I had this requirement where I wanted to pass a nullable property for a Parameter in DbCommand.
public void Execute(int? someId)
{
    using (DbCommand dbCommand = _context.Database.GetDbConnection().CreateCommand())
    {
        dbCommand.CommandType = CommandType.StoredProcedure;
        dbCommand.CommandText = "sp_SomeStoredProcedure";
        dbCommand.Parameters.Add(new SqlParameter("ParameterId", someId);

        // some code
    }

    // some code
}
I was expecting when someId is null, ADO.NET will consider passing null for the parameter. But apparently, that doesn't seem to be the case. Got required parameter is not supplied error. I even tried below which I felt would work,
dbCommand.Parameters.Add(new SqlParameter("ParameterId", someId.HasValue ? someId.Value : null));
But kept getting the error. Finally, Null coalescing operator with DBNull was there to my rescue.
dbCommand.Parameters.Add(new SqlParameter("ParameterId", someId ?? (object)DBNull.Value));
Happy Coding.

Regards,
Jaliya

No comments:

Post a Comment