Fun with Reflection, DataTable, Classes and SQLHelper

Some simple data conversion methods to help with the SQLHelper v2 utility class

Even though it first came out in 2001, the SQLHelper v2 ADO.NET utility class still has a lot going for it, and I still use it extensively in my work. Besides the fact that it can make stored procedure calls simple "one-liners", it also caches the SqlParameter set for a given stored proc when you use the object array method overloads. This can have a significant effect on the speed of your SQL server calls.

Since the project I'm working on right now does not use Entity Framework or LINQ To SQL, it often becomes a convenience to be able to take a one-row DataTable and convert this into an instance of a class, matching columns to class properties (and vice-versa).  Another situation is when I need to convert a class' property values  to an object array of parameter values for use with SQLHelper.

Here is a simple class with three static methods to accomplish these needed conversions:

using System;
using System.Data;
using System.Reflection;

namespace Utilities
{
    public class Converter
    {
// pass in an instance of the class for the obj parameter



public

static void DataTableToClass(DataTable table, object obj)

{

   Type t = obj.GetType();

  foreach (DataColumn col in table.Columns)

{

    string colName = col.ColumnName;

    PropertyInfo pInfo = t.GetProperty(colName);

     if (pInfo == null)

        throw new ArgumentException("Invalid Column Name vs. Property match: " +colName);

    Object val = table.Rows[0][colName];

    if (val is System.DBNull && pInfo.PropertyType.Name =="DateTime" )

val = DateTime.Parse("1/1/1900");

val = Convert.ChangeType(val, pInfo.PropertyType);

pInfo.SetValue(obj, val, null);

}

}

public static Object[] ClassToObjectArray (object obj)
        {
            PropertyInfo[] pInfos = obj.GetType().GetProperties();
             object[] objArray = new object[pInfos.Length];
             int ctr = 0;
             foreach (PropertyInfo pInfo in pInfos)
            {
                objArray[ctr] = pInfo.GetValue(obj, null);
                 ctr++;
             }
             return objArray;
        }


        public static DataTable ClassToDataTable(object obj)
        {
            PropertyInfo[] pInfos = obj.GetType().GetProperties();
            var table = new DataTable();
             foreach (PropertyInfo pInfo in pInfos)
            {
                table.Columns.Add(pInfo.Name, pInfo.GetType());
            }

            DataRow row = table.NewRow();
             foreach (PropertyInfo pInfo in pInfos)
            {
                row[pInfo.Name] = pInfo.GetValue(obj, null);
            }
            return table;
        }
     }
}

Example usage:

Object[] parms = Utilities.Converter.ClassToObjectArray(myClassInstance);
object retVal = SqlHelper.ExecuteScalar( connectionString, procName, parms);

Hey! Could it be simpler than that?

Also, I thought of (and it was also suggested to me) that because reflection is expensive, why not cache the PropertyInfo arrays? This can be done quite easily with the below additions:

public static Dictionary<Type, PropertyInfo[]> Dict = new Dictionary<Type, PropertyInfo[]>();
//....

public

static Object[] ClassToObjectArray (object obj)
{
Type t = obj.GetType();
PropertyInfo[] pInfos;
bool good= Dict.TryGetValue(t, out pInfos);
if (!good)
{
pInfos = t.GetProperties();
Dict.Add(t, pInfos);
}

object[] objArray = new object[pInfos.Length];
int ctr = 0;
foreach (PropertyInfo pInfo in pInfos)
{
objArray[ctr] = pInfo.GetValue(obj, null);
ctr++;
}
return objArray;
}



Needless to say, your Class or datatable must have its properties / columns in the correct order and with the correct names, and the casing must be the same. The methods should be self-explanatory. Try them out!

By Peter Bromberg   Popularity  (3443 Views)