|
P: 24
|
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!
| |
