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!