Click here to Skip to main content
14,486,572 members

Creating null value SqlParameter parameter objects with AddWithValue

Rate this:
4.90 (3 votes)
Please Sign up or sign in to vote.
4.90 (3 votes)
7 Jan 2012CPOL
On problems that arise tying to add a null valued SqlParameter using the SqlParameterCollection.AddWithValue method.
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Brady Kelly
Founder Erisia Web Development
South Africa South Africa
I am a software developer in Johannesburg, South Africa. I specialise in C# and ASP.NET MVC, with SQL Server, with special fondness for MVC and jQuery. I have been in this business for about eighteen years, and am currently trying to master Angular 4 and .NET Core, and somehow find a way to strengthen my creative faculties.
- Follow me on Twitter at @bradykelly

Comments and Discussions

 
PraiseThanks so much!! Pin
Member 136303576-Sep-18 5:55
MemberMember 136303576-Sep-18 5:55 
GeneralRe: Oh, good, thanks. (Though I expect some will put that whole ... Pin
PIEBALDconsult6-Jan-12 5:37
professionalPIEBALDconsult6-Jan-12 5:37 
Generalcmd.Parameters.AddWithValue("@Category", line.Category ?? (d... Pin
AlanSB200710-Jan-12 3:15
MemberAlanSB200710-Jan-12 3:15 
GeneralRe: Nice, thank you. However, my code doesn't have to deal with ... Pin
Brady Kelly10-Jan-12 3:42
MemberBrady Kelly10-Jan-12 3:42 
GeneralI've always used the Add(String, SqlDbType) overload of the ... Pin
PSU Steve6-Jan-12 10:14
professionalPSU Steve6-Jan-12 10:14 
GeneralRe: A performance hit has been been brought to my attention by t... Pin
Brady Kelly6-Jan-12 10:44
MemberBrady Kelly6-Jan-12 10:44 
GeneralRe: My opinion is that specifying the type for a Parameter is us... Pin
PIEBALDconsult7-Jan-12 3:45
professionalPIEBALDconsult7-Jan-12 3:45 
GeneralThat's all well and good, but don't keep clearing and recrea... Pin
PIEBALDconsult6-Jan-12 2:17
professionalPIEBALDconsult6-Jan-12 2:17 
GeneralRe: Thanks, that was really a very quick and dirty piece I used ... Pin
Brady Kelly6-Jan-12 2:52
MemberBrady Kelly6-Jan-12 2:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Tip/Trick
Posted 5 Jan 2012

Tagged as

Stats

57K views
3 bookmarked