DBNull and Nullable types

re: DBNull and Nullable types

Trygve Valoy — Mon, 30 Jun 2008 22:41:55 GMT

You could solve this with an extension method.

The extension method would look something like this:

public static TValue GetNullableValue<TValue>(this DbDataReader reader, string name)

{

object value = reader[name];

if (DBNull.Value.Equals(value))

{

return default(TValue);

}

return (TValue)value;

}

And your data access code will be:

int? age = customerReader.GetNullableValue<int?>("Age");

re: DBNull and Nullable types

jackbond — Tue, 01 Jul 2008 00:23:27 GMT

I've encountered the same ugly convention as well, and was thinking along the lines of an extension method similiar to the previous poster. Although he missed the part about not being a nullable type (easy enough to tweak his method to be similiar to SQLs IsNull function.)

When it is a nullable type, I've done this in the past:

int? age = dr["Age"] as int?;

re: DBNull and Nullable types

Steve Perry — Tue, 01 Jul 2008 02:32:06 GMT

This problem (dbnull) has been around since well before .net and Microsoft has never done anything to make it any better, nullable types don't really bring us any closer to a way to map between a database null and a data type.

Maybe the entity framework will finally fix this issue.

re: DBNull and Nullable types

farrio — Tue, 01 Jul 2008 04:10:13 GMT

I also noticed this issue when I was using VB2005 to retrieve the data from SQL2005. I created a class named DBNullable(Of T) to deal with it. But I think M$ need to do something to fix this problem because it's confusing and fussy when developing a data-based system.

re: DBNull and Nullable types

Omer van Kloeten — Tue, 01 Jul 2008 06:34:57 GMT

I love Trygve's idea, but it doesn't apply to DataSets (yes, some of us still need to use DataSets).

...and farrio, re: 'M$', are you like 4 years old or something?

re: DBNull and Nullable types

Trygve Valoy — Tue, 01 Jul 2008 16:54:58 GMT

There's no reason why this shouldn't work for DataSets. You just need another extension method. You can even take the table name as a parameter in this method to resolve which table of the dataset you want to get data from.

re: DBNull and Nullable types

thottams@microsoft.com — Wed, 02 Jul 2008 02:43:50 GMT

I like the extension method idea as it will work for both nullable and non-nullable types.

int? age = dr["Age"] as int?; also works. It works because .NET is unable to cast it and assigns a NULL instead, which ends up being what we desire in this case.

re: DBNull and Nullable types

Lothan — Wed, 02 Jul 2008 10:59:08 GMT

I think this depends on whether you need to keep -1 as the default value or what you want to keep the null value. One possibility is to use the as operator for a safe type cast:

int age = reader["Age"] as int? ?? -1;

int? age = reader["Age"] as int?;

Unfortunately, this has negative consequences that can be difficult to catch. If you mistakenly cast a smallint to byte, for example, the as operator will silently swallow InvalidCastException.

I think a more reasonable solution with some measure of safety is to use a nullable cast either with or without the null coalescing operator:

int age = (int?) reader["Age"] ?? -1;

int? age = (int?) reader["Age"];

The code is easy to read and it has the advantage that InvaidCastException is thrown early in case you mistakenly cast to the wrong type, which I've found is easy to do with large tables.

re: DBNull and Nullable types

Peter Strömblad — Mon, 25 Aug 2008 10:17:44 GMT

Thanks - the as operator with ?? <defaultvalue> works perfectly when using nullable types for the database tier.

C# Null, DBNull and Entities

Peter Strömblad — Mon, 25 Aug 2008 12:44:47 GMT

I've been working like mad to get my 3-tier construction to work. As I'm not all that great in C# yet,

re: DBNull and Nullable types

Naison Garvasis Pekkattil — Thu, 18 Sep 2008 11:49:23 GMT

Check out the SafeDataReader class from CSLA.NET. I had customized and used to solve the repeated dbnull check.

re: DBNull and Nullable types

Chi Afriq — Wed, 01 Oct 2008 15:05:01 GMT

How do you make these conversions using VB2005? I was initially developing my database system in C# but had prblms cz I'm a beginner, switched back to VB and the NullReferenceException and InvalidCastExceptions are still giving me hell!