使用 DataView 进行筛选 (LINQ to DataSet)
使用特定条件筛选数据,然后通过 UI 控件在客户端中表示该数据的能力是数据绑定的一个重要特征。 DataView 提供多种方式来筛选数据并返回满足指定筛选条件的数据行子集。 除了基于字符串的筛选功能之外,DataView 还提供将 LINQ 表达式用于筛选条件的功能。 通过 LINQ 表达式,还可以使用比基于字符串的筛选更复杂和强大的筛选操作。
使用 DataView 筛选数据有两种方式:
通过具有筛选信息的查询创建 DataView
可以通过 LINQ to DataSet 查询创建 DataView 对象。 如果该查询包含一个 Where
子句,则会使用查询中的筛选信息创建 DataView。 Where
子句中的表达式用于确定哪些数据行将包括在 DataView 中并作为筛选器的基础。
基于表达式的筛选器具有比基于字符串的简单筛选器更强大、更复杂的筛选功能。 基于字符串的筛选器和基于表达式的筛选器是互相排斥的。 如果在通过查询创建 RowFilter 后设置基于字符串的 DataView,则会清除从查询推断的基于表达式的筛选器。
备注
在大多数情况下,用于筛选的表达式不应有副作用且必须是确定的。 另外,表达式不应包含依赖于固定执行次数的任何逻辑,因为筛选操作可能会执行任意次。
示例
下面的示例查询 SalesOrderDetail 表中数量大于 2 且小于 6 的订单,通过查询创建 DataView,并将 DataView 绑定到 BindingSource:
DataTable orders = _dataSet.Tables["SalesOrderDetail"];
EnumerableRowCollection<DataRow> query = from order in orders.AsEnumerable()
where order.Field<short>("OrderQty") > 2 && order.Field<short>("OrderQty") < 6
select order;
DataView view = query.AsDataView();
bindingSource1.DataSource = view;
Dim orders As DataTable = dataSet.Tables("SalesOrderDetail")
Dim query = _
From order In orders.AsEnumerable() _
Where order.Field(Of Int16)("OrderQty") > 2 And _
order.Field(Of Int16)("OrderQty") < 6 _
Select order
Dim view As DataView = query.AsDataView()
bindingSource1.DataSource = view
示例
下面的示例通过查询 2001 年 6 月 6 日以后达成的订单来创建 DataView:
DataTable orders = _dataSet.Tables["SalesOrderHeader"];
EnumerableRowCollection<DataRow> query = from order in orders.AsEnumerable()
where order.Field<DateTime>("OrderDate") > new DateTime(2002, 6, 1)
select order;
DataView view = query.AsDataView();
bindingSource1.DataSource = view;
Dim orders As DataTable = dataSet.Tables("SalesOrderHeader")
Dim query = _
From order In orders.AsEnumerable() _
Where order.Field(Of DateTime)("OrderDate") > New DateTime(2002, 6, 1) _
Select order
Dim view As DataView = query.AsDataView()
bindingSource1.DataSource = view
示例
筛选也可以与排序组合使用。 下面的示例通过查询姓氏以“S”开始并按姓氏排序,然后按名字排序的联系人来创建 DataView:
DataTable contacts = _dataSet.Tables["Contact"];
EnumerableRowCollection<DataRow> query = from contact in contacts.AsEnumerable()
where contact.Field<string>("LastName").StartsWith("S")
orderby contact.Field<string>("LastName"), contact.Field<string>("FirstName")
select contact;
DataView view = query.AsDataView();
bindingSource1.DataSource = view;
dataGridView1.AutoResizeColumns();
Dim contacts As DataTable = dataSet.Tables("Contact")
Dim query = _
From contact In contacts.AsEnumerable() _
Where contact.Field(Of String)("LastName").StartsWith("S") _
Order By contact.Field(Of String)("LastName"), contact.Field(Of String)("FirstName") _
Select contact
Dim view As DataView = query.AsDataView()
bindingSource1.DataSource = view
dataGridView1.AutoResizeColumns()
示例
下面的示例使用 SoundEx 算法查找姓氏与“Zhu”相近的联系人。 SoundEx 算法在 SoundEx 方法中实现。
DataTable contacts = _dataSet.Tables["Contact"];
var soundExCode = SoundEx("Zhu");
EnumerableRowCollection<DataRow> query = from contact in contacts.AsEnumerable()
where SoundEx(contact.Field<string>("LastName")) == soundExCode
select contact;
DataView view = query.AsDataView();
bindingSource1.DataSource = view;
dataGridView1.AutoResizeColumns();
Dim contacts As DataTable = dataSet.Tables("Contact")
Dim soundExCode As String = SoundEx("Zhu")
Dim query = _
From contact In contacts.AsEnumerable() _
Where SoundEx(contact.Field(Of String)("LastName")) = soundExCode _
Select contact
Dim view As DataView = query.AsDataView()
bindingSource1.DataSource = view
dataGridView1.AutoResizeColumns()
SoundEx 是一种拼音算法,用于按英语发音来索引姓名,它最初由美国人口调查局开发。 SoundEx 方法返回一个表示姓名的四字符代码,由一个英文字母后跟三个数字构成。 字母是姓名的首字母,数字对姓名中剩余的辅音字母编码。 发音相近的姓名具有相同的 SoundEx 代码。 上一示例的 SoundEx 方法中使用的 SoundEx 实现如下所示:
static string SoundEx(string word)
{
// The length of the returned code.
const int length = 4;
// Value to return.
var value = "";
// The size of the word to process.
var size = word.Length;
// The word must be at least two characters in length.
if (size > 1)
{
// Convert the word to uppercase characters.
word = word.ToUpper(CultureInfo.InvariantCulture);
// Convert the word to a character array.
var chars = word.ToCharArray();
// Buffer to hold the character codes.
var buffer = new StringBuilder
{
Length = 0
};
// The current and previous character codes.
var prevCode = 0;
var currCode = 0;
// Add the first character to the buffer.
buffer.Append(chars[0]);
// Loop through all the characters and convert them to the proper character code.
for (var i = 1; i < size; i++)
{
switch (chars[i])
{
case 'A':
case 'E':
case 'I':
case 'O':
case 'U':
case 'H':
case 'W':
case 'Y':
currCode = 0;
break;
case 'B':
case 'F':
case 'P':
case 'V':
currCode = 1;
break;
case 'C':
case 'G':
case 'J':
case 'K':
case 'Q':
case 'S':
case 'X':
case 'Z':
currCode = 2;
break;
case 'D':
case 'T':
currCode = 3;
break;
case 'L':
currCode = 4;
break;
case 'M':
case 'N':
currCode = 5;
break;
case 'R':
currCode = 6;
break;
}
// Check if the current code is the same as the previous code.
if (currCode != prevCode)
{
// Check to see if the current code is 0 (a vowel); do not process vowels.
if (currCode != 0)
{
buffer.Append(currCode);
}
}
// Set the previous character code.
prevCode = currCode;
// If the buffer size meets the length limit, exit the loop.
if (buffer.Length == length)
{
break;
}
}
// Pad the buffer, if required.
size = buffer.Length;
if (size < length)
{
buffer.Append('0', length - size);
}
// Set the value to return.
value = buffer.ToString();
}
// Return the value.
return value;
}
Private Function SoundEx(ByVal word As String) As String
Dim length As Integer = 4
' Value to return
Dim value As String = ""
' Size of the word to process
Dim size As Integer = word.Length
' Make sure the word is at least two characters in length
If (size > 1) Then
' Convert the word to all uppercase
word = word.ToUpper(System.Globalization.CultureInfo.InvariantCulture)
' Convert the word to character array for faster processing
Dim chars As Char() = word.ToCharArray()
' Buffer to build up with character codes
Dim buffer As StringBuilder = New StringBuilder()
' The current and previous character codes
Dim prevCode As Integer = 0
Dim currCode As Integer = 0
' Append the first character to the buffer
buffer.Append(chars(0))
' Loop through all the characters and convert them to the proper character code
For i As Integer = 1 To size - 1
Select Case chars(i)
Case "A", "E", "I", "O", "U", "H", "W", "Y"
currCode = 0
Case "B", "F", "P", "V"
currCode = 1
Case "C", "G", "J", "K", "Q", "S", "X", "Z"
currCode = 2
Case "D", "T"
currCode = 3
Case "L"
currCode = 4
Case "M", "N"
currCode = 5
Case "R"
currCode = 6
End Select
' Check to see if the current code is the same as the last one
If (currCode <> prevCode) Then
' Check to see if the current code is 0 (a vowel); do not process vowels
If (currCode <> 0) Then
buffer.Append(currCode)
End If
End If
' Set the new previous character code
prevCode = currCode
' If the buffer size meets the length limit, then exit the loop
If (buffer.Length = length) Then
Exit For
End If
Next
' Pad the buffer, if required
size = buffer.Length
If (size < length) Then
buffer.Append("0", (length - size))
End If
' Set the value to return
value = buffer.ToString()
End If
' Return the value
Return value
End Function
使用 RowFilter 属性
DataView 现有的基于字符串的筛选功能仍可在 LINQ to DataSet 上下文中使用。 有关基于字符串的 RowFilter 筛选的详细信息,请参阅对数据进行排序和筛选。
下面的示例从 Contact 表创建 DataView,然后设置 RowFilter 属性以返回联系人的姓氏为“Zhu”的行:
DataTable contacts = _dataSet.Tables["Contact"];
DataView view = contacts.AsDataView();
view.RowFilter = "LastName='Zhu'";
bindingSource1.DataSource = view;
dataGridView1.AutoResizeColumns();
Dim contacts As DataTable = dataSet.Tables("Contact")
Dim view As DataView = contacts.AsDataView()
view.RowFilter = "LastName='Zhu'"
bindingSource1.DataSource = view
dataGridView1.AutoResizeColumns()
从 DataTable 或 LINQ to DataSet 查询创建 DataView 后,你可以使用 RowFilter 属性基于行的列值指定行的子集。 基于字符串的筛选器和基于表达式的筛选器是互相排斥的。 设置 RowFilter 属性将清除从 LINQ to DataSet 查询推断的筛选表达式,并且该筛选表达式无法重置。
DataTable contacts = _dataSet.Tables["Contact"];
EnumerableRowCollection<DataRow> query = from contact in contacts.AsEnumerable()
where contact.Field<string>("LastName") == "Hernandez"
select contact;
DataView view = query.AsDataView();
bindingSource1.DataSource = view;
dataGridView1.AutoResizeColumns();
view.RowFilter = "LastName='Zhu'";
Dim contacts As DataTable = dataSet.Tables("Contact")
Dim query = _
From contact In contacts.AsEnumerable() _
Where contact.Field(Of String)("LastName") = "Hernandez" _
Select contact
Dim view As DataView = query.AsDataView()
bindingSource1.DataSource = view
dataGridView1.AutoResizeColumns()
view.RowFilter = "LastName='Zhu'"
如果要返回特定数据查询的结果而不是提供数据子集的动态视图,则可以使用 Find 的 FindRows 或 DataView 方法,而不设置 RowFilter 属性。 RowFilter 属性最适合用于用绑定控件显示筛选结果的数据绑定应用程序。 设置 RowFilter 属性会重新生成数据的索引,从而增加应用程序的系统开销并降低性能。 Find 和 FindRows 方法使用当前索引,而不要求重新生成索引。 如果只想调用 Find 或 FindRows 一次,则应使用现有的 DataView。 如果想要调用 Find 或 FindRows 多次,则应该创建一个新的 DataView 以便对想要搜索的列重新生成索引,然后调用 Find 或 FindRows 方法。 有关 Find 和 FindRows 方法的详细信息,请参阅查找行和 DataView 性能。
清除筛选器
使用 DataView 属性设置筛选之后,可以清除 RowFilter 上的筛选器。 DataView 上的筛选器可以采用两种不同的方式清除:
示例
下面的示例通过查询创建 DataView,然后通过将 RowFilter 属性设置为 null
来清除该筛选器:
DataTable orders = _dataSet.Tables["SalesOrderHeader"];
EnumerableRowCollection<DataRow> query = from order in orders.AsEnumerable()
where order.Field<DateTime>("OrderDate") > new DateTime(2002, 11, 20)
&& order.Field<decimal>("TotalDue") < new decimal(60.00)
select order;
DataView view = query.AsDataView();
bindingSource1.DataSource = view;
view.RowFilter = null;
Dim orders As DataTable = dataSet.Tables("SalesOrderHeader")
Dim query = _
From order In orders.AsEnumerable() _
Where order.Field(Of DateTime)("OrderDate") > New DateTime(2002, 11, 20) _
And order.Field(Of Decimal)("TotalDue") < New Decimal(60.0) _
Select order
Dim view As DataView = query.AsDataView()
bindingSource1.DataSource = view
view.RowFilter = Nothing
示例
下面的示例从表创建 DataView,设置 RowFilter 属性,然后通过将 RowFilter 属性设置为一个空的字符串来清除该筛选器:
DataTable contacts = _dataSet.Tables["Contact"];
DataView view = contacts.AsDataView();
view.RowFilter = "LastName='Zhu'";
bindingSource1.DataSource = view;
dataGridView1.AutoResizeColumns();
// Clear the row filter.
view.RowFilter = "";
Dim contacts As DataTable = dataSet.Tables("Contact")
Dim view As DataView = contacts.AsDataView()
view.RowFilter = "LastName='Zhu'"
bindingSource1.DataSource = view
dataGridView1.AutoResizeColumns()
' Clear the row filter.
view.RowFilter = ""