Handling NULL DATES in .NET and SQL Server

I think no matter how solid is your application development skills and application testing skills … the end users always find ways to make your application do things which it not suppose to do and so eventually it crash. Or worse, they can generate inconsistency in your database. Being both as application developer and a dba, I know that both of these things can have serious effects and as a developer you are always required to deal with NULL values, as many (read mostly) users like to skip the required field but the manager always want us to be sure that data is consistent and application works just fine … but I think this happens only in books (specifically in books of database dev. ) … but as we all know, the real world is too harsh..

Okie, now about todays post … as we know, in many forms sometimes users are required to enter date. And in general, every data user feed goes to database. But something these database doesn’t like is NULL values for DATE datatype. So in general you will get an exception when you will leave the field empty that has Date datatype, something like “Format Exception” which basically means that it has some issues with datatype conversion.

I have re-produced the issue in my test machine. I have created a sample table, which has two fields and one of them is of DATE datatype. Then I have created a fronted application in ASP.NET (C#) which has two text fields and two buttons and a label. So in one button, when user click it generates an error because of no logic to handle NULL date. But second button does include a logic to handle NULL value as a date, so it will not throw an exception.

I have given code for this test below (Database + Frontend).

create table test (userName varchar(20),logdate date)
go
-- create proc 
-- i have created it 'coz of habit, but you can use incline query as well
create proc USP_Test
@user varchar(20),
@logDt date
as
insert into test(userName,logdate)
values (@user,@logDt)
go

-- see the final results
select * from test

And the frontend …

    protected void Button1_Click(object sender, EventArgs e)
        {
           // Its always good idea to store string in WEB.CONFIG for added security
         // I am just using this direct string for simplycity ...
    string sqlconn = "Server=Scarface;Database=testDB;Trusted_Connection=True;";
            SqlConnection conn = new SqlConnection(sqlconn);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.Parameters.Add("@user", SqlDbType.VarChar).Value = TextBox1.Text;
            cmd.Parameters.Add("@logDt", SqlDbType.Date).Value = TextBox2.Text;
            try
            {
                conn.Open();
                cmd.CommandText = "USP_Test";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.ExecuteReader();
            }
            catch (Exception ex) { Label1.Text = ex.Message; }
            finally { conn.Close(); }
            
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            string sqlconn = "Server=Scarface;Database=testDB;Trusted_Connection=True;";
            SqlConnection conn = new SqlConnection(sqlconn);
            SqlCommand cmd = new SqlCommand();
            SqlDateTime dtnull = SqlDateTime.Null;
            cmd.Connection = conn;
            cmd.Parameters.Add("@user", SqlDbType.VarChar).Value = TextBox1.Text;
            if (TextBox2.Text == "")
            {
                cmd.Parameters.Add("@logDt", SqlDbType.Date).Value = dtnull;
            }
            else
            {
                cmd.Parameters.Add("@logDt", SqlDbType.Date).Value = DateTime.Parse(TextBox2.Text);
            }

            try
            {
                conn.Open();
                cmd.CommandText = "USP_Test";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.ExecuteReader();
            }
            catch (Exception ex) { Label1.Text = ex.Message; }
            finally { conn.Close(); }
        }

And thus by using a simple SQL Datatype in our application, we can handle NULL Dates.

This is it for now …

It’s Just A Thought …