DataView RowFilter Syntax
DataView RowFilter Syntax
DataView RowFilter Syntax
This example describes syntax of DataView.RowFilter expression. It shows how to correctly build expression string (without SQL injection) using methods to escape values.
Column names
If a column name contains any of these special characters ~ ( ) # \ / = > < + - * % & | ^ ' " [ ], you must enclose the column name within square brackets [ ]. If a column name contains right bracket ] or backslash \, escape it with backslash (\] or \\).
= = = =
// // // //
no special character in column name "id" no special character in column name "$id" special character "#" in column name "#id" special characters in column name "[id]"
Literals
String values are enclosed within single quotes ' '. If the string contains single quote ', the quote must be doubled.
dataView.RowFilter = "Date = #12/31/2008#" // date value (time is 00:00:00) dataView.RowFilter = "Date = #2008-12-31#" // also this format is supported dataView.RowFilter = "Date = #12/31/2008 16:44:58#" // date and time value dataView.RowFilter = String.Format(CultureInfo.InvariantCulture.DateTimeFormat, "Date = #{0}#", new DateTime(2008, 12, 31, 16, 44, 58));
Alternatively you can enclose all values within single quotes ' '. It means you can use string values for numbers or date time values. In this case the current culture is used to convert the string to the specific value.
dataView.RowFilter = "Date = '12/31/2008 16:44:58'" // if current culture is English dataView.RowFilter = "Date = '31.12.2008 16:44:58'" // if current culture is German dataView.RowFilter = "Price = '1199.90'" // if current culture is English
Comparison operators
Equal, not equal, less, greater operators are used to include only values that suit to a comparison expression. You can use these operators = <> < <= > >=. Note: String comparison is culture-sensitive, it uses CultureInfo from DataTable.Localeproperty of related table (dataView.Table.Locale). If the property is not explicitly set, its default value is DataSet.Locale (and its default value is current system culture Thread.CurrentThread.CurrentCulture).
= = = =
"Num = 10" "Date < #1/1/2008#" "Name <> 'John'" "Name >= 'Jo'"
// // // //
number is equal to 10 date is less than 1/1/2008 string is not equal to 'John' string comparison
Operator IN is used to include only values from the list. You can use the operator for all data types, such as numbers or strings.
= = = =
"Id IN (1, 2, 3)" "Price IN (1.0, 9.9, 11.5)" "Name IN ('John', 'Jim', 'Tom')" "Date IN (#12/31/2008#, #1/1/2009#)"
// // // //
Operator LIKE is used to include only values that match a pattern with wildcards. Wildcardcharacter is * or %, it can be at the beginning of a pattern '*value', at the end 'value*', or at both '*value*'. Wildcard in the middle of a patern 'va*lue' is not allowed.
dataView.RowFilter = "Name LIKE 'j*'" dataView.RowFilter = "Name LIKE '%jo%'" dataView.RowFilter = "Name NOT LIKE 'j*'"
// values that start with 'j' // values that contain 'jo' // values that don't start with 'j'
If a pattern in a LIKE clause contains any of these special characters * % [ ], those characters must be escaped in brackets [ ] like this [*], [%], [[] or []].
// values that starts with '*' // values that starts with '['
The following method escapes a text value for usage in a LIKE clause.
public static string EscapeLikeValue(string valueWithoutWildcards) { StringBuilder sb = new StringBuilder(); for (int i = 0; i < valueWithoutWildcards.Length; i++) { char c = valueWithoutWildcards[i]; if (c == '*' || c == '%' || c == '[' || c == ']') sb.Append("[").Append(c).Append("]"); else if (c == '\'')
// select all that starts with the value string (in this case with "*") string value = "*"; // the dataView.RowFilter will be: "Name LIKE '[*]*'" dataView.RowFilter = String.Format("Name LIKE '{0}*'", EscapeLikeValue(value));
Boolean operators
Boolean operators AND, OR and NOT are used to concatenate expressions. Operator NOT has precedence over AND operator and it has precedence over OR operator.
// operator AND has precedence over OR operator, parenthesis are needed dataView.RowFilter = "City = 'Tokyo' AND (Age < 20 OR Age > 60)"; // following examples do the same dataView.RowFilter = "City <> 'Tokyo' AND City <> 'Paris'"; dataView.RowFilter = "NOT City = 'Tokyo' AND NOT City = 'Paris'"; dataView.RowFilter = "NOT (City = 'Tokyo' OR City = 'Paris')"; dataView.RowFilter = "City NOT IN ('Tokyo', 'Paris')";
Aggregate Functions
There are supported following aggregate functions SUM, COUNT, MIN, MAX, AVG (average), STDEV(statistical standard deviation) and VAR (statistical variance). This example shows aggregate function performed on a single table.
// select orders which have more than 5 items dataView.RowFilter = "COUNT(Child.IdOrder) > 5"; // select orders which total price (sum of items prices) is greater or equal $500 dataView.RowFilter = "SUM(Child.Price) >= 500";