ADO.NET Cookbook

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[] ...