Click here to Skip to main content
Click here to Skip to main content

How to generate an SQL filter clause using C#

, 18 Nov 2003
Rate this:
Please Sign up or sign in to vote.
Produces SQL filter clauses using C# objects

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'))

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

gokselm
Web Developer
United Kingdom United Kingdom

Comments and Discussions

 
Questionwhat about multiple tables? PinmemberAmadelle4-Aug-04 6:33 
AnswerRe: what about multiple tables? Pinmembergoxman4-Aug-04 6:51 
GeneralAnother approach Pinsussdrewnoakes30-Nov-03 0:29 
GeneralRe: Another approach Pinmembergoxman9-Dec-03 11:13 
GeneralRe: Another approach Pinmemberjmelgaard20-Jun-08 3:21 
GeneralSQL injection attacks PinmemberThePhoenix25-Nov-03 22:04 
GeneralRe: SQL injection attacks Pinmembergoxman9-Dec-03 11:23 
GeneralSQLOperators enumeration -&gt; struct PinmemberGirb19-Nov-03 20:30 
GeneralRe: SQLOperators enumeration -&gt; struct Pinmembergoxman20-Nov-03 10:44 
GeneralSuggestion PinadminChris Maunder19-Nov-03 11:30 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140814.1 | Last Updated 19 Nov 2003
Article Copyright 2003 by gokselm
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid