pass null value for date parameter?

See more:

C#

C#4.0

hello

I have little problem with retrieving data from stored procedure in sql server to my win form application datagridview

this is the code for procedure and in sql server it works

USE [db]
GO
create proc test_proc

@PageSize int output, 
@PageNum int output,
@column20 varchar(MAX) output,
@datefrom date = null output ,
@dateto date = null output


as
set nocount on

Declare @RowStart int 
Declare @RowEnd int 

if @PageNum > 0 
Begin 

SET @PageNum = @PageNum -1 

SET @RowStart = @PageSize * @PageNum + 1; 
SET @RowEnd = @RowStart + @PageSize - 1 ; 

With Cust AS 

(SELECT *, 
       ROW_NUMBER() OVER (order by ID) as RowNumber 
       FROM database
WHERE ((Column20 LIKE +@column20+ '%') OR (@column20 is null))
AND ((Date >= @datefrom) OR (@datefrom is null))
AND ((Date <= @dateto) OR (@dateto is null))

)

select * 
from Cust 
Where RowNumber >= @RowStart and RowNumber <= @RowEnd end 

and in winform application I have 3 checkoboxes called c1, c2, c3, and 1 textbox(column20) and two datetimepickers (date1 and date2) in short format, just dates. each checkbox if checked then that corresponding textbox or datetime picker should be used for filtering as you can see in procedure.

this is the code for retrieving data to datagridview

dsData.Clear();

            SqlCommand com = new SqlCommand("test_proc", cs);
            com.CommandType = CommandType.StoredProcedure;

            SqlParameter par1 = new SqlParameter("@column20", SqlDbType.VarChar, -1);
            SqlParameter par2 = new SqlParameter("@datefrom", SqlDbType.Date);
            SqlParameter par3 = new SqlParameter("@dateto", SqlDbType.Date);

            SqlParameter par4= new SqlParameter("@PageSize", SqlDbType.Int);
            SqlParameter par5 = new SqlParameter("@PageNum", SqlDbType.Int);

            
            par1.Direction = ParameterDirection.InputOutput;
            par2.Direction = ParameterDirection.InputOutput;
            par3.Direction = ParameterDirection.InputOutput;
            par4.Direction = ParameterDirection.InputOutput;
            par5.Direction = ParameterDirection.InputOutput;           

           
            par4.Value = "50";
            par5.Value = "1";             
            
            
            if (c1.Checked = false)
            {
                par3.Value = DBNull.Value; 
            }
            else
            {
                par3.Value = date1.Value;
            }
            if (c2.Checked = false)
            {
                par4.Value = DBNull.Value; 
            }
            else
            {
                par4.Value = date2.Value;
            }
            if (c3.Checked = false)
            {
                par1.Value = null;
            }
            else
            {
                par1.Value = column20.Text;

            }

            com.Parameters.Add(par1);
            com.Parameters.Add(par2);
            com.Parameters.Add(par3);
            com.Parameters.Add(par4);
            com.Parameters.Add(par5);           


            daData.SelectCommand = com;

            daData.Fill(dsData, "database");

            DataBS.DataSource = dsData.Tables["database"];

            datagridview.DataSource = DataBS;

but it doesnt work, it just doesnt ignore date values, doesnt pass them as null values, what am I doing wrong???