Simon Thorneycroft and Jonathan Hodgson

Building Dynamic Sql

There are advocates who argue between stored procedures and ad-hoc sql, normally I'll follow the development style of the current system where it makes sense. The main objection I have to stored procedures revolves around hundreds of ungrouped, often strangely named functions, the change between C# to T-Sql and the missing editing, debugging and tool support. I often find sql (using parameters) works in many cases just as well.

We'll see how things pan out with the CLR integration in Sql Server 2005, I'd be interested to hear more about how the ability to write Java code inside Oracle altered development?

On a recent project I used Sql.Net from Reebsoftware. It is a .NET library which provides an abstration layer over Sql, so you can generate Sql code in a more object-orientated way.

FromTerm tCustomers = FromTerm.Table("customers", "c");
FromTerm tProducts = FromTerm.Table("products", "p");
FromTerm tOrders = FromTerm.Table("orders", "o");

SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("name", tCustomers));
query.Columns.Add(new SelectColumn("name", tProducts));
query.Columns.Add(new SelectColumn("price", tProducts));

query.FromClause.BaseTable = tCustomers;
query.FromClause.Join(JoinType.Left, tCustomers, tOrders, "customerId", "customerId");
query.FromClause.Join(JoinType.Inner, tOrders, tProducts, "productId", "productId");

query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("name", tCustomers), SqlExpression.String("John"), CompareOperator.Equal));

query.OrderByTerms.Add(new OrderByTerm("price", OrderByDirection.Ascending));

string sql = new SqlServerRenderer().RenderSelect(query);

The main functionality I wanted was the automatic generation of paging sql code:

SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("name"));
query.FromClause.BaseTable = FromTerm.Table("customers");
query.OrderByTerms.Add(new OrderByTerm("name", null, OrderByDirection.Descending));

SqlServerRenderer renderer = new SqlServerRenderer();
string rowCountSql = renderer.RenderRowCount(query);
int totalRows = (int)ExecuteScalar(rowCountSql);
string sql = renderer.RenderPage(pageIndex, pageSize, totalRows, query);

The best bit of using the library is that it just generates the sql code and therefore leaves you loosely coupled from how you execute the sql against the database, ie. using System.Data.SqlClient or the Microsoft Data Access block SqlHelper style. So if you find a problem you just can build the sql manually instead.

I'm still keep toying with Object/Relational mapping technologies, prototyping systems with nHibernate (still quite behind the Java Hibernate) and Wilson ORMapper (my preference) but always seem to back away from using them in a full production applications; interesting blog post comparing the two products by Paul Wilson. I haven't tried any of the more expensive commercial implementations like LLBLGen Pro.

Patterns of Enterprise Application Architecture discusses many of the common issues such as Single Table Inheritance and Association Table Mapping which many O/R Mappers try to solve in addition to not writing sql by hand.