• Download source code and demo - 46.8 KB
• Download the compiled DALC4NET library - 7.91 KB
• Download sample SQL Server database - 144 KB
• Download sample MySQL database - 1.27 KB

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, MS Access, and MS Excel. DALC4NET was developed using C#.NET. Microsoft .NET Framework 2.0 is required to use DALC4NET. Users are free modify the source code as per their needs. For any feedback/ suggestions, you can mail the author at ak.tripathi@yahoo.com with the subject line ‘DALC4NET’.

Note: In order to connect with a MySQL database, you need to have the MySQL Connector for .NET, which may be downloaded from this 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 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 us for 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



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 the database. E.g., when an application is supposed to have three databases, MS 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".

2. Connection name as parameter



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

3. Connection string and provider name as parameters



This overload creates an 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, _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);

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 implements the following Design Patterns: Singleton, Provider, and Factory. DALC4NET has only three public classes: DBHelper, DBParameter, and DBParameterCollection.

5.1 Singleton Design Pattern implementation

5.1.1 DBHelper class

DBHelper is a singleton class and it has three private constructors. The 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 and returns that instance. If the 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;
}

5.1.2 AssemblyProvider class

The AssemblyProvider class is implemented as a singleton as this is the class that is 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 memory operation.

The Singleton implementation is similar as above.

5.2 Provider Pattern implementation

All the assemblies for each of the providers are maintained in a Hashtable data structure. There is a Hashtable which contains information about the 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, the above Hashtable is used to know and load the appropriate assembly for the requested provider using the concept of 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 loads the assembly.

6. DALC4NET help

Use the DALC4NET tester to see how SQL Commands / Stored Procedures are executed. Here you may find the example for execution of various kinds of SQL Command / Stored Procedure execution 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: