Generically Populate a Custom .NET Class from a SqlDataReader

Introduction

If you have ever written code that reads data from a table, then you will be familiar with having to map the columns from your SqlDataReader to your custom .NET class. For example, if you have a class called ClientEntity and you read a record into a SqlDataReader, then you will probably have had to write code that maps the columns from your SqlDataReader to the properties on your ClientEntity class.

In the following code snippet is a typical database function that reads a record from the ClientEntity table and returns an instance of the ClientEntity class. It does this by mapping the columns from the SqlDataReader to the properties on the ClientEntity class.

public ClientEntity GetClientInfo(string clientid)
{
    try
    {
        ClientEntity result = null;
        SqlConnection connection;
        using (connection = new SqlConnection(ConnectionString))
        {
            connection.Open();
            SqlCommand cmd;
            using (cmd = new SqlCommand("sp_get_clientinfo", connection))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ClientId", clientid);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    reader.Read();
                    //Here is the mapping code that maps our ClientEntity class to our SqlDataReader
                    result.Refno = reader["DF_REF"].ToString();
                    result.Name = reader["DF_NAME"].ToString();
                    result.Address = reader["DF_ADDR"].ToString();
                }
            }
            connection.Close();
        }
        return result;
    }
    catch (Exception ex)
    {
        throw;
    }
}

We need to write similar mapping code for all our database functions so that we return an instance of a custom .NET class to our client code rather than a SqlDataReader (your client code shouldn't need to have any prior knowledge of your underlying database technology).

Background

In this article, I am going to show a technique that allows you write this sort of mapping code in a more generic way. The technique will use .NET Reflection to inspect the class for the various properties to populate. It will also implement .NET Generics so that the technique can be used for any type of class you have implemented in your application.

Start by Creating Your Custom .NET Class

For the purposes of this article, I will create a class called ClientEntity. The class represents a client and contains information such as name, address and postcode.

Here is the definition for the ClientEntity class:

using Common.Attributes;

namespace Common.Entities
{
    public class ClientEntity
    {
        [DataField("CF_REF")]
        public string Refno { get; set; }
        [DataField("CF_NAME")]
        public string Name { get; set; }
        [DataField("CF_CONTACT")]
        public string Contact { get; set; }
        [DataField("CF_PC")]
        public string Postcode { get; set; }
        [DataField("CF_ADDR")]
        public string Address { get; set; }
    }
}

The class definition for ClientEntity maps each property to its underlying column name. It does this by decorating each of the class properties with the custom attribute DataField (see below). This allows us to give a more descriptive name to our class properties rather than using the underlying table column name.

The DataField Attribute that Decorates Your Class Properties

In the code snippet above, we saw how the ClientEntity class properties were decorated with the attribute DataField. This decorated each of the class properties with the underlying table column name. Here is the implementation for that attribute class.

using System;

namespace Common.Attributes
{
    /// <summary>
    /// Provides the mapping between the name of the source and target entity properties
    /// </summary>
    
    [AttributeUsage(AttributeTargets.Property, AllowMultiple = false, Inherited = true)] 
    public sealed class DataFieldAttribute : Attribute
    {
        private readonly string _name;

        public DataFieldAttribute(string name)
        {
            _name = name;
        }

        public string Name
        {
            get { return _name; }
        }
    }
}

Implementing the Mapping Class

Just to quickly recap, up to this point, we have defined a custom .NET class called ClientEntity which we have decorated with the column names from the underlying table. We now need a mechanism that takes a SqlDataReader (which contains the column names) and maps them to the corresponding class properties on our custom ClientEntity class.

Here is the code that does the mapping:

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

using Common.Attributes;

namespace Common
{
    /// <summary>
    /// Class that will map the public properties of a custom .NET class to the columns in a SqlDataReader.
    /// </summary>
    public static class ReflectPropertyInfo
    {
        public static TEntity ReflectType<TEntity>(IDataRecord dr) where TEntity : class, new()
        {
            TEntity instanceToPopulate = new TEntity();

            PropertyInfo[] propertyInfos = typeof(TEntity).GetProperties
            (BindingFlags.Public | BindingFlags.Instance);

            //for each public property on the original
            foreach (PropertyInfo pi in propertyInfos)
            {
                DataFieldAttribute[] datafieldAttributeArray = pi.GetCustomAttributes
                (typeof(DataFieldAttribute), false) as DataFieldAttribute[];

                //this attribute is marked with AllowMultiple=false
                if (datafieldAttributeArray != null && datafieldAttributeArray.Length == 1)
                {
                    DataFieldAttribute dfa = datafieldAttributeArray[0];

                    //this will blow up if the datareader does not contain the item keyed dfa.Name
                    object dbValue = dr[dfa.Name];

                    if (dbValue != null)
                    {
                        pi.SetValue(instanceToPopulate, Convert.ChangeType
                        (dbValue, pi.PropertyType, CultureInfo.InvariantCulture), null);
                    }
                }
            }
            
            return instanceToPopulate;
        }
    }
}

Using the Mapping Code in Our Earlier Example

In the first code snippet, we saw how we fetched a SqlDataReader that represented a ClientEntity instance. In that function, we saw how the code mapped each of the SqlDataReader columns to each of the properties on our custom .NET ClientEntity class. Here is the revised code that now implements our mapping class.

public ClientEntity GetClientInfo(string clientid)
{
    try
    {
        ClientEntity result = null;
        SqlConnection connection;
        using (connection = new SqlConnection(ConnectionString))
        {
            connection.Open();
            SqlCommand cmd;
            using (cmd = new SqlCommand("sp_get_clientinfo", connection))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ClientId", clientid);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    reader.Read();
                    //Here is the revised mapping code that maps our 
                    //ClientEntity class to our SqlDataReader using Reflection and Generics
                    result = ReflectPropertyInfo.ReflectType<ClientEntity>(reader);
                }
            }
            connection.Close();
        }
        return result;
    }
    catch (Exception ex)
    {
        throw;
    }
}

The key line in the above code snippet is the line that maps our SqlDataReader to our custom .NET class.

result = ReflectPropertyInfo.ReflectType<ClientEntity>(reader);

This one single line takes care of all our mapping. We have hidden all that code away elsewhere so it doesn't clutter our database function code. This same technique can be used to map any number of custom .NET classes to their corresponding SqlDataReader. The use of Reflection means that we can inspect the classes at runtime for their associated properties, whilst the use of Generics ensures that this will work with any custom .NET class.

Summary

I use this technique in all my database functions. Once you have defined your class properties and decorated these accordingly with the corresponding column names, then all the mapping is taken care of. Feel free to leave a comment if you would like me to further elaborate on anything within this article.