Easy Data Access Layer

• Download source code - 972.53 KB

Introduction

This library gives an easy to use and unified access to different data sources. This library has providers for Microsoft SQL Server, Postgree, FireBird and MySQL, SQLite, Oracle, SqlServerCe and it is easy to add new ones. This library uses the following technologies and patterns:

• Abstract Factory - for instantiation of the needed data sources
• Generic Singleton - to get a single instance of a data source
• LINQ - for transformation of data into a Generic List

The last one simplifies the implementation of the Entity Data Model (EDM).

Using the Code

Here is the hierarchy of the common classes:

The entry point of the library is the DataLayer class. It uses the "Abstract Factory" pattern, and serves for the instantiation of objects that implement interfaces of type IDataLayer of the required types. The factory method of the DataLayer class is GetDataLayer, which can be used as follows:

• Without parameters - Needed data for database types and connection string are obtained form the app.config of the library.
• With parameter for database types - In this case, the connection string is obtained from app.config.
• With parameters for database types and connection string.

From the GenericSingleton is obtained the needed instance for the connection to the database, and it is its responsibility to create only one instance from a given type.

The organization of the library: The interface IDataLayer describes the entire functionality. The abstract class DataLayerBase inherits IDataLayer and implements the base functionality for all data providers. When an instance of it is being created, it makes instances of common classes like DatabaseUtility, DBConnectionString, DBParameters for parsing and storing parameters and other classes. The idea of this class is to implement all the common functionality for the data providers; other methods which are specific for a data provider are abstract. The inheritor of the DataLayerBase class implements the abstract methods and creates a concrete connection object.

Configuration Settings

To create a layer from an app.config setting, add the following code:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="DataAccessLayer"
       type="System.Configuration.DictionarySectionHandler" />
  </configSections>

  <DataAccessLayer>
    <add key="ConnectionString"
       value="Server=localhost;User=SYSDBA;Password=masterkey;
              Database=D:\Catalog\trunk\DataBase\Catalog.fdb"/>
    <add key="DatabaseType" value="Firebird"/>
  </DataAccessLayer>
</configuration>

Here is an example of how the library can be used:

// Get data layer type from .config settings.
IDataLayer _dal = DataLayer.GetDataLayer();
// Get data layer following type
IDataLayer _dal = DataLayer.GetDataLayer(DatabaseType.MSSql,
   "Persist Security Info=True;Initial Catalog=pd_retrieval_frontend;" +
   "Data Source=SQLSERVER;User ID=sa;Password=strongPass;");

Example methods:

// Partial set SQL and Parameters.
_dal.Sql = "SELECT ID, Name FROM Employers WHERE ID > @ID";
_dal.Parameters.Add("ID", DbType.Int32, "10");
List<Employer> emp = _dal.ExecuteAndFillList<Employer>();

// Execute only SQL no parameters
List<Employer> emp = _dal.ExecuteAndFillList<Employer>(
                         "SELECT ID, Name FROM Employers");

// Execute SQL and Parameters lazy.
List<Employer> emp = _dal.ExecuteAndFillList<Employer>
	("SELECT ID, Name FROM Employers WHERE ID > @ID", 10);

The result is a list of a given type.

Other methods include:

• ExecuteNonQuery(); - Execute a SQL with no return value. For DELETE, UPDATE, or INSERT statements.
• ExecuteScalar(); - Execute a SQL and return only the first left cell from the table.
• ExecuteDataTable(); - Execute a SQL and return a DataTable object.
• ExecuteDataSet(); - Execute a SQL and return a DataSet object.
• ExecuteDataReader(); - Execute a SQL and return a DataReader object.
• ExecuteAndFillList<T>(); - Execute a SQL and fill List from the T entity.

Error Event Handler

To generate an error from the data layer, fire this error handler:

_dal.DatabaseError += new EventHandler<DatabaseErrorEventArgs>(DataLayer_DatabaseError);

The property RaisedError indicates if the previous operation raised an error.

Additional functionality: When the connection string is changed, the connection will be closed and reopened with actual data. When an Execute operation is invoked, a check is done to see if the connection is closed and an attempt made to establish a new connection without throwing an exception. If a connection cannot be established, an exception will be thrown. This exception can be processed and saved in LastError, or thrown again. When a new SQL command is applied, the previous parameters are cleaned.

Points of Interest

This library can be used while implementing an Entity Data Model (EDM) where we have the ability to use Stored Procedures and optimized queries for database requests.

History

The first implementation was only for Microsoft SQL Server, and later code was added for Postgree and Firebird. The major parts of the specific classes were moved to the base class. New execute void methods were added and a new event is fired if an exception is raised. Also added a new property RaisedError. New adds support for MySQL server.

Version 1.1

Support is added for the following servers: SQLite, Oracle, SqlServerCe. The usage of DataContext is removed which increased the performance with up to 20% when using ExecuteAndFillList methods. Other minor changes to the code. The default CommandType is set to Text. For every server implementation, for better handling of a broken connection, CheckConnBroken method is added. The library is 100% compatible with MONO.