2016-01-22 DataView RowFilter Syntax

Source: Http://www.csharp-examples.net/dataview-rowfilter/DataView RowFilter Syntax (C #)

This example describes the syntax of an dataview.rowfil-ter expression. It shows how to properly build the expression string (no?). SQL injection ") escape character use method.

Column Name

If a column name contains these special characters ~ ( ) # \ / = > < + - * % & | ^ ‘ " [ ] , you must add square brackets [] to the column name. If a column name contains a closing parenthesis ] or backslash \ , it is escaped with a backslash ( \] or \\ ).

(C #)

DataView.RowFilter ="ID = Ten";//no special character in column name "id"DataView.RowFilter ="$id = Ten";//no special character in column name "$id"DataView.RowFilter ="[#id] = ten";//special character "#" in Column name "#id"DataView.RowFilter ="[[id\]] = ten";//special characters in column name "[ID]"

Text

character type (String): Use single quotation marks to wrap '. If the string contains a single quote ', use two single quotation marks.

(C #)

DataView.RowFilter ="Name = ' John '"//String ValueDataView.RowFilter ="Name = ' John ' A '"//string with single quotes "John ' A '"DataView.RowFilter= String.Format ("Name = ' {0} '","John ' A '". Replace ("'",""'"));

Numeric: No special symbol package is required. The value should be the same as the result int.ToString() or float.ToString() method unchanged or English culture.

(C #)

" Year = " // integer value " Price = 1199.9 " // Float Value  = String.Format (CultureInfo.InvariantCulture.NumberFormat,"price= {0}"  1199.9f);

Date Type: Use the pound character to wrap # # . The date format is the same as the result DateTime.ToString() method unchanged or English culture.

(C #)

DataView.RowFilter ="Date = #12/31/2008#"//Date value (Time is 00:00:00)DataView.RowFilter ="Date = #2008 -12-31#"//also this format is supportedDataView.RowFilter ="Date = #12/31/2008 16:44:58#"//Date and time valueDataView.RowFilter= String.Format (CultureInfo.InvariantCulture.DateTimeFormat,"Date = #{0}#", Newdatetime ( -, A, to, -, -, -));

Or you can use single quotes to wrap ‘ ‘ . This means that you can use a string value for a number or datetime value. In this case, the current English is used for the string.

(C #)

DataView.RowFilter ="Date = ' 12/31/2008 16:44:58 '"//If current culture is 中文版DataView.RowFilter ="Date = ' 31.12.2008 16:44:58 '"//If current culture is GermanDataView.RowFilter="Price = ' 1199.90 '"//If current culture is 中文版DataView.RowFilter ="Price = ' 1199,90 '"//If current culture is German

Comparison operators

Equal, unequal, less than, greater than the operator is used to only include values that are suitable for comparison expressions. You can use these operators = <> < <= > >= .

Note: String comparisons are culture-related and it uses Cultureinfodatatable.locale real estate-related tables ( dataView.Table.Locale ). If the property is not explicitly set, its default value is the dataset. Region (and its default value is the current system culture thread.curren-tthread.curren-tculture).

(C #)

 dataview.rowfilter =  " num = ten   " //  number is equal to  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 =  " /span>name >= ' Jo '   " //  string comparison  

operator in: is used to include only values from the list. You can use operators on all data types, such as numbers or strings.

(C #)

DataView.RowFilter ="Id in (1, 2, 3)"//integer ValuesDataView.RowFilter ="Price in (1.0, 9.9, 11.5)"//Float ValuesDataView.RowFilter ="Name in (' John ', ' Jim ', ' Tom ')"//String ValuesDataView.RowFilter ="Date in (#12/31/2008#, #1/1/2009#)"//Date Time ValuesDataView.RowFilter="Id not in (1, 2, 3)"//values not from the list

The operator like: is used to contain only values that match a pattern with a wildcard character. Wildcards are * or % , it can be at the beginning of a pattern at the ‘*value‘， end ‘value*‘ , or on both sides ‘*value*‘ . Wildcard characters are not allowed in the middle of ' Va*lue '.

(C #)

 dataview.rowfilter =  " name like ' j* '  Span style= "COLOR: #800000" > " //  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 '  

If these special characters are included, * % [ ] These characters must be in parentheses [ ] like this, [*] [%] [[] or []] .

(C #)

" Name like ' [*]* ' " // values that starts with ' * ' " Name like ' [[]* ']//  values that starts with ' ['

The following method escapes an example of the use of a text.

(C #)

 Public StaticStringescapelikevalue (stringvaluewithoutwildcards) {StringBuilder SB=Newstringbuilder ();  for(inti =0; i < valuewithoutwildcards.length; i++)  {    Charc =Valuewithoutwildcards[i]; if(c = ='*'|| c = ='%'|| c = ='['|| c = =']') sb. Append ("["). Append (c). Append ("]"); Else if(c = ='\ '') sb. Append (""'"); Elsesb.  Append (c); }  returnsb. ToString ();}

(C #)

// Select All, starts with the value string (in this case with "*") String value = "*"; // the dataview.rowfilter would be: "Name like ' [*]* ']"dataview.rowfilter = String.Format ("name like ' { 0}* '"escapelikevalue (value));

Boolean operator

Boolean operators AND , OR and NOT for connection expressions. Operator not it takes precedence over the operator and takes precedence over the operator or.

(C #)

//operator and have precedence over OR operator, parenthesis is neededDataView.RowFilter ="City = ' Tokyo ' and (age </age >)";//following examples do the sameDataView.RowFilter ="City <> ' Tokyo ' and City <> ' Paris '";d Ataview.rowfilter="Not City = ' Tokyo ' and not city = ' Paris '";d Ataview.rowfilter="Not (city = ' Tokyo ' OR city = ' Paris ')";d Ataview.rowfilter="City isn't in (' Tokyo ', ' Paris ')";

Arithmetic and string operators

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

(C #)

" Motherage-age < ";   // people with young mother " Age % = 0 ";           // people with decennial birthday

There is also a string operator connection + .

Parent-Child Relationship reference

But the things table can use the column name prefixes referenced in the parent expression Parent. . The child column name can be used to reference the prefix in the Acolumn child table Child. .

The reference column to the child must be in an aggregate function, because the child's relationship may return multiple rows. For example, the expression SUM(Child.Price) returns all prices in parenttable rows related to the child table.

If a table has more than one child relationship, the prefix name must contain a relationship. For example Child(OrdersToItemsRelation).Price , the Expression Reference column price is used in the child table relationship called orderstoitemsre--pair modulation.

Aggregation functions

There are supported aggregation functions,,, SUM COUNT MIN MAX , AVG (average) STDEV (statistical standard deviation) VAR (statistical variance).

This example shows the execution of the singletable aggregation function.

(C #)

// Select people with above-average salary " Salary > AVG (Salary) ";

The following example shows a parent-child relationship of two tables by an aggregation function. Suppose there are form orders and items with parent-child relationships.

(C #)

// Select orders which has more than 5 items " COUNT (Child.idorder) > 5 " ; // Select orders which Total price (sum of items prices) is greater or equal " SUM (child.price) >= ";

Function

The following features are also supported. Detailed description can be found here datacolumn.ex-pressure.

• CONVERT--Specify the specific expression. NET Frameworktype
• LEN--Get the length of a string
• ISNULL--Check the expression and return the check expression or Replacementvalue
• IIF--get one of two values depending on the result of the logical expression
• TRIM--Remove all leading and trailing whitespace characters \ r, \ n \ t,? "
• SUBSTRING--Specifies the length of the substring, starting at TheString at the specified point

2016-01-22 DataView RowFilter Syntax