By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,905 Members | 900 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,905 IT Pros & Developers. It's quick & easy.

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

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:
Expand|Select|Wrap|Line Numbers
  1. USE [master]
  2. GO
  3.  
  4. IF EXISTS (SELECT name FROM sys.databases WHERE name = 'ExampleDatabase') 
  5. DROP DATABASE [ExampleDatabase]; 
  6. GO
  7.  
  8. CREATE DATABASE [ExampleDatabase]; 
  9. GO
  10.  
  11. USE [ExampleDatabase]; 
  12. GO
  13.  
  14. IF EXISTS (SELECT NAME FROM SYS.TABLES WHERE NAME = 'ExampleTable') 
  15. DROP TABLE dbo.ExampleTable; 
  16. GO
  17.  
  18. CREATE TABLE 
  19. dbo.ExampleTable
  20. (
  21. ID INT IDENTITY(1,1) NOT NULL, 
  22. UserID INT NULL, 
  23. Name NVARCHAR(50) NULL, 
  24. DateOfBirth DATETIME, 
  25. IsActive BIT, 
  26. Phone NVARCHAR(50) NULL,
  27. Fax NVARCHAR(50) NULL,
  28. CONSTRAINT PK_ID PRIMARY KEY(ID), 
  29. CONSTRAINT UNIQUE_Phone UNIQUE(Phone), 
  30. CONSTRAINT FK_UserID FOREIGN KEY(UserID) REFERENCES ExampleTable(ID),
  31. CONSTRAINT FK_Fax FOREIGN KEY(Fax) REFERENCES ExampleTable(Phone)
  32. );
  33. GO
  34.  
  35. INSERT INTO dbo.ExampleTable
  36. (
  37. UserID, 
  38. Name, 
  39. DateOfBirth, 
  40. IsActive, 
  41. Phone,
  42. Fax
  43. )
  44. VALUES
  45. (
  46. 1,
  47. 'Bill',
  48. '12-31-2000',
  49. 'False',
  50. '12345678',
  51. '12345678'
  52. );
  53. GO
  54.  
  55. INSERT INTO dbo.ExampleTable
  56. (
  57. UserID, 
  58. Name, 
  59. DateOfBirth, 
  60. IsActive
  61. )
  62. VALUES
  63. (
  64. 2,
  65. 'Larry',
  66. '12-31-2005',
  67. 'True'
  68. );
  69. GO
  70.  
  71. SELECT * FROM ExampleTable;
  72. GO
  73.  
  74.  
  75. IF EXISTS(SELECT NAME FROM SYS.PROCEDURES WHERE NAME = N'CheckForeignKeyFax') 
  76. DROP PROCEDURE dbo.CheckForeignKeyFax;
  77. GO
  78.  
  79. CREATE PROCEDURE dbo.CheckForeignKeyFax
  80. (
  81. @Fax NVARCHAR(50)
  82. )
  83. AS
  84. DECLARE @ResultFax INT
  85. IF EXISTS
  86. (
  87. SELECT
  88. NULL
  89. FROM
  90. dbo.ExampleTable WITH (UPDLOCK) 
  91. WHERE
  92. ISNULL(Phone, 'NULL') = ISNULL(@Fax, 'NULL')     
  93. --(Phone IS NULL AND @Fax IS NULL) OR (@Fax = Phone) 
  94. BEGIN SELECT @ResultFax = 0 END
  95. ELSE BEGIN SELECT @ResultFax = -1 END
  96. RETURN @ResultFax
  97. GO
  98.  
  99. DECLARE @ReturnValue INT
  100. EXEC @ReturnValue = CheckForeignKeyFax @Fax = '12345678'
  101. SELECT ReturnValue=@ReturnValue;
  102. GO
  103.  
  104. DECLARE @ReturnValue INT
  105. EXEC @ReturnValue = CheckForeignKeyFax @Fax = NULL
  106. SELECT ReturnValue=@ReturnValue;
  107. GO
  108.  
C# Code:
Expand|Select|Wrap|Line Numbers
  1. using System;
  2. using System.Collections.Generic;
  3. using System.ComponentModel;
  4. using System.Data;
  5. using System.Drawing;
  6. using System.Linq;
  7. using System.Text;
  8. using System.Windows.Forms;
  9.  
  10. using System.Data.SqlClient;
  11.  
  12. namespace WindowsFormsApplication1
  13. {
  14.     public partial class Form1 : Form
  15.     {
  16.         public Form1()
  17.         {
  18.             InitializeComponent();
  19.         }
  20.  
  21.         private string ConnectionString
  22.         {
  23.             get
  24.             {
  25.                 return @"Persist Security Info=False;Data Source=.\SQLEXPRESS;User ID=sa;Password=asdfglkjh;Initial Catalog=master;";                               
  26.             }
  27.         }
  28.  
  29.         private int CheckFax(string fax)
  30.         {
  31.             int Result = -1;
  32.  
  33.             try
  34.             {
  35.                 using (SqlConnection ConnectionSql = new SqlConnection(ConnectionString))
  36.                 {
  37.                     using (SqlCommand CommandSql = new SqlCommand("ExampleDatabase.dbo.CheckForeignKeyFax"))
  38.                     {
  39.                         CommandSql.CommandType = CommandType.StoredProcedure;
  40.                         CommandSql.Parameters.Add(new SqlParameter("@Fax", fax));
  41.  
  42.                         SqlParameter ParameterSql = new SqlParameter("@ReturnValue", DbType.Int32);
  43.                         ParameterSql.Direction = ParameterDirection.ReturnValue;
  44.  
  45.                         CommandSql.Parameters.Add(ParameterSql);
  46.  
  47.                         ConnectionSql.Open();
  48.                         CommandSql.Connection = ConnectionSql;
  49.                         CommandSql.ExecuteScalar();
  50.                         Result = Int32.Parse(CommandSql.Parameters["@ReturnValue"].Value.ToString());
  51.                         ConnectionSql.Close();
  52.                     }
  53.                 }
  54.             }
  55.             catch (Exception ex)
  56.             {
  57.                 MessageBox.Show(ex.ToString());
  58.             }
  59.             return Result;
  60.         }
  61.  
  62.         private void button1_Click(object sender, EventArgs e)
  63.         {
  64.             MessageBox.Show(CheckFax(textBox1.Text).ToString());
  65.         }
  66.     }
  67. }
  68.  

Please help!
Nov 3 '09 #1

✓ answered by 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

Share this Question
Share on Google+
2 Replies


Plater
Expert 5K+
P: 7,872
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
Nov 3 '09 #2

P: 24
Thanks for the reply!

@Plater
Nov 4 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.