C# How to check if null value exists in database table (using stored procedure)?

[qwedster]

Folk!

How to programattically check if null value exists in database table (using stored procedure)?

I know it's possble in the Query Analyzer (see last SQL query batch statements)?

But how can I pass null value as parameter to the database stored procedure programattically using C#?

Although I can check for empty column (the following code passes string.Empty as parameter but how to pass null value?), I cannot check for null value in the following code snippet:

SQL Queries:

USE [master]
GO
 
IF EXISTS (SELECT name FROM sys.databases WHERE name = 'ExampleDatabase') 
DROP DATABASE [ExampleDatabase]; 
GO
 
CREATE DATABASE [ExampleDatabase]; 
GO
 
USE [ExampleDatabase]; 
GO
 
IF EXISTS (SELECT NAME FROM SYS.TABLES WHERE NAME = 'ExampleTable') 
DROP TABLE dbo.ExampleTable; 
GO
 
CREATE TABLE 
dbo.ExampleTable
(
ID INT IDENTITY(1,1) NOT NULL, 
UserID INT NULL, 
Name NVARCHAR(50) NULL, 
DateOfBirth DATETIME, 
IsActive BIT, 
Phone NVARCHAR(50) NULL,
Fax NVARCHAR(50) NULL,
CONSTRAINT PK_ID PRIMARY KEY(ID), 
CONSTRAINT UNIQUE_Phone UNIQUE(Phone), 
CONSTRAINT FK_UserID FOREIGN KEY(UserID) REFERENCES ExampleTable(ID),
CONSTRAINT FK_Fax FOREIGN KEY(Fax) REFERENCES ExampleTable(Phone)
);
GO
 
INSERT INTO dbo.ExampleTable
(
UserID, 
Name, 
DateOfBirth, 
IsActive, 
Phone,
Fax
)
VALUES
(
1,
'Bill',
'12-31-2000',
'False',
'12345678',
'12345678'
);
GO
 
INSERT INTO dbo.ExampleTable
(
UserID, 
Name, 
DateOfBirth, 
IsActive
)
VALUES
(
2,
'Larry',
'12-31-2005',
'True'
);
GO
 
SELECT * FROM ExampleTable;
GO
 
 
IF EXISTS(SELECT NAME FROM SYS.PROCEDURES WHERE NAME = N'CheckForeignKeyFax') 
DROP PROCEDURE dbo.CheckForeignKeyFax;
GO
 
CREATE PROCEDURE dbo.CheckForeignKeyFax
(
@Fax NVARCHAR(50)
)
AS
DECLARE @ResultFax INT
IF EXISTS
(
SELECT
NULL
FROM
dbo.ExampleTable WITH (UPDLOCK) 
WHERE
ISNULL(Phone, 'NULL') = ISNULL(@Fax, 'NULL')     
--(Phone IS NULL AND @Fax IS NULL) OR (@Fax = Phone) 
) 
BEGIN SELECT @ResultFax = 0 END
ELSE BEGIN SELECT @ResultFax = -1 END
RETURN @ResultFax
GO
 
DECLARE @ReturnValue INT
EXEC @ReturnValue = CheckForeignKeyFax @Fax = '12345678'
SELECT ReturnValue=@ReturnValue;
GO
 
DECLARE @ReturnValue INT
EXEC @ReturnValue = CheckForeignKeyFax @Fax = NULL
SELECT ReturnValue=@ReturnValue;
GO
 

C# Code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
 
using System.Data.SqlClient;
 
namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
 
        private string ConnectionString
        {
            get
            {
                return @"Persist Security Info=False;Data Source=.\SQLEXPRESS;User ID=sa;Password=asdfglkjh;Initial Catalog=master;";                               
            }
        }
 
        private int CheckFax(string fax)
        {
            int Result = -1;
 
            try
            {
                using (SqlConnection ConnectionSql = new SqlConnection(ConnectionString))
                {
                    using (SqlCommand CommandSql = new SqlCommand("ExampleDatabase.dbo.CheckForeignKeyFax"))
                    {
                        CommandSql.CommandType = CommandType.StoredProcedure;
                        CommandSql.Parameters.Add(new SqlParameter("@Fax", fax));
 
                        SqlParameter ParameterSql = new SqlParameter("@ReturnValue", DbType.Int32);
                        ParameterSql.Direction = ParameterDirection.ReturnValue;
 
                        CommandSql.Parameters.Add(ParameterSql);
 
                        ConnectionSql.Open();
                        CommandSql.Connection = ConnectionSql;
                        CommandSql.ExecuteScalar();
                        Result = Int32.Parse(CommandSql.Parameters["@ReturnValue"].Value.ToString());
                        ConnectionSql.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            return Result;
        }
 
        private void button1_Click(object sender, EventArgs e)
        {
            MessageBox.Show(CheckFax(textBox1.Text).ToString());
        }
    }
}
 

Please help!

[Plater]

Check out the DBNull.Value object for passing in a null (or checking against a null value in a DataSet)

In SQL you can use the "is null" to check if a field is null

[qwedster]

Thanks for the reply!

@Plater