Writing typesafe SQL-WHERE clauses

I have written a code generator, that generates C# classes based on the tables and views in a database. It had for some time irritated me that even though everything was now neatly packaged in a data layer and an entity relation layer I still needed the occasional SQL-WHERE clause here and there. And as you know they never result in compile time errors, but instead in runtime errors, which is worse... So I wanted the compiler to catch me if I wrote a syntax incorrect SQL-WHERE clause, instead of getting a runtime error, when a user reached the specific page and state of things.

Also when I modified the database, I wanted the compiler to check the SQL-WHERE clauses for me, which can be a tideous chore to do manually. So I came up with this solution, that works best in combination with a code generator, because then you'll have complete syncronisation with the columns and datatypes to use.

Features

• Support for != and == operators
• Support for && and || binary operators
• Support for int datatype
• Support for string datatype

Examples

Note: The examples show how to create Filter instances. I use my code generator to do this for me. It creates a filter instance for each column. If I refer to a column that has been deleted or renamed in the database the compiler will catch this.

1. Filter.IntFilter iEmpl_Id = new Filter.IntFilter("Empl_Id");
string sSQLWhere = (iEmpl_Id=="A text").ToString(); Results in a compile time error.
2. Filter.IntFilter iEmpl_Id = new Filter.IntFilter("Empl_Id");
string sSQLWhere = (iEmpl_Id==13).ToString();
   Results in the string "Empl_Id=13".
3. Filter.StringFilter sEmpl_NameFirst = new Filter.StringFilter("Empl_Name");
Filter.IntFilter iEmpl_Id = new Filter.IntFilter("Empl_Id");
string sSQLWhere = (iEmpl_Id==13 || iEmpl_Id!=10&&sEmpl_NameFirst=="D'angelo").ToString();
   Results in the string "Empl_Id=13 OR (NOT (Empl_Id=10) AND Empl_Name='D''angelo')".

Todo

• Support for < and > operators
• Support for IN clause
• Support for Datetime datatype
• Support for Guid datatype
• Support for SqlTypes datatypes

using System;

namespace NatMus.Data.Core {
  public class Filter {
    protected string sOperandName = "";
    protected string sSqlOperator = "";
    protected string sSqlPrefix = "";
    protected string sSqlPostfix = "";
    protected string sValue = "";
    protected bool bNegated = false;
    protected Filter oFilterLeft = null;
    protected Filter oFilterRight = null;

    public Filter() {
    }

    public Filter(string sOperandName) {
      this.sOperandName = sOperandName;
    }

    public Filter(Filter oFilter) {
      this.sOperandName = oFilter.sOperandName;
      this.sSqlOperator = oFilter.sSqlOperator;
      this.sValue = oFilter.sValue;
      this.bNegated = oFilter.bNegated;
      this.sSqlPrefix = oFilter.sSqlPrefix;
      this.sSqlPostfix = oFilter.sSqlPostfix;
    }

    public static Filter operator & (Filter oFilterLeft, Filter oFilterRight) {
      Filter oFilterResult = new Filter();
      oFilterResult.sSqlPrefix = "(";
      oFilterResult.sSqlOperator = " AND ";
      oFilterResult.sSqlPostfix = ")";
      oFilterResult.oFilterLeft = oFilterLeft;
      oFilterResult.oFilterRight = oFilterRight;
      return oFilterResult;
    }

    public static Filter operator | (Filter oFilterLeft, Filter oFilterRight) {
      Filter oFilterResult = new Filter();
      oFilterResult.sSqlOperator = " OR ";
      oFilterResult.oFilterLeft = oFilterLeft;
      oFilterResult.oFilterRight = oFilterRight;
      return oFilterResult;
    }

    public static bool operator true(Filter v) {
      return false;
    }

    public static bool operator false(Filter v) {
      return false;
    }

    public override string ToString() {
      System.Text.StringBuilder oStringBuilder = new System.Text.StringBuilder();
      oStringBuilder.Append(sSqlPrefix);
      if ((object)oFilterLeft!=null) {
        oStringBuilder.Append(oFilterLeft.ToString());
      }
      oStringBuilder.Append(sOperandName + sSqlOperator + sValue);
      if ((object)oFilterRight!=null) {
        oStringBuilder.Append(oFilterRight.ToString());
      }
      oStringBuilder.Append(sSqlPostfix);
      return oStringBuilder.ToString();
    }

    public class StringFilter : Filter {
      public StringFilter(string sOperandName) : base(sOperandName) {
      }

      public StringFilter(Filter v) : base(v) {
      }

      public static StringFilter operator == (StringFilter v, string sValue) {
        StringFilter oFilter = new StringFilter(v);
        oFilter.sSqlOperator = "=";
        oFilter.sValue = "'" + sValue.ToString().Replace("'", "''") + "'";
        return oFilter;
      }

      public static StringFilter operator != (StringFilter v, string sValue) {
        return !(v==sValue);
      }

      public static StringFilter operator ! (StringFilter v) {
        StringFilter oFilter = new StringFilter(v);
        oFilter.bNegated = !oFilter.bNegated;
        if (oFilter.bNegated) {
          oFilter.sSqlPrefix = "NOT (" + oFilter.sSqlPrefix;
          oFilter.sSqlPostfix += ")";
        }
        return oFilter;
      }

      public override bool Equals(object o) {
        if (o==null) return false;
        return this==(StringFilter)o;
      }

      public override int GetHashCode() {
        return sOperandName.GetHashCode();
      }
    }

    public class IntFilter : Filter {
      public IntFilter(string sOperandName) : base(sOperandName) {
      }

      public IntFilter(Filter v) : base(v) {
      }

      public static IntFilter operator == (IntFilter v, int iValue) {
        IntFilter oFilter = new IntFilter(v);
        oFilter.sSqlOperator = "=";
        oFilter.sValue = iValue.ToString();
        return oFilter;
      }

      public static IntFilter operator != (IntFilter v, int iValue) {
        return !(v==iValue);
      }

      public static IntFilter operator ! (IntFilter v) {
        IntFilter oFilter = new IntFilter(v);
        oFilter.bNegated = !oFilter.bNegated;
        if (oFilter.bNegated) {
          oFilter.sSqlPrefix = "NOT (" + oFilter.sSqlPrefix;
          oFilter.sSqlPostfix += ")";
        }
        return oFilter;
      }

      public override bool Equals(object o) {
        if (o==null) return false;
        return this==(IntFilter)o;
      }

      public override int GetHashCode() {
        return sOperandName.GetHashCode();
      }
    }
  }
}