ADO.NET DataView Control
A major function of the DataView is to allow data binding on both Windows Forms and Web Forms. Additionally, a DataView can be customized to present a subset of data from the DataTable. This capability allows you to have two controls bound to the same DataTable, but showing different versions of the data. For example, one control may be bound to a DataView showing all of the rows in the table, while a second may be configured to display only the rows that have been deleted from the DataTable. The DataTable also has a DefaultView property which returns the default DataView for the table. For example, if you wish to create a custom view on the table, set the RowFilter on the DataView returned by the DefaultView.
A DataView object serves a similar purpose to views in the SQL context. Both are ways to select a subset of the columns and rows in a table. In SQL Server, it is possible to create a view based on the result of almost any query, but a DataView in ADO.NET is limited to operation on one table only. However, unlike views in some versions of SQL Server, a DataView can be sorted into a given order. The DataViewManager and the DataViewSetting classes do allow multiple table views.
A DataView enables you to create different views of the data stored in a DataTable, a capability that is often used in data-binding applications. Using a DataView, you can expose the data in a table with different sort orders, and you can filter the data by row state or based on a filter expression. A DataView provides a dynamic view of data whose content, ordering, and membership reflect changes to the underlying DataTable as they occur. This is different from the Select method of the DataTable, which returns a DataRow array from a table per a particular filter and/or sort order and whose content reflects changes to the underlying table, but whose membership and ordering remain static. The dynamic capabilities of the DataView make it ideal for data-binding applications.
A DataView provides you with a dynamic view of a single set of data to which you can apply different sorting and filtering criteria, similar to the view provided by a database. However, a DataView differs significantly from a database view in that the DataView cannot be treated as a table and cannot provide a view of joined tables. You also cannot exclude columns that exist in the source table, nor can you append columns, such as computational columns, that do not exist in the source table. You use a DataViewManager to manage view settings for all the tables in a DataSet. The DataViewManager provides you with a convenient way to manage default view settings for each table. When binding a control to more than one table of a DataSet, binding to a DataViewManager is the ideal choice.
To create a filtered and sorted view of data, set the RowFilter and Sort properties. Then use the Item property to return a single DataRowView. You can also add and delete from the set of rows using the AddNew and Delete methods. When you use those methods, the RowStateFilter property can set to specify that only deleted rows or new rows be displayed by the DataView.
public class DataView : MarshalByValueComponent, IBindingList,IList, ICollection, IEnumerable, ITypedList, ISupportInitialize
The following example creates a single DataTable with one column and five rows. Two DataView objects are created and the RowStateFilter is set on each to show different views of the table data. The values are then printed.
private void DemonstrateDataView()
{
// Create one DataTable with one column.
DataTable myTable = new DataTable("myTable");
DataColumn colItem = new DataColumn("item",Type.GetType("System.String"));
myTable.Columns.Add(colItem);
// Add five items.
DataRow NewRow;
for(int i = 0; i <5; i++)
{
NewRow = myTable.NewRow();
NewRow["item"] = "Item " + i;
myTable.Rows.Add(NewRow);
}
// Change the values in the table.
myTable.Rows[0]["item"]="cat";
myTable.Rows[1]["item"] = "dog";
myTable.AcceptChanges();
// Create two DataView objects with the same table.
DataView firstView = new DataView(myTable);
DataView secondView = new DataView(myTable);
// Print current table values.
PrintTableOrView(myTable,"Current Values in Table");
// Set first DataView to show only modified versions of original rows.
firstView.RowStateFilter=DataViewRowState.ModifiedOriginal ;
// Print values.
PrintTableOrView(firstView,"First DataView: ModifiedOriginal");
// Add one New row to the second view.
DataRowView myDataRowView;
myDataRowView=secondView.AddNew();
myDataRowView["item"] = "fish";
// Set second DataView to show modified versions of current rows, or New rows.
secondView.RowStateFilter=DataViewRowState.ModifiedCurrent |
DataViewRowState.Added;
// Print modified and Added rows.
PrintTableOrView(secondView, "Second DataView: ModifiedCurrent | Added");
}
private void PrintTableOrView(DataTable t, string label)
{
// This function prints values in the table or DataView.
Console.WriteLine("\n" + label);
for(int i = 0; i<t.Rows.Count;i++)
{
Console.WriteLine("\t" + t.Rows[i]["item"]);
}
Console.WriteLine();
}
private void PrintTableOrView(DataView dv, string label)
{
// This overload prints values in the table or DataView.
Console.WriteLine("\n" + label);
for(int i = 0; i<dv.Count;i++)
{
Console.WriteLine("\t" + dv[i]["item"]);
}
Console.WriteLine();
}
How we can create a DataView?
There are two ways to create a DataView. You can use the DataView constructor, or you can create a reference to the DefaultView property of the DataTable. The DataView constructor can be empty, or will also take either a DataTable as a single argument, or a DataTable along with filter criteria, sort criteria, and a row state filter. For more information about the additional arguments available for use with the DataView, see Sorting and Filtering Data Using a DataView.
Because the index for a DataView is built both when the DataView is created, and when any of the Sort, RowFilter, or RowStateFilter properties are modified, you will achieve best performance by supplying any initial sort order or filtering criteria as constructor arguments when you create the DataView. Creating a DataView without specifying sort or filter criteria and then setting the Sort, RowFilter, or RowStateFilter properties later results in the index being built at least twice: once when the DataView is created, and again when any of the sort or filter properties are modified.
Note that if you create a DataView using the constructor that does not take any arguments, you will not be able to use the DataView until you have set the Table property. The following code example demonstrates how to create a DataView using the DataView constructor. A RowFilter, Sort column, and DataViewRowState are supplied along with the DataTable.
DataView custDV = new DataView(customerDS.Tables["Customers"],
"Country = 'USA'",
"ContactName",
DataViewRowState.CurrentRows);
DataView custDV = customerDS.Tables["Customers"].DefaultView;
Sorting and Filtering Data Using a DataView
The DataView provides several capabilities for sorting and filtering data in a DataTable. Using the Sort property, you can specify single or multiple column sort orders and include ASC (ascending) and DESC (descending) parameters. You can use the ApplyDefaultSort property to automatically create a sort order, in ascending order, based on the primary key column or columns of the table. ApplyDefaultSort only applies when the Sort property is a null reference or an empty string, and when the table has a primary key defined.
Using the RowFilter property, you can specify subsets of rows based on their column values. For details about valid expressions for the RowFilter property, see the reference information for the Expression property of the DataColumn class. If you want to return the results of a particular query on the data, as opposed to providing a dynamic view of a subset of the data, to achieve best performance use the Find or FindRows methods of the DataView rather than setting the RowFilter property. Setting the RowFilter property causes the index for the data to be rebuilt, adding overhead to your application, and decreasing performance. The RowFilter property is best used in a data-bound application where a bound control displays filtered results. The Find and FindRows methods leverage the current index without requiring the index to be rebuilt.
Using the RowStateFilter property, you can specify which row versions to view. The DataView implicitly manages which row version to expose depending upon the RowState of the underlying row. For example, if the RowStateFilter is set to DataViewRowState.Deleted, the DataView will expose the Original row version of all Deleted rows because there is no Current row version. You can determine which row version of a row is being exposed using the RowVersion property of the DataRowView.
The following table shows the options for DataViewRowState.
DataViewRowState Description
- CurrentRows - The Current row version of all Unchanged, Added, and Modified rows. This is the default.
- Added - The Current row version of all Added rows.
- Deleted - The Original row version of all Deleted rows.
- ModifiedCurrent - The Current row version of all Modified rows.
- ModifiedOriginal - The Original row version of all Modified rows.
- None - No rows.
- OriginalRows - The Original row version of all Unchanged, Modified, and Deleted rows.
- Unchanged - The Current row version of all Unchanged rows.
The following code example creates a view that shows all the products where the units in stock is less than or equal to the reorder level, sorted first by supplier ID and then by product name.
DataView prodView = new DataView(prodDS.Tables["Products"],
"UnitsInStock <= ReorderLevel",
"SupplierID, ProductName",
DataViewRowState.CurrentRows);
continue article