Click here to Skip to main content
15,392,636 members
Articles / Programming Languages / C#
Article
Posted 16 Oct 2014

Tagged as

Stats

43.6K views
22 bookmarked

Generically Populate a Custom .NET Class from a SqlDataReader

Rate me:
Please Sign up or sign in to vote.
4.75/5 (16 votes)
16 Oct 2014CPOL3 min read
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.

C#
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:

C#
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.

C#
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:

C#
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.

C#
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.

C#
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Dominic Burford
Technical Lead Gold-Vision CRM
United Kingdom United Kingdom
I am a professional software engineer and technical architect with over twenty years commercial development experience with a strong focus on the design and development of web and mobile applications.

I have experience of architecting scalable, distributed, high volume web applications that are accessible from multiple devices due to their responsive web design, including architecting enterprise service-oriented solutions. I have also developed enterprise mobile applications using Xamarin and Telerik Platform.

I have extensive experience using .NET, ASP.NET, Windows and Web Services, WCF, SQL Server, LINQ and other Microsoft technologies. I am also familiar with HTML, Bootstrap, Javascript (inc. JQuery and Node.js), CSS, XML, JSON, Apache Cordova, KendoUI and many other web and mobile related technologies.

I am enthusiastic about Continuous Integration, Continuous Delivery and Application Life-cycle Management having configured such environments using CruiseControl.NET, TeamCity and Team Foundation Services. I enjoy working in Agile and Test Driven Development (TDD) environments.

Outside of work I have two beautiful daughters. I am also an avid cyclist who enjoys reading, listening to music and travelling.

Comments and Discussions

 
QuestionDataField decorator Pin
vervatovskis26-Dec-19 6:46
Membervervatovskis26-Dec-19 6:46 
QuestionError "Object must implement IConvertible" on pulling GUID field Pin
NatZol21-Mar-18 3:49
MemberNatZol21-Mar-18 3:49 
PraiseHandling Nullable types in a net class using reflection. Pin
George Sefcik28-Sep-17 17:05
professionalGeorge Sefcik28-Sep-17 17:05 
GeneralRe: Handling Nullable types in a net class using reflection. Pin
Dominic Burford29-Sep-17 4:30
professionalDominic Burford29-Sep-17 4:30 
QuestionInteresting! Experimentation Will Be Required Pin
Ross Cecil27-Oct-14 8:23
MemberRoss Cecil27-Oct-14 8:23 
QuestionI find this article profound, but specific Pin
Rollin Shultz21-Oct-14 3:11
MemberRollin Shultz21-Oct-14 3:11 
AnswerRe: I find this article profound, but specific Pin
Dominic Burford21-Oct-14 3:53
professionalDominic Burford21-Oct-14 3:53 
QuestionInteresting - questions on extensibility Pin
Member 1023856818-Oct-14 12:48
MemberMember 1023856818-Oct-14 12:48 
AnswerRe: Interesting - questions on extensibility Pin
Dominic Burford19-Oct-14 6:31
professionalDominic Burford19-Oct-14 6:31 
AnswerRe: Interesting - questions on extensibility Pin
Dominic Burford19-Oct-14 21:48
professionalDominic Burford19-Oct-14 21:48 
Question[My vote of 1] Performance Pin
TimMahy200317-Oct-14 23:02
MemberTimMahy200317-Oct-14 23:02 
AnswerRe: [My vote of 1] Performance Pin
Dominic Burford18-Oct-14 3:23
professionalDominic Burford18-Oct-14 3:23 
GeneralRe: [My vote of 1] Performance Pin
spamneggs25-Oct-14 7:10
Memberspamneggs25-Oct-14 7:10 
GeneralRe: [My vote of 1] Performance Pin
Dominic Burford25-Oct-14 8:28
professionalDominic Burford25-Oct-14 8:28 
GeneralRe: [My vote of 1] Performance Pin
Jörgen Andersson6-Nov-14 1:37
professionalJörgen Andersson6-Nov-14 1:37 
GeneralRe: [My vote of 1] Performance Pin
Dominic Burford6-Nov-14 1:53
professionalDominic Burford6-Nov-14 1:53 
GeneralRe: [My vote of 1] Performance Pin
Jörgen Andersson6-Nov-14 2:18
professionalJörgen Andersson6-Nov-14 2:18 
GeneralRe: [My vote of 1] Performance Pin
Dominic Burford6-Nov-14 2:47
professionalDominic Burford6-Nov-14 2:47 
GeneralRe: [My vote of 1] Performance Pin
Dominic Burford6-Nov-14 1:56
professionalDominic Burford6-Nov-14 1:56 
Questioncolumn names are the same as the class properties Pin
Isaac Koomson17-Oct-14 6:25
MemberIsaac Koomson17-Oct-14 6:25 
AnswerRe: column names are the same as the class properties Pin
Dominic Burford17-Oct-14 6:33
professionalDominic Burford17-Oct-14 6:33 
QuestionI like what you're trying to say, but ... Pin
Garth J Lancaster16-Oct-14 19:10
professionalGarth J Lancaster16-Oct-14 19:10 
AnswerRe: I like what you're trying to say, but ... Pin
Dominic Burford16-Oct-14 19:29
professionalDominic Burford16-Oct-14 19:29 
GeneralRe: I like what you're trying to say, but ... Pin
Garth J Lancaster16-Oct-14 19:35
professionalGarth J Lancaster16-Oct-14 19:35 
GeneralVery well written Pin
Sheepings16-Oct-14 4:00
professionalSheepings16-Oct-14 4:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.