Click here to Skip to main content
Click here to Skip to main content

Build Your Own Generic Data Access Layer: Part II

, 5 Dec 2013
Rate this:
Please Sign up or sign in to vote.
In this article, we will discover the power of generics in C# and we talk about "how to using design patterns in Data Access Layer" to the improving performance
Prize winner in Competition "Best Database article of November 2013"

Introduction   

 

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 use the generics in C# and design really powerful data access layer for use in all applications without requiring any changes. For more information about Generics in C#, you can follow one of these links: Of course it 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 is not be used with the same DBMS as Microsoft Access. And the other hand is aimed at showing the power of generics and facilities in programming. 

  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 version of this tip, we talked about generic data access layer. I suggest you read the previous post before reading this tip because there were some problems in the previous version that this version resolved, for example in the previous version I was only assuming we have a single Database and Model, Context Model from application. and some friends commented about singleton design pattern problems in Data Access Layer or SQL injection being another problem in the previous version found.

In this version, there are three goals for improvement:

  1. Support multiple data access layer with separated models, context models and database
  2. Use Command design pattern for execution of Entity and ADO.NET commands
  3. Secure against threats same as SQL Injection

Link III: Command Design Pattern

Namespace Description
DataAccessHandler This is the intermediate layer between Mode, Context and Database. There are different providers to make the connection between different database in this layer at the other side IEntity and IContext are located in this layer and models for using this layer should inherit model entities and context form IEntity and IContext.

HRModel

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

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

Data Access Handler

Data Access Handler has two important sub namespaces (Command and Collection). In this layer, we implemented Command Pattern from Command namespace and generic data structures from Collection. In the following, you can read a detailed description about each layer.

As you follow this image, you can see generic Entity Commands from the right side and ADO.NET commands from the left side. So we can consider Entity Commands are required to know Entity for execution, actually this means that UpdateCommand, InsertCommand or DeleteCommand must be known which one Entity should update, insert or remove from which one Context, thus each of entities must be aware of his context. Therefore some change has been made in IEntity than previous version because in previous version each Entity was not aware of their context, but in this version each Entity is aware of its Context. The following image illustrates this fact.

Following this image, you can see IEntity<T> used IContext for knowing which one context is his owner and IContext contains ConnectionInfo class for knowing about database connection information, thus each Entity Command is aware of its connection information at run time by IContext.

As you can see in the picture, IEntity<T> is used by IEntityCommand<T> so other inherited commands the same as UpdateCommand<T> and InsertCommand<T> are used following the IEntityCommand<T> interface too.

A Closer Look at IContext Responsibilities

Now we want take a closer look at the IContext activities and responsibilities. IContext plays a vital role in support of various databases, because in the IContext we can define various connection information by ConnectionInfo class to the connecting different database with different providers in ADO.NET and Redundancy is an important property of ConnectionInfo class. It is a recursive property, so IContext responsibilities can be listed as follows:

  1. Initialize Model entities connection information
  2. Introduce Model properly provider to Data Access Handler (IEnginCommands)
  3. Make a collection of database redundancy connections
  4. Ability to navigate model entities through a single entity (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

Entity Commands Sequence Diagram

Case 1 is about Insert Command sequence diagram:

Invoker is a Command Factory and here we can make our commands. In this example, IEntity<T> sent his 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. 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", "<span style="font-size: 9pt;">JustTest</span><span style="font-size: 9pt;">").FirstOrDefault();</span>
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; }
    }
} 

History

  • First published: 2013/25/11.  

License

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

About the Author

AYDIN EBRAHIMI HOMAY
Software Developer (Senior) KTC(Kerman Tablo Co.)-Industrial Automation
Iran (Islamic Republic Of) Iran (Islamic Republic Of)
I am an eternal student.
Follow on   Google+   LinkedIn

Comments and Discussions

 
GeneralMy vote of 5 PinprofessionalRenju Vinod9-Dec-13 1:46 
GeneralRe: My vote of 5 Pinmember_HOMAY_9-Dec-13 1:50 
GeneralMy vote of 5 Pinmemberhyblusea27-Nov-13 23:13 
GeneralRe: My vote of 5 PinprofessionalAYDIN EBRAHIMI HOMAY27-Nov-13 23:18 
GeneralMy vote of 5 PinmemberM Rayhan26-Nov-13 0:50 
GeneralRe: My vote of 5 PinprofessionalAYDIN EBRAHIMI HOMAY26-Nov-13 1:47 
QuestionDataAccessHandler is missing PinmemberMalenko25-Nov-13 21:24 
AnswerRe: DataAccessHandler is missing PinprofessionalAYDIN EBRAHIMI HOMAY25-Nov-13 22:41 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140709.1 | Last Updated 5 Dec 2013
Article Copyright 2013 by AYDIN EBRAHIMI HOMAY
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid