Skip to content

Passing Null values via Parameterized Queries in ADO.NET

July 1, 2011

A null value in C# is not the same as a null value in the database. There for if you want to pass a null value to your database the following code will throw an exception,

saying the parameterized query was expecting a value for @NullableId which was not provided. The reason for this is C# null or VB.NET Nothing is not the same as a

database null.

SqlCommand command = MyDatabase.GetCommand();

SqlTransaction trans = null;

 

try

{

    //begin transaction

    trans = command.Connection.BeginTransaction();

    command.Transaction = trans;

 

 

    command.CommandText = @"INSERT INTO [TestTable]

     ([TEMP_NULLABLE_ID])

    VALUES

    (@NullableId)";

 

    command.Parameters.Clear();

 

    command.Parameters.AddWithValue("@NullableId ",null);

 

    command.ExecuteNonQuery();

    trans.Commit();

}

catch (Exception exp)

{

    //rollback transactions

    trans.Rollback();

}

finally

{

    command.Dispose();

    trans.Dispose();

}

To pass a null value to the database, you need to pass in a DBNull.Value. One way to pass a null would be to check for the value to to be null and if it is pass in a DBNull.Value else pass in the value etc

SqlParameter param = new SqlParameter();

           

param.ParameterName = paramName;

if (value == null)

    param.Value = DBNull.Value;

else

    param.Value = value;

And then add the param to the SqlParameterCollection .

It would be really nice if SqlParameterCollection was smart enough to convert a null value to a DBNull. It would make our code a lot more

cleaner and more readable. Since SqlParameterCollection doesn’t provide this, we can use extension methods to extend it’s functionality.

A really good article on extension methods can be found at

http://weblogs.asp.net/dwahlin/archive/2008/01/25/c-3-0-features-extension-methods.aspx

This my implementation of the extension method

 

 

 

public static class ExtensionMethods

    {

       

        /// <summary>

        /// Add a SqlParameter to the SqlParameterCollection including null values.

        /// </summary>

        /// <param name="paramCollection"></param>

        /// <param name="paramName"></param>

        /// <param name="value"></param>

        public static void AddWithNullableValue(this SqlParameterCollection paramCollection, string paramName, object value)

        {

            SqlParameter param = new SqlParameter();

           

            param.ParameterName = paramName;

            param.Value = value == null ? DBNull.Value : value;

 

            if (value == null)

                param.Value = DBNull.Value;

            else

                param.Value = value;

 

            paramCollection.Add(param);

        }

    }

Now I have AddWithNullableValue available on the SqlParameterCollection class as an extension method and I can make a very subtle change to my code to be able to add null values to my parameterized queries.

 

SqlCommand command = MyDatabase.GetCommand();

SqlTransaction trans = null;

 

try

{

    //begin transaction

    trans = command.Connection.BeginTransaction();

    command.Transaction = trans;

 

 

    command.CommandText = @"INSERT INTO [TestTable]

     ([TEMP_NULLABLE_ID])

    VALUES

    (@NullableId)";

 

    command.Parameters.Clear();

 

    command.Parameters.AddWithNullableValue("@NullableId ",null);

 

    command.ExecuteNonQuery();

    trans.Commit();

}

catch (Exception exp)

{

    //rollback transactions

    trans.Rollback();

}

finally

{

    command.Dispose();

    trans.Dispose();

}

 

We need to make sure we import the namespace where our extension method is present for it to be available to us, as you can see the code remains pretty much the same and it’s very clean and readable.

About these ads
No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: