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

Build Your Own Generic Data Access Layer I

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

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 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.

Background

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.

Model

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.

Model

Context

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:

IEntity

public void AddPersonel(Mode.Personnel personnel)
{
    Mode.Context.Instance.Personnel.Insert(personnel);
}

public void UpadtePersonel(Mode.Personnel personnel)
{
    Mode.Context.Instance.Personnel.Update(personnel);
}

public void RemovePersonel(Mode.Personnel personnel)
{
    Mode.Context.Instance.Personnel.Delete(personnel);
}

DataAccessHandler

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

Class DescriptionName 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);
    #endregion
}   

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)
        Collection.Add(entity);
    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);
            break;
        case SqlCommand.Update:
            if(where == "")
                commandText = UpdateCommand(entity);
            else if(where != "")
                commandText = UpdateCommand(entity, where);
            break;
        case SqlCommand.Select:
            if (where == "")
                commandText = selectCommand(entity);
            else if (where != "")
                commandText = selectCommand(entity, where);
            break;
        case SqlCommand.Delete:
            if (where == "")
                commandText = deleteCommand(entity);
            else if (where != "")
                commandText = deleteCommand(entity, where);
            break;
        default:
            break;
    }
    return commandText;
} 

InsertCommand

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;
        [DisplayName("Identity")]
        [Category("Column")]
        [DataObjectFieldAttribute(true, true, false)]//Primary key attribute 
        public int ID
        {
            get { return _id; }
            set { _id = value; }
        }
        
        string _fName;
        [DisplayName("First Name")]
        [Category("Column")]
        public string FName
        {
            get { return _fName; }
            set { _fName = value; }
        }
 
        string _lName;
        [DisplayName("Last Name")]
        [Category("Column")]
        public string LName
        {
            get { return _lName; }
            set { _lName = value; }
        }
        #endregion
 
        #region Initialize
        public Personnel() { }
        #endregion
    }
}

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>
{
    try
    {
        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")
                        continue;
                    else if (item.PropertyType.IsEnum)
                        values.Add(item.Name, item.GetValue(entity, null).GetHashCode());
                    else
                        values.Add(item.Name, item.GetValue(entity, null));
                }

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

        throw;
    }
}    

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('=') || 
                    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... Wink | ;)

History

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

License

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

Share

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

 
AnswerRe: !! so why not n-tier PinprofessionalAYDIN EBRAHIMI HOMAY25-Nov-13 20:08 
GeneralMy vote of 3 PinmemberAhmad Abd-Elghany16-Aug-13 8:57 
GeneralRe: My vote of 3 PinmemberAYDIN EBRAHIMI HOMAY16-Aug-13 23:40 
GeneralRe: My vote of 3 PinprofessionalAYDIN EBRAHIMI HOMAY25-Nov-13 20:09 
SuggestionIsSqlInjection hmmm [modified] PinprofessionalPaw Jershauge15-Aug-13 22:33 
GeneralRe: IsSqlInjection hmmm PinmemberAYDIN EBRAHIMI HOMAY15-Aug-13 23:34 
AnswerRe: IsSqlInjection hmmm PinprofessionalPaw Jershauge15-Aug-13 23:39 
GeneralRe: IsSqlInjection hmmm PinmemberAYDIN EBRAHIMI HOMAY16-Aug-13 0:32 
GeneralRe: IsSqlInjection hmmm PinprofessionalAYDIN EBRAHIMI HOMAY25-Nov-13 20:10 
GeneralRe: IsSqlInjection hmmm PinprofessionalAYDIN EBRAHIMI HOMAY25-Nov-13 20:10 
GeneralRe: IsSqlInjection hmmm PinprofessionalAYDIN EBRAHIMI HOMAY25-Nov-13 20:09 
QuestionI have to agree... PinmemberSpiff Dog15-Aug-13 14:01 
AnswerRe: I have to agree... PinmemberAYDIN EBRAHIMI HOMAY15-Aug-13 21:38 
GeneralRe: I have to agree... PinmemberSpiff Dog20-Sep-13 10:27 
GeneralRe: I have to agree... PinmemberAYDIN EBRAHIMI HOMAY21-Sep-13 21:04 
QuestionGood intention but..... PinmemberFZelle15-Aug-13 5:32 
AnswerRe: Good intention but..... PinmemberAYDIN EBRAHIMI HOMAY15-Aug-13 5:53 
GeneralRe: Good intention but..... PinmemberPaulo Zemek15-Aug-13 6:22 
GeneralRe: Good intention but..... PinmemberAYDIN EBRAHIMI HOMAY15-Aug-13 6:24 
AnswerRe: Good intention but..... PinmemberPaulo Zemek15-Aug-13 6:17 
GeneralRe: Good intention but..... PinmemberAYDIN EBRAHIMI HOMAY15-Aug-13 6:19 
GeneralRe: Good intention but..... PinmemberPaulo Zemek15-Aug-13 6:20 
GeneralRe: Good intention but..... PinmemberAYDIN EBRAHIMI HOMAY15-Aug-13 19:21 
GeneralRe: Good intention but..... PinmemberFZelle16-Aug-13 2:56 
GeneralRe: Good intention but..... PinmemberAYDIN EBRAHIMI HOMAY16-Aug-13 3:00 
GeneralRe: Good intention but..... PinprofessionalAYDIN EBRAHIMI HOMAY25-Nov-13 20:10 
GeneralRe: Good intention but..... PinprofessionalAYDIN EBRAHIMI HOMAY25-Nov-13 20:11 
GeneralMy vote of 5 PinprofessionalAmir Mohammad Nasrollahi15-Aug-13 4:38 
GeneralRe: My vote of 5 PinmemberAYDIN EBRAHIMI HOMAY15-Aug-13 5:42 

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 | Terms of Use | Mobile
Web03 | 2.8.141223.1 | Last Updated 26 Nov 2013
Article Copyright 2013 by AYDIN EBRAHIMI HOMAY
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid