.NET Development : ADO.NET

Get SQL Parameters From an Object Revisited.

Gal Ratner — Wed, 24 Feb 2010 05:35:00 GMT

I have my own custom data layer adapter. Its pretty slick and you probably know it by now if you read my blog post Move a DataReader to an Object with reflection Revisited. If you downloaded and are using the file ObjectHelper.cs you probably noticed a method called GetSQLParametersFromPublicProperties. This method returns an array of SqlParameters from a live object. You can use this array if you are invoking a Stored Procedure and need to send some parameters into it. Something like:

public int AddCustomer(Customer customer)
        {
            try
            {
                SqlParameter[] paramArray = ObjectHelper.GetSQLParametersFromPublicProperties(customer);
                customer.CustomerID = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.mainConnectionString, CommandType.StoredProcedure, "AddCustomer", paramArray));
            }
            catch (Exception e)
            {
                throw new Exception("Error Adding Customer", e);
            }
            return customer.CustomerID;
        }

A friend of mine was using this function today. He was converting a system from NHibernate to ObjectHelper and needed something I was missing. He needed to skip some fields when creating the SqlParameter array. He was executing an Insert sproc and he didn’t need the object ID as a parameter. It was a field that was auto generated by the Insert statement. He then asked me for a way to hide object fields at will so that the function will only return the parameters he needed and suggested NHibernate like attributes on the fields he needed to hide. This is a great idea and I immediately went to work on implementing it.
First I added a new custom attribute. I created a new class that derived from System. Attribute:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
 
namespace InvertedSoftware.ShoppingCart.DataLayer.DataAttributes
{
    [Flags]
    public enum CrudFieldType
    {
        Create = 0x01,
        Read = 0x02,
        Update = 0x04,
        Delete = 0x08,
        DontUse = 0x10
    }
 
    public class CrudField : Attribute
    {
        public CrudFieldType UsedFor { get; set; }
    }
}

I used a bit enum to allow multiple operations on the same field and simply left the ones I didn’t need behind. Then I added an attribute to my Data Object

 public class Customer
    {
        [CrudField(UsedFor = CrudFieldType.Delete | CrudFieldType.Read | CrudFieldType.Update)]
        public int CustomerID { get; set; }
    }

Finally I re wrote the function to ignore a field based on the operation type we specify:

public static SqlParameter[] GetSQLParametersFromPublicProperties(object dataObject, CrudFieldType usedFor)
        {
            Type type = dataObject.GetType();
            PropertyInfo[] props = type.GetProperties();
            List<SqlParameter> paramList = new List<SqlParameter>();
            for (int i = 0; i < props.Length; i++)
            {
                if (props[i].PropertyType.IsValueType || props[i].PropertyType.Name == "String")
                {
                    object fieldValue = type.InvokeMember(props[i].Name, BindingFlags.GetProperty, null, dataObject, null);
                    CrudField usedForAttr = Attribute.GetCustomAttribute(props[i], typeof(CrudField)) as CrudField;
                    if (usedForAttr != null && (usedForAttr.UsedFor & usedFor) == usedFor)
                    {
                        SqlParameter sqlParameter = new SqlParameter("@" + props[i].Name, fieldValue);
                        paramList.Add(sqlParameter);
                    }
                    else if (usedForAttr == null)
                    {
                        SqlParameter sqlParameter = new SqlParameter("@" + props[i].Name, fieldValue);
                        paramList.Add(sqlParameter);
                    }
                }
            }
            return paramList.ToArray();
        }

The end result was the ability to have granular control over what parameters we send into the sproc and was still backward compatible with older versions of ObjectHelper. Data objects without the CrudField attribute will be handled in a similar way to the old version and will always be included in the returned array.
Thank you Jason for the idea! You can find Jason Brown here and he is always available for consulting.
I have attached the new ObjectHelper to this post. I hope you all find it as useful as I have.

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!