Click here to Skip to main content
Click here to Skip to main content
Go to top

DALC4NET: A Generic Data Access Layer for .NET

, 1 Feb 2012
Rate this:
Please Sign up or sign in to vote.
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, MS Access, and MS Excel.

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.
  4. <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>
  5. Use the correct Version and PublicKeyToken of the installed MySQL Connector for .NET (modify the highlighted attributes).
  6. 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

Database Provider to be used

Microsoft SQL Server

System.Data.SqlClient

Oracle

System.Data.OracleClient

MySQL

MySql.Data.MySqlClient

Microsoft Access / Microsoft Excel

System.Data.OleDb

Microsoft Access / Microsoft Excel

System.Data.Odbc

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
  2. 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".

  3. Connection name as parameter
  4. private DBHelper _dbHelper = new DBHelper("sqlCon");

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

  5. Connection string and provider name as parameters
  6. 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.

License

This article, along with any associated source code and files, is licensed under A Public Domain dedication

Share

About the Author

Ashish Tripathi
Founder Aspirea Technologies Pvt Ltd
India India
• 8 years of experience in IT Industry as a Developer.
• Experience of End-To-End Software Development and Implementation (Entire SDLC i.e Software Development Life Cycle)
• Real time Exposure to Banking, Finance and Energy industry.
• Expertise in distributed application architecture as well as web based applications using Microsoft.NET platform.
• Expertise in database design, SQL programming and SQL performance tuning.
• Expertise in Web Services and WCF Services.
• Experience of Rich Internet Application using Adobe Flex.
• Experience in migration of legacy application to latest technology, migration of VB application to .NET.
• Knowledge of OOPS and Design Concepts.
• Expertise in Agile/ Scrum software development processes.
 
Specialties
• Languages\ Technologies-
.NET Framework 1.1/2.0/3.0/3.5, C#.NET, VB.NET, ASP.NET, VB6, AJAX, ASP.NET, Adobe Flex 3.0, Web Services, Windows Communication Foundation (WCF), LINQ, SQL Server, Oracle, MySql, MS Access, HTML, XML, JavaScript, C# Script, CSS and XSLT.
 
• Methodology/ Concepts-
OOPS, Data Structures, Design Concepts and Agile/ Scrum Software Development
Follow on   Twitter

Comments and Discussions

 
QuestionMaybe useful...but... PinmemberMember 101937535-Aug-14 10:15 
QuestionLast Inserted ID Pinmember... Dani ...21-Jul-14 6:37 
QuestionMaster Detail Information for Execute Non Query PinmemberMember 1051252818-May-14 19:49 
QuestionImplementing Connection Pooling PinmemberPiratePrasad22-Apr-14 21:55 
GeneralMy vote of 1 PingroupAkhil_Mittal12-Aug-13 0:30 
GeneralUnnecessary PinmemberKChandos18-Jul-13 7:43 
AnswerVery good!! Pinmemberfelipeaj9-May-13 10:03 
GeneralMy vote of 5 Pinmemberfredatcodeproject1-May-13 11:01 
QuestionHow to use Dispose Command in DALC4NET? PinmemberPriyaaammu15-Feb-13 18:41 
GeneralSQL Connection Pool issue Pinmembersantokhhcl20-Dec-12 18:02 
GeneralRe: SQL Connection Pool issue Pinmembercrackcode8510-Jan-13 11:33 
GeneralRe: SQL Connection Pool issue Pinmemberfelipeaj20-May-13 7:54 
GeneralRe: SQL Connection Pool issue PinmemberPiratePrasad10-May-14 9:52 
QuestionIssue in DBHelper.cs PinmemberMember 964879319-Dec-12 9:51 
QuestionGreat work! Pinmembertp200616-Oct-12 11:09 
QuestionI in use found a lot of connection is not closed yet Pinmembertudousi9910-Sep-12 23:49 
GeneralMy vote of 5 Pinmembereg_Anubhava7-Sep-12 16:51 
Questionhow to use ExecuteDataSet ??? PinmemberSunLucDong14-Jul-12 2:30 
QuestionBrilliant Pinmemberrohit kakria27-Apr-12 22:07 
GeneralMy vote of 5 Pinmembermeltamps7-Feb-12 22:52 
QuestionORM PinmemberLaserson1-Feb-12 18:18 
Questionyet another DAL Pinmemberdevvvy1-Feb-12 14:40 
why not LINQ (with adapters for other db besides M$ SQL) or NHibernate? so many DAL's
 
Personally, I just prefer LINQ for SQL which lets me simply drag a table to canvas, generates the classes (save me lots of time) yes I'm lazy
dev

GeneralMy vote of 5 PinmemberShahin Khorshidnia1-Feb-12 11:05 
GeneralMy vote of 3 PinmemberDean Oliver1-Feb-12 8:17 
GeneralMy vote of 3 PinmemberTito71708-May-11 3:03 

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 | Mobile
Web03 | 2.8.140916.1 | Last Updated 1 Feb 2012
Article Copyright 2011 by Ashish Tripathi
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid