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

Pagination with Sql Query Result

When query database, you don't want to get all the result set of records, because of possible huge number. Users have to see them one page at a time anyways. What you do is providing page number and page size as parameters and get the right record set for viewing. In another case with what are called top-N, bottom-N and inner-N reporting, you also need winowing out certain range of records. To accomplish this, you can work out with different brand dbs by using slightly different clauses.

MySQL

With MySql, LIMIT clause provide the simplest solution. As shown in following example:

-- Get record from 501 to 600
SELECT * FROM EMPLOYEES WHERE id >10000 ORDER BY last_name LIMIT 100 OFFSET 500

SQL Server

With SQL Server, TOP is useful to only get certain records listed in the front:

SELECT TOP 20 * FROM EMPLOYEES WHERE ID>10000 ORDER BY LAST_NAME

However, it dosen't help if you want retrieve a page other than the first. What SQL Server has to offer for this problem is the ROW_NUMBER(), a system function for generating analytic data. Here is an example:

SELECT * FROM (
SELECT FIRST_NAME, EMAIL, ROW_NUMBER() OVER (ORDER BY LAST_NAME DESC) AS RN
FROM EMPLOYEES) WHERE RN BETWEEN 501 AND 600
) ORDER BY RN

Note, the inner query result is ordered by the order defined in ROW_NUMBER function. In case you have more than one ROW_NUMBER() functions in a query, as demonstrated in the following code snippet, the result is ordered based on the last function.

select * from (
select name, email, ROW_NUMBER() over (order by name desc) as rn,
ROW_NUMBER() over (order by name asc) as rn2
from contact) result where rn between 7 and 10
order by rn desc

I thought this solution is not a clean cut to pagination, because you end up getting a nested query and have to refactor the existing query to make it run. For example, you already have a query, now you just want to add pagination. Even SQL allows * and aggregation functions together as shown in following code snippet, that allows you to just add ROW_NUMBER() column without requiring to change much on the SELECT clause, you have to remove the ORDER BY clause from the original place and put it into ROW_NUMBER function's OVER clause or put into the outer query statement, because normally ORDER BY clause is not allowed in subquery.

select *, ROW_NUMBER() over (order by name desc) as rn from contact

Oracle

Don't mention that Oracle offers ROW_NUMBER() analytic function as with SQL SVR. But Oracle has a psuedo column ROWNUM that comes with any query. I believe it is most efficient way and can cover most cases of query pagination. Here is an example:

SELECT * FROM EMPLOYEES WHERE ROWNUM>50 AND ROWNUM<101 ORDER BY LAST_NAME

Most importantly, to add pagination to existing query you only need to add a condition on ROWNUM to the WHERE clause, no more further efforts.

More on ROW_NUMBER()

Surely with ROW_NUMBER() in SQL SVR and Oracle, you can do much more than pagination. Following example can give a heads up, it gets first five employees from all departments they have top smallest id in their departments:

SELECT * FROM
(
SELECT DEPARTMENT_NAME, LAST_NAME, EMPLOYEE_ID, ROW_NUMER()
OVER (PARTITION BY DEPARTMENT_NAME ORDER BY EMPLOYEE_ID) AS RN_ID
FROM EMPOYEES
)
WHERE RN_ID <6

Pagination with LINQ to SQL

To end this topic, I would like to have a look at LINQ to SQL. You must know it has skip(m) and take(n) feature (following code snippet). But, what it ends up translated into SQL?

var query = (from employee in Employees
select employee).Skip(10).Take(20);

Under the hood it is translated into something like this:

SELECT FIRST_NAME, LAST_NAME, EMAIL
FROM (
SELECT ROW_NUMBER() OVER (
ORDER BY FIRST_NAME, LAST_NAME, EMAIL) ROW_NUMBER,
FIRST_NAME,
LAST_NAME,
EMAIL
FROM EMPLOYEES
)
WHERE ROW_NUMBER BETWEEN @P0+1 AND @P0+@P1
ORDER BY ROW_NUMBER

Where @p0 is the value specified within Skip(), while @p1 is that within Take().

A couple of definitions

3-Tiers Application

Presentation Tier
Middle Tier
Data Tier

Middle Tier is further decoupled into following sub layers:

DAL - Data Access Layer
BLL - Business Logic Layer
Shared Components (such as Validation, Authentication etc.)

Data Tier is refered to database schema, stored procedures and so forth.



RESTful Service
Referered to Reprentational State Change, It sees a service as combination of resources and access verbs. A RESTful service is considered stateless treated as Datasource like a database.

Resource are represented by URLs and accessing verbs are by Http verbs.

Lo-REST (GET and POST)
Hi-REST - (DELETE, PUT as well)

The question is that do you trust your client to perform this critical operations? The answere is that it depends the application environment, the clients are preauthorized or not.

WADL - Web Application Description Language, it is the RESTful version of WSDL.

Use DataSet Genterator Build DAL

Visual 2005 comes with a DataSet Designer that is a convenient way to generate DAL and datasets as domain library. However you need to seperate the datasets and the TableAdapters, or you expose connection string and DAL logic that are not supposed to be exposed for security reason and from architecteure point of view. Unfortunately you have to cut and paste to a new project (assembly) manually. In Visual 2008 or Orcas, there is a DataSet Project property for a dataset, which can be used to seperate automatically when you compile the project


References

[1] MSDN Library, "Creating an N-Tier Data Application", http://msdn.microsoft.com/en-us/library/bb384570.aspx
[2] Guy Bursteins, "Orcas Datasets - Separate Datasets from TableAdapters", http://blogs.microsoft.co.il/blogs/bursteg/archive/2007/05/19/OrcasDatasets.aspx