Click here to Skip to main content
15,867,325 members
Articles / Database Development / SQL Server
Article

Very Lightweight Data Access Layer in C# and .NET 2.0

Rate me:
Please Sign up or sign in to vote.
4.11/5 (31 votes)
4 Apr 2007CPOL5 min read 200.2K   2.4K   126   62
Very Lightweight Data Access Layer in C# and .NET 2.0

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:

C#
/// <summary>
/// This class holds information about mapping a database field to a 
/// object property.
/// </summary>
public class 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>
    /// <param name="mappings">mapping information in format 
    //     "[property name]=[field name]"</param>
    public void Add(params string[] mappings)
    {
        foreach (string map in mappings)
            mMappingInfo.Add(map);
    }


    /// <summary>
    /// Return mapping information held in this class as string array
    /// </summary>
    public string[] MappingInformation
    {
        get
        {
            string[] mappings = new string[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>
    /// <param name="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>
    /// <param name="index">the index</param>
    /// <returns>mapping information</returns>
    public string this[int index]
    {
        get
        {
            if (index < mMappingInfo.Count)
                return mMappingInfo[index];
            else
                return null;
        }
    }

    /// <summary>
    /// Get the property name from the mapping information
    /// </summary>
    /// <param name="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>
    /// <param name="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:

C#
/// <summary>
/// Specifies the name of the field in the table that the property maps to 
/// </summary>
[AttributeUsage(AttributeTargets.Property)]
    public class DBFieldAttribute : Attribute
    {
        private string mFieldName;

        /// <summary>
        /// constructor
        /// </summary>
        /// <param name="fieldName">name of the field that the 

property will
        /// be mapped to</param>
        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:

C#
public class 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>
    /// <typeparam name="T>The type of object to be 

instantiated</typeparam>
    /// <param name="rdr">Data Reader where the data will be 

read from</param>
    /// <param name="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>
/// <typeparam name="T">The type of object to be 

instantiated</typeparam>
/// <param name="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>
/// <typeparam name="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>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>
/// <typeparam name="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>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>
/// <typeparam name="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>
/// <typeparam name="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, 
    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:

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

SQL
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:

SQL
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:

SQL
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).

License

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


Written By
Software Developer (Senior)
United Kingdom United Kingdom
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
BugPossible Memory Leak in public static List<T> ReadCollection<T>(IDbCommand command) where T function Pin
neil_mahaseth4-Mar-15 3:41
neil_mahaseth4-Mar-15 3:41 
GeneralRe: Possible Memory Leak in public static List<T> ReadCollection<T>(IDbCommand command) where T function Pin
Fitim Skenderi5-Mar-15 21:37
professionalFitim Skenderi5-Mar-15 21:37 
GeneralMy vote of 5 Pin
hari111r28-Nov-12 16:46
hari111r28-Nov-12 16:46 
GeneralMy vote of 5 Pin
Kanasz Robert25-Sep-12 22:40
professionalKanasz Robert25-Sep-12 22:40 
QuestionMapper Class - This Functions Pin
Jay D Morlan18-Jul-12 7:56
Jay D Morlan18-Jul-12 7:56 
AnswerRe: Mapper Class - This Functions Pin
Jay D Morlan18-Jul-12 9:26
Jay D Morlan18-Jul-12 9:26 
QuestionReally Article Pin
Muhammed Yaseen21-Nov-11 21:56
Muhammed Yaseen21-Nov-11 21:56 
GeneralMy vote of 1 Pin
Dino77714-Nov-11 3:19
Dino77714-Nov-11 3:19 
GeneralGet the last ID of an inserted record Pin
obinna_eke2-Nov-10 6:33
obinna_eke2-Nov-10 6:33 
GeneralRe: Get the last ID of an inserted record Pin
obinna_eke2-Nov-10 21:35
obinna_eke2-Nov-10 21:35 
GeneralNested Object Pin
sky391317-Sep-10 23:44
sky391317-Sep-10 23:44 
GeneralRe: Nested Object Pin
Fabrizio Magosso18-Oct-10 23:41
Fabrizio Magosso18-Oct-10 23:41 
GeneralMy vote of 5 Pin
pp_williams7-Jul-10 14:42
pp_williams7-Jul-10 14:42 
GeneralOMG! Pin
NandoMan3-Jun-10 11:32
NandoMan3-Jun-10 11:32 
GeneralRe: OMG! Pin
NandoMan3-Jun-10 11:38
NandoMan3-Jun-10 11:38 
GeneralIsDirty on object Pin
Nic_Roche18-Aug-09 10:35
professionalNic_Roche18-Aug-09 10:35 
GeneralGenerics Pin
Member 460984425-Mar-09 18:28
Member 460984425-Mar-09 18:28 
GeneralRe: Generics Pin
Fitim Skenderi25-Mar-09 23:28
professionalFitim Skenderi25-Mar-09 23:28 
GeneralWorking with Stored Procedure Output Parameters Pin
bcox13-Mar-09 2:17
bcox13-Mar-09 2:17 
GeneralRe: Working with Stored Procedure Output Parameters Pin
Fitim Skenderi25-Mar-09 23:25
professionalFitim Skenderi25-Mar-09 23:25 
GeneralEnhancements/extensions to this framework Pin
technicaltitch8-Oct-08 1:42
technicaltitch8-Oct-08 1:42 
Generallicense Pin
MonsurAhmed31-Jul-08 17:34
MonsurAhmed31-Jul-08 17:34 
GeneralRe: license Pin
Fitim Skenderi31-Jul-08 22:52
professionalFitim Skenderi31-Jul-08 22:52 
Generalmy wrapper Pin
Marcos Vazquez28-Feb-08 5:40
Marcos Vazquez28-Feb-08 5:40 
GeneralRe: my wrapper Pin
Fitim Skenderi31-Jul-08 22:57
professionalFitim Skenderi31-Jul-08 22:57 

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.