Recipe 7.15 Adding Search Capabilities to Windows Forms

Problem

You need to use a search criteria specified by a user to locate a record displayed in a DataGrid without executing a query against the database.

Solution

Use the Find( ) method of the DataView with a sort key value to locate a record displayed in a DataGrid and reposition the row in the DataGrid.

The sample code contains two event handlers:

Form.Load

Sets up the sample by creating a DataTable and filling it with the Customers table from the Northwind sample database. A DataView is created based on the default view of the Customers DataTable, its sort key is set to the CustomerID column, and it is bound to the data grid on the form. Finally, a CurrencyManager is created from the DataView.

Go Button.Click

Uses the Find( ) method of the DataView to locate a record with the CustomerID specified by the user. If the CustomerID is found, the CurrencyManager created in the Form.Load event handler is used to select the matching record in the data grid.

The C# code is shown in Example 7-31.

Example 7-31. File: SearchDataGridForm.cs

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

private DataView dv;
private CurrencyManager cm;

//  . . . 

private void SearchDataGridForm_Load(object sender, System.EventArgs e)
{
    // Create the DataAdapter and load the Customers data in a table.
    String sqlText = "SELECT * FROM Customers";
    SqlDataAdapter da = new SqlDataAdapter(sqlText,
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    DataTable dt = new DataTable( );
    da.Fill(dt);

    // Create a view from the default view for the table.
    dv = dt.DefaultView;
    dv.Sort = "CustomerID";

    // Bind the view to the grid.
    findDataGrid.DataSource = dv;

    // Get the CurrencyManager for the DataView.
    cm = (CurrencyManager)findDataGrid.BindingContext[dv];
}

private void findButton_Click(object sender, System.EventArgs e)
{
    if(findTextBox.Text != "")
    {
        // Find the customer.
        int i = dv.Find(findTextBox.Text);
        if(i < 0)
            // A match was not found.
            MessageBox.Show("No matching records found.", "Find",
                MessageBoxButtons.OK,
                MessageBoxIcon.Information);
        else
            // Reposition the grid record using the CurrencyManager.
            cm.Position = i;
    }
    else
    {
        MessageBox.Show("Enter find criteria.", "Find",
            MessageBoxButtons.OK, MessageBoxIcon.Question);
        findTextBox.Focus( );
    }
}

Discussion

The Find( ) method of the DataView locates a row matching a specified sort key value. The Sort property gets or sets the column or columns that the DataView is sorted on. The Sort property is a string that contains the column name, or multiple column names separated by commas, followed by an optional ASC or DESC clause specifying sort direction.

There are two methods that you can use to locate records in a DataView:

Find( )

This method of the DataView returns the index of the first row matching the specified sort key value or and array of sort key values, for sort keys based on multiple columns. If no records match, it returns -1.

FindRows( )

This method of the DataView returns an array of rows matching the specified sort key value or array of sort key values. It returns an empty array if the sort key value does not exist.

The Find( ) and FindRows( ) methods use the current index of the DataView without requiring the index to be rebuilt.

Both methods take an argument that is an object array of values whose length matches the number of columns in the sort order of the DataView. The order of columns in the array must match the order of columns specified in the Sort property. A single value can be passed instead of an array if the sort is based on a single column. The sort key value must match exactly in order to return a result. The RowFilter property can be used to locate records matching an expression. For an example, see Recipe 3.1.

The sort order must be specified either by setting the ApplyDefaultSort property of the DataView to true or by setting the Sort property explicitly; otherwise, an exception is thrown. The case sensitivity of the search is controlled by the CaseSensitive property of the underlying DataTable.