thanby thanby - 1 year ago 149
SQL Question

Inline null check for SqlDataReader objects

I'm trying to build a method that queries a SQL table and assigns the values it finds to a new list of objects. Here's a quick example of how it works (assume the reader and connection are set up and working properly):

List<MyObject> results = new List<MyObject>();
int oProductID = reader.GetOrdinal("ProductID");
int oProductName = reader.GetOrdinal("ProductName");

while (reader.Read())
results.Add(new MyProduct() {
ProductID = reader.GetInt32(oProductID),
ProductName = reader.GetString(oProductName)

There are about 40 other properties too, all of them nullable in the
definition, so I'm trying to keep the assignments as tidy as possible. The problem is that I need to assign null values to the object wherever the reader returns a null. In the above code, if the reader throws a "Data is Null" exception. I'm aware it's possible to use an
statement to check for a
first, but since there are so many properties I'm hoping to keep the code cleaner by not having to spell out an
statement for every single property.

A bit of searching led me to the null-coalescing operator, which seems like it should do exactly what I want. So I tried changing the assignments to look like this:

ProductID = reader.GetInt32(oProductID) ?? null,
ProductName = reader.GetString(oProductName) ?? null

Which works fine for any
but gives me errors of
Operator '??' cannot be applied to operands of type 'int' and '<null>'
(or any other data type except
. I specifically called out the
(and everything else) as nullable in the object definition, but here it's telling me it can't do that.

The Question

Is there a way to handle nulls in this case that can: (1) Be written clearly in-line (to avoid separate
statements for each property), and (2) Work with any data type?

Answer Source

Null from a database is not "null", it's DbNull.Value. ?? and ?. operators won't work in this case. GetInt32, etc. will throw an exception if the value is null in the DB. I do a generic method and keep it simple:

T SafeDBReader<T>(SqlReader reader, string columnName)
   object o = reader[columnName];

   if (o == DBNull.Value)
      // need to decide what behavior you want here

   return (T)o;

If your DB has nullable ints for example, you can't read those into an int unless you want to default to 0 or something like. For nullable types, you can just return null or default(T).

Shannon's solution is both overly complicated and will be a performance issue (lots of over the top reflection) IMO.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download