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.