Move a DataReader to an Object with reflection Revisited

Gal Ratner — Sun, 08 Nov 2009 13:52:00 GMT

if you have read my article Move a DataReader to an Object with reflection where i explain how to move a row from a DataReader into a custom object. Mapping the query fields into object properties, you may have noticed the rigidity of the code. You needed to have the exact numcers of fields in the query without any NULL values. I have since refactored the code to be more loose.

using System;
using System.Data.SqlClient;
using System.Reflection;
using System.Collections.Generic;
using System.Linq;
using System.Text;
 
namespace InvertedSoftware.ShoppingCart.DataLayer.Helpers
{
    internal class ObjectHelper
    {
        /// <summary>
        /// Return the current row in the reader as an object
        /// </summary>
        /// <param name="reader">The Reader</param>
        /// <param name="objectToReturnType">The type of object to return</param>
        /// <returns>Object</returns>
        public static Object GetAs(SqlDataReader reader, Type objectToReturnType)
        {
            // Create a new Object
            Object newObjectToReturn = Activator.CreateInstance(objectToReturnType);
            // Get all the properties in our Object
            PropertyInfo[] props = objectToReturnType.GetProperties();
            // For each property get the data from the reader to the object
            for (int i = 0; i < props.Length; i++)
            {
                if (ColumnExists(reader, props[i].Name) && reader[props[i].Name] != DBNull.Value) 
                    objectToReturnType.InvokeMember(props[i].Name, BindingFlags.SetProperty, null, newObjectToReturn, new Object[] { reader[props[i].Name] });
            }
            return newObjectToReturn;
        }
 
        /// <summary>
        /// Check if an SqlDataReader contains a field
        /// </summary>
        /// <param name="reader">The reader</param>
        /// <param name="columnName">The column name</param>
        /// <returns></returns>
        public static bool ColumnExists(SqlDataReader reader, string columnName)
        {
            reader.GetSchemaTable().DefaultView.RowFilter = "ColumnName= '" + columnName + "'";
            return (reader.GetSchemaTable().DefaultView.Count > 0);
        }
    }
}

Using the class will looks like this:

public Customer GetCustomer(int customerID)
        {
            Customer customer = null;
 
            SqlParameter CustomerIDSqlParameter = new SqlParameter("@CustomerID", SqlDbType.Int);
            CustomerIDSqlParameter.Value = customerID;
 
            try
            {
                using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.mainConnectionString, CommandType.StoredProcedure, "GetCustomer", CustomerIDSqlParameter))
                {
                    while (reader.Read())
                        customer = (Customer)ObjectHelper.GetAs(reader, typeof(Customer));
                }
            }
            catch (Exception e)
            {
                throw new Exception("Error getting customer", e);
            }
            return customer;
        }

As you can tell, you can now modify the output of Stored Procedures without breaking your current code, even if the sproc's output is not identical to your object.

Note: In .NET 4.0 you can use a DynamicObject to TryGetMember when loading properties. David Ebbo has a good blog post about how to do it here with the MSDN class library here

Here is the same code. this time we are using generics to avoid casting into Object:

public static T GetAs<T>(SqlDataReader reader)
        {
            // Create a new Object
            T newObjectToReturn = Activator.CreateInstance<T>();
            // Get all the properties in our Object
            PropertyInfo[] props = typeof(T).GetProperties();
            // For each property get the data from the reader to the object
            for (int i = 0; i < props.Length; i++)
            {
                if (ColumnExists(reader, props[i].Name) && reader[props[i].Name] != DBNull.Value)
                    typeof(T).InvokeMember(props[i].Name, BindingFlags.SetProperty, null, newObjectToReturn, new Object[] { reader[props[i].Name] });
            }
            return newObjectToReturn;
        }

Invoking the generic GetAs looks like:

public Product GetProduct(int productID)
        {
            Product product = null;
 
            SqlParameter ProductIDSqlParameter = new SqlParameter("@productID", SqlDbType.Int);
            ProductIDSqlParameter.Value = productID;
 
            try
            {
                using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.mainConnectionString, CommandType.StoredProcedure, "GetProduct", ProductIDSqlParameter))
                {
                    while (reader.Read())
                        product = ObjectHelper.GetAs<Product>(reader);
                }
            }
            catch (Exception e)
            {
                throw new Exception("Error Getting Product", e);
            }
            return product;
        }

Now that we got all the basic methods in place, let’s start some performance tuning. First off, we are going to cache the type’s properties. GetProperties uses reflaction and carries a bit of an overhead, especially when we call it in a loop. Lets cache all of the properties in a Dictionary.

// Dictionary to store cached properites
        private static IDictionary<string, PropertyInfo[]> propertiesCache = new Dictionary<string, PropertyInfo[]>();
        // Help with locking
        private static ReaderWriterLockSlim propertiesCacheLock = new ReaderWriterLockSlim();
        /// <summary>
        /// Get an array of PropertyInfo for this type
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns>PropertyInfo[] for this type</returns>
        public static PropertyInfo[] GetCachedProperties<T>()
        {
            PropertyInfo[] props = new PropertyInfo[0];
            if (propertiesCacheLock.TryEnterUpgradeableReadLock(100))
            {
                try
                {
                    if (!propertiesCache.TryGetValue(typeof(T).FullName, out props))
                    {
                        props = typeof(T).GetProperties();
                        if (propertiesCacheLock.TryEnterWriteLock(100))
                        {
                            try
                            {
                                propertiesCache.Add(typeof(T).FullName, props);
                            }
                            finally
                            {
                                propertiesCacheLock.ExitWriteLock();
                            }
                        }
                    }
                }
                finally
                {
                    propertiesCacheLock.ExitUpgradeableReadLock();
                }
                return props;
            }
            else
            {
                return typeof(T).GetProperties();
            }
        } 

Notice we used ReaderWriterLockSlim in order to synchronize the Dictionary.
Next we are going to try and minimize the number of calls to ColumnExists. Filtering the DefaultView on every column is also an overhead. Let’s create a new function and return the list of columns in the reader.

public static List<string> GetColumnList(SqlDataReader reader)
        {
            List<string> columnList = new List<string>();
            System.Data.DataTable readerSchema = reader.GetSchemaTable();
            for (int i = 0; i < readerSchema.Rows.Count; i++)
                columnList.Add(readerSchema.Rows[i]["ColumnName"].ToString());
            return columnList;
        }

Finally, lets refactor our GetAs function to use the new methods.

public static T GetAs<T>(SqlDataReader reader)
        {
            // Create a new Object
            T newObjectToReturn = Activator.CreateInstance<T>();
            // Get all the properties in our Object
            PropertyInfo[] props = GetCachedProperties<T>();
            // For each property get the data from the reader to the object
            List<string> columnList = GetColumnList(reader);
            for (int i = 0; i < props.Length; i++)
            {
                if (columnList.Contains(props[i].Name) && reader[props[i].Name] != DBNull.Value)
                    typeof(T).InvokeMember(props[i].Name, BindingFlags.SetProperty, null, newObjectToReturn, new Object[] { reader[props[i].Name] });
            }
            return newObjectToReturn;
        }

For your convenience, I have attached the final version to this blog post. Just download and use!

.NET Development : Data Layer

Move a DataReader to an Object with reflection Revisited