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

使用特定準則來篩選資料,然後透過 UI 控制項呈現資料給用戶端的功能是資料繫結的重要層面。The ability to filter data using specific criteria and then present the data to a client through a UI control is an important aspect of data binding. DataView 提供了許多方式來篩選資料並傳回符合特定篩選準則的資料列子集。DataView provides several ways to filter data and return subsets of data rows meeting specific filter criteria. 除了以字串為基礎的篩選功能之外,DataView 也提供使用 LINQ 運算式做為篩選準則的功能。In addition to the string-based filtering capabilities, DataView also provides the ability to use LINQ expressions for the filtering criteria. LINQ 運算式比以字串為基礎的篩選,允許更複雜且功能強大的篩選作業。LINQ expressions allow for much more complex and powerful filtering operations than the string-based filtering.

目前有兩種方式可以使用 DataView 來篩選資料:There are two ways to filter data using a DataView:

  • 使用 Where 子句從 LINQ to DataSet 查詢建立 DataViewCreate a DataView from a LINQ to DataSet query with a Where clause.

  • 使用 DataView 現有的以字串為基礎的篩選功能。Use the existing, string-based filtering capabilities of DataView.

從含有篩選資訊的查詢中建立 DataViewCreating DataView from a Query with Filtering Information

可以從 LINQ to DataSet 查詢建立 DataView 物件。A DataView object can be created from a LINQ to DataSet query. 如果該查詢包含 Where 子句,DataView 就是使用查詢的篩選資訊建立的。If that query contains a Where clause, the DataView is created with the filtering information from the query. Where 子句中的運算式可用來決定哪些資料列將包含在 DataView 中,而且它是篩選的基礎。The expression in the Where clause is used to determine which data rows will be included in the DataView, and is the basis for the filter.

以運算式為基礎的篩選會比較簡單的以字串為基礎的篩選提供功能更強大且更複雜的篩選。Expression-based filters offer more powerful and complex filtering than the simpler string-based filters. 以字串為基礎的篩選和以運算式為基礎的篩選會互斥。The string-based and expression-based filters are mutually exclusive. 如果您從查詢中建立 RowFilter 之後才設定以字串為基礎的 DataView,就會清除從查詢中推斷的以運算式為基礎的篩選。When the string-based RowFilter is set after a DataView is created from a query, the expression based filter inferred from the query is cleared.

注意

在大部分清況中,用於篩選的運算式不應該具有副作用 (Side Effect) 而且必須具決定性。In most cases, the expressions used for filtering should not have side effects and must be deterministic. 此外,這些運算式不應該包含取決於固定執行次數的任何邏輯,因為篩選作業可能會執行任何次數。Also, the expressions should not contain any logic that depends on a set number of executions, because the filtering operations might be executed any number of times.

範例Example

下列範例會在 SalesOrderDetail 資料表中查詢是否有數量大於 2 而小於 6 的訂單、從該查詢中建立 DataView,然後將 DataView 繫結至 BindingSourceThe following example queries the SalesOrderDetail table for orders with a quantity greater than 2 and less than 6; creates a DataView from that query; and binds the DataView to a BindingSource:

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

範例Example

下列範例會從 2001 年 6 月 6 日之後下單的訂單查詢中建立 DataViewThe following example creates a DataView from a query for orders placed after June 6, 2001:

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

範例Example

篩選也可以與排序結合。Filtering can also be combined with sorting. 下列範例會從姓氏以 "S" 為開頭並先依據姓氏,然後再依據名字排序的連絡人查詢中建立 DataViewThe following example creates a DataView from a query for contacts whose last name start with "S" and sorted by last name, then first name:

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

範例Example

下列範例會使用 SoundEx 演算法來尋找姓氏類似於 "Zhu" 的連絡人。The following example uses the SoundEx algorithm to find contacts whose last name is similar to "Zhu". SoundEx 演算法是在 SoundEx 方法中實作的。The SoundEx algorithm is implemented in the SoundEx method.

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();
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 is a phonetic algorithm used for indexing names by sound, as they are pronounced in English, originally developed by the U.S. Census Bureau. SoundEx 方法會針對名稱傳回四個字元碼,其中包含一個英文字母,後面接著三個數字。The SoundEx method returns a four character code for a name consisting of an English letter followed by three numbers. 該字母是名稱的第一個字母,而這些數字則編碼名稱中的其餘子音字母。The letter is the first letter of the name and the numbers encode the remaining consonants in the name. 類似發音的名稱會共用相同的 SoundEx 代碼。Similar sounding names share the same SoundEx code. 在先前範例之 SoundEx 方法中使用的 SoundEx 實作如下所示:The SoundEx implementation used in the SoundEx method of the previous example is shown here:

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;            
}
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 屬性Using the RowFilter Property

DataView 的現有以字串為基礎的篩選功能仍可在 LINQ to DataSet 內容中運作。The existing string-based filtering functionality of DataView still works in the LINQ to DataSet context. 如需以字串為基礎 RowFilter 篩選的詳細資訊,請參閱排序和篩選資料For more information about string-based RowFilter filtering, see Sorting and Filtering Data.

下列範例會從 Contact 資料表中建立 DataView,然後設定 RowFilter 屬性,以便傳回連絡人姓氏為 "Zhu" 的資料列:The following example creates a DataView from the Contact table and then sets the RowFilter property to return rows where the contact's last name is "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 屬性,根據其資料行值來指定資料列的子集。After a DataView has been created from a DataTable or LINQ to DataSet query, you can use the RowFilter property to specify subsets of rows based on their column values. 以字串為基礎的篩選和以運算式為基礎的篩選會互斥。The string-based and expression-based filters are mutually exclusive. 設定 RowFilter 屬性將會清除從 LINQ to DataSet 查詢推斷的篩選運算式,而且無法重設篩選運算式。Setting the RowFilter property will clear the filter expression inferred from the LINQ to DataSet query, and the filter expression cannot be reset.

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

如果您想要傳回特定資料查詢的結果,但不要提供資料子集的動態檢視,就可以使用 FindFindRowsDataView 方法,而非設定 RowFilter 屬性。If you want to return the results of a particular query on the data, as opposed to providing a dynamic view of a subset of the data, you can use the Find or FindRows methods of the DataView, rather than setting the RowFilter property. RowFilter 屬性最適於資料繫結應用程式,因為這種應用程式會用繫結控制項顯示篩選結果。The RowFilter property is best used in a data-bound application where a bound control displays filtered results. 設定 RowFilter 屬性會重建資料索引,因而增加應用程式的負荷並降低效能。Setting the RowFilter property rebuilds the index for the data, adding overhead to your application and decreasing performance. FindFindRows 方法會使用目前的索引,而不需要重建索引。The Find and FindRows methods use the current index without requiring the index to be rebuilt. 如果您只要呼叫 FindFindRows 一次,就應該使用現有的 DataViewIf you are going to call Find or FindRows only once, then you should use the existing DataView. 如果您要呼叫 FindFindRows 多次,就應該建立新的 DataView 來重建您想要搜尋之資料行的索引,然後呼叫 FindFindRows 方法。If you are going to call Find or FindRows multiple times, you should create a new DataView to rebuild the index on the column you want to search on, and then call the Find or FindRows methods. 如需 FindFindRows 方法的詳細資訊,請參閱尋找資料列DataView 效能For more information about the Find and FindRows methods see Finding Rows and DataView Performance.

清除篩選Clearing the Filter

在您已經使用 DataView 屬性來設定篩選之後,就可以清除 RowFilter 上的篩選。The filter on a DataView can be cleared after filtering has been set using the RowFilter property. 您可以使用兩種不同的方式來清除 DataView 上的篩選:The filter on a DataView can be cleared in two different ways:

範例Example

下列範例會從查詢中建立 DataView,然後將 RowFilter 屬性設定為 null,藉以清除篩選:The following example creates a DataView from a query and then clears the filter by setting RowFilter property to 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

範例Example

下列範例會從資料表中建立 DataView、設定 RowFilter 屬性,然後將 RowFilter 屬性設定為空字串,藉以清除篩選:The following example creates a DataView from a table sets the RowFilter property, and then clears the filter by setting the RowFilter property to an empty string:

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

請參閱See also