Metainformationen zur Seite
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen Revision Vorhergehende Überarbeitung Nächste Überarbeitung | Vorhergehende Überarbeitung | ||
start:visualstudio2019:programmieren:dotnet:component:dataview [2022/02/21 11:01] wikiadmin |
start:visualstudio2019:programmieren:dotnet:component:dataview [2022/12/05 11:34] wikiadmin |
||
---|---|---|---|
Zeile 1: | Zeile 1: | ||
====== How to filter your row data ====== | ====== How to filter your row data ====== | ||
This document briefly explains how to use the data filter syntax. | This document briefly explains how to use the data filter syntax. | ||
+ | |||
+ | [[https:// | ||
=== Column names === | === Column names === | ||
Zeile 14: | Zeile 16: | ||
=== Literals === | === Literals === | ||
- | String values are enclosed within single quotes **' '**. If the string contains single quote ', the quote must be doubled. | + | **String values** are enclosed within single quotes **' '**. If the string contains single quote ', the quote must be doubled. |
<code C# [enable_line_numbers=" | <code C# [enable_line_numbers=" | ||
+ | dataView.RowFilter = "Name = ' | ||
+ | dataView.RowFilter = "Name = 'John '' | ||
+ | dataView.RowFilter = String.Format(" | ||
+ | </ | ||
+ | **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. | ||
+ | <code C# [enable_line_numbers=" | ||
+ | dataView.RowFilter = "Year = 2008" | ||
+ | dataView.RowFilter = "Price = 1199.9" | ||
+ | dataView.RowFilter = String.Format(CultureInfo.InvariantCulture.NumberFormat, | ||
+ | " | ||
+ | </ | ||
+ | **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. | ||
+ | <code C# [enable_line_numbers=" | ||
+ | dataView.RowFilter = "Date = # | ||
+ | dataView.RowFilter = "Date = # | ||
+ | dataView.RowFilter = "Date = #12/31/2008 16: | ||
+ | dataView.RowFilter = String.Format(CultureInfo.InvariantCulture.DateTimeFormat, | ||
+ | " | ||
+ | </ | ||
+ | **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. | ||
+ | <code C# [enable_line_numbers=" | ||
+ | dataView.RowFilter = "Date = ' | ||
+ | dataView.RowFilter = "Date = ' | ||
+ | dataView.RowFilter = "Price = ' | ||
+ | dataView.RowFilter = "Price = ' | ||
+ | </ | ||
+ | |||
+ | ===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**, | ||
+ | <code C# [enable_line_numbers=" | ||
+ | dataView.RowFilter = "Num = 10" | ||
+ | dataView.RowFilter = "Date < # | ||
+ | dataView.RowFilter = "Name <> ' | ||
+ | dataView.RowFilter = "Name >= ' | ||
+ | </ | ||
+ | **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. | ||
+ | <code C# [enable_line_numbers=" | ||
+ | dataView.RowFilter = "Id IN (1, 2, 3)" | ||
+ | dataView.RowFilter = "Price IN (1.0, 9.9, 11.5)" | ||
+ | dataView.RowFilter = "Name IN (' | ||
+ | dataView.RowFilter = "Date IN (# | ||
+ | dataView.RowFilter = "Id NOT 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 ' | ||
+ | <code C# [enable_line_numbers=" | ||
+ | dataView.RowFilter = "Name LIKE ' | ||
+ | dataView.RowFilter = "Name LIKE ' | ||
+ | dataView.RowFilter = "Name NOT LIKE ' | ||
+ | </ | ||
+ | If a pattern in a LIKE clause contains any of these special characters * % [ ], those characters must be escaped in brackets [ ] like this [*], [%], [[] or []]. | ||
+ | <code C# [enable_line_numbers=" | ||
+ | dataView.RowFilter = "Name LIKE ' | ||
+ | dataView.RowFilter = "Name LIKE ' | ||
+ | </ | ||
+ | The following method escapes a text value for usage in a LIKE clause. | ||
+ | <code C# [enable_line_numbers=" | ||
+ | public static string EscapeLikeValue(string valueWithoutWildcards) | ||
+ | { | ||
+ | StringBuilder sb = new StringBuilder(); | ||
+ | for (int i = 0; i < valueWithoutWildcards.Length; | ||
+ | { | ||
+ | char c = valueWithoutWildcards[i]; | ||
+ | if (c == ' | ||
+ | sb.Append(" | ||
+ | else if (c == ' | ||
+ | sb.Append("''" | ||
+ | else | ||
+ | sb.Append(c); | ||
+ | } | ||
+ | return sb.ToString(); | ||
+ | } | ||
+ | </ | ||
+ | |||
+ | <code C# [enable_line_numbers=" | ||
+ | // 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(" | ||
+ | </ | ||
+ | === 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. | ||
+ | <code C# [enable_line_numbers=" | ||
+ | // operator AND has precedence over OR operator, parenthesis are needed | ||
+ | dataView.RowFilter = "City = ' | ||
+ | // following examples do the same | ||
+ | dataView.RowFilter = "City <> ' | ||
+ | dataView.RowFilter = "NOT City = ' | ||
+ | dataView.RowFilter = "NOT (City = ' | ||
+ | dataView.RowFilter = "City NOT IN (' | ||
+ | </ | ||
+ | === Arithmetic and string operators === | ||
+ | |||
+ | **Arithmetic operators** are addition +, subtraction -, multiplication *, division / and modulus %. | ||
+ | <code C# [enable_line_numbers=" | ||
+ | dataView.RowFilter = " | ||
+ | dataView.RowFilter = "Age % 10 = 0"; | ||
+ | </ | ||
+ | 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 // | ||
+ | |||
+ | If a table has more than one child relation, the prefix must contain relation name. For example expression // | ||
+ | |||
+ | === 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. | ||
+ | |||
+ | <code C# [enable_line_numbers=" | ||
+ | // select people with above-average salary | ||
+ | dataView.RowFilter = " | ||
+ | </ | ||
+ | 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. | ||
+ | <code C# [enable_line_numbers=" | ||
+ | // select orders which have more than 5 items | ||
+ | dataView.RowFilter = " | ||
+ | // select orders which total price (sum of items prices) is greater or equal $500 | ||
+ | dataView.RowFilter = " | ||
+ | </ | ||
+ | |||
+ | === 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 | ||
+ | |||
+ | |||
+ | <code C# [enable_line_numbers=" | ||
</ | </ |