12.3 Accessing Data Through a DataView

A DataView isn't just for data binding. You can also use it when making programmatic changes. For example, you might create a DataView that contains rows that match certain criteria and then apply a global change to these rows. For example, the following code creates a view that includes all rows with a null value in the Country field and then deletes them:

// Find all the rows where a Country isn't specified.
DataView view = new DataView(ds.Tables["Customers"]);
view.RowFilter = "Country IS NULL";

// Delete these rows.
foreach (DataRowView row in view)
{
    row.Delete();
}

// Display the results.
dataGrid1.DataSource = ds.Tables["Customers"].DefaultView;

This example uses the indexer for the DataView, which accesses the collection of DataRowView objects. Each DataRowView represents a single row from the original DataTable. The DataRowView provides most of the same features as the underlying DataRow object, including the ability to begin and end editing, access values using the field name, and delete the row. You can also access the underlying DataRow directly through the DataRowView.Row property.

12.3.1 Searching a DataView

Once you have defined a sort order for a DataView, either by setting the RowFilter or the ApplyDefaultSort properties, you can use criteria to search for rows. The DataView provides two methods for this task: Find( ) and FindRows( ).

For example, if you have a sort defined on the ContactName column of the Customers table, you can use the Find( ) method to search for a row with the ContactName Roland Mendel. If a match is found, Find( ) returns the index number of the row in the DataView. If no match is found, it returns -1.

DataView view = new DataView(ds.Tables["Customers"]);
view.Sort = "ContactName";

int rowIndex = view.Find("Roland Mendel");

if (rowIndex == -1)
{
    Console.WriteLine("No match found.");
}
else
{
    Console.WriteLine(view[rowIndex]["CustomerID"].ToString() + 
                      " is a match.");
}

The only limitation is that the string you use to search must match the sort fields exactly. The Find( ) and FindRows( ) methods don't support partial matches or wildcards (in the previous example, you can't search for just "Roland").

If there is a possibility that more than one row will match the criteria you use, you should use the FindRows( ) method instead of Find( ). It returns an array of DataRowView objects that reference the matching rows. If no match is found, FindRows( ) returns an empty array.

DataView view = new DataView(ds.Tables["Customers"]);
view.Sort = "Country";

DataRowView[] rows = view.FindRows("Germany");

if rows.Length == 0
{
    Console.WriteLine("No match found.");
}
else
{
    foreach (DataRowView row in rows)
    {
        Console.WriteLine(row["CustomerID"].ToString() + " is a match.");
    }
}

Finally, if you've created a sort expression that incorporates information from multiple columns, you must use an overloaded version of the Find( ) or FindRows( ) methods that accepts an array with search values for all columns, in the same order:

DataView view = new DataView(ds.Tables["Customers"]);
view.Sort = "Country, City";

DataRowView[] rows = view.FindRows(new object[] {"Germany", "Berlin"};

The case-sensitivity of search values for the Find( ) and FindRows( ) methods is determined by the CaseSensitive property of the underlying DataTable.

12.3.2 Navigating Relations with a DataView

Unlike the DataRow object, the DataRowView doesn't include methods such as GetChildRows( ) and GetParentRow( ). However, you can still use DataRelations with the DataView, thanks to the CreateChildView( ) method. CreateChildView( ) accepts a reference to a DataRelation object and creates a new DataView that contains the appropriate child rows.

// Define a DataRelation.
DataColumn parentCol = ds.Tables["Categories"].Columns["CategoryID"];
DataColumn childCol = ds.Tables["Products"].Columns["CategoryID"];
DataRelation relation = new DataRelation("Cat_Prod", parentCol, childCol);
ds.Relations.Add(relation);

// Create a view on the Categories table.
DataView viewCategories = new DataView(ds.Tables["Categories"]);
view.Sort = "CategoryName";

// Select a specific category row through the DataRowView.
DataRowView rowCategory = viewCategories.Find("Beverages");

// Find products in this category using a new DataView.
DataView viewProducts = rowCategory.CreateChildView(relation);

// Display the products.
foreach (DataRowView row in viewProducts)
{
    Console.WriteLine(row["ProductName"]);
}

This navigation works in one direction only. There is no direct way to retrieve a reference to the parent row of a DataRowView.



    Part I: ADO.NET Tutorial
    Part II: ADO.NET Core Classes
    Part III: API Quick Reference