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.
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.
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.
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.
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:
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);
}
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 | Name 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 |
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
} 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;
} 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;
} 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;
} 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
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;
}
} 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> 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.
I hope that will be useful...
联系客服
