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 email@example.com.
Note: In order to connect with a MySQL database, follow the steps below:
- Click here to download the MySQL Connector for .NET.
- Install the downloaded MySQL Connector. This will install the
MySql.Data assembly to your GAC (Global Assembly Cache).
- 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.
<add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient"
description=".Net Framework Data Provider for MySQL"
- Use the correct
PublicKeyToken of the installed MySQL Connector for .NET (modify the highlighted attributes).
- In order to know the
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
PublicKeyToken and use those values in your config file.
2. Various Providers
|Database||Provider to be used|
Microsoft SQL Server
Microsoft Access / Microsoft Excel
Microsoft Access / Microsoft Excel
3. How to use DALC4NET?
- Download DALC4NET.dll from http://www.codeproject.com/dalc4net/
- Add a reference to DALC4NET.dll to your project
- Import the namespace
- 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:
- No parameter
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
mySqlCon. If you want the application to use SQL Server, set
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
value = “oracleCon".
- Connection name as parameter
private DBHelper _dbHelper = new DBHelper("sqlCon");
This overload creates an instance for the connection name specified in the app/web.config file.
- Connection string and provider name as parameters
private DBHelper _dbHelper = new DBHelper("Server=126.96.36.199;
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. 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"),
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", "firstname.lastname@example.org");
DBParameterCollection paramCollection = new DBParameterCollection();
IDbTransaction transaction = _dbHelper.BeginTransaction();
rowsAffected = _dbHelper.ExecuteNonQuery
paramCollection, transaction, CommandType.StoredProcedure);
message = rowsAffected > 0 ? "Record inserted successfully." :
"Error in inserting record.";
catch (Exception err)
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
5.1 DBHelper class
DBHelper is a
public class which enables the user to communicate with the database with the help of three
#region "Constructor Methods"
public DBHelper(string connectionName)
public DBHelper(string connectionString, string providerName)
5.2 AssemblyProvider class
AssemblyProvider class implements and uses the Factory design pattern to obtain the appropriate provider type from the provider name.
internal DbProviderFactory Factory
DbProviderFactory factory = DbProviderFactories.GetFactory(_providerName);
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.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:
- Download the appropriate database backup (SQL Server/ MySQL)
- Restore the backup with the name
Now you can play around with the sample code:
Changes Made in this Update
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.