Use of dataview.rowfilter (including operators in SQL In,like, etc.) _ Practical Tips

DataView RowFilter Syntax [C #]
This example describes syntax of dataview.rowfil ter expression. It shows 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"; No special character in column name "id" dataview.rowfilter = "$id = 10"; No special character in column name "$id" DataView.RowFilter = "[#id] = 10"; Special character "#" in Column name "#id" DataView.RowFilter = "[[Id/]] = 10"; Special characters in column name "[ID]"
Literals
String values are enclosed within single quotes '. If The string contains single quote ', the quote must to be doubled.

[C #]

DataView.RowFilter = "name = ' john '"//String value DataView.RowFilter = "name = ' John ' ' A '" "//string with single quot Es "John ' A" "DataView.RowFilter = String.Format (" Name = ' {0} ' "," John ' A ' ".) Replace ("'", "" "));
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 中文版 culture.

[C #]

DataView.RowFilter = "year = 2008"//integer value DataView.RowFilter = ' Price = 1199.9 '//float value Dataview.rowfilte R = String.Format (CultureInfo.InvariantCulture.NumberFormat, "Price = {0}", 1199.9f);
Date values are enclosed within sharp characters #. The date format is the ' same as is ' the result of datetime.tostring () to invariant or 中文版 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 supported DataView.RowFilter = ' date = #12/31/2008 16:44:58# '//date and time value Dataview.rowfilte R = String.Format (CultureInfo.InvariantCulture.DateTimeFormat, "Date = #{0}#", New DateTime (2008, 12, 31, 16, 44, 58));
Alternatively can enclose all values within single quotes '. It means can use string values for numbers or date values. In the, the current culture are used to convert the string to the specific value.

[C #]

DataView.RowFilter = "date = ' 12/31/2008 16:44:58 '"//if current culture is 中文版 dataview.rowfilter = "date = ' 31.12.2 008 16:44:58 ' "//if current culture are German DataView.RowFilter =" Price = ' 1199.90 ' "//if current culture is 中文版 D Ataview.rowfilter = "Price = ' 1199,90 '"//If the culture is German
Comparison operators
Equal, not Equal, less, greater operators are-used to include only values, which suit to a comparison. can use these operators = <> < <= > >=.

Note:string comparison is culture-sensitive, it uses CultureInfo from Datatable.locale property of related table (Datavie W.table.locale). If the is isn't explicitly set, its default value was Dataset.locale (and its default value are current system Cultur E thread.curren tthread.curren tculture).

[C #]

DataView.RowFilter = "Num = ten"//number is equal to ten 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
Operator in are used to include only values from the list. You can use the operator to all data types, such as numbers or strings.

[C #]

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/2 009#) "//Date time values DataView.RowFilter =" Id not in (1, 2, 3) "//values not from the list
Operator like are used to include only values, match a pattern with wildcards. wildcard character is * or%, it can be in the beginning of a pattern ' *value ', at the end ' value* ', or at both ' *value* '. Wildcard in the middle of a patern ' va*lue ' are not allowed.

[C #]

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 ' don ' t start with ' J '
If A is LIKE clause contains any of the special characters *%