Passing Null values via Parameterized Queries in ADO.NET
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.
