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?
- Download DALC4NET.dll from C-Sharp Corner
- Add reference of the DALC4NET.dll to your project
- Import the namespace DALC4NET (e.g. using DALC4NET;)
- 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. - 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"
-
Connection Name as a parameter
This overload creates instance for the connection name specified into app/web.config file.
- 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: -
Execute SQL Command
string sqlCommand = "SELECT Count(1) FROM USERDETAILS";
object objCont = _dbHelper.ExecuteScalar(sqlCommand);
-
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
- 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.
-
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.
