Monday, August 8, 2011

Get SQL Expression out of WhereClause


Several years ago I posted a method to get SQL expression out of a WhereClause. I did not dig deep enough, so it was practically unusable because of Null Object Exception. This post is a long overdue correction.


Put the following code in a .cs file under App_Code (website) or Shared (web application) folder.
using BaseClasses;
using BaseClasses.Data;
using BaseClasses.Data.SqlProvider;

namespace DingJing {
	public static class ExtensionWhereClause {
		public static string GetSQL(this WhereClause wc, BaseTable tbl) {
			var f = new CompoundFilterExt(wc.GetFilter() as CompoundFilter);
			return f.GetSQL(tbl.DataAdapter);

	class CompoundFilterExt : CompoundFilter {
		public CompoundFilterExt(CompoundFilter cf)
			: base(cf.CompoundingOperator, cf.GetFilters()) { }

		public string GetSQL(IRelationalDataAdapter adapter) {
			var arg = new SqlGenerationArgs() {
				Adapter = adapter,
				Encoder = new SqlFragmentEncoder()
			var tjl = new TableJoinList();
			var s = ToSql(arg, ref tjl);
			return s.Expression;

The above code adds an extension method GetSQL() to WhereClause. To get the SQL expression, you only need 1 line of code. For example,
public override WhereClause CreateWhereClause() {
	var wc = base.CreateWhereClause();
	if (wc != null)
		SQLClause.Text = wc.GetSQL(OrdersTable.Instance);

	return wc;


AlanT_maxtel said...

Here is the equivalent VB.NET code:

Imports BaseClasses
Imports BaseClasses.Data
Imports BaseClasses.Data.SqlProvider

'Namespace DingJing ' rename as appropriate
Public Module ExtensionWhereClause
Public Function GetSQL(wc As WhereClause, tbl As BaseTable) As String
Dim f = New CompoundFilterExt(TryCast(wc.GetFilter(), CompoundFilter))
Return f.GetSQL(tbl.DataAdapter)
End Function
End Module

Class CompoundFilterExt
Inherits CompoundFilter
Public Sub New(cf As CompoundFilter)
MyBase.New(cf.CompoundingOperator, cf.GetFilters())
End Sub

Public Function GetSQL(adapter As IRelationalDataAdapter) As String
Dim arg As SqlGenerationArgs = New SqlGenerationArgs() With {.Adapter = adapter, .Encoder = New SqlFragmentEncoder()}
Dim tjl = New TableJoinList()
Dim s = ToSql(arg, tjl)
Return s.Expression
End Function
End Class
'End Namespace

Tran Xuan Truong said...

Sorry, I not found where ToSql function came from. Please help me.