Map DataTable DataColumn To Class Properties Using Custom Attributes in C# / ADO.NET

By Robbe D. Morris

Printer Friendly Version

Robbe Morris
Robbe & Melisa Morris
Related:  ADO.NET Code Generator - Map DataBase Objects To .NET Classes And Stored Procedures
There is nothing quite like laziness to drive a self starter.  I've been looking for a way to speed up the development of the data access layers I've been writing.  Particularly in the area of populating class properties with the results returned from a DataTable and I really didn't want to mess with strongly typed DataSets.  Plus, you really can't encapsulate business logic.  In many cases, you wouldn't want to do this anyway but in some instances (and I have a few) it makes good sense.  Our UI or front end programmers seem to work best with having class properties that show up with intellisense.  So, I needed something to duplicate this aspect of strongly typed DataSets without the extra work.  We've all read about the wonders of programming with custom attributes but I hadn't yet found a real world situation where I could make use of them.  That is...until now.
The code sample you see below demonstrates how to create a custom class and custom attributes to dynamically use a DataRow to populate its properties at runtime.  Thus, negating the need to hard code MyClass.MyProperty = oRow["MyColumnName"].ToString() when iterating through the DataTable.  Naturally, I had three big concerns with this approach: ease of use, data type interaction, and performance.


To tackle the ease of use portion, I created a separate class that holds a standard custom attribute called ColumnAttributes.  For now, it simply accepts a database column name as a parameter in the constructor.  This could be expanded to include things like data types or custom business rule flags if desired.  Then, I used inheritance to more or less attach it to my actual classes to hold data.  This cuts down the amount of code I had to plug in to various data access layer methods.  Plus, I can exclude a property from being considered by removing its custom attribute that stores the database column name.  It is also possible to use a different database column name in the attribute than the property name on the class to offer greater flexibility.
Data type interaction surprised me a bit.  In the Sample.ColumnInfo.SetFields method, we dynamically populate the class property with the DataRow/Column value without having to perform any custom transformations.  The framework handles this for us with the .SetValue method on each field in our class.
The last hurdle was performance.  I studied this quite a bit and found this methodology to be a little slower in many cases and faster in others.  A lot of it depends on what type of data type conversions you need to perform when populating your class.  Still, I had to perform an iteration through the DataTable 10,000 times just to see a grand total of a 1.5 second difference.  For a huge majority of applications, 1.5 seconds spread over 10,000 iterations is minimal at best.  However, you'll want to evaluate the cost of development time versus a small decrease in speed.  As part of that evaluation, you may opt to incorporate both practices depending on how often certain database calls are being executed.  In any event, I've left the extremely basic methods of timing these two options in the sample code for you to play around with.
You'll want to notice that I put in the extra step of capturing the custom attributes for each field in the class once and then reuse those same values for each DataRow processed.  Retrieving custom attributes and FieldInfo collections is relatively slow and performing this task with every DataRow just killed performance.  The end result is that you have one more line of code to speed up the process about 25 times.
If you have any good ideas for speeding up this process even more, please post them to our Article Discussions thread in the message board.  I'd love to hear them.  In the following zip file, I've include the C# console project source code (VS.NET 2003): download.
 
Class1.cs
 
using System;
using System.Reflection;
using System.Diagnostics;  
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes; 

namespace Sample
{

  class Class1
  {
        

    [STAThread]
    static void Main(string[] args)
    {
     string ConStr="Data Source=localhost;User ID=youruser;Password=yourpassword;Initial Catalog=Northwind";
     DataTable oTable;
     Class1 oClass = new Class1();
     Sample.Employee oEmployee;
     System.DateTime sStartTime;
     TimeSpan elapsed;
     string Sql="";
     int i=0;
     int Max=10000;
 
     try 
     {           
               
      Sql = "select EmployeeID,FirstName,LastName,BirthDate,HomePhone,";
      Sql += "Address,City,PostalCode,Country ";
      Sql += " from Employees";

      oTable = oClass.GetDataTable(Sql,ConStr); 

      sStartTime = System.DateTime.Now;

      for(i=0;i<Max;i++)
      {
        foreach(DataRow oRow in oTable.Rows)
        {
          oEmployee = new Sample.Employee();
          oEmployee.EmployeeID = Convert.ToInt32(oRow["EmployeeID"]);
          oEmployee.FirstName = oRow["FirstName"].ToString();
          oEmployee.LastName = oRow["LastName"].ToString();
          oEmployee.BirthDate  = (DateTime)oRow["BirthDate"];
          oEmployee.Address  = oRow["Address"].ToString();
          oEmployee.City = oRow["City"].ToString();
          oEmployee.PostalCode  = oRow["PostalCode"].ToString();
          oEmployee.HomePhone  = oRow["HomePhone"].ToString();
          oEmployee.Country  = oRow["Country"].ToString();
        }
      }
      elapsed = System.DateTime.Now - sStartTime;
      Console.WriteLine("Old Method: " + elapsed.TotalMilliseconds.ToString());
          
      oEmployee = new Sample.Employee();
      object[,] oFields = oEmployee.GetFields(typeof(Sample.Employee)); 

      sStartTime = System.DateTime.Now;

      for(i=0;i<Max;i++)
      {
        foreach(DataRow oRow in oTable.Rows)
        {
          oEmployee = new Sample.Employee();
          oEmployee.SetFields(oFields,oRow);      
        }
      }
      elapsed = System.DateTime.Now - sStartTime;
      Console.WriteLine("New Method: " + elapsed.TotalMilliseconds.ToString());
 
    } 
    catch (Exception e) { Debug.WriteLine(e.Message);   }   
    Console.ReadLine(); 

  }
  
 
  public DataTable GetDataTable(string Sql,string ConnectionString)
  {

    SqlConnection oConn = new SqlConnection(); 
    DataTable oTable = new DataTable(); 
         
    try 
    {   
      oConn.ConnectionString =  ConnectionString;
      oConn.Open();       
      SqlDataAdapter oDA = new SqlDataAdapter(Sql,oConn); 
      oDA.Fill(oTable); 
      oConn.Close(); 
    } 
    catch (Exception) { throw;   }   
    finally{ if (oConn.State == ConnectionState.Open) { oConn.Close(); } } 
    return oTable;

  }

 }

}
 
Structures.cs
 
using System;

namespace Sample
{
 
    public class Employee  : Sample.ColumnInfo
    {

        [ColumnAttributes("EmployeeID")]
        public int EmployeeID;

        [ColumnAttributes("FirstName")]
        public string FirstName;

        [ColumnAttributes("LastName")]
        public string LastName;

        [ColumnAttributes("Address")]
        public string Address;

        [ColumnAttributes("City")]
        public string City;

        [ColumnAttributes("PostalCode")]
        public string PostalCode;

        [ColumnAttributes("Country")]
        public string Country;

        [ColumnAttributes("HomePhone")]
        public string HomePhone;

        [ColumnAttributes("BirthDate")]
        public DateTime BirthDate;

    }
 
}
 
Attributes.cs
 
using System;
using System.Reflection;
using System.Diagnostics;  
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes; 

namespace Sample
{
  
    public class ColumnInfo
    { 
 
        public object[,] GetFields(Type t)
        {
              
            FieldInfo[] oFields = t.GetFields();
            FieldInfo oField;
            Attribute[] attributes;
            object[,]  StructureInfo = new object[oFields.Length,2]; 

            try
            {
                for(int i =0;i<oFields.Length;i++)
                {      
                    oField = oFields[i];
                    attributes =  Attribute.GetCustomAttributes(oField,typeof(Sample.ColumnAttributes),false);
                    StructureInfo[i,0] = oField;
                    StructureInfo[i,1] = attributes;
                }
            }
            catch (Exception e) { Debug.WriteLine(e.Message); }
            return StructureInfo;

        }
 

 
        public void SetFields(object[,] StructureInfo,DataRow oRow)
        {
            FieldInfo oField;
            Attribute[] attributes;

            try
            {

                for(int i =0;i<=StructureInfo.GetUpperBound(0);i++)
                {
                       
                    oField = (FieldInfo)StructureInfo[i,0];
                    attributes = (Attribute[])StructureInfo[i,1];

                    foreach(Attribute attribute in attributes)
                    {
                        Sample.ColumnAttributes oColumnAttributeName = (Sample.ColumnAttributes)attribute;
                        if (oRow[oColumnAttributeName.ColumnName] != System.DBNull.Value) 
                        {
                            oField.SetValue(this,oRow[oColumnAttributeName.ColumnName]);
                        }
                        break;
                    }
               
                }
              
            } 
            catch(Exception e) { Debug.WriteLine(e.Message); throw; }
            return;

        }
 
    }

 
    [AttributeUsage(AttributeTargets.Field,AllowMultiple = true)]
    public class ColumnAttributes : System.Attribute 
    {
       public string ColumnName="";

       public ColumnAttributes(string DataBaseColumnName)
       {
          this.ColumnName = DataBaseColumnName;
       }
    }
 
}  
 

Robbe has been a Microsoft MVP in C# since 2004.  He is also the co-founder of NullSkull.com which provides .NET articles, book reviews, software reviews, and software download and purchase advice.