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

Abstract Data Access Layer Design

, 5 Sep 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
The present document tries to describe the architecture of a specific layer of access to data for relational databases. This document tries to present/display a form to automate tasks of access to data.

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

<?xml version="1.0" encoding="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.

License

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

Share

About the Author

Anwar Ibáñez O'Kamell
Systems Engineer
Colombia Colombia
http://www.construirsoftware.blogspot.com/

Comments and Discussions

 
GeneralGood work ! Pinmemberigetorix8-Sep-09 10:28 
GeneralRe: Good work ! PinmemberAnwar Ibáñez O'Kamell8-Sep-09 15:16 

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
Web04 | 2.8.1411023.1 | Last Updated 5 Sep 2009
Article Copyright 2009 by Anwar Ibáñez O'Kamell
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid