A Killer Example for Functional Programming

Published at dotnetslakers.

Get SQL Expression out of WhereClause

Introduction

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.

Implementation

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;
}