in Search

Do you Twitter? Follow us @devlicious

Alan Northam

ADO.NET, Nullable Types, Casting DBNull and You

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*

Published Mar 06 2008, 09:21 PM by anortham

Filed under: Casting, ADO.NET, Nullable Types

Comments

Daily Bits - March 7, 2008 | Alvin Ashcraft's Daily Geek Bits said:

Pingback from Daily Bits - March 7, 2008 | Alvin Ashcraft's Daily Geek Bits

# March 7, 2008 9:25 AM

Chris Forsyth said:

Just a little clarification, I think that when the cast fails, null is actually assigned to Property1 and Property2, so if they were non-null before, they would be null after.

Same thing for your purposes here, just don't want anyone confused about the way null works.

# March 7, 2008 9:59 AM

Christopher Bennage said:

2001? I think I was still using ADO! (or was it RDO, or DAO, or...) I was slow to change back then. :-)

# March 7, 2008 10:12 AM

anortham said:

Chris,

Thanks, I've tried to clarify.

Christopher,

How many years of therapy did it take to recover?

# March 7, 2008 3:23 PM

DotNetKicks.com said:

You've been kicked (a good thing) - Trackback from DotNetKicks.com

# March 13, 2008 11:10 AM

Sam said:

I like it. I had never seen "as int?;", but now that I think of it, that is a great use for the nullable value types. I learned something today, thanks!

# March 14, 2008 1:22 AM

Ultram for long term chronic pain. said:

Ultram pain reliever. Ultram in mexico. Low dose naltrexone with ultram. Ultram dosage. How many cc s ultram. Ultram.

# July 2, 2008 8:32 AM

Roobin said:

This had to be posted on MSDN article Handling Null Values, so I put it there.

This is how nullables are supposed to work! Love it!

# July 17, 2008 11:06 AM

Generic cialis. said:

Multiple orgasms through cialis. Cheapest cialis. Cialis effects. Cialis.

# August 5, 2008 12:11 AM

TheHangedMan said:

"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."

I used to follow this same approach, but then I noticed that is somewhat dangerous. Imagine the next scenario:

- We have the following table:

CREATE TABLE tab1( column1 INT NULL);

- We have this data-access code:

DateTime? myValue

myValue = row["column1"] as DateTime?;

Notice how the column's type is INT, but we are trying to get a DateTime?. This code would return always null, even if the value of column1 is not! And that's because a value is being returned, but it is the cast which is failing since we are trying to cast from and object whose underlying value is an int to a DateTime?.

Errors like this are not so uncommon, and using this approach we would be possibly letting in some hard-to-debug bugs. I think the use of the "as" casting should be discouraged in this context. It would be better if the code throws an exception so that we soon discover the flaw in the code.

# October 30, 2008 11:00 PM