Introduction

In my spare time I write a lot of small applications for my friends and
family. The common thing for all these small applications is that they grab
some data from the database, display the data to the user and save the changes
back to database.

When I design programs, I break them into tiers. Most of the time I have
three logical tiers, which are User Interface Layer,
Business Logic and Data Access Layer. I
usually start with the class diagram, and only after I am happy with the class
diagram then I build the tables and stored procedures to access the data
from/to tables. User Interface is usually dictated by the user.

As I was building more and more of these small apps, I realized that I was
spending a lot of time on my Data Access Layer, since I had to create mappings
for populating the business objects from the tables in database, and since each
application had a different business logic and business objects, I ended up
writing Data Access Layer from scratch. So to make my life easier, I decided to
build a generic data access helper class that could be reused on all my
projects with little or no changes.

To be able to populate the object with the data from the database, the
business object needs to have public properties with GET and
SET methods. Then using reflection I could query the object for
the public properties and if the property name matched the name of the field in
the table, then the object would be populated with the data from that
field.

There were times when the properties and the fields in the database were
different or that the object could have more properties than the fields in
database, so I decided to have two ways of populating the object with the data
from the database:

Use a mapping class that would provide information of what property is
mapped to what field, and

Decorate the properties with a custom attribute to show to what field the
property is mapped.

The first step is to build a mapping class. This is a very simple class that
holds a collection of a strings. The first string would be in the format
property=field, where property is the name of the
property of the object, and the field is the name of the field in
database. So the mapping class, after implementation, looks like this:

///<summary>/// This class holds information about mapping a database field to a
/// object property.
///</summary>publicclass Mapper
{
private List<string> mMappingInfo;
///<summary>/// Default constructor
///</summary>public Mapper()
{
mMappingInfo = new List<string>();
}
///<summary>/// Add mapping information. This method can be used to add more than
/// one mapping at a time.
/// You could use it like: mapper.Add("property1=field1",
/// "property2=field2", "property3=field3", ...)
///</summary>///<paramname="mappings">mapping information in format
// "[property name]=[field name]"</param>
publicvoid Add(paramsstring[] mappings)
{
foreach (string map in mappings)
mMappingInfo.Add(map);
}
///<summary>/// Return mapping information held in this class as string array
///</summary>publicstring[] MappingInformation
{
get
{
string[] mappings = newstring[mMappingInfo.Count];
mMappingInfo.CopyTo(mappings);
return mappings;
}
}
///<summary>/// Indexer property. By providing the name it returns the mapping info
/// for that property.
/// If the mapping information for the provided property does not exist,
/// the indexer
/// return null.
/// You could use it like: string mapInfo = mapper["property1"];
///</summary>///<paramname="propertyName">the name of the property to
return/// mapping information</param>///<returns>mapping information for the property
provided</returns>
public string this[string propertyName]
{
get
{
foreach (string map in mMappingInfo)
{
string[] spplitedString = map.Split('=');
if (spplitedString[0] == propertyName)
return map;
}
return null;
}
}
/// <summary>
/// Another indexer property. This property returns mapping information,
/// that is stored in the list, in order.
/// </summary>
/// <paramname="index">the index</param>
/// <returns>mapping information</returns>
public string this[int index]
{
get
{
if (index <mMappingInfo.Count)returnmMappingInfo[index];elsereturnnull;}}///<summary>
/// Get the property name from the mapping information
/// </summary>
/// <paramname="map">mapping information</param>
/// <returns>the name of the property from the provided mapping
/// information</returns>
public static string GetProperty(string map)
{
// split the mapping info and return the name of the property
string[] spplitedString = map.Split('=');
return spplitedString[0];
}
/// <summary>
/// Get the field name from the mapping information
/// </summary>
/// <paramname="map">mapping information</param>
/// <returns>the name of the field from the provided mapping
/// information</returns>
public static string GetField(string map)
{
// split the mapping info and return the name of the field
string[] spplitedString = map.Split('=');
return spplitedString[1];
}
}

The next thing to implement is the custom attribute that will be used to map
properties with the fields in database. Again this is a very simple attribute
that will be used to store the name of the field. The implementation looks
like:

///<summary>/// Specifies the name of the field in the table that the property maps to
///</summary>[AttributeUsage(AttributeTargets.Property)]
publicclass DBFieldAttribute : Attribute
{
privatestring mFieldName;
///<summary>/// constructor
///</summary>///<paramname="fieldName">name of the field that the
property will
/// be mapped to</param>public DBFieldAttribute(string fieldName)
{
mFieldName = fieldName;
}
publicstring FieldName
{
get { return mFieldName; }
}
}

Since this attribute can only be used on properties, the class is decorated
with AttributeUsage(AttributeTargets.Property) attribute.

And now finally the only thing left is the helper class that will be used to
retrieve data from the database. The implemented class looks like:

publicclass DBHelper
{
///<summary>/// Generic method. Gets an object of type T from the data reader. It
/// uses mapping information provided to read a field from the reader,
/// and gets the property name and sets the value of the property with
/// the data which are held in database field
///</summary>///<typeparamname="T>The type of object to be
instantiated</typeparam>
/// <paramname="rdr">Data Reader where the data will be
read from</param>
/// <paramname="mappings">mapping
information</param>
/// <returns>an instance of type T with the properties populated from
/// database</returns>
private static T GetItemFromReader<T>(IDataReader rdr, Mapper mappings)
where T : class
{
Type type = typeof(T);
T item = Activator.CreateInstance<T>(); // create an instance of the
// type provided
foreach(string map in mappings.MappingInformation)
{
// for each mapping information
string property = Mapper.GetProperty(map);
string field = Mapper.GetField(map);
PropertyInfo propInfo = type.GetProperty(property);//get the property
//by name
if (Convert.IsDBNull(rdr[field])) // data in database is null, so do
// not set the value of the property
continue;
if (propInfo.PropertyType == rdr[field].GetType())
// if the property and database field are the same
propInfo.SetValue(item, rdr[field], null); // set the value of
// property
else
{
// change the type of the data in table to that of property and
// set the value of the property
propInfo.SetValue(item, Convert.ChangeType(rdr[field],
propInfo.PropertyType), null);
}
}
return item;
}
/// <summary>
/// Generic method. Gets an object of type T from the data reader. It uses
/// attribute information
/// applied to a property to read a field from the reader, and gets the
/// property name and sets
/// the value of the property with the data which are held in database field
/// </summary>
/// <typeparamname="T">The type of object to be
instantiated</typeparam>
/// <paramname="rdr">Data Reader where the data will be read
from</param>
/// <returns>an instance of type T with the properties populated from
/// database</returns>
private static T GetItemFromReader<T>(IDataReader rdr) where T : class
{
Type type = typeof(T);
T item = Activator.CreateInstance<T>();
PropertyInfo[] properties = type.GetProperties();
foreach (PropertyInfo property in properties)
{
// for each property declared in the type provided check if the
// property is decorated with the DBField attribute
if (Attribute.IsDefined(property, typeof(DBFieldAttribute)))
{
DBFieldAttribute attrib = (DBFieldAttribute)Attribute.
GetCustomAttribute(property, typeof(DBFieldAttribute));
if (Convert.IsDBNull(rdr[attrib.FieldName]))
// data in database is null, so do not set the value of the
// property
continue;
if (property.PropertyType == rdr[attrib.FieldName].GetType())
// if the property and database field are the same
property.SetValue(item, rdr[attrib.FieldName], null);
// set the value of property
else
{
// change the type of the data in table to that of property
// and set the value of the property
property.SetValue(item, Convert.ChangeType(
rdr[attrib.FieldName], property.PropertyType), null);
}
}
}
return item;
}
/// <summary>
/// Get one object from the database by using the attribute information
/// </summary>
/// <typeparamname="T">the type of object the collection will
hold</typeparam>
/// <paramname="command">DbCommand that is used to read data
from the
/// database</param>
/// <returns>populated object from the database</returns>
public static T ReadObject<T>(IDbCommand command) where T : class
{
IDataReader reader = command.ExecuteReader();
if (reader.Read())
return GetItemFromReader<T>(reader);
else
return default(T);
}
/// <summary>
/// Get one object from the database by using the mapping information
/// provided by Mapper class
/// </summary>
/// <typeparamname="T">the type of object the collection will
hold</typeparam>
/// <paramname="command">DbCommand that is used to read data
from the
/// database</param>
/// <returns>populated object from the database</returns>
public static T ReadObject<T>(IDbCommand command, Mapper mappingInfo)
where T : class
{
IDataReader reader = command.ExecuteReader();
if (reader.Read())
return GetItemFromReader<T>(reader, mappingInfo);
else
return default(T);
}
/// <summary>
/// Get a collection of objects from the database by using the attribute
/// information
/// </summary>
/// <typeparamname="T>the type of object the collection will
hold</typeparam>
/// <param name="command">DbCommand that is used to read data
from the
/// database</param>
/// <returns>a collection of populated objects from the
database</returns>
public static List<T> ReadCollection<T>(IDbCommand command) where T
: class
{
List<T> collection = new List<T>();
IDataReader reader = command.ExecuteReader();
while (reader.Read())
{
T item = GetItemFromReader<T>(reader);
collection.Add(item);
}
return collection;
}
/// <summary>
/// Get a collection of objects from the database by using the mapping
/// information provided
/// by Mapper class
/// </summary>
/// <typeparamname="T">the type of object the collection will
hold</typeparam>
/// <paramname="command">DbCommand that is used to read data
from the
/// database</param>
/// <returns>a collection of populated objects from the
database</returns>
public static List<T> ReadCollection<T>(IDbCommand command,
Mapper mappingInfo) where T : class
{
List<T> collection = new List<T>();
IDataReader reader = command.ExecuteReader();
while (reader.Read())
{
T item = GetItemFromReader<T>(reader, mappingInfo);
collection.Add(item);
}
return collection;
}
}

DBHelper is a static class which means that it does not need to
be instantiated and all the methods are static as well. It is generic in a
sense that you provide the type of the object to be loaded from the database,
and the class creates the objects and populates its public properties (the ones
that either are decorated with the DBField attribute or mapping
information is provided) from the data read from the table.

As you can see from the code above we are using constraints on generics,
which basically means that the generic type T has to be an object (reference
type) and can't be of value type (primitives like int, float, byte, and so on,
or struct which is also of value type). The above class also uses reflection to
check if the properties have the DBField set, and if yes then the
code reads the attribute, gets the field name from the attribute and reads the
data from the table.

Using the code

To use the class above is very easy, as you can see from the following
example. Suppose we have a table that contains persons' details and its
definition is like the below:

As you can see from the code of class Person, public properties
are decorated with the attribute DBField. Each attribute
corresponds with the field name in the table. Now to read the data from the
table and return populated objects we would use the DBHelper class
like this:

As you can see from the examples above, the DBHelper class can
be used easily on different projects to get the data from the database and
convert them to objects in a convenient way. So the first step is to declare
the appropriate class with default constructor (parameterless constructor) and
provide public properties with get/set methods, then either use DBField
attribute on properties to map them to database fields, or use Mapper class to
provide mapping information.

This class could be extended to support storing object to the database as
well.

History

I have changed the source code, so now the DBHelper class can be used to
insert/update business objects to database. I have also created a small demo
program (attached to source solution).

I don't think you gain anything in speed when accessing data (It should be equal or slightly faster than using DataSets). The biggest gain as you say is from a coding approach, since it reduces quite a bit the amount of code to be written, hence less errors and more productive.

You will probably lose some speed because you are using reflection. I have a similiar library that uses XML files to map result sets to objects. The speed boost will come from using a DynamicMethod and actually "compiling" the reflective calls.

Great code thanks! Does anyone know what the performance hit will be compared to using the readers explicitly without reflection? A random browse on the internet reveals mixed opinions on this.. If the performance hit is small, why is this entitled "*Very lightweight* DAL.."?