Click here to Skip to main content
15,867,488 members
Articles / Database Development / MySQL

DALC4NET: A Generic Data Access Layer for .NET

Rate me:
Please Sign up or sign in to vote.
4.73/5 (33 votes)
1 Feb 2012Public Domain5 min read 176.3K   20.6K   119   47
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. 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>
  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.

Image 1

2. Various Providers

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

    Image 2

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

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

4.2 Execute a Stored Procedure with parameters

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

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

Image 3

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.

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

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

Image 4

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


Written By
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

Comments and Discussions

 
QuestionDo you have DALC4NET in asp.net with vb code behind Pin
kbabsu19-Aug-21 22:58
kbabsu19-Aug-21 22:58 
QuestionProvider Oracle.DataAccess is not working. Pin
Member 1481226515-Jul-20 19:05
Member 1481226515-Jul-20 19:05 
BugConnection pool issue Pin
Diwesh Shrivastava17-Sep-17 21:27
Diwesh Shrivastava17-Sep-17 21:27 
This is a good library for DB access layer, but I found a bug which was causing a big issue.

Bug: Facing DB Connection issue because it reaches to max connection pool.

Root cause: During creation of object of class DBHelper, code is setting _connection which is opening a connection of DB, but this variable is never used. Hence this connection remains open and causing connection issue for the application.

Fix: Open DBHelper.cs, go to its constructor and comment the below line:
_connection = _connectionManager.GetConnection()

Repeat the same in all 3 constructors. You can also comment the declaration of this variable.

@Ashish Tripathi: Thanks you for your good work and providing this library. Please validate the bug and upload the fixed version so that everyone can use it without any issue.

Hope this will help.

Thanks.
Diwesh
QuestionIs connections managed by this library (closing)? Pin
carlito_brigante4-Mar-17 6:57
carlito_brigante4-Mar-17 6:57 
QuestionObject reference not set to an instance of an object Pin
5G5-Jan-17 5:36
5G5-Jan-17 5:36 
QuestionAny updates, my vote 5 Pin
longnights30-Dec-16 12:18
longnights30-Dec-16 12:18 
QuestionGet Value for Output parameter with Stored Procedure Pin
techchallenger25-Dec-16 9:01
techchallenger25-Dec-16 9:01 
QuestionCan some help about this 'MySql.Data.MySqlClient' is already present. Pin
rahul_ahuja42021-Sep-14 7:55
rahul_ahuja42021-Sep-14 7:55 
QuestionMaybe useful...but... Pin
Member 101937535-Aug-14 10:15
Member 101937535-Aug-14 10:15 
QuestionLast Inserted ID Pin
... Dani ...21-Jul-14 6:37
... Dani ...21-Jul-14 6:37 
QuestionMaster Detail Information for Execute Non Query Pin
me.HappySmile18-May-14 19:49
me.HappySmile18-May-14 19:49 
QuestionImplementing Connection Pooling Pin
PiratePrasad22-Apr-14 21:55
PiratePrasad22-Apr-14 21:55 
GeneralMy vote of 1 Pin
Akhil Mittal12-Aug-13 0:30
professionalAkhil Mittal12-Aug-13 0:30 
GeneralUnnecessary Pin
KChandos18-Jul-13 7:43
professionalKChandos18-Jul-13 7:43 
AnswerVery good!! Pin
felipeaj9-May-13 10:03
felipeaj9-May-13 10:03 
GeneralMy vote of 5 Pin
fredatcodeproject1-May-13 11:01
professionalfredatcodeproject1-May-13 11:01 
QuestionHow to use Dispose Command in DALC4NET? Pin
Priyaaammu15-Feb-13 18:41
Priyaaammu15-Feb-13 18:41 
GeneralSQL Connection Pool issue Pin
santokhhcl20-Dec-12 18:02
santokhhcl20-Dec-12 18:02 
GeneralRe: SQL Connection Pool issue Pin
crackcode8510-Jan-13 11:33
crackcode8510-Jan-13 11:33 
GeneralRe: SQL Connection Pool issue Pin
felipeaj20-May-13 7:54
felipeaj20-May-13 7:54 
GeneralRe: SQL Connection Pool issue Pin
PiratePrasad10-May-14 9:52
PiratePrasad10-May-14 9:52 
GeneralRe: SQL Connection Pool issue Pin
Diwesh Shrivastava17-Sep-17 21:11
Diwesh Shrivastava17-Sep-17 21:11 
QuestionIssue in DBHelper.cs Pin
Aaron 00719-Dec-12 9:51
Aaron 00719-Dec-12 9:51 
QuestionGreat work! Pin
tp200616-Oct-12 11:09
tp200616-Oct-12 11:09 
QuestionI in use found a lot of connection is not closed yet Pin
tudousi9910-Sep-12 23:49
tudousi9910-Sep-12 23:49 

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.