ADO.NET 3.5 Cookbook, 2nd Edition

3.20. Passing a Null Value to a Query Parameter

Problem

You need to pass a null value to a query parameter.

Solution

Use the System.DbNull.Value static value.

The solution uses a single stored procedure shown in Example 3-27.

Example 3-27. Stored procedure: PassNullParameter

CREATE PROCEDURE PassNullParameter
    @ValueIn int
AS
    IF @ValueIn IS NULL
        SELECT 1 AS IsParameterNull
    ELSE
        SELECT 0 AS IsParameterNull
    RETURN 0

The stored procedure accepts a single parameter and returns a one-row result set containing a single value indicating whether that input parameter was null.

The solution creates a stored procedure command for the stored procedure PassNullParameter and defines the single input parameter. The input parameter is set to both null and System.DbNull.Value. The stored procedure is executed in each case using the ExecuteScalar( ) method. In each case, output to the console indicates whether the input parameter to the stored procedure is null.

The C# code in Program.cs in the project PassNullValueToStoredProcedureParameter is shown in Example 3-28.

Example 3-28. File: Program.cs for PassNullValueToStoredProcedureParameter solution

using System; using System.Data; using System.Data.SqlClient; namespace PassNullValueToStoredProcedureParameter { class Program { static void Main(string[] args) { // Create the connection string sqlConnectString = "Data Source=(local);" + "Integrated security=SSPI;Initial Catalog=AdventureWorks;"; bool isNullParameter; using (SqlConnection connection = new SqlConnection(sqlConnectString)) ...