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.
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:
- A Data Access Layer with separated models, context and models to connecting diffrent DBMS
- Using command design pattern for executing of Entities and ADO.NET commands
- 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) . |
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
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:
- Initializing entities connection information in model
- Introduce provider of model to Data Access Handler (
IEnginCommands
) - Handleing database connection redundancy
- 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 Position
s 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)
{
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
- Updated: 2013/25/11.
- Updated: 2014/21/12