Database Vendor Independent Code with Improved Connection Pooling

Description

ADO .NET provides us with SQL Server and OLE Db providers. I have seen many programmers code directly using the classes in the System.Data.SqlClient namespace while connecting to the SQL Server and using the classes in the System.Data.OleDb namespace while connecting to other databases like MS Access. They do this mainly because of the System.Data.SqlClient classes are optimized for SQL Server. Good. But what if somebody wants to switch databases? Say you initially thought of using MS Access and then later decided to switch to SQL Server. Now what? Do you rewrite all your business logic and database layer classes again using the SqlClient provider? Well... You will have to... if you need performance.

What if you had some high-level interface that acted as a wrapper over ADO .NET that totally abstracted the database? It would be great! Right? In that case, you will not have to rewrite your classes to switch the providers. Just tell the wrapper to switch the underlying provider at runtime.

That is exactly what my Sql Wrapper does. It provides you a JDBC like Wrapper on top of ADO. NET. The underlying database can be easily switched by changing a few configuration lines in the Application Configuration file. That's all. In addition, it also provides you with connection pooling that can speed up your application to a large extent. The pooling parameters can also be changing using the same configuration file.

The Sample Program:

The zip file comes with the wrapper DLL (KishoreSql.DLL) and a sample program (KishoreSqlSample.EXE). It also contains an MS Access DB (KishoreAccessDb.mdb) that is used by the sample program. In addition, it also contains all the source files. You can choose to have your database. The current sql wrapper supports Access and SQL Server but it can be easily extended for other databases. If you plan to use your own database for the sample, you are free to do so. The sample needs a database with two tables

Table 1: Role

Fields: Id (AutoIncrement) Name (VarChar) 50 Desc (VarChar) 50

Table 1: Permission

Fields: Id (AutoIncrement) Name (VarChar) 50

The sample form is shown below: It consists of two DB Grid controls. One to display roles and others for permissions. There are buttons for adding and deleting roles and permissions.

How to use SQL Wrapper:

The basic service for providing connection management is done by an object called DbConnectionManager. This is a singleton class and only one instance of this exists. As a part of its initialization, this object reads the driver and connection pooling configuration that are set in the configuration file. A sample configuration for the SQL Server and MS access is shown below.

<DbConnectionManager>
<ConnectionPool name="DemoPool">
<DriverClass>Kishore.Sql.SqlServer.Driver</DriverClass>
<ConnectionString>Persist Security Info=False;Initial Catalog=KishoreProject;Data Source=kishore;User ID=kishorch;Password=krckrc;Connect Timeout=10;</ConnectionString>
<MinimunConnections>2</MinimunConnections>
<MaximumConnections>10/MaximumConnections>
<ConnectionTimeout>60</ConnectionTimeout>
<ConnectionReaperDelay>60</ConnectionReaperDelay>
</ConnectionPool>
<ConnectionPool name="SamplePool">
<DriverClass>Kishore.Sql.OleDb.Driver</DriverClass>
<ConnectionString>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Learning\ADONET\Kishore\KishoreSqlSample\KishoreAccessDb.mdb</ConnectionString>
<MinimunConnections>2</MinimunConnections>
<MaximumConnections>10</MaximumConnections>
<ConnectionTimeout>60</ConnectionTimeout>
<ConnectionReaperDelay>60</ConnectionReaperDelay>
</ConnectionPool>
</DbConnectionManager>

The pool is configured with a Driver class to use during runtime. This driver must implement the Kishore.Sql.IDriver interface. The driver acts as a factory of connections. It is possible to configure more than one pool. For example you could have a pool of MSAccess connections and also one or more pools of SqlServer connections side by side. The DBConnectionManager handles all the pools. The pools are identified by a unique name.

A Connection can be retrieved from the DbConnectionManager by using the GetConnection() method as shown below:

IConnection conn = DbConnectionManager.GetInstance().GetConnection("SamplePool");

The code snippet below shows the IConnection interface

public interface IConnection {
IDbTransaction BeginTransaction();
IDbTransaction BeginTransaction(System.Data.IsolationLevel iso);
IStatement CreateStatement(string sql);
IPreparedStatement PrepareStatement(string sql);
ICallableStatement PrepareCall(string sql);
void Open();
void Close();
void ReOpen();
ConnectionState State { get; }
}

The "SamplePool" string in the GetConnection() call is the name of the pool that is configured in the configuration file. The DBConnectionManager internally delegates the request to the right IDriver implementation class for connections. There will be a separate implementation of the IDriver for every database. The current example ships with two implementations. One for MS Access and the other for SQL Server.

We got the connection. Now what? How do we execute SQL statements? There are different types of statements like a normal statement, prepared statements, stored procedures. How does the wrapper solve this?

The Wrapper uses JDBC style interfaces to solve this. But at the same time uses all the advantages that ADO .NET provides. So we get the best of both the worlds here. These interfaces will look very familiar to Java users. The code below shows how to use each of the above-mentioned statements.

Executing simple NonQueries
1. IStatement stmt = conn.CreateStatement("Delete from Role");
2. stmt.ExecuteNonQuery();

(Java guys, Does this look familiar? )

Executing Queries and reading results
1. IResultReader output = null;
2. IConnection conn = null;
3. try {
4. conn = DbConnectionManager.GetInstance().GetConnection("SamplePool");
5. IStatement stmt = conn.CreateStatement("Select * from Role");
6. output = stmt.ExecuteReader();
7. while (output.Read()) {
8. //Do processing
9. }
10. } catch (Exception e) {
11. ShowException(e);
12. } finally {
13. try {
14. if (output != null) {
15. output.Close();
16. }
17. } catch {}
18. if (conn != null) {
19. conn.Close();
20. }
21. }
Executing Queries using PreparedStatements
1. ...
2. IPreparedStatement stmt = conn.PrepareStatement("Insert into Role (Name, Description) Values(@name, @desc)");
3. stmt.SetParameter("name", this.name, SqlDbType.VarChar, NAME_SIZE);
4. stmt.SetParameter("desc", this.description, SqlDbType.VarChar, DESCRIPTION_SIZE);
5. IResultReader result = stmt.ExecuteReader();
6. ...
Executing Queries using StoredProcedures (CallableStatements)
1. ...
2. ICallableStatement stmt = conn.PrepareCall("kishorch.GetRoleName");
3. stmt.SetParameter("@id", 0, SqlDbType.Int, 4);
4. stmt.RegisterOutParameter("name", SqlDbType.VarChar, 50);
5. stmt.RegisterReturnParameter("count", SqlDbType.Int, 4);
6. int output = stmt.ExecuteNonQuery();
7. ...

Ok... That's not all...

...
int ExecuteNonQuery();
IResultReader ExecuteReader();
DataSet ExecuteDataSet();
void ExecuteDataSet(DataSet ds);
object ExecuteScalar();
XmlReader ExecuteXmlReader();
...

The IResultReader provides all the methods that the OleDbDataReader and SqlDataReader provide. The statement objects also provide methods to get DataSets from the query directly.

Most of the work of setting the right parameters and other things are handled by the Wrapper implementations making your business logic code and the database layer classes very clean and easy to understand. Also, the point to be noted it that it does not add any overhead in the wrapper. It is simply acting as a wrapper that just delegates the calls to the right object during runtime. So you are losing nothing but gaining the advantage of abstracting your business logic from the database and at the same time gaining the performance because of connection pooling.

How to Run Sample?

Unzip the file.
Modify the KishoreSqlSample.exe.config file found in the bin directory to point to the right database file.
Run the sample

What things are pending?

I implemented this wrapper because of two reasons:

Because I did not want business logic classes to know about the actual database provider it is using
Improving performance using Connection pooling
To learn and understand the ADO .NET framework

This code is not yet production quality. It can be improved by adding the missing APIs provided in ADO and JDBC. It needs more error checking to avoid it from crashing. Also in the OLEDB driver implementation (Kishore.Sql.OleDb namespace), the SqlDbToOleDbTypeMapping class does not handle all the data types. I just added whatever was required for the demo. This can be extended as required for any database vendor.