DataView.RowFilter的使用(包括in,like等SQL中的操作符)

DataView RowFilter 语法详解
本文详细介绍了C#中DataView.RowFilter属性的使用方法,包括如何正确构造过滤表达式,避免SQL注入风险,以及如何处理特殊字符、字符串、数字、日期等不同类型的值。

DataView RowFilter Syntax [C#]
This example describes syntax of DataView.RowFil ter expression. It shows how 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 be doubled.

[C#]

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("'", "''"));
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.

[C#]

dataView.RowFilter = "Year = 2008" // integer value dataView.RowFilter = "Price = 1199.9" // float value dataView.RowFilter = 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() method for invariant 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 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));
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.

[C#]

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
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).

[C#]

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
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.

[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/2009#)" // date time values dataView.RowFilter = "Id NOT IN (1, 2, 3)" // values not from the list
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.

[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 that don't start with 'j'
If a pattern in a LIKE clause contains any of these special characters * %

使用 DataGrid 控件进行模糊搜索时,可以使用 DataViewRowFilter 属性来实现。但是,如果在使用 RowFilter 时出现错误,则可能是由于以下原因之一: 1. 搜索字符串包含了特殊字符,比如单引号、百分号等。这些字符需要转义,否则会导致语法错误。可以使用字符串的 Replace 方法将这些特殊字符替换成转义字符。 2. 搜索字符串中包含了非法的正则表达式语法,比如未闭合的括号、不匹配的方括号等。这些语法错误需要修正,以保证正则表达式语法正确。 3. DataView 的数据源中包含了空值或者 DBNull 值。在使用 RowFilter 进行搜索时,需要特别处理这些值,否则会导致运行时异常。 下面是一些示例代码,用于演示如何使用 DataViewRowFilter 进行模糊搜索: ```csharp private void DoSearch(string keyword) { // 将特殊字符转义 keyword = keyword.Replace("'", "''"); // 构造 DataViewRowFilter 表达式 string filter = string.Format("Name LIKE '%{0}%' OR Age LIKE '%{0}%'", keyword); // 进行搜索操作 DataView view = new DataView(dataTable, filter, "", DataViewRowState.CurrentRows); dataGridView.DataSource = view; } ``` 在上面的代码中,我们首先使用字符串的 Replace 方法将单引号替换成两个单引号,以避免语法错误。然后,构造 DataViewRowFilter 表达式,使用 LIKE 运算符进行模糊搜索。最后,将 DataView 对象设置为 DataGridView 控件的数据源,以显示搜索结果。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值