Introduction

Imagine that we are developing an application that needs to support multiple data base systems.

What options do we have?

Basically I would say that we have three options

  • Stick to ANSI SQL and make sure that all SQL runs on all supported databases.
  • Keep separate versions of each statement for each target database.
  • Use an OR mapper that abstracts the SQL away from your code

 

Let’s quickly review each option

ANSI SQL

The ANSI standard is a standard that aims to define a common way of expressing SQL syntax.
What this means is that we should be able to write one query and live happily ever after regardless of the target DBMS.

Sweet, but is this really the case?

Let’s put this to the test and write a query that returns the string length of some column.

We start of using MS SQL Server

    SELECT LEN(ProductName) FROM Products	

What if we take the same query and execute it against MySql?

“FUNCTION northwind.LEN does not exist” 

Now that is not exactly the result we were hoping for. What could be wrong?

Obviously MySql does not understand the LEN function, but why?

If we investigate further we find out that the ANSI SQL version of LEN would be CHAR_LENGTH.

Okay, lets rewrite the query and hit MySql again.

 SELECT CHAR_LENGTH(ProductName) FROM Products

Success!!!

We now have a query that is compliant with the ANSI standard.

So this should work if we switch back to MS SQL Server again too, right?

Let’s find out

“'CHAR_LENGTH' is not a recognized built-in function name.”

No good, SQL Server does not support this function and we are pretty much back maintaining two versions of this query, one for MS Sql Server and one for MySql.

And we see this all over the place.  Most database systems implement the functionality defined by the standard, but under different names.

You would be surprised to see how much they differ although they might claim to be  ANSI compliant to some extent.

Keep separate versions of the query for each DBMS

I don't think so :)

Use an OR mapper

Most object relation mappers, such as NHibernate and Entity framework support a wide range of database systems.

The abstraction is built into the mapper itself so that we don’t need to think about the semantics of each database.

Back in the days, each OR mapper provides their own query language that pretty much tied your code to that specific mapper, such as NHibernate.

With Linq (Language Integrated Query Language) we are able to abstract ourselves from the underlying provider and work with data through a common interface (Linq).

The bits and pieces are starting to fall into place.

The only problem is that this abstraction is built into the Linq provider itself and can not be used in other scenarios.

And what if we don’t wish to use an OR mapper and we still want to be DBMS independent?

Introducing DbExpressions

In an effort to solve the problems described above, the idea was to abstract the implementation details for each DBMS into an abstract syntax tree and provide a fluent interface to help build this tree.

The following requirement were set for this library

Support all four query types (SELECT, UPDATE, DELETE AND INSERT)

Support most common basic string,numeric and datetime functions

Support most common language constructs, such as JOIN, GROUP BY, ORDER BY and so on

Support a provider model that makes it easy to implement new providers for new database systems.

Support MS Sql Server and MySql  out of the box.

 

How does this work?

If you are familiar with the way Expressions works in .Net, you will soon get the idea behind this library.

While for instance, an Expression<T> delegate compiles into IL, a DbExpression “compiles“  into the vendor specific SQL dialect.

The query is represented by a set of nodes in a tree that when visited (Visitor pattern), translates each DbExpression to the expected syntax.

There are a lot of different expression types and they are available using the static DbExpressionFactory  class.

This means that we can construct an expression tree manually like this.

var dbExpressionFactory = new DbExpressionFactory(); 
var columnExpression = dbExpressionFactory.Column("SomeColumn"); 
var constantExpression = dbExpressionFactory.Constant(1); 
var binaryExpression = dbExpressionFactory.MakeBinary(DbBinaryExpressionType.Equal, columnExpression, constantExpression); 
Console.WriteLine(binaryExpression);

The output from this DbExpression when translated(MS SQL) would be

“([SomeColumn] = @p0)”

While constructing the expression tree manually can be useful in some scenarios, it can be pretty cumbersome if all we want to do is to write a query and execute it.

The library provides a fluent interface that enables us to write a query like this:

selectQuery.Select(f => f.Length(f.Column("ProductID"))) 
	.From(f => f.Table("Products")); 

The Select method has two overload defined like this: 

DbSelectQuery Select(this DbSelectQuery dbSelectQuery, params Func<dbexpressionfactory, dbexpression>[] expressionSelector)

DbSelectQuery Select(this DbSelectQuery dbSelectQuery, DbExpression expression)

An instance of the DbExpressionFactory is passed into the Select method so that it makes it easy to construct the query. 

As we can see there is also an overload that just takes a DbExpression instance and that is very powerful if we are using a combination of manually constructing pieces of the query and the fluent API. 

var factory = new DbExpressionFactory(); 
var projection = factory.List(new[] {factory.Column("SomeColumn"), factory.Column("SomeOtherColumn")}); 
var selectQuery = new DbSelectQuery(); 
selectQuery.Select(projection).From(f => f.Table("SomeTable"));

Now , that looks a lot better than string concatenation and it is DBMS independent.Using the code

Paging 

Paging is the ability to select a "page" of data that means that out of 1000 possible rows we may want to return row 10 through 20.

The SQL syntax for this can vary a lot between the different SQL dialects.

MySql solves this very elegant with the LIMIT and OFFSET keywords, while in Sql Server this becomes a little more complicated.

The DbExpressions library provides Skip and Take methods similar to the ones we find in Linq.

selectQuery.Select(f => f.Column("ProductID")) 
	.From(f => f.Table("Products")).Skip(10).Take(10);

This means that we now can write queries with paging without worrying about the actual syntax needed. 

Translating the expression into SQL

Translating the query into SQL is just a simple matter of visiting every node in the expression tree and return the appropriate syntax according to the target DBMS.

Some of you may recall the ExpressionVisitor class first discussed by Matt Warren and later made available to us in the MSDN documentation.

The ExpressionVisitor class takes care of visiting every node and rewrite the tree if something has changed. 

This class is somewhat essential when it comes to translating a Expression<T> delegate into another representation, such as SQL.

The DbExpressions library has a similar class that is used as a base class whenever we need to inspect (or rewrite) the expression tree.

Derived from the DbExpressionVisitor class we find the abstract DbQueryTranslator  that serves as the base class for provider specific query translators.

If we take a look at the MySqlQueryTranslator class we find the following code that translates a DbColumnExpression into something that MySql can understand.

protected override DbExpression VisitColumnExpression(DbColumnExpression columnExpression)
{ 
	var sqlFragment = QuoteIdentifier(columnExpression.ColumnName); 
	return ExpressionFactory.Sql(sqlFragment); 
} 

If you want more information about how to implement custom query translators, take a look here

Operator overloading 

The DbExpressons library uses operator overloading so that we can write our queries in a more natural way.

So instead of writing this:

selectQuery.Select(f => f.Column("ProductID")) 
	.From(f => f.Table("Order Details")).Where(f => f.GreaterThan(f.Column("ProductID"),f.Constant(10))); 

we can write

selectQuery.Select(f => f.Column("ProductID")) 
	.From(f => f.Table("Order Details")).Where(e => e.Column("ProductID") > 10);

Parameterized queries 

We all know the danger of SQL injection and we need to make sure that every constants such as a string literal or number is represented as a parameter in the query.

The DbExpressions library takes care of this by translating DbConstantExpression instances into data parameters. 

This means that if we write : 

selectQuery.Select(f => f.Column("ProductID")) 
	.From(f => f.Table("Order Details")).Where(e => e.Column("ProductID") > 10);

We end up with the following output (MS SQL): 

SELECT [ProductID] FROM [Order Details] WHERE ([ProductID] > @p0)

Query Execution

So we have our query and we need to get it executed.

The first thing we need to do is to “compile” the  syntax tree into the vendor specific SQL statement.

While the Expression<T> delegate have the Compile method, the counterpart for DbExpression is called Translate

And what does it translate into? It translates into an IDbCommand instance with all the parameters already populated.

var command = selectQuery.Translate(); 
using (IDbConnection connection = CreateConnection() ) 
{ 
	var reader = command.ExecuteReader(); 
	while(reader.Read()) 
	{ 
		//Do stuff 
	} 
}   

Configuration

The configuration for the DbExpressions library is very simple and consist of the ability to add new providers and specify the default provider.

<?xml version="1.0" encoding="utf-8" ?> 
<configuration> 
	<configSections> 
		<section name="dbExpressions" type ="DbExpressions.Configuration.DbExpressionSettings, DbExpressions"/> 
	</configSections> 
	<dbExpressions defaultProvider="System.Data.SqlClient"> 
		<queryTranslators> 
			<queryTranslator providerName="System.Data.SqlClient" type ="SqlQueryTranslator, DbExpressions"></queryTranslator> 
		</queryTranslators> 
	</dbExpressions> 
</configuration> 

Note: This is just a sample configuration and is not necessary since support for MySql and MS SQL Server is already bundled with the DbExpressions library.

We can also add new providers or set the default provider using  methods on the DbQueryTranslatorFactory class.

DbQueryTranslatorFactory.RegisterQueryTranslator("System.Data.SqlClient", typeof (SqlQueryTranslator)); 

DbQueryTranslatorFactory.SetDefaultProvider("System.Data.SqlClient");

MySql

In order to use this library with MySql, you need to install the MySql Connector for .Net. This is the native .Net data provider for MySql.

The library has been tested using version 6.3.5

Final words 

The DbExpressions library is hosted on CodePlex and updates and fixes will be made available there. 

If you come across any problems or have questions, please feel free to post in the discussion list or in the issue tracker

If you would like to contribute to the project that would be greatly appreciated and in particular someone with Oracle knowledge so that we can get support for that too.

推荐.NET配套的通用数据层ORM框架:CYQ.Data 通用数据层框架
新浪微博粉丝精灵,刷粉丝、刷评论、刷转发、企业商家微博营销必备工具"