Inline null check for SqlDataReader objects

Question

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?

SledgeHammer · Accepted Answer · 2016-08-31 22:38:07Z

3

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.

answered Aug 31, 2016 at 22:38

SledgeHammer

7,7668 gold badges54 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

thanby Over a year ago

Elegantly simple and works amazingly well. Hats off to you, sir.

Steve · Accepted Answer · 2016-09-01 14:00:51Z

You can write a series of extensions method for each of the standard GetXXXX. These extensions receive an extra parameter that is the default to return in case the value of the field is null.

public static class SqlDataReaderExtensions
{
    public int GetInt32(this SqlDataReader reader, int ordinal, int defValue = default(int))
    {
        return (reader.IsDBNull(ordinal) ? defValue : reader.GetInt32(ordinal);
    }
    public string GetString(this SqlDataReader reader, int ordinal, int defValue = "")
    {
        return (reader.IsDBNull(ordinal) ? defValue : reader.GetString(ordinal);
    }
    public int GetDecimal(this SqlDataReader reader, int ordinal, decimal defValue = default(decimal))
    {
       ....
    }
}

This allows you to leave your current code as is without changes or just change the fields that needs the null as return

while (reader.Read())
{
    results.Add(new MyProduct() {
        ProductID = reader.GetInt32(oProductID),
        ProductName = reader.GetString(oProductName, "(No name)"),
        MinReorder = reader.GetInt32(oReorder, null)
        .....
    });
}

You can also have a version where you pass the column name instead of the ordinal position and do the search for the position inside the extension, but this is probably not good from a performance point of view.

Shannon Holsinger · Accepted Answer · 2016-08-31 22:28:05Z

Here's an example that works for fields (can easily be converted to properties) and allows for null checks. It does the dreaded if (in a switch), but it's pretty fast.

 public static object[] sql_Reader_To_Type(Type t, SqlDataReader r)
    {
        List<object> ret = new List<object>();
        while (r.Read())
        {
            FieldInfo[] f = t.GetFields();
            object o = Activator.CreateInstance(t);
            for (int i = 0; i < f.Length; i++)
            {
                string thisType = f[i].FieldType.ToString();
                switch (thisType)
                {
                    case "System.String":

                        f[i].SetValue(o, Convert.ToString(r[f[i].Name]));
                        break;
                    case "System.Int16":
                        f[i].SetValue(o, Convert.ToInt16(r[f[i].Name]));
                        break;
                    case "System.Int32":
                        f[i].SetValue(o, Convert.ToInt32(r[f[i].Name]));
                        break;
                    case "System.Int64":
                        f[i].SetValue(o, Convert.ToInt64(r[f[i].Name]));
                        break;
                    case "System.Double":
                       double th;
                        if (r[f[i].Name] == null)
                        {
                            th = 0;
                        }
                        else
                        {
                            if (r[f[i].Name].GetType() == typeof(DBNull))
                            {
                                th = 0;
                            }
                            else
                            {
                                th = Convert.ToDouble(r[f[i].Name]);
                            }
                        }
                        try { f[i].SetValue(o, th); }
                        catch (Exception e1)
                        {
                            throw new Exception("can't convert " + f[i].Name + " to doube - value =" + th);
                        }
                        break;
                    case "System.Boolean":
                        f[i].SetValue(o, Convert.ToInt32(r[f[i].Name]) == 1 ? true : false);
                        break;
                    case "System.DateTime":
                        f[i].SetValue(o, Convert.ToDateTime(r[f[i].Name]));
                        break;
                    default:
                        throw new Exception("Missed data type in sql select ");

                }
            }
            ret.Add(o);

        }
        return ret.ToArray();


    }

If you don't care what order things come in, you can take this parallel/async as well.

Collectives™ on Stack Overflow

Inline null check for SqlDataReader objects

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related