DataView RowFilter Syntax [C#]

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 \\).

[C#]

dataView.RowFilter = "id = 10";      

Literals

String values are enclosed within single quotes ' '. If the string contains single quote ', the quote must be doubled.

[C#]

dataView.RowFilter = "Name = 'John'"        

Number values are not enclosed within any characters. The values should be the same as is the result of int.ToString() or float.ToString() method for invariant or English culture.

[C#]

dataView.RowFilter = "Year = 2008"          

Date values are enclosed within sharp characters # #. The date format is the same as is the result of DateTime.ToString() method for invariant or English culture.

[C#]

dataView.RowFilter = "Date = #12/31/2008#"          

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.

[C#]

dataView.RowFilter = "Date = '12/31/2008 16:44:58'" 

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.Locale property 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).

[C#]

dataView.RowFilter = "Num = 10"             

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.

[C#]

dataView.RowFilter = "Id IN (1, 2, 3)"                    

Operator LIKE is used to include only values that match a pattern with wildcards. Wildcard character 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.

[C#]

dataView.RowFilter = "Name LIKE 'j*'"       

If a pattern in a LIKE clause contains any of these special characters * % [ ], those characters must be escaped in brackets [ ] like this [*], [%], [[] or []].

[C#]

dataView.RowFilter = "Name LIKE '[*]*'"     

The following method escapes a text value for usage in a LIKE clause.

[C#]

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 == '\'')
      sb.Append("''");
    else
      sb.Append(c);
  }
  return sb.ToString();
}

[C#]

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.

[C#]

Arithmetic and string operators

Arithmetic operators are addition +, subtraction -, multiplication *, division / and modulus %.

[C#]

dataView.RowFilter = "MotherAge - Age < 20";   

There is also one string operator concatenation +.

Parent-Child Relation Referencing

A parent table can be referenced in an expression using parent column name with Parent. prefix. A column in a child table can be referenced using child column name with Child. prefix.

The reference to the child column must be in an aggregate function because child relationships may return multiple rows. For example expression SUM(Child.Price) returns sum of all prices in child table related to the row in parent table.

If a table has more than one child relation, the prefix must contain relation name. For example expression Child(OrdersToItemsRelation).Price references to column Price in child table using relation named OrdersToItemsRelation.

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.

[C#]

Following example shows aggregate functions performed on two tables which have parent-child relation. Suppose there are tables Orders and Items with the parent-child relation.

[C#]

Functions

There are also supported following functions. Detailed description can be found here DataColumn.Expression.

• CONVERT – converts particular expression to a specified .NET Framework type
• LEN – gets the length of a string
• ISNULL – checks an expression and either returns the checked expression or a replacement value
• IIF – gets one of two values depending on the result of a logical expression
• TRIM – removes all leading and trailing blank characters like \r, \n, \t, ‚ ‘
• SUBSTRING – gets a sub-string of a specified length, starting at a specified point in the string

See also

• DataView.RowFilter – MSDN – expression used to filter rows
• DataColumn.Expression – MSDN – syntax description

引用地址：DataView RowFilter Syntax [C#] (csharp-examples.net)