Regardless of how your UI works, it's up to you to decide how to set the parameter value passed to the query processor. For example, there are a couple of options for the UI in Figure 2:
- Option 1: Set the parameter value to NULL. Test for a checked state in the Not Shipped Yet check box. If that check box is selected, set the @DateWanted parameter value to DBNull.Value or Nothing in Visual Basic.NET code or null in C# code. Otherwise, use the date in the Date Shipped input box for the @DateWanted parameter value. The code in Listing 3 shows the use of both DBNull.Value and Nothing. Both lines produce the same result.
Listing 3: Setting the Parameter Value to NULL |
 |
- Option 2: Bypass the creation of the parameter. Test for a checked state in the Not Shipped Yet check box. If that check box is selected, bypass the creation of the parameter for the SqlCommand object. Otherwise, use the date in the Date Shipped input box for the @DateWanted parameter value. The code in Listing 4 demonstrates this approach. This code depends on having the default parameter value set in the stored procedure definition. Although coupling your UI to the database doesn't follow best practices, this option is easy to understand and manage.
Listing 4: Bypassing Parameter Creation |
 |
Note that not all of the new SQL Server 2008 data types are exposed in Visual Studio 2008 SP1. Although I could see the parameter value types in an enumerated list, when I tried to use the Type.parse method, only the old types were available. Fortunately, the Visual Studio 2008 SP1 development tools knew how to enumerate the new SqlDbType data types.
Handling the Unknown
There are many situations in which an input parameter value might not be provided. In such cases, you have to be careful when including a value that essentially says "we don't know what the value is." As you've seen here, it's possible to handle NULL parameters in several ways. Given that many stored procedures have dozens of input parameters, it's handy to know how you can invoke them without having to set a value for each and every parameter.