Click here to Skip to main content
13,449,172 members (52,252 online)
Click here to Skip to main content
Add your own
alternative version


112 bookmarked
Posted 25 Nov 2013

Self-Synchronized Data Access Layer

, 11 Feb 2015
Rate this:
Please Sign up or sign in to vote.
In the software implementing business synchronizing structures/templates of the database entities with Data Access Layer SQL (commands/Transactions) is the most expensive thinks because you need to re-implement when you have change/s in your entities structure's such as Data-type, Name, and etc.


The Data Access Layer not also is one of the most important part of software development challenge, but also is critical bottleneck for software systems, and also it has good potential for making adding overhead and making a lot side effects. However, we have several methods/design patterns to implementing this layer but we have always terrible critters that can reduce maintenance cost.

In the software implementing business synchronizing structures/templates of the database entities with Data Access Layer SQL (commands/Transactions) is the most expensive thinks because you need to re-implement when you have change/s in your entities structure's such as Data-type, Name, and etc.

The most important criteria is a guarantee your transaction reliability in your DBMS, but what is the reliable transaction? That is a good question.

ACID (AtomicityConsistencyIsola tionDurability) is a set of properties that guarantee that database transactions are processed reliably. In the context of databases, a single logical operation on the data is called a transaction. Jim Gray defined these properties of a reliable transaction system in the late 1970s.

In the software implementing business synchronizing structure/template of Database entities with Data Access Layer SQL commands/Translations is most expensive think and you need re-write SQL commands whenever you are changing your entities structure's such as Data-type, Name or etc.
In this article I have to go implementing flexible and self-synchronized Data Access Layer by using generics, reflectors, and design patterns, by this way we dose not need to re-write any SQL command, after/before developing/updating entities in Database. As you know each entity in Database is a simple table and each instance of entity is a row in that table.

Microsoft Entity Framework introduces some solutions like this, but unfortunately EF does not supports some DBMS such as Microsoft Access and it is not open source solution. One more update, by this case you can implement your own search algorithms or any other type of special think in your DAL to accessing data in several Databases, such as optimum search algorithms, apart of from, you can use for scientific purpose, like evaluating new methods, solutions or data access algorithms and redundant thinks. 

Link I: An Introduction to C# Generics Link II: Introduction to Generics (C# Programming Guide) Link III: Sqlite for
Note: Please download sqlite .net 4 x82 or 64 compatible with your target machine from top link III and add reference in your DataAccessHandler project.


In the previous article "Generic Data Access Layer" , I discussed about generic types in .NET and their benefits, but this version of my work was not so sufficient and also this version was very ad-lip type of this idea. But I recommended to you, take a look this version before than starting Self-Synchronized DAL.

In this version, we are three main goal:

  1. A Data Access Layer with separated models, context and models to connecting diffrent DBMS
  2. Using command design pattern for executing of Entities and ADO.NET commands
  3. Making Secure against typical threats such as as SQL Injection

Link III: Command Design Pattern

Namespace Description
DataAccessHandler This is a intermediate layer between Model, Context and Database. The different providers can make different connections between different database, at the other side IEntity and IContext are located in this layer and model entities for using this layer should be inherit from abstract model and context. (IEntity , IContext).


This namespace introduces Human Resource Model to the connecting HRDatabase (Human Resource Database) with Data Access Handler

This namespace introduces Accounting Model to the connecting AccDatabase (Accounting Database) with Data
Access Handler

Data Access Handler

DataAccessHandler has two important sub namespaces (Command and Collection). Command layer is a holder of all commands, for implemented this part, I used command pattern, factory pattern and generic data structures. Namespace of Command demonstrat this thnk and Collection is a namespace for generic types, regarding of below figure you can see more detail about DataAccessHandler.

Figure1: Command Desing Pattern

The Figure1 shows, Entity Commands from the right side and ADO.NET commands from the left side. You can consider Entity Commands needs Entity for execution, actually this means the UpdateCommand, InsertCommand or DeleteCommand must be known which one of entities should be update, insert or removed from which one of contexts (Context), so each entity must be aware of own context. This is reason why we have IEntity as interface, in the previous version each Entity was not aware of own context. The Figure2 illustrates this fact.

Figure2: Architecture of IEntity

Following Figure3, you can see IEntity<T> and IContext relationship for getting context of each IEntity and ConnectionInfo class for knowing about database connection information, thus each Entity Command will be aware of own database connection and also redundant connection  in run time by IContext.

Figure3: IEntity<T> and IContext Relationship

IEntity<T> contains IEntityCommand<T> to using of UpdateCommand<T>, InsertCommand<T> and etc.

Context Responsibilities

Now we want to know IContext activities and responsibilities. IContext has a vital role to supporting of various databases, because in the IContext we can define various providers and connections by using of ConnectionInfo class. To the connecting different database with need different providers and also for making redundancy business I used Redundancy structure to holding several ConnectionInfo class. Pay-attention to It is a recursive property

The below list is IContext responsibilities:

  1. Initializing entities connection information in model
  2. Introduce provider of model to Data Access Handler (IEnginCommands)
  3. Handleing database connection redundancy 
  4. Providing abilities to achieving model from entity and entity from model (for example, from HRModel if I have Personnel entity and Position entity, I can access list of Positions through Personnel entity and conversely) it can make some facilities work with Model entities.

IContext Overview

Figure4: Big picture of IContext

Entity Commands Sequence Diagram

Figure5: Entity Commands Sequence Diagram

Invoker is a Command Factory and we can make our commands. In this example, IEntity<T> sent the request to Invoker and Invoker returned requested command same as InsertCommand<T> to IEntity<T> and finally IEntity<T> called command Execute() method. Other commands have the same scenario.

 public virtual object Insert(T entity)
    iEntityCommand = Invoker.Instanc.GetCommand(entity, EntityCommandType.Insert);
    TransactionResult<T> result = iEntityCommand.Execute(entity);
    if (result.Commit)
    return result.Entity.PrimaryKey.Value;

Initialize Context

From the Context, we have a model and database connection information, so we should initialize our contexts before using of GDA (General Data Acess). This initialize includes creating ConnectionInfo data structure and use it for initialize Context database connection information.

static DataAccessHandler.ConnectionInfo HROledbConnectionInfo = new DataAccessHandler.ConnectionInfo()
    DataSourceProvider = DataAccessHandler.Provider.Oledb,
    Password = "",
    Username = "",
    DataSource = @"C:\Users\Homay\Desktop\GenericDataAccess\GenericDataAccessII\Database\HRDatabase.mdb",
    IsValid = true

static DataAccessHandler.ConnectionInfo AccOledbConnectionInfo = new DataAccessHandler.ConnectionInfo()
    DataSourceProvider = DataAccessHandler.Provider.Oledb,
    Password = "",
    Username = "",
    DataSource = @"C:\Users\Homay\Desktop\GenericDataAccess\GenericDataAccessII\Database\AccDatabase.mdb",
    IsValid = true

static void Main(string[] args)

Insert New Record

From here I want to show you how to add new record to context:

AccModel.Account newAccount = new AccModel.Account() 
    Name = "AccTest" ,
    Description = "for test",

Update Record

From here I want to show you how to update record in context:

AccModel.Account account = AccModel.Context.Instanc.Account.Collection.Select("Name", "AccTest").FirstOrDefault();
account.Name = "JustTest";

Delete Record

From here I want to show you how to delete record from context:

AccModel.Account account = AccModel.Context.Instanc.Account.Collection.Select("Name", "JustTest").FirstOrDefault();

SQL Injection

Now I want to talk about sql injection and explain how do I solve this problem, sql injection is a code injection technique and used to attack data driven applications from wiki you can read about it. I solve this problem with passing parameter to commands. follow of code illustrate it:

public class InsertCommand<T> : IEntityCommand<T> where T : IEntity<T>
    public InsertCommand(T entity, EntityCommandType entityCommandType)
        : base(entity, entityCommandType)

    public override TransactionResult<T> Execute(T entity)
        TransactionResult<T> result = new TransactionResult<T>() { Commit = false, Entity = null };
        string Insert = "INSERT INTO " + ((IEntity<T>)entity).EntityName;
        string columns = "(";
        string values = "VALUES(";
        Dictionary<string, object> parameters = new Dictionary<string, object>();
        Dictionary<string, object> infos = GetInfo(entity);
        foreach (var item in infos) // create parameters
            if (item.Value != null && item.Value.ToString() != "")
                if (entity.PrimaryKeyReadOnly && item.Key == entity.PrimaryKey.Name)
                columns += "[" + item.Key + "],";
                values += "@" + item.Key.ToString() + ",";
                parameters.Add("@"+item.Key, item.Value);
        columns = columns.Remove(columns.Length - 1, 1) + ") ";
        values = values.Remove(values.Length - 1, 1) + ") ";
        Insert += columns + values;
        IEnginCommand executeNoneQuery = Invoker.Instanc.GetCommand(EnginCommandType.ExecuteNoneQuery);
        ((ExecuteNoneQuery)executeNoneQuery).Execute(entity.Context.ConnectionInfo, Insert, parameters);
        entity.InitializePrimaryKeyValue(new PrimaryKey(){Value= 
          ((ExecuteNoneQuery)executeNoneQuery).Identity,Name = entity.PrimaryKey.Name});
        result.Commit = true;
        result.Entity = entity;
        return result;

    public override EntityCommandType Type
        get { return EntityCommandType.Insert; }


  • Updated: 2013/25/11.
  • Updated: 2014/21/12


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.
Portugal Portugal
I am an eternal programmer.

You may also be interested in...


Comments and Discussions

QuestionError accessing example Access DB's Pin
Martin Stephenson22-Jan-16 1:35
memberMartin Stephenson22-Jan-16 1:35 
AnswerRe: Error accessing example Access DB's Pin
Aydin Homay22-Jan-16 2:01
professionalAydin Homay22-Jan-16 2:01 
QuestionDon't know if you know this or not.... Pin
Peter Shaw17-Feb-15 23:37
professionalPeter Shaw17-Feb-15 23:37 
AnswerRe: Don't know if you know this or not.... Pin
Ranjan.D5-Apr-15 13:48
mvpRanjan.D5-Apr-15 13:48 
GeneralRe: Don't know if you know this or not.... Pin
AYDIN HOMAY7-Apr-15 0:35
professionalAYDIN HOMAY7-Apr-15 0:35 
QuestionWhich tool you used to generate diagram? Pin
Tridip Bhattacharjee12-Feb-15 1:08
memberTridip Bhattacharjee12-Feb-15 1:08 
AnswerRe: Which tool you used to generate diagram? Pin
AYDIN HOMAY12-Feb-15 1:14
professionalAYDIN HOMAY12-Feb-15 1:14 
GeneralRe: Which tool you used to generate diagram? Pin
Tridip Bhattacharjee12-Feb-15 20:31
memberTridip Bhattacharjee12-Feb-15 20:31 
GeneralRe: Which tool you used to generate diagram? Pin
AYDIN HOMAY13-Feb-15 0:14
professionalAYDIN HOMAY13-Feb-15 0:14 
GeneralRe: Which tool you used to generate diagram? Pin
Tridip Bhattacharjee16-Feb-15 0:50
memberTridip Bhattacharjee16-Feb-15 0:50 
GeneralMy vote of 3 Pin
Erik Funkenbusch11-Feb-15 5:22
memberErik Funkenbusch11-Feb-15 5:22 
GeneralRe: My vote of 3 Pin
AYDIN HOMAY12-Feb-15 0:14
professionalAYDIN HOMAY12-Feb-15 0:14 
GeneralRe: My vote of 3 Pin
Erik Funkenbusch12-Feb-15 4:05
memberErik Funkenbusch12-Feb-15 4:05 
GeneralRe: My vote of 3 Pin
AYDIN HOMAY13-Feb-15 0:20
professionalAYDIN HOMAY13-Feb-15 0:20 
QuestionSpecial Character insert Pin
Member 1086973421-Dec-14 2:46
memberMember 1086973421-Dec-14 2:46 
AnswerRe: Special Character insert Pin
memberAYDIN EBRAHIMI HOMAY21-Dec-14 11:28 
QuestionGreat Article, why not implement for SQL Server? Pin
Hevin.Zhao15-Sep-14 19:31
memberHevin.Zhao15-Sep-14 19:31 
QuestionEntity Framework example Pin
datasmith6912-Sep-14 4:41
memberdatasmith6912-Sep-14 4:41 
AnswerRe: Entity Framework example Pin
professionalAYDIN EBRAHIMI HOMAY12-Sep-14 18:53 
GeneralMy vote of 5 Pin
Renju Vinod9-Dec-13 1:46
professionalRenju Vinod9-Dec-13 1:46 
GeneralRe: My vote of 5 Pin
_HOMAY_9-Dec-13 1:50
member_HOMAY_9-Dec-13 1:50 
GeneralMy vote of 5 Pin
hyblusea27-Nov-13 23:13
memberhyblusea27-Nov-13 23:13 
GeneralRe: My vote of 5 Pin
professionalAYDIN EBRAHIMI HOMAY27-Nov-13 23:18 
GeneralMy vote of 5 Pin
M Rayhan26-Nov-13 0:50
memberM Rayhan26-Nov-13 0:50 
GeneralRe: My vote of 5 Pin
professionalAYDIN EBRAHIMI HOMAY26-Nov-13 1:47 

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 | Terms of Use | Mobile
Web01-2016 | 2.8.180318.3 | Last Updated 11 Feb 2015
Article Copyright 2013 by Aydin Homay
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid