Finding Rows in a DataView

[ Team LiB ] 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...
Nội dung trích xuất từ tài liệu:
Finding Rows in a DataView [ Team LiB ]Recipe 3.9 Finding Rows in a DataViewProblemYou need to find a row or group of rows in a DataView meeting certain criteria.SolutionUse 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 constantsusing 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 REQUIRED DATE + Environment.NewLine); // Iterate over the collection of found rows. foreach(DataRowView row in foundRows) { result.Append(row[OrderID] + + row[RequiredDate] + Environment.NewLine); } result.Append(COUNT + foundRows.Length + Environment.NewLine); } resultTextBox.Text = result.ToString( );}DiscussionThe Find( ) and FindRows( ) methods of the DataView search for rows in a DataViewusing its sort key values. The search values must match the sort key values exactly toreturn 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 nomatch 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 beaccessed using the DataRow property of the DataRowView.Before either method can be used, a sort order must be specified or an exception will beraised. 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 objectvalue if the DataView is sorted on a single column or an array of objects containingvalues for all of the columns defined by the Sort property in the same order as specifiedby the Sort property.The Find( ) and FindRows( ) methods perform better than the RowFilter property when aresult set from the DataView matching specific criteria is required rather than a dynamicview on the subset of data. This is because setting the RowFilter property of theDataView causes the index for the DataView to be rebuilt, while the Find( ) andFindRows( ) methods use the existing index.[ Team LiB ]