Data Access Layer
Managed architecture for .Net platform
Matej Tomčík

DOWNLOAD

Data Access Layer example in C#

Behind the concept

The term Data Access Layer comes from the three tier architecture model which divides development steps for any data driven application into three tiers:

This model is considered to be the best approach when a developer needs to create an application binded to data objects. However, there are many ways how to create such a solution for your project and if you prefer the DIY (Do It Yourself) way, you may face some serious problems when implementing this model. Goal of this know-how article is to give an alternate view on this subject, providing a different Data Access Layer implementation.

Issues you may face

Many implementations focus on the reusability. And this is where things start to get complicated. Let's say we want to keep information about orders, so we create a class named Order

using System;
 
namespace ConsoleApplication
{
  public class Order
  {
    private uint id;
    private DateTime time;
    private double amount;
 
    public uint Id
    {
      get { return id; }
    }
 
    public DateTime Time
    {
      get { return time; }
      set { time = value; }
    }
 
    public double Amount
    {
      get { return amount; }
      set { amount = value; }
    }
  }
}
 

The Order resides in the business tier, because it is a business object. When you load this object from a database, you display it to the user via a form, an user control or something else. But how do you define the process of loading business object from the data tier (your storage) and vice versa (updating/inserting your object into the database)? There are many ways how one can do this, choosing the right one to fit your needs is essential.

Let's examine various approaches to this solution. From my own experience, I have done this in 2 different ways and it never worked as I expected. First time I didn't use business objects at all. Data from the user input (WinForms) were stored into the database through a bundle object and Xml documents which specified how was the data presented in the database. This gave me sense at the beginning, because I had no idea that the project I was working on can grow so enormously. It escalated from simple inserts using 5 parameters, to complex SQL queries taking seconds to complete. I was forced to make it easier so I started putting some logic into the business objects. However, the project was still using both Xml-to-MySQL serializers and business objects merged with data access layer. What I have learned from this fiasco was that you always have to expect that things can and most likely will get very complicated unless you are the one who is designing your own project. When there is an end user involved in the process, he can change his ideas anytime making your effort useless.

Next project I was working on can be considered as an example of a working three tier architecture. However, the data access layer was still embedded in the business tier, making it more of a two and a half tier architecture. The reason for this is very simple and many of you may understand why did I choose to go this way. Imagine you build your project on one database type, in my case it was MySQL, and you don't plan to change this in the future, even if there was an alternative database system (free) which could compete with MySQL. The need for a separate data access layer was irrelevant to me at the time, baceuse I was thinking about DAL as some kind of a tier you can rewrite when there is a dramatic change in you database system or you decide to go on with a different storage. But since it is very unlikely for the creators of MySQL to make such changes, that would force you to rewrite your data access layer and the idea of switching to a different database is so improbable that the decision to make your DAL static and bind it to a specific database system is understandable. So why I didn't like the the way I modeled the business objects? Each such object derived from a base class DbObject and had to implement methods to insert, update and delete the object in the database. See code below

// Simplified, no error checking
public override void Update(OdbcCommand com)
{
  com.CommandText = "UPDATE order SET time=?,amount=? WHERE id=?";
  com.Parameters.AddWithValue("time", time);
  com.Parameters.AddWithValue("amount", amount);
  com.Parameters.AddWithValue("id", id).OdbcType = OdbcType.BigInt;
  com.ExecuteNonQuery();
}

As you can see it is a very straightforward approach. All the object properties are set as parameters and the SQL differs only in the table and field names. But as you make more of these objects you start to realize that you are basically using the same principles all over again. And changing something globally would take a great amount of time. This is where you have to stop and understand what have you done wrong and why. Most likely you end up with a conclusion that you wanted to make your database objects available in your application in an object-oriented way but you stuck at the data access layer.

For me, the answer is clear:

Reflection and attributes

Why not to take advantage of .Net reflection and class attributes? The same way you declare your class to be serializable you mark an object with an attribute, telling the DAL that the object is designed to be used as a data object between your application and the database. Then, the DAL will take your object, enumerate its fields and loads data from or into it. It sounds so simple and it really is. If you keep the DAL design as straightforward as possible, and apply the same principles to your business objects, you can build your business logic upon it and finally have a robust data access layer.

So I made three attributes: DataObject, DataField and NonDataField. First one describes the business object and specifies how will the DAL access it's storage. For example, I define the table name for orders and can also define insert/update/delete procedure names (optional). DataField specifies additional attributes like if the field is primary or autoincrement. NonDataField is used to mark a variable which is not intended to be stored in the database. In my example the only change I had to make is to add two attributes to my class:

[Dal.DataObject("order")]
public class Order
{
  [Dal.DataField(IsPrimary = true, IsAutoIncrement = true)]
  private uint id;

And then build a simple program to connect to a database, create an instance of the DAL storage and then store the object into the database:

using System;
using System.Data.Odbc;
 
namespace ConsoleApplication
{
  class Program
  {
    static void Main(string[] args)
    {
      // Create an ODBC connection
      using (OdbcConnection connection = new OdbcConnection("DSN=test"))
      {
        // Connect to the database
        connection.Open();
        // Create DAL storage engine
        Dal.OdbcMySqlStorage storage = new Dal.OdbcMySqlStorage();
        // Create an order
        Order order = new Order() { Time = DateTime.Now, Amount = 250.65 };
        // Save the order into the database
        storage.SaveObject(order, connection.CreateCommand());
      }
    }
  }
}

The order in the example above is serialized into the database table named "order" and assigned a new ID of 1. To load the object back from the database, you will simply call:

// Load order from the database
Order order = storage.LoadObject<Order>(connection.CreateCommand(), 1);