Click here to Skip to main content
15,867,704 members
Articles / Programming Languages / C#

Easy Data Access Layer

Rate me:
Please Sign up or sign in to vote.
4.56/5 (28 votes)
6 Sep 2010CPOL7 min read 430.3K   6.3K   133   57
This library gives an easy data access tool for different data sources.

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:

C#
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
<?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:

C#
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:

C#
_dal.RERaiseException = true;

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

C#
_dal.DefaultCommandType = CommandType.Text;

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

C#
_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:

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

If the query contains parameters, we set them:

C#
_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):

C#
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.

    Image 1

  • 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.

    Image 2

    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.

C#
_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.

License

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


Written By
Software Developer (Senior) Bulpros AD
Bulgaria Bulgaria
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
AnswerRe: can you provide an example whith params Object[] parameters? Pin
Plamen Kovandjiev25-Jul-10 19:21
professionalPlamen Kovandjiev25-Jul-10 19:21 
Questionhow to support Cursor parms for Oracle StoredProcedure to return DataSet? Pin
JLKEngine00824-Jul-10 20:54
JLKEngine00824-Jul-10 20:54 
GeneralAny 2005 version Pin
McRey Marcelo19-Jul-10 22:49
McRey Marcelo19-Jul-10 22:49 
GeneralRe: Any 2005 version Pin
Plamen Kovandjiev20-Jul-10 8:31
professionalPlamen Kovandjiev20-Jul-10 8:31 
Questioncan you provide a Example with Oracle StoredProcedure to return DataSet? Pin
JLKEngine00812-Jul-10 22:40
JLKEngine00812-Jul-10 22:40 
AnswerRe: find a error in AddParametersCommand! ------暂时去掉参数验证 Pin
Plamen Kovandjiev13-Jul-10 9:18
professionalPlamen Kovandjiev13-Jul-10 9:18 
Generalcan you provide a Example with StoredProcedure to return DataSet? Pin
JLKEngine00813-Jul-10 22:47
JLKEngine00813-Jul-10 22:47 
GeneralRe: can you provide a Example with StoredProcedure to return DataSet? Pin
Plamen Kovandjiev14-Jul-10 8:58
professionalPlamen Kovandjiev14-Jul-10 8:58 
Once you fix the problem with SP and will publish examples. I am currently very busy. Fix and the examples I will take several weeks.
QuestionCan CommandType support StoredProcedure? Pin
JLKEngine0089-Jul-10 21:58
JLKEngine0089-Jul-10 21:58 
AnswerRe: Can CommandType support StoredProcedure? Pin
Plamen Kovandjiev12-Jul-10 9:22
professionalPlamen Kovandjiev12-Jul-10 9:22 
GeneralCan support Oracle StoredProcedure which return DataSet? Pin
JLKEngine00824-Jul-10 21:01
JLKEngine00824-Jul-10 21:01 
GeneralExcellent Code.... Pin
Gary Noble9-Mar-09 4:13
Gary Noble9-Mar-09 4:13 
GeneralRe: Excellent Code.... Pin
Plamen Kovandjiev9-Mar-09 8:40
professionalPlamen Kovandjiev9-Mar-09 8:40 
GeneralSQL server compact Pin
aldo hexosa18-Dec-08 16:34
professionalaldo hexosa18-Dec-08 16:34 
GeneralRe: SQL server compact Pin
Plamen Kovandjiev20-Dec-08 5:26
professionalPlamen Kovandjiev20-Dec-08 5:26 
QuestionAny support for SQLite? Pin
Tawani Anyangwe6-Dec-08 4:37
Tawani Anyangwe6-Dec-08 4:37 
AnswerRe: Any support for SQLite? Pin
Plamen Kovandjiev7-Dec-08 3:01
professionalPlamen Kovandjiev7-Dec-08 3:01 
GeneralExcellent Pin
thund3rstruck5-Dec-08 9:50
thund3rstruck5-Dec-08 9:50 
GeneralMySQL Pin
Member 178098924-Nov-08 5:40
Member 178098924-Nov-08 5:40 
GeneralRe: MySQL Pin
Plamen Kovandjiev24-Nov-08 7:24
professionalPlamen Kovandjiev24-Nov-08 7:24 
JokeRe: MySQL Pin
Plamen Kovandjiev7-Dec-08 2:55
professionalPlamen Kovandjiev7-Dec-08 2:55 
QuestionWhere's the code? Pin
PIEBALDconsult20-Oct-08 5:43
mvePIEBALDconsult20-Oct-08 5:43 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.