Click here to Skip to main content
13,734,201 members
Click here to Skip to main content
Add your own
alternative version


176 bookmarked
Posted 15 Aug 2013
Licenced CPOL

Self-Synchronized Data Access Layer: Part I

, 26 Nov 2013
Rate this:
Please Sign up or sign in to vote.
In this article we are discover the power of generics in C#


A data access layer can be an important part of a software application. Applications almost always need access to data and we should design separated layer for this purpose. Now we want to the use the generics in C# and design really power full data access layer for use in all applications without requiring any changes. for more information about Generics in C# you can follow of these links:

Of course I should be noted, that all of these ideas have been implemented in the Entity Framework the best possible way, but the problem is that the Entity Framework an not be used with the some DBMS same as MS Access. And the other hand is aimed at showing the power of generics and facilities in programming.


After reading current version of this article you can read new version from here at new version I solved some problems same as sql injection and multiple database support. 

Data Access Layer Overview

In this article we are introduce our data access layer architect follow of below picture, as you see in the picture we have two top level name space Model and DataAccessHandler , Model is a class view of database and DataAccessHandler is a wrapper for the ADO.Net to the building communication with DBMS.


Each table in our database should have a implemented class in Model with same field names and field data types. Pay attention to, each class must inherit from IEntity follow of below picture, for example in this sample Personnel is a class view of tblPersonnel in database and it inherited from IEntity.



Context is implemented with singleton design pattern as a model container, using this container to insert, update and delete the entities makes Collections is synchronized with the database, Thus the changes is not require update the Collections again and it can be reduce a cost of data access layer. Insert, Update and Delete methods in IEntity are implemented to consider this requirement. Please pay attention the follow of codes:


public void AddPersonel(Mode.Personnel personnel)

public void UpadtePersonel(Mode.Personnel personnel)

public void RemovePersonel(Mode.Personnel personnel)


In this project our top level name space is DataAccessHandler so this container has DataAccessAdapter, IEntity, Entities, SqlCommand enumerator and PrimaryKey data-structure.

Class Description Name space
IEntity It is a interface for our entities in entity model. for example if we want to the use of this DAL in our Personnel Management System the personnel object is a target entity should be implement this interface. Each entity contains a method to insert and update and remove and a collection of own, if will be inherited from IEntity DataAccessHandler
Entities t is a collection of entities, this is a generic list and can be really useful for us , so each entity has its own list. DataAccessHandler.Collection
PrimaryKey This property is used to specify primary key for each entity. it is a structure with two element Name and Value, Name is a primary key name and value is a primary key value DataAccessHandler.IEntity
DataAccessAdapter It is a wrapper to the working with ADO.Net objects, so we expect this object to communicate with the our database.(Execute the sql commands same as insert, update, delete and select on the target database). this object is to do your job requires a sqlcommand enumerator. DataAccessHandler
SqlCommand This is a enumerator object and used to specify our DAL sql command to transaction with database. DataAccessHandler

DataAccessAdapter Backbone Methods

Initialize Method:

This method used for initialize DataAccessAdapter connection string and password for connect to the database. we used this method only for once.

     static void Main(string[] args) 
    #region Initialize DataAccessAdapter
    string datasource = @"../../../Testdb.mdb";
    string password = "";
    DataAccessHandler.DataAccessAdapter.Initialize(datasource, password);

DoEntity<T> Method:

This method is a generic method and has 3 parameter first parameter get the entity and second parameter get the sqlCommand (Insert, Update, Delete, or Select), at the finally third parameter get the where condition however it is not necessary and usually used with Update, Delete or Select commands. for example see the Insert method of IEntity class:

    public virtual bool Insert(T entity)
    bool result = DataAccessAdapter.DoEntity(entity, SqlCommand.Insert, "");
    if (result)
    return result;

GenerateCommand <T> Method:

This method is a generic method and same as DoEntity method has 3 parameter too. But this method should be decide about sqlCommand, if the sqlCommand is a insert it should be call InsertCommand and if it is a update command it should be call UpdateCommand and, etc.

public static string GenerateCommand<T>(SqlCommand sqlCommand,T entity,
   string where = "        ")where T:IEntity<T>
    string commandText = string.Empty;
    switch (sqlCommand)
        case SqlCommand.Insert:
            commandText = InsertCommand(entity);
        case SqlCommand.Update:
            if(where == "")
                commandText = UpdateCommand(entity);
            else if(where != "")
                commandText = UpdateCommand(entity, where);
        case SqlCommand.Select:
            if (where == "")
                commandText = selectCommand(entity);
            else if (where != "")
                commandText = selectCommand(entity, where);
        case SqlCommand.Delete:
            if (where == "")
                commandText = deleteCommand(entity);
            else if (where != "")
                commandText = deleteCommand(entity, where);
    return commandText;


In this method we should generate SQL Insert command for entity parameter, _perfix and _suffix are used when the entity table name in database has prefix or suffix same as tblPersonel, The most important part of this method is GetInfo function, other methods (Update,Delete,Select) are similar this method so we don't explain these methods.

private static string InsertCommand<T>(T entity) where T:Entity<T>
    string Insert = "INSERT INTO " + _perfix + ((Entity<T>)entity).EntityName + _suffix;
    string columns = "(";
    string values = "VALUES(";
    Dictionary<string, object> infos = GetInfo(entity);
    //Read Column Names
    foreach (var item in infos)
        if (item.Value != null && item.Value != "" && 
               !entity.PrimaryKey.Exists(p => p.Name == item.Key))
            columns += item.Key + ",";
                values += Formatting(item.Value.ToString()) + ",";
    columns = columns.Remove(columns.Length - 1, 1)+") ";
    values = values.Remove(values.Length - 1, 1) +") ";
    Insert += columns + values;
    return Insert;

GetInfo Method:

This method used Reflection in .NET library for getting properties of entities in Model , we remind each table in database has a class in the Model and called entity so my mean when I say, getting properties of entities is properties of classes in the Model. at the follow of code you can see properties of Personnel entity in this sample.

Note: The Primary-key of entity is marked with this attribute [DataObjectFieldAttribute(true, true, false)]

namespace Mode
    public class Personnel:DataAccessHandler.IEntity<Personnel>
        #region Table Fields
        int _id;
        [DataObjectFieldAttribute(true, true, false)]//Primary key attribute 
        public int ID
            get { return _id; }
            set { _id = value; }
        string _fName;
        [DisplayName("First Name")]
        public string FName
            get { return _fName; }
            set { _fName = value; }
        string _lName;
        [DisplayName("Last Name")]
        public string LName
            get { return _lName; }
            set { _lName = value; }
        #region Initialize
        public Personnel() { }

Another important note about this method are the conditions. first condition check the item is the property or no and second condition check the item is readable, is serializable and accessible follow of access modifier or no and the final condition remove the PrimaryKey and EntityName at this scenario !! Why ? think about Wink | <img src= 

public static Dictionary<string, object> GetInfo<T>(T entity) where T : IEntity<T>
        Dictionary<string, object> values = new Dictionary<string, object>();
        foreach (var item in ((IEntity<T>)entity).GetType().GetProperties())
            if (item.MemberType == System.Reflection.MemberTypes.Property)
                if (item.CanRead && item.PropertyType.IsSerializable && item.PropertyType.IsPublic)
                    if (item.Name == "PrimaryKey" || item.Name == "EntityName")
                    else if (item.PropertyType.IsEnum)
                        values.Add(item.Name, item.GetValue(entity, null).GetHashCode());
                        values.Add(item.Name, item.GetValue(entity, null));

        IsSqlInjection(values);//we can improve this method
        return values;
    catch (Exception)


What is IsSqlInjection() Method

For answer this question we should answer what is sql injection attacks but I prefer redirect you to the here for getting more information about it. so this method prevent them, however I know it is not a really secure method for this purpose but we can improve it and I create it to the development at the future.

internal static void IsSqlInjection(Dictionary<string,object> source)
    foreach (var item in source)
        if (item.Value != null)
            if (item.Value.ToString().Contains('@') || item.Value.ToString().Contains('=') || 
                throw new Exception("It is not secure using");

IEntity<T> Interface

IEntity<T> is a generic interface for the entities in the model. each entity from the model can be inherit from IEntity<T> and IEntity<T> have a some basic methods same as Insert, Update, Delete, Select and Load. In the following we will review IEntity<T> methods sequence diagram.


Update Method Sequence Diagram


Insert Method Sequence Diagram


Delete Method Sequence Diagram

I hope that will be useful... ;)


  • Revision: 13/Aug/2013
  • Revision: 16/Aug/2013


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


About the Author

Aydin Homay
Software Developer (Senior) CM S. A.
Germany Germany
I am an eternal programmer.

You may also be interested in...


Comments and Discussions

QuestionGreat article Pin
23-Apr-15 14:10
member23-Apr-15 14:10 
AnswerRe: Great article Pin
Aydin Homay25-Apr-15 3:09
professionalAydin Homay25-Apr-15 3:09 
GeneralRe: Great article Pin
27-Apr-15 9:53
member27-Apr-15 9:53 
GeneralRe: Great article Pin
Aydin Homay28-Apr-15 5:20
professionalAydin Homay28-Apr-15 5:20 
GeneralMy vote of 1 Pin
s2bert12-Feb-15 4:42
members2bert12-Feb-15 4:42 
GeneralRe: My vote of 1 Pin
Aydin Homay13-Feb-15 0:23
professionalAydin Homay13-Feb-15 0:23 
GeneralRe: My vote of 1 Pin
s2bert13-Feb-15 0:57
members2bert13-Feb-15 0:57 
GeneralRe: My vote of 1 Pin
Aydin Homay13-Feb-15 1:04
professionalAydin Homay13-Feb-15 1:04 
GeneralRe: My vote of 1 Pin
surfer_sk8er_boy4-Sep-18 0:25
membersurfer_sk8er_boy4-Sep-18 0:25 
GeneralMy vote of 5 Pin
yenercoder6-Sep-14 3:02
memberyenercoder6-Sep-14 3:02 
QuestionThere are already many frameworks out there that already handle tis Pin
26-Nov-13 6:39
member26-Nov-13 6:39 
AnswerRe: There are already many frameworks out there that already handle tis Pin
Aydin Homay26-Nov-13 7:46
professionalAydin Homay26-Nov-13 7:46 
GeneralRe: There are already many frameworks out there that already handle tis Pin
JV999927-Nov-13 1:52
memberJV999927-Nov-13 1:52 
QuestionNice article Pin
chirag solanki 9085123-Nov-13 6:57
memberchirag solanki 9085123-Nov-13 6:57 
AnswerRe: Nice article Pin
Aydin Homay25-Nov-13 19:07
professionalAydin Homay25-Nov-13 19:07 
QuestionN-Tier, Layers Pin
kiquenet.com18-Nov-13 20:23
memberkiquenet.com18-Nov-13 20:23 
AnswerRe: N-Tier, Layers Pin
Aydin Homay20-Nov-13 4:58
professionalAydin Homay20-Nov-13 4:58 
AnswerRe: N-Tier, Layers Pin
Aydin Homay20-Nov-13 22:42
professionalAydin Homay20-Nov-13 22:42 
AnswerRe: N-Tier, Layers Pin
Aydin Homay25-Nov-13 19:07
professionalAydin Homay25-Nov-13 19:07 

Thank you very much you can read new version of this article with minor changed at here

Best Regards

GeneralRe: N-Tier, Layers Pin
kiquenet.com5-Dec-13 3:32
memberkiquenet.com5-Dec-13 3:32 
SuggestionData Access Layer Pin
Alexander Chernosvitov17-Oct-13 22:22
memberAlexander Chernosvitov17-Oct-13 22:22 
AnswerRe: Data Access Layer Pin
Aydin Homay17-Oct-13 22:41
professionalAydin Homay17-Oct-13 22:41 
GeneralRe: Data Access Layer Pin
Aydin Homay25-Nov-13 19:07
professionalAydin Homay25-Nov-13 19:07 
GeneralMy vote of 5 Pin
Amir Farid24-Sep-13 0:35
memberAmir Farid24-Sep-13 0:35 
GeneralRe: My vote of 5 Pin
Aydin Homay24-Sep-13 1:53
memberAydin Homay24-Sep-13 1:53 

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.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web06-2016 | 2.8.180920.1 | Last Updated 26 Nov 2013
Article Copyright 2013 by Aydin Homay
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid