Click here to Skip to main content
13,771,798 members
Click here to Skip to main content
Add your own
alternative version

Stats

178.4K views
5.6K downloads
183 bookmarked
Posted 15 Aug 2013
Licenced CPOL

Self-Synchronized Data Access Layer - Part I

, 31 Oct 2018
Rate this:
Please Sign up or sign in to vote.
In this article, we will 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 a separate layer for this purpose. Now we want to use the generics in C# and design a 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 in the best possible way, but the problem is that the Entity Framework an not to 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 the current version of this article, you can read the new version from here. In the new version, I solved some problems which are the same as SQL injection and multiple database support.

Data Access Layer Overview

In this article, we introduce our data access layer architect (follow the below picture). As you see in the picture, we have two top level namespaces, 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 an implemented class in Model with the same field names and field data types. Pay attention that each class must inherit from IEntity (see the below picture). For example in this sample, Personnel is a class view of tblPersonnel in database and it is 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 synchronized with the database. Thus the changes are not required to update the Collections again and it can reduce a cost of data access layer. Insert, Update and Delete methods in IEntity are implemented to consider this requirement. Please pay attention to the following 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 Description Namespace
IEntity It is an interface for our entities in entity model. For example, if we want the use of this DAL in our Personnel Management System, the personnel object is a target entity that should implement this interface. Each entity contains a method to insert and update and remove a collection of its own, if it will be inherited from IEntity. DataAccessHandler
Entities It 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 our database. (Execute the sql commands same as insert, update, delete and select on the target database). This object is to do your job that requires a sqlcommand enumerator. DataAccessHandler
SqlCommand This is an enumerator object and is used to specify our DAL SQL command to transact with database. DataAccessHandler
   

DataAccessAdapter Backbone Methods

Initialize Method

This method is used to initialize DataAccessAdapter connection string and password for connecting to the database. We used this method only 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 parameters, the first parameter gets the entity and the second parameter gets the sqlCommand (Insert, Update, Delete, or Select), finally, the third parameter gets 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 is the same as DoEntity method which has 3 parameters too. But this method should be decided about sqlCommand, if the sqlCommand is an insert, it should be called InsertCommand and if it is an update command, it should be called UpdateCommand, 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 to 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 called entity so what I mean when I say, getting properties of entities is properties of classes in the Model. At the end of this 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. The first condition checks the item is the property or not and the second condition checks the item is readable, is serializable and accessible follow of access modifier or no and the final condition removes the PrimaryKey and EntityName at this scenario !! Why? Think about it. 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 answering this question, we should answer what are SQL injection attacks but I prefer to redirect you here for getting more information about it. So this method prevents them, however I know it is not a really secure method for this purpose but we can improve it and I create it for development in 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 inherited from IEntity<T> and IEntity<T> has 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...

Linke to the next part:

This topic has been extended at the below article with more optimal solution.

Self-Synchronized-Data-Access-Layer-Part-II

History

  • 13th August, 2013: Revision
  • 16th August, 2013: Revision

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 Homay
Software Developer (Senior) BHGE
Germany Germany
I am an eternal programmer.

You may also be interested in...

Comments and Discussions

 
GeneralMy vote of 5 Pin
La Tuan Kiet1-Nov-18 17:24
memberLa Tuan Kiet1-Nov-18 17:24 
QuestionGreat article Pin
Member 1150391023-Apr-15 15:10
memberMember 1150391023-Apr-15 15:10 
AnswerRe: Great article Pin
Aydin Homay25-Apr-15 4:09
memberAydin Homay25-Apr-15 4:09 
GeneralRe: Great article Pin
Member 1150391027-Apr-15 10:53
memberMember 1150391027-Apr-15 10:53 
GeneralRe: Great article Pin
Aydin Homay28-Apr-15 6:20
memberAydin Homay28-Apr-15 6:20 
GeneralMy vote of 1 Pin
s2bert12-Feb-15 5:42
members2bert12-Feb-15 5:42 
GeneralRe: My vote of 1 Pin
Aydin Homay13-Feb-15 1:23
memberAydin Homay13-Feb-15 1:23 
GeneralRe: My vote of 1 Pin
s2bert13-Feb-15 1:57
members2bert13-Feb-15 1:57 
GeneralRe: My vote of 1 Pin
Aydin Homay13-Feb-15 2:04
memberAydin Homay13-Feb-15 2:04 
GeneralRe: My vote of 1 Pin
surfer_sk8er_boy4-Sep-18 1:25
membersurfer_sk8er_boy4-Sep-18 1:25 
GeneralMy vote of 5 Pin
yenercoder6-Sep-14 4:02
memberyenercoder6-Sep-14 4:02 
QuestionThere are already many frameworks out there that already handle tis Pin
Member 436622026-Nov-13 7:39
memberMember 436622026-Nov-13 7:39 
AnswerRe: There are already many frameworks out there that already handle tis Pin
Aydin Homay26-Nov-13 8:46
memberAydin Homay26-Nov-13 8:46 
GeneralRe: There are already many frameworks out there that already handle tis Pin
JV999927-Nov-13 2:52
professionalJV999927-Nov-13 2:52 
QuestionNice article Pin
chirag solanki 9085123-Nov-13 7:57
memberchirag solanki 9085123-Nov-13 7:57 
AnswerRe: Nice article Pin
Aydin Homay25-Nov-13 20:07
memberAydin Homay25-Nov-13 20:07 
QuestionN-Tier, Layers Pin
kiquenet.com18-Nov-13 21:23
professionalkiquenet.com18-Nov-13 21:23 
AnswerRe: N-Tier, Layers Pin
Aydin Homay20-Nov-13 5:58
memberAydin Homay20-Nov-13 5:58 
AnswerRe: N-Tier, Layers Pin
Aydin Homay20-Nov-13 23:42
memberAydin Homay20-Nov-13 23:42 
AnswerRe: N-Tier, Layers Pin
Aydin Homay25-Nov-13 20:07
memberAydin Homay25-Nov-13 20:07 
Hello

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

Best Regards
Homay

GeneralRe: N-Tier, Layers Pin
kiquenet.com5-Dec-13 4:32
professionalkiquenet.com5-Dec-13 4:32 
SuggestionData Access Layer Pin
Alexander Chernosvitov17-Oct-13 23:22
memberAlexander Chernosvitov17-Oct-13 23:22 
AnswerRe: Data Access Layer Pin
Aydin Homay17-Oct-13 23:41
memberAydin Homay17-Oct-13 23:41 
GeneralRe: Data Access Layer Pin
Aydin Homay25-Nov-13 20:07
memberAydin Homay25-Nov-13 20:07 
GeneralRe: Data Access Layer Pin
AFell231-Oct-18 5:42
memberAFell231-Oct-18 5:42 

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 | 2.8.181119.1 | Last Updated 31 Oct 2018
Article Copyright 2013 by Aydin Homay
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid