Self-Synchronized Data Access Layer - Part II

• Download DBGenericConsole
• Download System.Data.SQLite

Introduction

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 (Atomicity, Consistency, Isolation, Durability) 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 ADO.net

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.

Background

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

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.

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)
       Collection.Add(entity);
    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)
{
    AccModel.Context.Instanc.Initialize(AccOledbConnectionInfo);
    HRModel.Context.Instanc.Initialize(HROledbConnectionInfo);
}  

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",
};
AccModel.Context.Instanc.Account.Insert(newAccount); 

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";
AccModel.Context.Instanc.Account.Update(account); 

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();
AccModel.Context.Instanc.Account.Delete(account); 

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)
                    continue;
                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; }
    }
} 

Go to Previous Part

History

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