It ain't null until I SAY it's null!

The fiasco around System.DbNull and "null" and Databases kind of reminds me of the "Hanes Lady" commercial where she is pulling the elastic of the briefs (Now that one was Marketing 101 exemplified -- how many TV ads do you really remember like that one?).

The typical forum or newsgroup post goes:

"When I insert a blank value into a SQL Server database for a DateTime column, my database is inserting 1/1/1900 even if I assign Null to the variable in my application."


When you are inserting data into a database, the ADO.NET data providers and your database may distinguish between a null object and an uninitialized value on a spcific data type. In this case, inserting a null into a DateTime column causes the database to seed the field with the default initialized value - 1/1/1900. What you really want is to tell the database that the field in question should remain uninitialized. To do that there is a System.DBNull class and you use the Value property of the class, e.g. "System.DbNull.Value".

To insert a row into your database, and maintain the uninitialized state of the DateTime fields you use code like this:

SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType=CommandType.Text;
cmd.CommandText = "INSERT INTO USERS (Name, RegisterDate, CancelDate) VALUES (@Name, @RegisterDate, @CancelDate)";
cmd.Parameters.Add("@Name", "FeeFiFoFum");
cmd.Parameters.Add("@RegisterDate", DateTime.Now);
//Use System.DBNull.Value to keep the CancelDate field uninitialized
cmd.Parameters.Add("@CancelDate", System.DBNull.Value);



A BETTER WAY - USING GENERICS

I've seen a number of approaches to this, but one engineered by Adam Anderson is clearly the best. In .NET 2.0 , we can have one function for all the data types:

public static class CastDBNull
{
public static T To( object value, T defaultValue )
{
return ( value != DBNull.Value ) ? (T) value : defaultValue;
}
}


To use this:

// Pass a string type to cast to string; you could pass either String.Empty or null,
//depending on what you want for the default value:
string s = CastDBNull.To( dr[0], String.Empty );
//Now with the same class and method, passing int type to cast to int:
int i = CastDBNull.To( dr[0], 0 );


Some programmers prefer to use nullable types to handle DBNull, the reasoning being that using null to represent DBNull is better than using a "magic number" such as 0 to indicate DBNull.

However, there are times when you can't use nullable types, because you need to know the difference between having "no data" and null data. If you try to select a field from a row with certain criteria, there might be no matching row, so your field value remains uninitialized (null), or a row where that field's value is DBNull, or a non-null value. In cases like this, where you can have three different kinds of results, nullable types are difficult to "make fit".