DALC4NET: A Generic Data Access Layer for .NET

• Download source code and demo - 90.72 KB
• Download the compiled DALC4NET library - 6.44 KB
• Download sample SQL Server database - 144 KB
• Download sample MySQL database - 1.28 KB

1. Introduction

DALC4NET is an Open Source data access layer built for Microsoft .NET projects. It enables us to access data from databases including SQL Server, Oracle, MySQL, Microsoft Access, and Microsoft Excel. DALC4NET was developed using C#.NET. Microsoft .NET Framework 2.0 is required to use DALC4NET. Users can freely modify the source code as per their needs. For any feedback/ suggestions, you can email the author at ak.tripathi@yahoo.com.

Note: In order to connect with a MySQL database, follow the steps below:

1. Click here to download the MySQL Connector for .NET.
2. Install the downloaded MySQL Connector. This will install the MySql.Data assembly to your GAC (Global Assembly Cache).
3. Add the following lines to your App.config/ Web.config file in order to indicate the version and public key token of the newly installed assembly.
XML

<system.data>
    <DbProviderFactories>
        <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient"
          description=".Net Framework Data Provider for MySQL"
          type="MySql.Data.MySqlClient.MySqlClientFactory,
                MySql.Data,Version=6.0.3.0,
                Culture=neutral,PublicKeyToken=c5687fc88969c44d"/>
    </DbProviderFactories>
</system.data>

4. Use the correct Version and PublicKeyToken of the installed MySQL Connector for .NET (modify the highlighted attributes).
5. In order to know the Version and PublicKeyToken of a .NET assembly, you may go to Start -> Run -> Type assembly, and hit the Enter key. Search for the assembly MySql.Data and right click on it. A new window will open; copy the Version and PublicKeyToken and use those values in your config file.

2. Various Providers

3. How to use DALC4NET?

1. Download DALC4NET.dll from http://www.codeproject.com/dalc4net/
2. Add a reference to DALC4NET.dll to your project
3. Import the namespace DALC4NET (e.g., using DALC4NET;)
4. Create an instance of the DBHelper class of the DALC4NET library; this class facilitates execution of any kind of SQL Command or Stored Procedure

DBHelper is a Singleton class and hence we will not see any constructor for the DBHelper class (Singleton classes have a private constructor). The GetInstance() method can be used for creating the instance of the class. The GetInstance() method has three overloads:

1. No parameter
C#

private DBHelper _dbHelper = new DBHelper();

This instance does not require a parameter. This overload creates a connection for the connection string name mentioned as the default connection.



Note: For using this overload, add an appSettings key “defaultConnection" and set your appropriate connection’s name as the value for this key. This is the most recommended overload as you need not do any kind of code change if you want to switch to a different database. E.g., let's say an application is supposed to have three databases, Microsoft SQL Server, Oracle, and MySQL. Create three connection strings in app/web.config file’s connectionString section, say sqlCon, oracleCon, mySqlCon. If you want the application to use SQL Server, set value="sqlCon" for appSetting’s key="defaultConnection". In future, if your client wants to use an Oracle database, then after porting the Oracle database, you simply need to change the defaultConnection value: value = “oracleCon".

2. Connection name as parameter
C#

private DBHelper _dbHelper = new DBHelper("sqlCon");

This overload creates an instance for the connection name specified in the app/web.config file.

3. Connection string and provider name as parameters
C#

private DBHelper _dbHelper = new DBHelper("Server=1.1.1.1;
Initial Catalog=SOME_DB_NAME;UserId=sa;Password=sa;",
"System.Data.SqlClient");

This overload creates an instance for the specified connection string and provider name.

4. How to Execute SQL Commands/Stored Procedures

In section 2, we created an instance of the DBHelper class, _dbHelper. We can now execute any SQL Command as follows:

4.1 Execute a SQL Command

string sqlCommand = "SELECT Count(1) FROM USERDETAILS";
object objCont = _dbHelper.ExecuteScalar(sqlCommand);

4.2 Execute a Stored Procedure with parameters

object objCont = _dbHelper.ExecuteScalar("PROC_DALC4NET_EXECUTE_SCALAR_SINGLE_PARAM",
                           new DBParameter("@FIRSTNAME", "ashish"),
                           CommandType.StoredProcedure);

4.3 Execute a Stored Procedure with multiple parameters and using Transaction

int rowsAffected = 0;
DBParameter param1 = new DBParameter("@FIRSTNAME", "Yash");
DBParameter param2 = new DBParameter("@LASTNAME", "Tripathi");
DBParameter param3 = new DBParameter("@EMAIL", "yash.tripathi@yahoo.com");

DBParameterCollection paramCollection = new DBParameterCollection();
paramCollection.Add(param1);
paramCollection.Add(param2);
paramCollection.Add(param3);

IDbTransaction transaction = _dbHelper.BeginTransaction();

try
{
    rowsAffected = _dbHelper.ExecuteNonQuery
        ("PROC_DALC4NET_EXECUTE_NON_QUERY_STORED_PROC_MULTIPLE_PARAM",
    paramCollection, transaction, CommandType.StoredProcedure);
    message = rowsAffected > 0 ? "Record inserted successfully." : 
                "Error in inserting record.";
    _dbHelper.CommitTransaction(transaction);
}
catch (Exception err)
{
    _dbHelper.RollbackTransaction(transaction);
}

In a similar way, we can use the appropriate method and overload to execute a SQL Command or Stored Procedure.

5. DALC4NET Design Overview

DALC4NET mainly implements Provider and Factory patterns to enable the library to connect with any kind of database. DALC4NET has only three public classes: DBHelper, DBParameter, and DBParameterCollection.

5.1 DBHelper class

DBHelper is a public class which enables the user to communicate with the database with the help of three public constructors.

#region "Constructor Methods"

        /// <summary>
        /// This Constructor creates instance of the class for defaultConnection
        /// </summary>
        public DBHelper()
        {
           //SOME CODE HERE...
        }

        /// <summary>
        /// This constructor should be used for creation of the instance 
        /// for the specified app settings connection name
        /// </summary>
        /// <param name="connectionName">App Setting's connection name</param>
        public DBHelper(string connectionName)
        {
            //SOME CODE HERE...
        }

        /// <summary>
        /// Constructor creates instance of the class for the specified connection 
        /// string and provider name
        /// </summary>
        /// <param name="connectionString">Connection String</param>
        /// <param name="providerName">Provider name</param>
        public DBHelper(string connectionString, string providerName)
        {
            //SOME CODE HERE...
        }

        #endregion

5.2 AssemblyProvider class

The AssemblyProvider class implements and uses the Factory design pattern to obtain the appropriate provider type from the provider name.

internal DbProviderFactory Factory
    {
        get
        {
            DbProviderFactory factory = DbProviderFactories.GetFactory(_providerName);
            return factory;
        }
    }

5.2 Provider Pattern Implementation

DALC4NET implements the Provider design pattern. Nowhere into the code will you find a reference to any specific connector, i.e., System.Data.SqlClient or System.Data.OracleClient etc. The appropriate assembly or type is loaded and instantiated depending on the provider name specified in the connection string.

6. DALC4NET Help

Use the DALC4NET tester to see how SQL Commands / Stored Procedures are executed. Here you may find an example for execution of various kinds of SQL Commands / Stored Procedures and the uses of their result.

In order to use the DALC4NET Test application:

1. Download the appropriate database backup (SQL Server/ MySQL)
2. Restore the backup with the name DALC4NET_DB

Now you can play around with the sample code:

Changes Made in this Update

Initially DALC4NET library was implemented using a singleton design pattern which has some limitations, i.e., You cannot connect to multiple databases at a time for example: Suppose you have a scenario where you want to read the data from an Excel file and insert these data to SQL Server database. In such a case, you are supposed to interact with two databases, i.e., Excel (for Reading the Data) and SQL Server (For Inserting the data). If Singleton Pattern was used, then to achieve the above said scenario was not possible. Now a user can interact with any number of databases at a time.