DALC4NET (All in One Data Access Layer for .NET)

Introduction

DALC4NET is an Open Source data access layer built for Microsoft .NET projects.  This enables us accessing data from SQL Server, Oracle, MySql, MS Access, MS Excel etc data bases.
DALC4NET is developed using C#.NET.  Microsoft .NET Framework 2.0 is required to use DALC4NET.Users are free modify the source code as per their need.

For any feedback/ suggestion you can mail the author at ak.tripathi@yahoo.com with subject line ‘DALC4NET’

Note: In order to connect with MySql database you need to have MySql connector for .NET. Which may be downloaded from the below url
http://dev.mysql.com/downloads/connector/net/

Various Providers 

How to use DALC4NET?

1.    Download DALC4NET.dll from http://www.codegain.com/dalc4net/
2.    Add reference of the DALC4NET.dll to your project
3.    Import the namespace DALC4NET (e.g. using DALC4NET;)
4.    Create instance of DBHelper class of DALC4NET library. This class facilitate us for execution of any kind of SQL Command or stored procedure.

DBHelper.cs is a singleton class and hence we will not see any constructor for DBHelper class (singleton class has private contructor). GetInstance() method can be used for creating the instance of the class. GetInstance() method has three overloads.

i.    No Parameter

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

Note: For using this overload ad an appSettings key “defaultConnection” and set you appropriate connection’s name as the value for this key.  This is the most recommended overload as we need not to do any kind of code changes if we want switch over the database. E.g. Application is supposed to have three databases MS SQL Server, Oracle and MySql. Create three Connection strings into app/web.config file’s connectionString’s section say sqlCon, oracleCon, mySqlCon. If you want application to use SQL Server set value=”sqlCon” for the appSetting’s key=” defaultConnection”. In future if your client want to use oracle database then after porting the oracle database you simply need to change the defaultConnection value i.e. value = “oracleCon”

ii.    Connection Name as a parameter 

This overload creates instance for the connection name specified into app/web.config file.
iii.    Connection String and Provider Name as parameters 

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

How Execute SQL Command/ Stored Procedures

In section 2 we created instance of the DBHelper class say _dbHelper. We can execute any Sql Command as follows
4.1    Execute SQL Command

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

4.2    Execute Stored Procedure with parameters

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

In the similar way we may use the appropriate method and overload to execute Sql Command or stored procedure.

DALC4NET Design Overview

DALC4NET is implements following design patterns Singleton, Provider and Factory design pattern.
DALC4NET has only three public classes i.e. DBHelper, DBParameter and DBParameterCollection 

Singleton Design Pattern Implementation

DBHelper class

DBHelper is a singleton class and it has three private constructors. Appropriate constructor is called on invoking the static method GetInstance. This method first of all checks if there is any live instance of the class then that instance is returned. If instance is null (i.e. no live instance) then a new instance is created using appropriate constructor.

private static DBHelper _dbHelper = null;
public static DBHelper GetInstance()
      	{
            	if (_dbHelper == null)
                		_dbHelper = new DBHelper();

            return _dbHelper;
        	}

AssemblyProvider class

AssemblyProvider class also is implemented as singleton as this is the class responsible for loading the appropriate assembly for the specified provider. If this class is not implemented as singleton then every time when this class is instantiated assembly is loaded, this may be costly operation for memory.
Singleton implementation is similar as above.

Download Sample Projects 

Download DALC4NET Dll -7 kb

Download source files -50 kb

Summary

DALC4NET is an Open Source data access layer for Microsoft.NET projects. This enables users to connect any kind of database (SQL Server/ Oracle/ My Sql/ MS Access/ MS Excel etc) in optimal fashion.Ability to connect to MySql database using MySql Connector for .NET is the key feature of the DALC4NET. In fact various data access layer components available mainly connects to Sql Server/ Oracle etc.. but not MySql Database using the connector provided by MySql.