使用 DataView 進行篩選 (LINQ to DataSet)

發行項
05/22/2023

使用特定準則來篩選資料，然後透過 UI 控制項呈現資料給用戶端的功能是資料繫結的重要層面。 DataView 提供了許多方式來篩選資料並傳回符合特定篩選準則的資料列子集。除了字串型篩選功能，DataView 也提供使用 LINQ 運算式篩選準則的能力。 LINQ 運算式允許比字串型篩選更複雜且高效的篩選作業。

目前有兩種方式可以使用 DataView 來篩選資料：

從使用 Where 子句的 LINQ to DataSet 查詢，建立 DataView。
使用 DataView 現有的以字串為基礎的篩選功能。

從含有篩選資訊的查詢中建立 DataView

您可以從 LINQ to DataSet查詢建立 DataView 物件。如果該查詢包含 Where 子句，DataView 就是使用查詢的篩選資訊建立的。 Where 子句中的運算式可用來決定哪些資料列將包含在 DataView 中，而且它是篩選的基礎。

以運算式為基礎的篩選會比較簡單的以字串為基礎的篩選提供功能更強大且更複雜的篩選。以字串為基礎的篩選和以運算式為基礎的篩選會互斥。如果您從查詢中建立 RowFilter 之後才設定以字串為基礎的 DataView，就會清除從查詢中推斷的以運算式為基礎的篩選。

注意

在大部分清況中，用於篩選的運算式不應該具有副作用 (Side Effect) 而且必須具決定性。此外，這些運算式不應該包含取決於固定執行次數的任何邏輯，因為篩選作業可能會執行任何次數。

範例

下列範例會在 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 是用於依據聲音索引名稱的語音演算法，而這些名稱都是以英文發音 (原本由美國人口普查局 (U.S. Census Bureau) 所開發)。 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 屬性

在 LINQ to DataSet 內容中，DataView 現有的字串型篩選功能仍可運作。如需字串型 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 屬性

清除篩選

範例

範例

另請參閱

其他資源