Easy Data Access Layer

• Download source code - 1.21 MB

Introduction

This library gives an easy and unified access to different data sources. The current version supports MSSQL, PostgreSQL, FireBird, MySQL, SQLite, Oracle, SqlServerCE, and OLEDB. The user can easily extend the library and add new databases.

There are some Design Patterns used in the library – Abstract Factory for choosing the necessary data source, and Generic Singleton which guarantees onlly one instance of the source.

The library can easily convert data from table to list format, which facilitates implementing the Entity Data Model (EDM) pattern. Optimal performance is reached due to SP (Stored Procedures) and easy processing of the received data in the higher layers (such as Business).

Using the Code

The entry point of the library is the Data Layer. Its purpose is to deliver an interface of the IDataLayer type to the specific class communicating with the database. The Data Layer class contains only one method: GetInstance, which returns the appropriate class instance for the chosen connection.

Example:

private IDataLayer _dal;
        
public FormDataLayerTest()
{
    _dal = DataLayer.GetInstance();
}

The settings needed for instantiation are located in the app.config file. The following configuration is mandatory:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="DataAccessLayer" 
      type="System.Configuration.DictionarySectionHandler, System, 
            Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
 </configSections>
  <DataAccessLayer>
    <add key="ConnectionString" 
      value="Server=localhost;User=SYSDBA;Password=masterkey;
             Database=C:\Program Files\Firebird\Firebird_2_1\
             examples\empbuild\EMPLOYEE.FDB"/>
    <add key="DatabaseType" value="Firebird"/>
    <!-- This key optional  -->
    <!-- This is a default value -->
    <add key="Singleton" value="true"/>
   </DataAccessLayer>
</configuration>

In this case, an instance with a connection to a Firebird  database is created, having ConnectionString Server=localhost;User=SYSDBA;Password=masterkey;Database=C:\Program Files\Firebird\Firebird_2_1\examples\empbuild\EMPLOYEE.FDB. The instance is of Singleton type.

Overloads of the GetInstance method are:

• _dal = DataLayer.GetInstance(DatabaseTypes.MSSql);

A class supporting a connection to a MSSQL database is created. The instance is of singleton type.

• IDataLayer GetInstance(DatabaseTypes dbType, string connectionString);

A class supporting a connection to databases of DatabaseTypes type is created. Also, the connection string is passed as an argument. The instance is of singleton type.

• IDataLayer GetInstance(DatabaseTypes dbType, string connectionString, bool singleton);

A class supporting a connection to databases of DatabaseTypes type is created. Also, the connection string is passed as an argument. It can be chosen if the instance will be of singleton type.

• IDataLayer GetInstance(DatabaseTypes dbType, bool singleton);

A class supporting connection to databases of DatabaseTypes type is created. It can be chosen if the instance will be of singleton type.

Example:

C#

_dal = DataLayer.GetInstance(DatabaseTypes.MSSql, false);
// Set connection string
_dal.ConnectionString = "Persist Security Info=True;Initial " + 
  "Catalog=Northwind;Data Source=(LOCAL);User ID=sa;Password=masterkey;";

Here, a class supporting a connection to a MSSQL database is created. A connection string is also added.

Opening a connection to the database

There are two ways:

1. The user and the password are set in the connection string. Then the
   C#

   _dal.Open();

   can be directly called to establish the connection.

   C#

   bool Open(); 

   returns if the connection was successfully established.

2. If the user and the password are not set in the connection string, or they are not valid, then the following overload of the method is used:
   C#

   bool Open(string userName, string password);

   It again returns if the connection was successfully established.

Checking for errors caused by database operations is done by the following way:

if (_dal.IsError)
    MessageBox.Show(_dal.LastError);

LastError is a property containing the error message. There is a way of handling errors by throwing an Exception:

_dal.RERaiseException = true;

The library supports the default type of the query. If you do not set it explicitly, it is:

_dal.DefaultCommandType = CommandType.Text;

In case Stored Procedures are used mostly, the query type can be changed:

_dal.DefaultCommandType = CommandType.StoredProcedure;

The purpose is not to change the type for every query. Given below are some examples:

The sequence of commands is changed in version 1.3 to the following:

SQL query, procedure's name, or table's name are set to the type of the query if needed. If the type is different than _dal.DefaultCommandType, then it has to be set explicitly. For example, if text queries are mainly used and now we want to execute a Stored Procedure, then we do the following:

_dal.Sql = "CustOrderHist";
_dal.CommandType = CommandType.StoredProcedure;

If the query contains parameters, we set them:

_dal.AddParameter("CustomerID", "ALFKI");

The method for executing the query is called (depending on the wanted result the method is different; they are described below):

DataTable dt = _dal.ExecuteDataTable();

In this case, the result consists of many rows and columns, and by using “ExecuteDataTable”, we fill it into a table.

Using methods for query execution:

All queries in the examples are for MS SQL Server and the Northwind database. You can find the create script here in the download file: ..\SQL\ instnwnd.sql.

• int ExecuteNonQuery();

It is used for Data Definition Language (DDL) statements and Data Manipulation Language (DML) statements – INSERT, UPDATE, DELETE, and STORED PROCEDURES which don't return a result. It can also be used for Stored Procedures which have output parameters – they can be read after the query execution.

Example:

C#

_dal.Sql = "   INSERT INTO Customers"
                + "       ( CustomerID,  CompanyName)"
                + " VALUES"
                + "       (@CustomerID, @CompanyName)";
_dal.AddParameter("CustomerID", "TESTC");
_dal.AddParameter("CompanyName", "Test company name");
 
int rowsAffected = _dal.ExecuteNonQuery();

The number of affected rows is returned as a result. For this specific example, it is 1.

• object ExecuteScalar();

The first row and the first column of the RowSet is returned as a result. If the RowSet is empty, then null is returned. It is mainly used for SELECT queries which return only one value. Also for INSERT queries which return the Primary Key value.

Example:

C#

_dal.Sql =
    "  SELECT EmployeeID"
    + "  FROM Employees"
    + " WHERE EmployeeID > @ID"
    + " ORDER BY LastName";
_dal.AddParameter("ID", 2, DbType.Int32);
 
object o = _dal.ExecuteScalar();

• DataTable ExecuteDataTable();

It is mainly used for SELECT queries or Stored Procedures which return more than one row and column.

Example:

C#

_dal.Sql =
    "  SELECT EmployeeID, LastName, FirstName, Title"
       + "  FROM Employees"
    + " WHERE EmployeeID > @ID"
    + " ORDER BY LastName";
_dal.AddParameter("ID", 2, DbType.Int32);
 
DataTable dt = _dal.ExecuteDataTable();

The result is a table filled with data. In this case, it has 4 columns and 7 rows.



• DataSet ExecuteDataSet();

It is usually used for consecutive SELECT queries which return more than one table as a result.

Example:

C#

_dal.Sql = "SelInvoiceOrder";
_dal.CommandType = CommandType.StoredProcedure;
// ParameterDirection of ReturnValue
// must be specified before the other parameters.
IDbDataParameter ret = _dal.AddParameter("RETURN_VALUE", DbType.Int32, 
                                         ParameterDirection.ReturnValue);
_dal.AddParameter("EmployeeID", 1, DbType.Int32, ParameterDirection.Input);
_dal.AddParameter("OrderID", 10248, DbType.Int32, ParameterDirection.Input);
// Add parameters which will later be read a output values
IDbDataParameter outEmployeeCount = _dal.AddParameter("EmployeeCount", 
                                    DbType.Int32, ParameterDirection.Output);
IDbDataParameter outOrderCount = _dal.AddParameter("OrderCount", 
                                 DbType.Int32, ParameterDirection.Output);
DataSet ds = _dal.ExecuteDataSet();

// Reading the results of the parameters
int retValue = ret.Value == DBNull.Value ? 0 : (int)ret.Value;
int outInvCount = 
  outEmployeeCount.Value == DBNull.Value ? 0 : (int)outEmployeeCount.Value;
int outOrdCount = 
  outOrderCount.Value == DBNull.Value ? 0 : (int)outOrderCount.Value;

The result is a set of tables filled with data. In this case, a SP is executed which returns 2 tables and has 3 output parameters.



The procedure "SelInvoiceOrder" is added to the “instnwnd.sql” script.

• List<T> ExecuteAndFillList<T>();

It is used for querying data which consists of several rows and columns. The data is filled into a list of the specified class type. It simplifies the following data processing using LINQ.

Characteristics:

To set the properties of the class, they must be the same names as the column names and have “set;” accesses. The property type and the column type read from the database must be the same.

Example:

A class type which will be used for querying data:

C#

public class IdValueEntity
{
    public Int32 Id { get; set; }
    public string Value { get; set; }
}

Query:

C#

_dal.Sql =
    "  SELECT OrderID AS ID, ProductID, ProductName AS Value, UnitPrice"
    + "  FROM [dbo].[Order Details Extended]";
// CommandType.Text is default
//_dal.CommandType = CommandType.Text;
List<IdValueEntity> list = _dal.ExecuteAndFillList<IdValueEntity>();

To set the properties of the class, the column names in the result are changed: OrderID -> ID and ProductName -> Value. The columns which do not exist in the class are simply skipped. In this case, the list contains 2155 entries.

• T ExecuteAndFill<T>();

It is like the previous query, but extracts only the first row from the result.

Example:

C#

_dal.Sql =
    "  SELECT OrderID AS ID, ProductID, ProductName AS Value, UnitPrice"
    + "  FROM [dbo].[Order Details Extended]";
        // CommandType.Text is default
//_dal.CommandType = CommandType.Text;
 
IdValueEntity ent = _dal.ExecuteAndFill<IdValueEntity>();

To set the properties of the class, the column names in the result are changed: OrderID -> ID and ProductName -> Value. The columns which do not exist in the class are simply skipped.

Working with an OLEDB connection

In the following example, a test Excel file is used. The cell “D2” in the “Sheet1” sheet is populated with value 55.

_dal = DataLayer.GetInstance(DatabaseTypes.OleDB);
            
_dal.ConnectionString =
            @"Provider=Microsoft.Jet.OLEDB.4.0;"
            + "Data Source="
            // Excel file name
            + "ExcelWorksheet.xls"
            + ";Extended Properties=\"Excel 8.0;HDR=NO;\"";

string sheetName = "Sheet1";
string cell = "D2";
_dal.Sql =
    " UPDATE [" + sheetName + "$" + cell + ":" + cell + "]"
    + " SET F1 = :value";
_dal.AddParameter("value", 55, DbType.Int32);
 
int rowsAffected = _dal.ExecuteNonQuery();

A big part of the examples and other queries can be found in the Test application. For testing a specific Stored Procedure, a new one is created which is added to the create database script for the Northwind database – ..\SQL\instnwnd.sql.

Internal logic

• If the connection string is changed, the connection is closed and opened again when one of the methods Open or Execute is called.
• If the connection is broken or was not opened, it is opened when Execute iscalled. In case of failure, an exception is thrown.
• When a new SQL statement is given, the previous parameters are cleared (if there were any) and a new query type is set, by default from DefaultCommandType.

Points of Interest

The library gives an easy way for working with databases. It is not necessary to care for opening the connection and its status - if a reconnect or opening a connection is needed, it is done automatically. Another useful feature is that the query result can be saved easily into an entity which the application uses. This way, the strong features when working with DB are kept – like optimized and specific queries. And about the application – the data is received in an easy for manipulation way.

History

Ver. 1.3: Changes in the base functionality of the library. The query parameters are with standard type, and can be now input-output. Working with queries and parameters passing is now changed. The new method ExecuteAndFill allows extracting only one data row from the query.

The methods ExecuteAndFillList and ExecuteAndFill has been optimized for efficiency. The library is 100% compatible with MONO.