2.14. Passing Null Values to Parameters
Problem
You need to pass a null value to a parameter.
Solution
Use the
System.DbNull.Value
static value.
The sample code, as shown in Example 2-18, uses a single stored procedure:
SP0214_NullParameter
Accepts a single parameter and a returns one-row result set containing a single value indicating whether that input parameter was null.
Example 2-18. Stored procedure: SP0214_NullParameter
CREATE PROCEDURE SP0214_NullParameter @ValueIn int AS if @ValueIn is null select 1 as IsParameterNull else select 0 as IsParameterNull return 0
The sample code contains two event handlers:
- Go
Button.Click
Creates a stored procedure command for
SP0214_NullParameter
and defines its single input parameter. The input parameter is set toSystem.DbNull.Value
if the user has checked the Null Parameter check box on the form; otherwise it is set to the value that the user has entered in the Parameter text box. The stored procedure is executed using theExecuteScalar( )
method and the value returned indicates whether the input parameter for the stored procedure is null.- Null Parameter
CheckBox.CheckedChange
Clears the value of the Parameter text box if checked and enables the Parameter text box if not checked.
The C# code is shown in Example 2-19.
Example 2-19. File: NullParameterForm.cs
// Namespaces, variables, and constants using System; using System.Configuration; using System.Data; using System.Data.SqlClient; // . . . private void goButton_Click(object sender, System.EventArgs ...
Get ADO.NET Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.