Methods to build a SQL clause for use as a filter expression for BindingSource components.
For a list of all members of this type, see SqlFilterBuilder Members.
System.Object
SqlFilterBuilder
Public static (Shared in Visual Basic) members of this type are safe for multithreaded operations. Instance members are not guaranteed to be thread-safe.
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.
Namespace: CleanCode.Data
Assembly: CleanCode (in CleanCode.dll)
SqlFilterBuilder Members | CleanCode.Data Namespace