How to generate an SQL filter clause using C#

• Download source files - 2.56 Kb
• Download demo project - 4.37 Kb

Introduction

This article shows how to generate a SQL filter clause using C# objects. Sometimes it may be needed to use queries instead of stored procedures. If there are lots of parameters to filter the SQL clause, it might be difficult to produce such a query by writing itself in the code. Future developments may be complicated as well. And it might be difficult to produce such a filter query and it may cause bugs. Using a filter producer can prevent mistakes. Also DataView's RowFilter property can be populated using these classes.

I wrote some classes to produce such a filter clause. Filter expressions such as Greater, Less, Equal, Starts Like, Ends Like, Like, Not Like, Less Or Equal, Greater Or Equal, Not Equal etc. can be generated. It is possible to use AND and OR with the filter expressions. You can use them regardless of the sequence or count. You can also add your own class to expand the filter library.

Design of Classes

• FilterExpression: Filter | InFilter | ANDFilter | ORFilter
• Filter: Filter Name & SQL operator & Filter Value
• InFilter: Filter Name & 'IN' & {Filter Value}+
• ANDFilter: '(' & FilterExpression & {'AND' & FilterExpression}+ & ')'
• ORFilter: '(' & FilterExpression & {'OR' & FilterExpression}+ & ')'

A Filter includes a filter name, a SQL operator and a filter value.

E.g.: Name5 LIKE '%Value5%'

InFilter includes a filter name and the values that the filter can contain.

E.g.: Name11 IN('1','2','3')

ANDFilter can include more than two FilterExpressions. They are separated by AND keyword. It is surrounded by '(' and ')' characters.

ORFilter can include more than two FilterExpressions. They are separated by OR keyword. It is surrounded by '(' and ')' characters.

Implementation

We need an interface for the other classes. All the classes will use this interface and FilterString method will be called for each Filter class.

public interface IFilter{
    string FilterString{
     get;
    }
}

Here is the Filter class.

[Serializable()]
public class Filter:IFilter{
   private string m_strFilterName;
   private SqlOperators m_sqlOperator;
   private string m_strFilterValue;

   public Filter(string strFilterName, 
     SqlOperators sqlOperator ,string strFilterValue) { 

      m_strFilterName=strFilterName;
      m_sqlOperator=sqlOperator;
      m_strFilterValue=strFilterValue;
   }

   public string FilterString{

      get{

         string strFilter="";
            m_strFilterValue= 
              m_strFilterValue.Replace("'","''");

            switch (m_sqlOperator){
               case SqlOperators.Greater:
                  strFilter=m_strFilterName + 
                     " > '" + m_strFilterValue + "'";
                  break;
               case SqlOperators.Less:
                  strFilter=m_strFilterName + 
                     " < '" + m_strFilterValue + "'";
                  break;
               ...........................
               default:
                  throw new Exception
                    ("This operator type is not supported");
            } 
            
         return strFilter;
      }
   }
}

And I needed a class to carry the filter expressions. It is inherited from IEnumerable and IEnumerator interfaces. It stores the FilterExpression objects.

[Serializable()]
public class FilterExpressionList :IEnumerable,IEnumerator {

   ArrayList alItems;
   System.Collections.IEnumerator ienum; 

   public FilterExpressionList() {
      alItems=new ArrayList();
      ienum = alItems.GetEnumerator(); 
   }

   public System.Collections.IEnumerator GetEnumerator(){
      return (IEnumerator) this;
   }

   public IFilter Current{
      get{
         return (IFilter) ienum.Current;
      }
   }

   object IEnumerator.Current{
      get{
         return ienum.Current;
      }
   }

   public void Reset() { 
      ienum.Reset(); 
   } 

   public bool MoveNext() { 
      return ienum.MoveNext(); 
   } 

   public void Add(IFilter filterExpresion){
      alItems.Add(filterExpresion);
   }

   public IFilter this[int index]{
      get{
         return (IFilter) alItems[index];
      }

      set{
         alItems[index]=value;
      }
   }

   public int Count{
      get{
         return alItems.Count;
      }
   }
}

And here is the ANDFilter class.

[Serializable()]
public class ANDFilter:IFilter{

   private FilterExpressionList 
    m_filterExpressionList=new FilterExpressionList();
   
   public ANDFilter(IFilter filterExpressionLeft,
                   IFilter filterExpressionRight){
      m_filterExpressionList.Add(filterExpressionLeft);
      m_filterExpressionList.Add(filterExpressionRight);
   }

   public ANDFilter(FilterExpressionList 
                     filterExpressionList){
      m_filterExpressionList=filterExpressionList;
   }

   public string FilterString{
      get{
         string strFilter="";
         if (m_filterExpressionList.Count>0){

            for (int i=0; 
             i<m_filterExpressionList.Count-1; i++){
                strFilter += 
                  m_filterExpressionList[i].FilterString 
                  + " AND ";
            }

            strFilter+= 
              m_filterExpressionList[m_filterExpressionList.Count-1].FilterString;
            strFilter="(" + strFilter +")";
         } 

         return strFilter; 

      }
   }
}

I did not include OrFilter and INFilter in this page. They are included in the code. You can add your own objects to create different types of filters. Just create an object and inherit it from IFilter interface.

Here is the sample to produce a filter query:

Filter filter1=new Filter("Name1",SqlOperators.Equal,"Value1");

Filter filter2=new Filter("Name2",SqlOperators.EndsLike,"Value2");

Filter filter3=new Filter("Name3",SqlOperators.Greater,"Value3");

Filter filter4=new Filter("Name4",SqlOperators.GreaterOrEqual,"Value4");

Filter filter5=new Filter("Name5",SqlOperators.Like,"Value5");

Filter filter6=new Filter("Name6",SqlOperators.StartsLike,"Value6");

Filter filter7=new Filter("Name7",SqlOperators.NotLike,"Value7");

Filter filter8=new Filter("Name8",SqlOperators.Less,"Value8");

Filter filter9=new Filter("Name9",SqlOperators.NotEqual,"Value9");

Filter filter10=new Filter("Name10",SqlOperators.LessOrEqual,"Value10");

StringCollection strCol=new StringCollection();

strCol.Add("1");

strCol.Add("2");

strCol.Add("3");

INFilter infilter=new INFilter("Name11",strCol); 

ANDFilter AND1=new ANDFilter(filter1,filter2);

ORFilter OR1=new ORFilter(filter3,filter4);

FilterExpressionList filterlist1=new FilterExpressionList();

filterlist1.Add(filter5);

filterlist1.Add(filter6);

filterlist1.Add(filter7);

filterlist1.Add(filter8);

ANDFilter AND2=new ANDFilter(filterlist1);

ANDFilter AND3=new ANDFilter(AND1,OR1);

ORFilter OR2=new ORFilter(AND3,AND2);

ANDFilter AND4=new ANDFilter(OR2,infilter);

txtSqlFilter.Text=AND4.FilterString;

Output is:

((((Name1 = 'Value1' AND Name2 LIKE '%Value2') AND (Name3 > 
'Value3' OR Name4 >= 'Value4')) OR (Name5 LIKE '%Value5%' AND Name6 
LIKE 'Value6%' AND Name7 NOT LIKE 'Value7' AND Name8 < 'Value8')) 
AND Name11 IN('1','2','3'))