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/12/05 11:07] 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 44: | Zeile 46: | ||
</ | </ | ||
+ | ===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=" | <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=" | <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=" | <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=" | <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=" | <code C# [enable_line_numbers=" | ||
</ | </ |