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

1. Introduction

DALC4NET is an Open Source data access layer built for Microsoft .NET projects.  This enables us to access data from SQL Server, Oracle, MySql, MS Access, MS Excel etc. data bases.

DALC4NET is developed using C#.NET.  The Microsoft .NET Framework 2.0 is required to use DALC4NET.

Users are free to modify the source code as per their need.

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

2. Various Providers

Database	Provider to be used
MS SQL Server	System.Data.SqlClient
Oracle	System.Data.OracleClient
MySQL	MySql.Data.MySqlClient
MS Access/ MS Excel	System.Data.OleDb
MS Access/ MS Excel	System.Data.Odbc

3. How to use DALC4NET?

1. Download DALC4NET.dll from C-Sharp Corner
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 facilitates 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.

1. No Parameter
   
   
   
   This instance does not require any parameter. This overload creates a connection using the connection string name of the default connection.
   
   
   
   Note: For using this overload ad an appSettings key "defaultConnection" and set your appropriate connection's name as the value for this key.  This is the most recommended overload as we need not do any kind of code changes if we want to switch the database. E.g. if the 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 the application to use SQL Server set value="sqlCon" for the appSetting's key=" defaultConnection". Then in the future if your client wants to use an Oracle database then after porting the oracle database you simply need to change the defaultConnection value i.e. value = "oracleCon"
    

2. Connection Name as a parameter
   This overload creates instance for the connection name specified into app/web.config file.
   
   
    

3. Connection String and Provider Name as parameters
   
   This overload creates instance for the specified connection string and provider name.
   
   
   

4. How To Execute SQL Command/ Stored Procedures
In section 2 we created an instance of the DBHelper class, say _dbHelper. We can execute any Sql Command as follows:

1. Execute SQL Command

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

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. 

5. DALC4NET Design Overview

DALC4NET is implemented following the design patterns Singleton, Provider and Factory design pattern.

DALC4NET has only three public classes i.e. DBHelper, DBParameter and DBParameterCollection

1. Singleton Design Pattern Implementation
   
       1. DBHelper class

DBHelper is a singleton class and it has three private constructors. The appropriate constructor is called by 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 the appropriate constructor.

private static DBHelper _dbHelper = null;

public static DBHelper GetInstance()

         {

               if (_dbHelper == null)

                           _dbHelper = new DBHelper();

 

            return _dbHelper;

        }

2. AssemblyProvider class

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

1. Provider Pattern Implementation

   All the assemblies for each of the providers are maintained into Hastable data structure. There is a Hashtable which contains information about assembly details of each provider.

   _dbProviders Hashtable

   Key	Value
   System.Data.SqlClient	System.Data, version=2.0.0.0, culture=Neutral, PublicKeyToken=b77a5c561934e089
   System.Data.OracleClient	System.Data.OracleClient, version=2.0.0.0, culture=Neutral, PublicKeyToken=b77a5c561934e089
   MySql.Data.MySqlClient	MySql.Data, version=6.0.3.0, culture=Neutral, PublicKeyToken=c5687fc88969c44d
   ...	...

When the GetInstance method of this class is called then the above hashtable is used to determine and load the appropriate assembly for the requested provider using reflection.

The LoadAssembly method is responsible for loading the appropriate method.

 private void LoadAssembly(string providerName)

       {           

            string assemblyName =  _dbProviders[providerName].ToString();

            _assemblyName = = new AssemblyName(assemblyName);          

            _assembly = Assembly.Load(_assemblyName);           

    }

First of all this method gets the name of the assembly from the hashtable, then it instantiates the _assemblyName and then it loads the assembly.

 
6. DALC4NET Help

Use the DALC4NET tester to see how Sql Commands and Stored Procedures are executed. Here you may find the example for execution of various kind of sql command/ stored procedure execution and uses of their result. 

In order to use the DALC4NET Test application

1. Download the appropriate database backup (SQL Server/ My Sql)

2. Restore the backup with name DALC4NET_DB

Now you can play around with the sample code.