public class SqlFilterBuilder

public class SqlFilterBuilder

Public Class SqlFilterBuilder

Public Class SqlFilterBuilder

public ref class SqlFilterBuilder

public ref class SqlFilterBuilder

The methods in this class abstract some of the details of SQL query construction and provide some labor-saving as well. Besides the description here, I have also published an article discussing the practical uses of this tool -- Exploring Secrets of BindingSource Filters -- available on the DevX online magazine. You may build individual SQL clauses (equality, matching, or inclusion) using the basic building block GetPhrase(). Once you have individual clauses, you may combine these via GetConjunction() and GetAlternation() to generate a complete where clause. (If you want to construct custom clauses the helper method Quote() is also available.)

The Expression reference page describes the syntax that may be used in a clause. A return clause from SqlFilterBuilder may be used to update GUI controls (e.g. a DataGridView) by assigning it to the Filter property.

You may create a SqlFilterBuilder with or without a reference to your data source (DataTable or DataView). If you do it is used to guide the query construction as follows. The data types of columns are significant in an expression such as [id] like '%999' (i.e. match IDs that end in 999). If the data type of the ID field is a string that expression will work fine when assigned to a BindingSource filter; if ID is a number it will fail. Note that it does not fail when used in general queries as delivered by QueryAnalyzer or SqlDeveloper but since SqlFilterBuilder supports BindingSource filters, this item is important. So when the type of the field can be examined (by your reference to a DataTable or DataView) and determined that it is not a string, the above expression would instead be rendered as convert([id], 'System.String') LIKE '%999'.

A second optimization is when the data type of a field is a number and the literals provided are numbers, then the literals do not need to be quoted. So you could get an expression such as [id] IN (23, 42, 67, 122) rather than [id] IN ('23', '42', '67', '122').

Since CleanCode 0.9.13.