thanby thanby - 3 months ago 7
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
MyObject
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
if
statement to check for a
DbNull
first, but since there are so many properties I'm hoping to keep the code cleaner by not having to spell out an
if
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
string
but gives me errors of
Operator '??' cannot be applied to operands of type 'int' and '<null>'
(or any other data type except
string
. I specifically called out the
int
(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
if
statements for each property), and (2) Work with any data type?

Answer

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.

Comments