c# sql filter class

home | software | utility classes | c# sql filter class

using the class

An example usage, showing a simple case:

Filter f = new Filter(); f.EqualTo("foo", "there's text in here"); f.EqualTo("bar", 123); Console.WriteLine(f.ToString());

...will produce console output...

Where [foo]='there''s text in here' And [bar]=123

Note that the column names have been escaped appropriately for Sql Server and the ADO.NET DataView.Filter property (as per my original requirement for this class). Similarly, string values are properly quoted and escaped, and numeric types are handled correctly.

Supported conditional operators include:

EqualTo
NotEqualTo
LessThan
LessThanOrEqualTo
GreaterThan
GreaterThanOrEqualTo
Like (pattern matching)
IsNull
NotNull

Logical operators And & Or are supported. Calling the conditional operator methods listed above gives logical And behavior. Methods for logical Or behaviour are prefixed with Or such as OrEqualTo. For example:

Filter f = new Filter(); foreach (string userName in allowedUserNames) { f.OrEqualTo("UserName", userName); } DataView userView = userTable.DefaultView; userView.Filter = f.ToString();

Once you have a Filter class instance, you can use it in ADO.NET code, such as the creation of Sql commands

how it works

The Filter class maintains a tree structure of logical operators and conditional operators. Every time a filtering method is called (EqualTo, LessThan...) this tree has a new leaf or node added. Creating the final expression involves traversing this tree. A hierarchy of internal types are included within the Filter class to support this structure in a strongly-typed fashion.

download

Filter.cs The main class file
FilterTest.cs The NUnit test suite

submitting feedback

Please feel free to provide feedback on this class . Bug reports are most appreciated when accompanied by a failing NUnit test case.