使用 DataView 进行筛选 (LINQ to DataSet)

项目
04/07/2023

使用特定条件筛选数据，然后通过 UI 控件在客户端中表示该数据的能力是数据绑定的一个重要特征。 DataView 提供多种方式来筛选数据并返回满足指定筛选条件的数据行子集。除了基于字符串的筛选功能之外，DataView 还提供将 LINQ 表达式用于筛选条件的功能。通过 LINQ 表达式，还可以使用比基于字符串的筛选更复杂和强大的筛选操作。

使用 DataView 筛选数据有两种方式：

使用 Where 子句从 LINQ to DataSet 查询创建 DataView。
使用 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 上的筛选器可以采用两种不同的方式清除：

将 RowFilter 属性设置为 null。
将 RowFilter 属性设置为一个空字符串。

示例

下面的示例通过查询创建 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 = ""

使用 DataView 进行筛选 (LINQ to DataSet)

通过具有筛选信息的查询创建 DataView

示例

示例

示例

示例

使用 RowFilter 属性

清除筛选器

示例

示例

请参阅

其他资源