SqlClient Wrapper

Recently I got a chance to review code for an application. I find a lot of duplication when it comes to database access. The typical code snippet that duplicates is like the following:

public DataTable GetData(){
string conn = ConfigurationManager.ConnectionStrings["DemoProjectConnectionString"].ConnectionString;
string cmd = "SELECT * FROM Plans";
SqlConnection sconn = new SqlConnection(conn);
SqlCommand scmd = new SqlCommand(cmd, sconn);
DataSet ds = new DataSet();
SqlDataAdapter ada = new SqlDataAdapter(scmd);
ada.Fill(ds);
return ds.Tables[0];
}

Similar code repeats again and again in the application because the application has a number of business functions that need to call database procedures or execute sql statements to accomplish its task. This happened because there missed a layer between business functions and ADO.Net database access components. Unfortunately though, before Hibernate-type ORM layer was introduced, we don't have a standard abstraction layer to mitigate the gap.

Nowadays ORM layer has become de facto standard to accomplish model-driven application. In .Net we have LINQ to SQL and EF. ORM layer encapsulated the afore mentioned missing layer. However, to refactor code for a legacy application, jumping from old style code to ORM is more like rewriting the application. Also, old relational data centric approach could be better performance-wise, especially given the fact that ASP.Net controls support data binding to relational data.

So, a reasonable solution falls into building a custom layer that just wraps the required ADO.Net components and provide a simplistic interface to the business layer. Following is a wrapper I proposed.

Components

The wrapper consists of following classes:
1. Trans class: provides interface for unit of work that may be required from business context.
2. Db class: provides a wrapper for sql command, parameter, and execution logic.
3. DbResult class: provides return type from Db class Execute method.
4. ConnectionFactory class: provides sql connection instances.
　
Code example using the wrapper

// With Transaction
void TestTrans()
{
Trans t = Trans.Begin();
try{
string cmd1 = "INSERT INTO Plans(ID, NAME, DESCRIPTION) VALUES (31, 'BBB', 'BBB DESC')";
string cmd2 = "INSERT INTO Plans(ID, NAME, DESCRIPTION) VALUES (32, 'BBB', 'BBB DESC')";

Db.New().Statement(cmd1)
.ExecutionType(ExecutionType.NonQuery)
.Transaction(t)
.Execute();
Db.New().Statement(cmd2)
.ExecutionType(ExecutionType.NonQuery)
.Transaction(t)
.Execute();
t.Commit();
}catch(Exception e)
{
t.Rollback();
}
}
// Without Transaction
void GetData()
{
string cmd = "SELECT * FROM Plans";
DbResult dr = Db.New().Statement(cmd)
.ExecutionType(ExecutionType.DataSet)
.Execute();
Console.Read();
}

.Net platform has been armoured with rich of components (package System.Data and System.Data.*) to ultimately support database connections with a variety of protocols, to allow connections to different db product sources. However, in a given application, only a small set of very common featurs, rather than all features from the platform, are used, such as SqlConnection, SqlCommand, Transaction and so on. By wrapping only these commonly used components into a new layer, we provide business service layer a concise interface, and totally removed the tedious duplication of code. The code shown in this article is wrapping of SqlClient, it is not difficult to reshape it for other protocols.

For a full code package, you can request at kluan@procaseconsulting.com