I'm sure you are all familiar with code such as the following, to easily convert a bunch of parameter values stored in good old .NET object properties:

using (var sqn = new SqlConnection(ConfigurationManager.ConnectionStrings["ReportStaging"].ConnectionString)) {
    using (var cmd = new SqlCommand(sql, sqn)) {
        sqn.Open();
        cmd.Parameters.AddWithValue("@Category", line.Category);
        cmd.Parameters.AddWithValue("@DailyActual", line.DailyActual);
        cmd.Parameters.AddWithValue("@DailyPlan", line.DailyPlan);
        cmd.ExecuteNonQuery();        
    }
}

A problem arises when a property such as line.DailyActual is set to null. Our friend, the SqlParameterCollection.AddWithValue method inconveniently ignores the well known fact that the .NET null value and the SqlClient DBNull value are worlds apart. Instead of AddWithValue adding a parameter with the value of DBNull.Value when it encounters a null reference for its value parameter, it simply doesn't add a parameter at all, exposing the suggestion of convenience in its name as a dirty, stinking lie. Only when the code above reaches the cmd.ExecuteNonQuery() call is the brain death of AddWithValue exposed, by an exception of type SqlException, bearing the message:

Quote:
The [parameterized query] expects the parameter '@DailyActual', which was not supplied.

Most workarounds for this scenario involve convoluted ternary operator voodoo or excessively verbose if statements, but we can cleanly and easily apply .NET's wonderful extension methods in cases like this. In the following class and extension method, I add an overload to AddWithValue that takes a third parameter, nullValue that specifies what value to create a parameter with if the actual parameter value is null:

public static class SqlParameterCollectionExtensions {
    public static SqlParameter AddWithValue(this SqlParameterCollection target, string parameterName, object value, object nullValue) {
        if (value == null) {
            return target.AddWithValue(parameterName, nullValue ?? DBNull.Value);
        }
        return target.AddWithValue(parameterName, value);
    }
}

Now we insist that our method is called instead of the dodgy Microsoft one, by supplying the third parameter, like this, and it adds all required parameters, with DBNull.Value values when we want to pass nulls:

cmd.Parameters.AddWithValue("@Category", line.Category);
cmd.Parameters.AddWithValue("@DailyActual", line.DailyActual, null);
cmd.Parameters.AddWithValue("@DailyPlan", line.DailyPlan, null);

This is a quick after supper scrawl and by no means a lofty exegisis on this topic. Please feel free to correct me or make suggestions.

Creating null value SqlParameter parameter objects with AddWithValue