Filtering database data
As we have seen in Part III, the SQL Select statement is composed of the following parts:
SELECT [field1], [field2],… [fieldn]
FROM [database table or ‘table-like’ store]
WHERE [conditions]
ORDER BY [field name] <sort direction>,
[field name] <sort direction>
It is the ‘WHERE’ clause of the SQL statement that is used to filter the data.
‘WHERE’ is followed by ‘conditions’. Several conditions are concatenated using AND or OR operator.
What is a filter condition?
A ‘condition item’ can be presented as:
[Field name] [Comparison operator] [Filter value]
A collection of ‘condition items’ can be presented as something like:
<Condition item> <Concatenation operator> <Other condition item>
Examples:
Name = ‘John’
Amount >= 100
(Last_name LIKE ‘%bama’) AND (Age >= 20)
That seems easy to be represented by an object:
public class SilverFiltertItem
{
string m_field_name,
m_filter_value;
CompareOperator m_compare_operator = CompareOperator.EQUAL;
ConcatOperator m_concat_operator = ConcatOperator.AND;
...
...
The concatenation and comparison operators are members of the following enum types:
public enum filter_concat_operator
{
and,
or,
none
};
public enum filter_compare_operator
{
Equal,
Not_equal,
Like,
GreatertThan,
GreatertThanOrEqual,
LessThan,
LessThanOrEqual
...
...
};
Two properties can give us the Compare and Concatenate strings when required:
protected string ConcatString
{
get
{
if( m_concat_opertaor == filter_concat_operator.none)
return "";
// return ‘AND’ or ‘OR’
return Enum.GetName( typeof( filter_concat_operator), m_concat_opertaor).ToUpper();
}
}
protected string CompareString
{
get
{
switch (m_compare_operator)
{
case filter_compare_operator.Equal:
return "=";
case filter_compare_operator.Like:
return " Like ";
case filter_compare_operator.Not_equal:
return "!=";
case filter_compare_operator.GreatertThan:
return ">";
case filter_compare_operator.GreatertThanOrEqual:
return ">=";
case filter_compare_operator.LessThan:
return "<";
case filter_compare_operator.LessThanOrEqual:
return "<=";
default:
throw new Exception("Unknown Compare opertor encoutered!");
}
}
}
The FilterItem object can now provide us with its SQL string through a property like the following:
public string SqlFilterString
{
get
{
if( string.IsNullOrEmpty(m_filter_value)
|| string.IsNullOrEmpty( m_target_column_name))
return "";
string field_name = "[" + m_target_column_name + "]",
str_ret = ConcatString + "(";
string str_value = m_filter_value;
str_ret += field_name + " " + CompareString + " " + str_value + ") ";
return str_ret;
}
A filter collection class may look like this:
public class SilverFilter : List<SilverFilterItem>
{
...
...
The filter collection can return the entire SQL filter string through successive calls to its member items:
public string SqlFilterString
{
get
{
if( Count <= 0)
return "";
int ndx = 0;
string str_ret = "";
foreach (SilverFilterItem f in this)
{
if (ndx <= 0)
f.Concat_operator = filter_concat_operator.none;
else
{
if( f.Concat_operator == filter_concat_operator.none)
f.Concat_operator = filter_concat_operator.and;
}
str_ret += f.SqlFilterString;
ndx++;
}
return str_ret;
}
Our DataTable object (see previous posts) can now include a Filter collection object and construct the SQL Where clause:
protected SilverFilter m_filter = new SilverFilter();
...
...
public string SqlCommand
{
get
{
...
...
string str_cmd = "SELECT * FROM " + m_table_name;
string str_sort = m_sort.StrSql;
string str_filter = m_filter.SqlFilterString;
if( ! string.IsNullOrEmpty( str_filter))
str_cmd += " WHERE(" + str_filter + ")";
if( ! string.IsNullOrEmpty( str_sort))
str_cmd += " ORDER BY " + str_sort;
return str_cmd;
}
Note: a better approach would, of course, be to map the filter field names to the table’s meta-data table fields… or directly use MetaDataTable’s columns as filter members (instead of using field names as strings). In this last case, we would be able to check columns’ data types and also act more closely to database’s business logic.
In other words: what is proposed here is a simple approach that can evolve according to your needs.
You can download the sample code: SilverDbDataGrid-2-sorting&filtering.zip (1.87 mb)