Recipe 3.9 Finding Rows in a DataView

Problem

You need to find a row or group of rows in a DataView meeting certain criteria.

Solution

Use a sorted DataView to find rows using columns that are not part of the primary key.

The sample code contains two event handlers:

Form.Load

Sets up the sample by creating a DataTable containing the Orders table from the Northwind database. A DataView of the table sorted by the CustomerID and EmployeeID is created.

Find Button.Click

Uses the FindRows( ) method of the DataView to retrieve the array of DataRowView objects matching the CustomerID and OrderID specified. The code iterates over the collection to return the results.

The C# code is shown in Example 3-9.

Example 3-9. File: DataViewSearchPerformanceForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Text;
using System.Data;
using System.Data.SqlClient;

private DataView dv;

//  . . . 

private void DataViewSearchPerformanceForm_Load(object sender,
    System.EventArgs e)
{
    // Fill the source table with schema and data.
    SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Orders",
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    DataTable dt = new DataTable("Orders");
    da.FillSchema(dt, SchemaType.Source);
    da.Fill(dt);

    // Create the data view for the Orders table and sort.
    dv = new DataView(dt);
    dv.Sort = "CustomerID, EmployeeID";
}

private void findButton_Click(object sender, System.EventArgs e)
{
    StringBuilder result = new StringBuilder( );
    DataRowView[] foundRows;

    // Find the rows by the sort key value ProductID.
    try
    {
        foundRows = dv.FindRows(new object[] {customerIdTextBox.Text,
            employeeIdTextBox.Text});
    }
    catch (FormatException ex)
    {
        resultTextBox.Text = ex.Message;
        return;
    }

    // Display the results.
    if(foundRows.Length == 0)
    {
        result.Append("No rows found.");
    }
    else
    {
        result.Append("ORDER\tREQUIRED DATE" + Environment.NewLine);

        // Iterate over the collection of found rows.
        foreach(DataRowView row in foundRows)
        {
            result.Append(row["OrderID"] + "\t" +
                row["RequiredDate"] +
                Environment.NewLine);
        }

        result.Append("COUNT\t" + foundRows.Length +
            Environment.NewLine);
    }

    resultTextBox.Text = result.ToString( );
}

Discussion

The Find( ) and FindRows( ) methods of the DataView search for rows in a DataView using its sort key values. The search values must match the sort key values exactly to return a result; wild card matches are not possible.

The primary difference between the Find( ) and FindRows( ) methods is that Find( ) returns the zero-based index of the first row that matches the search criteria (or -1 if no match is found) while FindRows( ) returns a DataRowView array of all matching rows (or an empty array if no match is found). The DataRow for a DataRowView can be accessed using the DataRow property of the DataRowView.

Before either method can be used, a sort order must be specified or an exception will be raised. You can do this in two ways:

• Set the ApplyDefaultSort property of the DataView to true. This automatically creates an ascending sort order based on the primary column or columns of the table. The default sort can be applied only when the Sort property of the DataView is a null reference or an empty string and when the underlying DataTable has a primary key defined. By default, the AutoDefaultSort property is set to false, so it must be explicitly set.

• Setting the Sort property of the DataView to a string containing one or more column names followed by nothing, or ASC for an ascending sort, or by DESC for a descending sort. Use commas to separate multiple sort column names.

Both the Find( ) and FindRows( ) methods take a single input argument. This is an object value if the DataView is sorted on a single column or an array of objects containing values for all of the columns defined by the Sort property in the same order as specified by the Sort property.

The Find( ) and FindRows( ) methods perform better than the RowFilter property when a result set from the DataView matching specific criteria is required rather than a dynamic view on the subset of data. This is because setting the RowFilter property of the DataView causes the index for the DataView to be rebuilt, while the Find( ) and FindRows( ) methods use the existing index.