Working with database fields that allow nulls

I know asp.net 2.0 has been out for a while now but i’ve only recently setup this blog and thought i should post an article on something that troubled me for a while as a newcomer to asp.net, i am talking of database fields that allow null values.

First i would like to start with how i tackled the problem of casting the data returned from the database to the correct data types. With the help of a website i have forgotten the address of, i use the following method:

public static T CastTo(object value)
{
  return value != DBNull.Value ? (T)value : default(T);
}

Then all i have to do is wrap my data returned using a DataReader with this method, ie int? categoryID = CastTo<int?>(reader["CategoryID"]).

The second problem when working with null database fields is making sure that you store a null value when inserting/updating data. For this problem i wanted an expression that didn’t mean i had to write much extra code and was consistent for all data types. The following example shows how to update a field that allows null fields using a nullable type:

using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
  int? categoryID = null;
  SqlCommand cmd = new SqlCommand("UPDATE Documents SET CategoryID = @CategoryID WHERE DocumentID = @DocumentID", cn);
  cmd.Parameters.Add("@DocumentID", SqlDbType.Int).Value = 1;
  cmd.Parameters.Add("@CategoryID", SqlDbType.Int).Value = (object)categoryID ?? DBNull.Value;
  cn.Open();
  cmd.ExecuteNonQuery()
}

What i personally like about the code above is when adding the parameter i can keep it on one line and it works for any data type. Please note that you must pass DBNull.Value instead of null as the value for your parameter. If passing null asp.net will treat it as if no parameter was added and an exception will be thrown.

I hope this approach helps you as much as it has helped me.