Displaying and Updating Data

In this section, we'll cover some of the tasks that come up when using data bound controls. For more details about interacting with a database, see the section on ADO.NET.

Filtering Data

You've queried the database, retrieved a dataset of results, and bound a dataview to a DataGrid on your form. One of the additional pieces of functionality that you may want to give your users is the ability to filter the data based on some type of search criteria. Apart from re-issuing a new SQL query to the database, this can be accomplished using the RowFilter property on the DataView object. This property is a string value similiar in form to the WHERE portion of a SQL statement. Here are some examples of legitimate row filters:

dataview1.RowFilter = "LastName = 'Smith'";

dataview1.RowFilter = "State = 'MA'";

dataview1.RowFilter = "CompanyName LIKE 'American%'";

Another instance of where you may need to filter data is in a data entry application, where users can modify many rows client-side, and then submit all the changes to the database. If you are using a dataset to manage the changes, you can filter rows based on their modification state. The DataView class has a RowStateFilter property where you can apply state filters from the DataViewRowState enumeration. Here are the possible values from this enumeration:

Added A new row.

CurrentRows Current rows including unchanged, new, and modified rows.

Deleted A deleted row.

ModifiedCurrent A current version, which is a modified version of original data (see ModifiedOriginal).

ModifiedOriginal The original version (although it has since been modified and is available as ModifiedCurrent).

None None.

OriginalRows Original rows including unchanged and deleted rows.

Unchanged An unchanged row.

The following code sample shows a RowStateFilter being applied to a dataview to only show modified rows (containing the new values, not the original values):

public DataView GetModifiedRows(DataSet DataSet1)
{
   //Create DataView
   DataView dv = new DataView();
   dv.Table = DataSet1.Tables["Suppliers"];

   //Apply Filter
   dv.RowStateFilter = DataViewRowState.ModifiedCurrent;

   return dv;
}

Sorting Data

In addition to filtering data, you may want users to be able to sort data by various columns, in ascending and descending order. If you are binding your data to a datagrid, you may follow the convention of allowing a user to click a column header to sort by that column. To sort data, use the Sort property on the DataView. The syntax for this property is similar to that of the ORDER BY clause in a SQL statement. Set this property with a string indicating the column you want to sort by, followed by ASC or DESC for ascending or descending order respectively. Here is an example:

// Get the Data View
DataView dv = dsCustomers.Tables[0].DefaultView;

// Apply Sort
dv.Sort = "CompanyName DESC";

// Bind Data
DataGrid1.DataSource = dv;