Metainformationen zur Seite
  •  

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen Revision Vorhergehende Überarbeitung
Nächste Überarbeitung 		Vorhergehende Überarbeitung
start:visualstudio2019:programmieren:dotnet:component:dataview [2022/02/21 10:54]
wikiadmin [Column names] 		start:visualstudio2019:programmieren:dotnet:component:dataview [2022/12/05 11:34] (aktuell)
wikiadmin
Zeile 1: Zeile 1:
-====== How to filter your 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.
-==== 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 \\).+[[https://learn.microsoft.com/en-us/dotnet/api/system.data.datacolumn.expression?view=net-7.0#system-data-datacolumn-expression|Dot Net 7.0 C# ]] 
 +=== 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 \\). 
 + 
 +<code C# [enable_line_numbers="true",highlight_lines_extra="0,"]> 
 +"id = 10";      // no special character in column name "id" 
 +"$id = 10";     // no special character in column name "$id" 
 +"[#id] = 10";   // special character "#" in column name "#id" 
 +"[[id\]] = 10"; // special characters in column name "[id]" 
 +</code> 
 + 
 +=== Literals === 
 + 
 +**String values** are enclosed within single quotes **' '**. If the string contains single quote ', the quote must be doubled. 
 +<code C# [enable_line_numbers="true",highlight_lines_extra="0,"]> 
 +dataView.RowFilter = "Name = 'John'"        // string value 
 +dataView.RowFilter = "Name = 'John ''A'''"  // string with single quotes "John 'A'" 
 +dataView.RowFilter = String.Format("Name = '{0}'", "John 'A'".Replace("'", "''")); 
 +</code> 
 + 
 +**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="true",highlight_lines_extra="0,"]> 
 +dataView.RowFilter = "Year = 2008"          // integer value 
 +dataView.RowFilter = "Price = 1199.9"       // float value 
 +dataView.RowFilter = String.Format(CultureInfo.InvariantCulture.NumberFormat, 
 +                     "Price = {0}", 1199.9f); 
 +</code> 
 +**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="true",highlight_lines_extra="0,"]> 
 +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)); 
 +</code> 
 +**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="true",highlight_lines_extra="0,"]> 
 +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 
 +dataView.RowFilter = "Price = '1199,90'"            // if current culture is German 
 +</code> 
 + 
 +===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.Curren­tThread.Curren­tCulture). 
 +<code C# [enable_line_numbers="true",highlight_lines_extra="0,"]> 
 +dataView.RowFilter = "Num = 10"             // number is equal to 10 
 +dataView.RowFilter = "Date < #1/1/2008#"    // date is less than 1/1/2008 
 +dataView.RowFilter = "Name <> 'John'"       // string is not equal to 'John' 
 +dataView.RowFilter = "Name >= 'Jo'"         // string comparison 
 +</code> 
 +**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="true",highlight_lines_extra="0,"]> 
 +dataView.RowFilter = "Id IN (1, 2, 3)"                    // integer values 
 +dataView.RowFilter = "Price IN (1.0, 9.9, 11.5)"          // float values 
 +dataView.RowFilter = "Name IN ('John', 'Jim', 'Tom')"     // string values 
 +dataView.RowFilter = "Date IN (#12/31/2008#, #1/1/2009#)" // date time values 
 +dataView.RowFilter = "Id NOT IN (1, 2, 3)"  // values not from the list 
 +</code> 
 +**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. 
 +<code C# [enable_line_numbers="true",highlight_lines_extra="0,"]> 
 +dataView.RowFilter = "Name LIKE 'j*'"       // values that start with 'j' 
 +dataView.RowFilter = "Name LIKE '%jo%'"     // values that contain 'jo' 
 +dataView.RowFilter = "Name NOT LIKE 'j*'"   // values that don't start with 'j' 
 +</code> 
 +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="true",highlight_lines_extra="0,"]> 
 +dataView.RowFilter = "Name LIKE '[*]*'"     // values that starts with '*' 
 +dataView.RowFilter = "Name LIKE '[[]*'"     // values that starts with '[' 
 +</code> 
 +The following method escapes a text value for usage in a LIKE clause. 
 +<code C# [enable_line_numbers="true",highlight_lines_extra="0,"]> 
 +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(); 
 +} 
 +</code> 
 + 
 +<code C# [enable_line_numbers="true",highlight_lines_extra="0,"]> 
 +// 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)); 
 +</code> 
 +=== 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="true",highlight_lines_extra="0,"]> 
 +// 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')"; 
 +</code> 
 +=== Arithmetic and string operators === 
 + 
 +**Arithmetic operators** are addition +, subtraction -, multiplication *, division / and modulus %. 
 +<code C# [enable_line_numbers="true",highlight_lines_extra="0,"]> 
 +dataView.RowFilter = "MotherAge - Age < 20";   // people with young mother 
 +dataView.RowFilter = "Age % 10 = 0";           // people with decennial birthday 
 +</code> 
 +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 OrdersToItemsRe­lation. 
 + 
 +=== 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="true",highlight_lines_extra="0,"]> 
 +// select people with above-average salary 
 +dataView.RowFilter = "Salary > AVG(Salary)"; 
 +</code> 
 +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="true",highlight_lines_extra="0,"]> 
 +// 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"; 
 +</code> 
 + 
 +=== Functions === 
 + 
 +There are also supported following functions. Detailed description can be found here DataColumn.Ex­pression. 
 + 
 +  *  **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="true",highlight_lines_extra="0,"]> 
 +</code>