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

發行項
04/19/2011

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

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

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

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

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

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

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

範例

下列範例會在 SalesOrderDetail 資料表中查詢是否有數量大於 2 而小於 6 的訂單、從該查詢中建立 DataView，然後將 DataView 繫結至 BindingSource：

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

DataTable orders = dataSet.Tables["SalesOrderDetail"];

EnumerableRowCollection<DataRow> query = from order in orders.AsEnumerable()
                                         where order.Field<Int16>("OrderQty") > 2 && order.Field<Int16>("OrderQty") < 6 
                                         select order;

DataView view = query.AsDataView();

bindingSource1.DataSource = view;

範例

下列範例會從 2001 年 6 月 6 日之後下單的訂單查詢中建立 DataView：

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

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;

範例

篩選也可以與排序結合。下列範例會從姓氏以 "S" 為開頭並先依據姓氏，然後再依據名字排序的連絡人查詢中建立 DataView。

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

            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();

範例

下列範例會使用 SoundEx 演算法來尋找姓氏類似於 "Zhu" 的連絡人。 SoundEx 演算法是在 SoundEx 方法中實作的。

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

DataTable contacts = dataSet.Tables["Contact"];

string 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();

SoundEx 是用於依據聲音索引名稱的語音演算法，而這些名稱都是以英文發音 (原本由美國人口普查局 (U.S. Census Bureau) 所開發)。 SoundEx 方法會針對名稱傳回四個字元碼，其中包含一個英文字母，後面接著三個數字。該字母是名稱的第一個字母，而這些數字則編碼名稱中的其餘子音字母。類似發音的名稱會共用相同的 SoundEx 代碼。在先前範例之 SoundEx 方法中使用的 SoundEx 實作如下所示：

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

static private string SoundEx(string word)
{
    // The length of the returned code.
    int length = 4;

    // Value to return.
    string value = "";

    // The size of the word to process.
    int 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(System.Globalization.CultureInfo.InvariantCulture);

        // Convert the word to a character array.
        char[] chars = word.ToCharArray();

        // Buffer to hold the character codes.
        StringBuilder buffer = new StringBuilder();
        buffer.Length = 0;

        // The current and previous character codes.
        int prevCode = 0;
        int 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 (int 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;            
}

使用 RowFilter 屬性

DataView 現有的以字串為基礎的篩選功能仍然可在 LINQ to DataSet 內容中運作。如需以字串為基礎之 RowFilter 篩選的詳細資訊，請參閱排序和篩選資料 (ADO.NET)。

下列範例會從 Contact 資料表中建立 DataView，然後設定 RowFilter 屬性，以便傳回連絡人姓氏為 "Zhu" 的資料列：

Dim contacts As DataTable = dataSet.Tables("Contact")

Dim view As DataView = contacts.AsDataView()
view.RowFilter = "LastName='Zhu'"
bindingSource1.DataSource = view
dataGridView1.AutoResizeColumns()

DataTable contacts = dataSet.Tables["Contact"];

DataView view = contacts.AsDataView();

view.RowFilter = "LastName='Zhu'";

bindingSource1.DataSource = view;
dataGridView1.AutoResizeColumns();

在您已經從 DataTable 或 LINQ to DataSet 查詢中建立 DataView 之後，就可以使用 RowFilter 屬性，根據資料列的資料行值來指定資料列的子集。以字串為基礎的篩選和以運算式為基礎的篩選會互斥。因此，設定 RowFilter 屬性將會清除從 LINQ to DataSet 查詢中推斷的篩選運算式，而且無法重設篩選運算式。

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'"

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'";

如果您想要傳回特定資料查詢的結果，但不要提供資料子集的動態檢視，就可以使用 DataView 的 Find 或 FindRows 方法，而非設定 RowFilter 屬性。 RowFilter 屬性最適於資料繫結應用程式，因為這種應用程式會用繫結控制項顯示篩選結果。設定 RowFilter 屬性會重建資料索引，因而增加應用程式的負荷並降低效能。 Find 和 FindRows 方法會使用目前的索引，而不需要重建索引。如果您只要呼叫 Find 或 FindRows 一次，就應該使用現有的 DataView。如果您要呼叫 Find 或 FindRows 多次，就應該建立新的 DataView 來重建您想要搜尋之資料行的索引，然後呼叫 Find 或 FindRows 方法。如需 Find 和 FindRows 方法的詳細資訊，請參閱尋找資料列 (ADO.NET)和 DataView 效能。

清除篩選

在您已經使用 RowFilter 屬性來設定篩選之後，就可以清除 DataView 上的篩選。您可以使用兩種不同的方式來清除 DataView 上的篩選：

將 RowFilter 屬性設定為 null。
將 RowFilter 屬性設定為空字串。

範例

下列範例會從查詢中建立 DataView，然後將 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

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;

範例

下列範例會從資料表中建立 DataView、設定 RowFilter 屬性，然後將 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 = ""

DataTable contacts = dataSet.Tables["Contact"];

DataView view = contacts.AsDataView();

view.RowFilter = "LastName='Zhu'";


bindingSource1.DataSource = view;
dataGridView1.AutoResizeColumns();

// Clear the row filter.
view.RowFilter = "";

請參閱

概念

資料繫結和 LINQ to DataSet

使用 DataView 進行排序 (LINQ to DataSet)

共用方式為

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

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

範例

範例

範例

範例

使用 RowFilter 屬性

清除篩選

範例

範例

請參閱

概念

其他資源