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

Checking If Form Data Is Stale Before Saving

With multiple users working on the same database using ASP.NET webforms, you have to make a design decision how to prevent stale data from being saved. By definition in a web application the data you see in a form is a snapshot in time, so when you post your changes back to the server, the save method must check to see if the data you worked on is stale, meaning it has been changed by another user in the meantime.

public void Form_OnSave() {
  if (DataIsStale()) {
    if (!WarnContinue("The data is stale, mate!"))
      return;
    }
  SaveData()
}

DataIsStale():

MEANING: The currently shown data has a RevisionNumber that is older than the data currently in database

RevisionNumber:

MEANING: The RevisionNumber is the id of the last HistoryItem of an "updated"-class type (created, changed, deleted, deactivated)

HistoryItem:

MEANING: A record in a table called History with columns: history item id, date, user id, guid of element, historytype (created, viewed, changed, deactivated, deleted, etc), session id (only in Method B)

Method A

When saving an element the RevisionNumbers are compared
UPSIDE: Simple and easy solution
DOWNSIDE: You need to store the revision number in the form

Method B

The current SessionID is saved alongside the HistoryItem, so the date of the last HistoryItem of the viewed type and for the current SessionID is compared to the date of the last HistoryItem of an "updated"-class type for all other session ids.
UPSIDE: Doesn't require any extra fields added to the form
DOWNSIDE: The session id is the same when a user has more windows open so it will not alarm a user that edits the same element in two browser windows and saves them both.

Method C

All the original data is compared to the current data in database, if different the the two objects must be different revisions, hence data is stale.
UPSIDE: You don't need HistoryItems and RevisionNumbers and SessionIDs
DOWNSIDE: All the orignal values must also be stored in the form