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();
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
{
[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
{
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);
foreach (PropertyInfo pi in propertyInfos)
{
DataFieldAttribute[] datafieldAttributeArray = pi.GetCustomAttributes
(typeof(DataFieldAttribute), false) as DataFieldAttribute[];
if (datafieldAttributeArray != null && datafieldAttributeArray.Length == 1)
{
DataFieldAttribute dfa = datafieldAttributeArray[0];
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();
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.
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.