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:
public class Mapper
{
private List<string> mMappingInfo;
public Mapper()
{
mMappingInfo = new List<string>();
}
public void Add(params string[] mappings)
{
foreach (string map in mappings)
mMappingInfo.Add(map);
}
public string[] MappingInformation
{
get
{
string[] mappings = new string[mMappingInfo.Count];
mMappingInfo.CopyTo(mappings);
return mappings;
}
}
return
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;
}
}
public string this[int index]
{
get
{
if (index < mMappingInfo.Count)
return mMappingInfo[index];
else
return null;
}
}
public static string GetProperty(string map)
{
string[] spplitedString = map.Split('=');
return spplitedString[0];
}
public static string GetField(string map)
{
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:
[AttributeUsage(AttributeTargets.Property)]
public class DBFieldAttribute : Attribute
{
private string mFieldName;
property will
public DBFieldAttribute(string fieldName)
{
mFieldName = fieldName;
}
public string 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:
public class DBHelper
{
instantiated</typeparam>
read from</param>
information</param>
private static T GetItemFromReader<T>(IDataReader rdr, Mapper mappings)
where T : class
{
Type type = typeof(T);
T item = Activator.CreateInstance<T>();
foreach(string map in mappings.MappingInformation)
{
string property = Mapper.GetProperty(map);
string field = Mapper.GetField(map);
PropertyInfo propInfo = type.GetProperty(property);
if (Convert.IsDBNull(rdr[field]))
continue;
if (propInfo.PropertyType == rdr[field].GetType())
propInfo.SetValue(item, rdr[field], null);
else
{
propInfo.SetValue(item, Convert.ChangeType(rdr[field],
propInfo.PropertyType), null);
}
}
return item;
}
instantiated</typeparam>
from</param>
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)
{
if (Attribute.IsDefined(property, typeof(DBFieldAttribute)))
{
DBFieldAttribute attrib = (DBFieldAttribute)Attribute.
GetCustomAttribute(property, typeof(DBFieldAttribute));
if (Convert.IsDBNull(rdr[attrib.FieldName]))
continue;
if (property.PropertyType == rdr[attrib.FieldName].GetType())
property.SetValue(item, rdr[attrib.FieldName], null);
else
{
property.SetValue(item, Convert.ChangeType(
rdr[attrib.FieldName], property.PropertyType), null);
}
}
}
return item;
}
hold</typeparam>
from the
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);
}
hold</typeparam>
from the
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);
}
hold</typeparam>
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;
}
hold</typeparam>
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:
and a class Person that looks like the code below:
public class Person
{
private int mID;
private string mName;
private string mSurname;
private DateTime mDob;
private string mProfession;
public Person()
{
}
[DBField("ID")]
public int ID
{
get { return mID; }
set { mID = value; }
}
[DBField("Name")]
public string Name
{
get { return mName; }
set { mName = value; }
}
[DBField("Surname")]
public string Surname
{
get { return mSurname; }
set { mSurname = value; }
}
[DBField("DOB")]
public DateTime DateOfBirth
{
get { return mDob; }
set { mDob = value; }
}
[DBField("Profession")]
public string Profession
{
get { return mProfession; }
set { mProfession = value; }
}
public int CalculateAge()
{
int age = DateTime.Now.Year - mDob.Year;
return age;
}
}
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:
public List<Person> GetAllPersonsFromDB()
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand("Select * from Persons order
by
_Surname", connection);
connection.Open();
List<Person> persons =
DBHelper.ReadCollection<Person>(command);
return persons;
}
}
or, if you need to retrieve a particular object, you could use the code
below:
public Person GetPersonByID(int id)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand("Select * from Persons where
ID
_= @ID", connection);
SqlParameter param = command.Parameters.Add("@ID",
SqlDbType.Int);
param.Value = id;
connection.Open();
Person person = DBHelper.ReadObject<Person>(command);
return person;
}
}
If for some reason you need to get data from another table where the fields
are named differently from the attributes, we need to provide mapping
information by using Mapper class to the DBHelper.
Example: If we need to read data from another table and the table definition
is as follows:
To read the data from the above table, we need to provide mapping
information to DBHelper as follows:
public List<Person> GetAllPersonsFromDB()
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand("Select * from Persons order
by
_Surname", connection);
Mapper mapper = new Mapper();
// Provide the mapping information in format: "[Property Name]=
// [Field Name]" for the appropriate fields
mapper.Add("ID=ID", "Name=FirstName",
"Surname=Surname",
_"Profession=Profession",
"DateOfBirth=DateOfBirth");
connection.Open();
List<Person> persons =
DBHelper.ReadCollection<Person>(command,
_mapper);
return persons;
}
}
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).
