ADO.NET, Nullable Types, Casting DBNull and You

anortham — Fri, 07 Mar 2008 03:21:00 GMT

I know this may be a little too scary for some of you so I apologize in advance. Take a deep breath and try to imagine that you can't use NHibernate and you're stuck using ADO.NET (circa 2001, did they even have computers back then?).

If you've ever tried to read the value from a column in a datarow, you've probably run into something like this (a quick Google search will bring up page after page of variations):

myValue = (int)row["ColumnName"]; //wait, what if it's null? if( row["ColumnName"] == DBNull.Value) myValue = 0; //or maybe null if myValue is nullable int? else myValue = Convert.ToInt32(row["ColumnName"]); //lot of code for something simple, hope I don't have to do this too many times... //what about if(row.IsNull("ColumnName")) //blah blah blah

Wow, that's ugly. What if I have several nullable columns? What if I have a lot of nullable columns? There has to be a better way.

Let's suppose I have a class MyClass (original huh?) and it has a nullable DateTime property and a nullable integer property.
//snip from MyClass public DateTime? Property1; public int? Property2; //end snip //some contrived ado.net code List<MyClass> list = new List<MyClass>(); foreach(DataRow row in dataTable.Rows) { MyClass instance = new MyClass(); instance.Property1 = row["Column1"] as DateTime?; instance.Property2 = row["Column2"] as int?; list.Add(instance); }

I like a good one line solution. Of course, you really can't cast DBNull.Value (which would be the value of row["ColumnX"] if the column is null in the database) as a nullable int (or DateTime?). (edit: thanks Chris) The cast above is actually failing but casting with "as" doesn't throw an exception and returns a null value which is just what we want.

"as"... you're my hero...*sniff*

Alan Northam : ADO.NET

ADO.NET, Nullable Types, Casting DBNull and You