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:

Presentation layer - most often the front-end

Business layer - represents data objects, which are stored in the lower layer and presented in the upper layer

Data access layer - provides access to the data storage, usually a database

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

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

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.

How can you divide your business layer from the data access layer, so when the DAL changes your business objects
stay intact?

How to create a DAL that can handle multiple database systems and connectors without making the slightest
changes to business layer?

How to make the DAL unique so it can handle every business object yet make it sofisticated enough
to enable the business layer to modify the behaviour when storing/loading objects?

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: