C# – Problem inserting string or NULL into SQL Server database


I'm having trouble coming up with the proper syntax for allowing either a string or a NULL to be passed to the database. Here's my code:

string insertString = String.Format(
    @"INSERT INTO upload_history (field1, field2, field3) 
    VALUES ('{0}', '{1}', '{2}')",
    varField1, varField2, varField3);

I used single quotes around the variable placeholders so that the database would properly accept a string value. However, if NULL is passed, it ends up going into the database as the string "NULL".

Is there a way I can leave the single quotes out of the InsertCommand string and conditionally add single quotes to my variables?

Best Solution

Don't concatenate the string (string.Format) - use parameters (@p1 etc) - then you can pass DBNull.Value to mean null to SQL Server

SqlCommand cmd = new SqlCommand();
cmd.CommandText = @"INSERT INTO upload_history (field1, field2, field3) 
   VALUES (@p1, @p2, @p3)";
cmd.Parameters.AddWithValue("@p1", (object)someVar ?? DBNull.Value);

This also protects you from SQL injection