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

[qwedster]

Folk!

How to programatticall y 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 programatticall y 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:
[code=SQL]
USE [master]
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = 'ExampleDatabas e')
DROP DATABASE [ExampleDatabase];
GO

CREATE DATABASE [ExampleDatabase];
GO

USE [ExampleDatabase];
GO

IF EXISTS (SELECT NAME FROM SYS.TABLES WHERE NAME = 'ExampleTable')
DROP TABLE dbo.ExampleTabl e;
GO

CREATE TABLE
dbo.ExampleTabl e
(
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(Ph one)
);
GO

INSERT INTO dbo.ExampleTabl e
(
UserID,
Name,
DateOfBirth,
IsActive,
Phone,
Fax
)
VALUES
(
1,
'Bill',
'12-31-2000',
'False',
'12345678',
'12345678'
);
GO

INSERT INTO dbo.ExampleTabl e
(
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'CheckForeignK eyFax')
DROP PROCEDURE dbo.CheckForeig nKeyFax;
GO

CREATE PROCEDURE dbo.CheckForeig nKeyFax
(
@Fax NVARCHAR(50)
)
AS
DECLARE @ResultFax INT
IF EXISTS
(
SELECT
NULL
FROM
dbo.ExampleTabl e 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 = CheckForeignKey Fax @Fax = '12345678'
SELECT ReturnValue=@Re turnValue;
GO

DECLARE @ReturnValue INT
EXEC @ReturnValue = CheckForeignKey Fax @Fax = NULL
SELECT ReturnValue=@Re turnValue;
GO
[/code]
C# Code:
[code=c#]
using System;
using System.Collecti ons.Generic;
using System.Componen tModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows. Forms;

using System.Data.Sql Client;

namespace WindowsFormsApp lication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeCompo nent();
}

private string ConnectionStrin g
{
get
{
return @"Persist Security Info=False;Data Source=.\SQLEXP RESS;User ID=sa;Password= asdfglkjh;Initi al Catalog=master; ";
}
}

private int CheckFax(string fax)
{
int Result = -1;

try
{
using (SqlConnection ConnectionSql = new SqlConnection(C onnectionString ))
{
using (SqlCommand CommandSql = new SqlCommand("Exa mpleDatabase.db o.CheckForeignK eyFax"))
{
CommandSql.Comm andType = CommandType.Sto redProcedure;
CommandSql.Para meters.Add(new SqlParameter("@ Fax", fax));

SqlParameter ParameterSql = new SqlParameter("@ ReturnValue", DbType.Int32);
ParameterSql.Di rection = ParameterDirect ion.ReturnValue ;

CommandSql.Para meters.Add(Para meterSql);

ConnectionSql.O pen();
CommandSql.Conn ection = ConnectionSql;
CommandSql.Exec uteScalar();
Result = Int32.Parse(Com mandSql.Paramet ers["@ReturnVal ue"].Value.ToString ());
ConnectionSql.C lose();
}
}
}
catch (Exception ex)
{
MessageBox.Show (ex.ToString()) ;
}
return Result;
}

private void button1_Click(o bject sender, EventArgs e)
{
MessageBox.Show (CheckFax(textB ox1.Text).ToStr ing());
}
}
}
[/code]


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!

Thanks for the reply!

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