As most people are aware the .NET 2.0 framework supports nullable value types. There are many articles on this topic and a few that address the issues of using nullable types in combination with your ado.net code. However, most of these discuss the issue of using nullable types in combination with the DbDataReader objects, though few address the conflicts that arise when using a nullable type to set or get an ado.net parameter value.
Prior to .net 2.0 you'd run into this issue when attempting to pass a null string to the value of an input parameter. In this case most of us would have written conditional code that looked something like this:
string firstName = null;
if(firstName == null) {
Command.Parameters["FirstName"].Value = DBNull.Value;
} else {
Command.Parameters["FirstName"].Value = firstName;
}
Or you could have used a ternary operation:
string firstName = null;
Command.Parameters["FirstName"].Value = firstName == null ? (object)DBNull.Value : firstName;
With the inception of nullable types you now have to perform the same logic for all parameters that will use a nullable type to set its value. This isn't a huge deal really, though it will start to get a bit tedious with stored procedures that have a lot of parameters to assign and downright egregious over the lifecycle of your project. Fortunately there is a little known (and used) operator that we can use to solve this issue cleanly; the null coalescing operator "??". This operator specifies that if the argument on the left evaluates to null then the argument on the right will be substituted.
int? age = null;
Command.Parameters["Age"].Value = age ?? (object)DBNull.Value;
This code is far more readable than the previous conditional statement. Alternatively you could write a simple method that takes the parameter and the value that you want to assign to it and have the value set using the conditional method above (reusability, huzzah!), but that would either involve an inline method call or a loop over the parameters collection. These approaches while certainly valid aren't nearly as compelling as using the coalescing operator.Unfortunately vb.net programmers don't have a comparable operator and so they'll have to resort to either the conditional, the ternary or the inline method techniques.
Dim age As Integer? = Nothing
Command.Parameters("Age").Value = IIf(age Is Nothing, DirectCast(DBNull.Value, Object), age)
So far I've explained how to handle input parameters, but whats the best way to handle output parameters? Working from my previous post on system.nullable vs. tryparse we can use the new NullableParser static object and its TryParse methods to set the value of a nullable variable properly from a stored procedure output parameter.
int? age = null;
NullableParser.TryParse(Command.Parameters["Age"].Value.ToString(), out age, true);
Asside from being easy to read and support, this code provides a robust and efficient method for setting the nullable age variable to the value of the Age output parameter using the familiar T.TryParse(string s, out T result) syntax.