Images in this post missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at imagehelp@codebetter.com
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* 

 


Posted 03-06-2008 9:21 PM by anortham

[Advertisement]

Comments

Daily Bits - March 7, 2008 | Alvin Ashcraft's Daily Geek Bits wrote Daily Bits - March 7, 2008 | Alvin Ashcraft's Daily Geek Bits
on 03-07-2008 9:25 AM

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

Chris Forsyth wrote re: ADO.NET, Nullable Types, Casting DBNull and You
on 03-07-2008 9:59 AM

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.

Christopher Bennage wrote re: ADO.NET, Nullable Types, Casting DBNull and You
on 03-07-2008 10:12 AM

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

anortham wrote re: ADO.NET, Nullable Types, Casting DBNull and You
on 03-07-2008 3:23 PM

Chris,

Thanks, I've tried to clarify.

Christopher,

How many years of therapy did it take to recover?

DotNetKicks.com wrote DBNull
on 03-13-2008 11:10 AM

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

Sam wrote re: ADO.NET, Nullable Types, Casting DBNull and You
on 03-14-2008 1:22 AM

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!

Roobin wrote re: ADO.NET, Nullable Types, Casting DBNull and You
on 07-17-2008 11:06 AM

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!

TheHangedMan wrote re: ADO.NET, Nullable Types, Casting DBNull and You
on 10-30-2008 11:00 PM

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

kirti wrote re: ADO.NET, Nullable Types, Casting DBNull and You
on 11-19-2008 3:10 AM

Cannot set Column 'colunm name'  to be null. Please use DBNull instead.

Anton wrote re: ADO.NET, Nullable Types, Casting DBNull and You
on 06-05-2009 4:10 AM

As Chris Forsyth said earlier.

Such construction as

instance.Property1 = row["Column1"] as DateTime?;

works correctly only in the case when internal ADO column representation type is the same as the casting type. No guarantee that this internal type corresponds to type that you want and will not changed in future .Net versions.

Another point that some databses have not corresponding types for .Net types.

For exmaple:

bool? myBool = row["Column1"] as bool?;

works correctly for MS SQL server (if DBType for Column1 is bit) and fails for Oracle server (for any type).

About The CodeBetter.Com Blog Network
CodeBetter.Com FAQ

Our Mission

Advertisers should contact Brendan

Subscribe
Google Reader or Homepage

del.icio.us CodeBetter.com Latest Items
Add to My Yahoo!
Subscribe with Bloglines
Subscribe in NewsGator Online
Subscribe with myFeedster
Add to My AOL
Furl CodeBetter.com Latest Items
Subscribe in Rojo

Member Projects
DimeCasts.Net - Derik Whittaker

Friends of Devlicio.us
Red-Gate Tools For SQL and .NET

NDepend

SlickEdit
 
SmartInspect .NET Logging
NGEDIT: ViEmu and Codekana
LiteAccounting.Com
DevExpress
Fixx
NHibernate Profiler
Unfuddle
Balsamiq Mockups
Scrumy
JetBrains - ReSharper
Umbraco
NServiceBus
RavenDb
Web Sequence Diagrams
Ducksboard<-- NEW Friend!

 



Site Copyright © 2007 CodeBetter.Com
Content Copyright Individual Bloggers

 

Community Server (Commercial Edition)