Introduction
This article tries to describe the architecture for a specific data access layer for relational databases. The document tries to show a way to automate data access tasks.
Background
Below, I expose a series of definitions or concepts that will be needed to understand this document with clarity.
Data Provider
In this article, it should be understood that as a data provider, any kind of component that offers access to all data stored in a relational database is associated with a specific database management engine. Based on that definition, we will find data providers for Oracle, SQL Server, Microsoft Jet, MySQL and other engine types used for managing relational databases.
Data Access Tasks
A data access task will be defined as an abstraction for an operation to be performed in a database. Considering that, the tasks used to access all data will encapsulate the necessary information required for the successful operation execution, and this information will be independent of databases engines that we use. Some task samples for accessing data could be:
- To insert a new record in a table
- To update an existing record in a table
- To eliminate a record in a table
- To create a relation between two tables
Data Access Task Performers
It is another abstraction, its function is to execute the defined operations independently of the database engine used for the connection. Basically a component that inherits from this class will have to implement the way in which the tasks for their specific engine would be executed. A data access task performer would use a specific data provider for its implementation.
Script Builder
Again it consists of an abstraction that allows to generate standard SQL scripts using the stored information in the data access tasks objects. Each data task performer will be able to implement its own script builder or use the default script generator provided by this layer.
Class Diagram
Implementation for a Data Access Task Performer for the SqlServer Data Provider:
Use of Data Access Tasks
The data access task performer used will be defined by default using the connection string and the data type specified in a settings file. The data type specified should inherit from the DataTaskPerformer class and it could be in other assembly causing the model to be extensible.
QueryTask task = new QueryTask("MITABLA");
DataTable resultado = task.Execute();
Sample code - Query for all the fields and registries of a table.
QueryTask task = new QueryTask("MITABLA");
task.Where("Campo1 == '1'");
DataTable resultado = task.Execute();
Sample code - Query for all the fields of a table filtering the records number.
QueryTask task = new QueryTask("MITABLA");
task.Select("Campo1", "Campo2", "Campo3", "Campo4");
task.Select("Campo5");
task.Where("Campo1 == '1'");
DataTable resultado = task.Execute();
Sample code - Query used to get a particular field for a table.
QueryTask task = new QueryTask("MITABLA");
task.Select("Campo1", "Campo2", "Campo3", "Campo4");
task.Select("Campo5");
task.Where("Campo1 == '1'");
DataTable resultado = task.Execute();
Sample code - Query used to get some fields and some joins with tables.
InsertTask newclient = new InsertTask("CLIENTE");
newclient.Set("Nombre", "Anwar Ibañez");
newclient.Set("Edad", 29);
newclient.Set("FechaDeIngreso", DateTime.Now);
newclient.Execute();
decimal newrecord = newclient.ScopeIdentity;
Sample code - Insert task used to add a new record in the Client table.
new DeleteTask("CLIENT", "State == 1").Execute();
Sample code - To eliminate all the records in the Client table where the State field is equal to 1.
UpdateTask updateclient = new UpdateTask("CLIENT");
updateclient.Set("State", 1);
updateclient.Set("Name", "New Name");
updateclient.Set("EntryDate", DateTime.Now);
updateclient.Where("State == 0");
updateclient.Execute();
Sample code - Update task used to modify the selected fields for the CLIENT table where the state is equal to 0.
Executing Tasks in a Transaction
Up next appears how a task set to be executed under the same transaction should be defined.
QueryTask task = new QueryTask("MITABLA");
PerformerTransaction trans = task.Performer.CreateTransaction();
try
{
task.Select("Campo1", "Campo2", "Campo3", "Campo4");
task.Select("Campo5");
task.LeftJoin("TABLA2", "MITABLA.Campo1 = TABLA2.Campo1");
task.RightJoin("TABLA3", "MITABLA.Campo2 = TABLA3.Campo2");
task.Where("Campo1 == '1'");
DataTable resultado = task.Execute();
InsertTask newclient = new InsertTask("CLIENT", trans);
newclient.CurrentTransaction = trans;
newclient.Set("Nombre", "Anwar Ibañez");
newclient.Set("Edad", resultado.Rows[0]["Campo1"]);
newclient.Set("FechaDeIngreso", DateTime.Now);
newclient.Execute();
decimal newrecord = newclient.ScopeIdentity;
new DeleteTask("CLIENT", trans, "State == 1").Execute();
UpdateTask updateclient = new UpdateTask("CLIENT", trans);
updateclient.Set("State", 1);
updateclient.Set("Name", "New Name");
updateclient.Set("EntryDate", DateTime.Now);
updateclient.Where("State == 0");
updateclient.Execute();
trans.Commit();
}
catch (Exception myex)
{
trans.Rollback();
}
Sample code - Execution of many tasks in a transaction with execution control from code.
Implementing an Automatic Persistence Layer Based on the Last Model
Static Attributes for Embedded Mappings
Base Class for Persistence
Implementation Sample
Using the Code
ClaseCompra nuevacompra = new ClaseCompra();
nuevacompra.Cliente.Nombre = "Nombre 1";
nuevacompra.Cliente.Apellido = "Apellido 1";
ClaseDetalleCompra detalle = new ClaseDetalleCompra();
detalle.Producto.Nombre = "Producto 1";
detalle.Producto.Precio = 1200;
nuevacompra.Detalle.Add(detalle);
nuevacompra.Save();
Sample code - To keep a new purchase with a client and a detail related in a single transaction.
ClaseCompra compraexistente = new ClaseCompra();
compraexistente.Load(12);
Sample code - To load the purchase of equal primary key to 12 with its related objects.
Defining Persistence Classes
[MapTo("CLIENTE")]
public class ClaseCliente:PersistentObject
{
private int identifier = 0;private string nombre = "";
private string apellido = ""; private string telefono = "";
private string direccion = "";
[PrimaryKey]
[MapTo("IdCliente")]
public int Identifier
{
get{ return identifier; }
set{ identifier = value;}
}
public string Nombre
{
get{ return nombre; }
set{ nombre = value;}
}
public string Apellido
{
get{ return apellido; }
set{ apellido = value;}
}
public string Telefono
{
get{ return telefono; }
set{ telefono = value;}
}
public string Direccion
{
get{ return direccion; }
set{ direccion = value;}
}
}
Sample code for the Client class - The properties without attributes MapTo have the same names of the fields in the database.
Not Encrypted Dynamic Persistence
We say that it is dynamic since once the types are compiled, we can modify the destiny of the properties in an external file. It is not encrypted because the file is not encrypted. If it is required that it cannot be modified, an advanced end user must amount the file and pass the second parameter of the MappingFile attribute as true.
[MappingFile("PersistenceFile.xml", false)]
public class ClaseCliente:PersistentObject
{. . .
Sample code for the Client class - The persistence is defined in a file and reference to this is with the MappingFile attribute.
Persistence Mapping File Structure
="1.0"="utf-8"
<persistencecatalog>
<typemapping assembly="Example.dll" typename="Example.ClaseCliente"
table="CLIENTE">
<mapping property="Identifier" field="IdCliente" nullsallowed="false"
isprimarykey="true" />
<mapping property="Nombre" field="Nombre" nullsallowed="true"
isprimarykey="false" />
<mapping property="Edad" ignored="true" />
</typemapping>
<typemapping assembly="Example.dll" typename="Example.ClaseCompra" table="COMPRA">
<mapping property="Identifier" field="IdCliente" nullsallowed="false"
isprimarykey="true" />
<mapping property="Nombre" field="Nombre" nullsallowed="true"
isprimarykey="false" />
<mapping property="Edad" ignored="true" />
</typemapping>
</persistencecatalog>
Sample code for a persistence definition file using dynamic persistence is not encrypted for the Client object. All properties with no attributes are called with their database column name in the target table. The fields without mapping must be defined with the attribute ignored as true.
History
This is my first article.
