Raw SQL insert statement with optional parameter

Question

I'm trying to execute this command but get an exception:

cmd.CommandText = @"insert into Foo (Column1, Column2) 
                    values (@Parameter1, @Parameter2)";

cmd.Parameters.Add(new SqlParameter("@Parameter1", 'bar'));
cmd.Parameters.Add(new SqlParameter("@Parameter2", null));

The exception states that @Parameter2 is expected but was not supplied.

Actually I'm providing it with null value. So how can I insert this very null into nullable Column2?

Answer 1

If you want to provide a NULL to the underlying database, use DBNull.Value:

cmd.Parameters.Add("@Parameter2", SqlDbType.Int).Value = DBNull.Value;

Adapt to whatever datatype your parameter really is (I'm just guessing here, since you didn't tell us - and I prefer to always explicitly tell the parameter what datatype it is)

Answer 2

Use DBNull.Value to represent a null value to the database:

cmd.CommandText = @"insert into Foo (Column1, Column2) 
                    values (@Parameter1, @Parameter2)";

cmd.Parameters.Add(new SqlParameter("@Parameter1", 'bar'));
cmd.Parameters.Add(new SqlParameter("@Parameter2", DBNull.Value));