14.10 Creating Different DataViews of a Table

14.10 Creating Different DataView s of a Table

You want to create different views of a table to enable sorting and filtering.

Technique

A DataSet object stores data just as it is received from the database. It doesn't allow you to perform custom sorting nor does it allow you to apply a filter to create a subset of data to bind to a control. The DataView class allows you to create custom views of a single table residing in a DataSet . By using a separate DataView , you are able to apply sorting, column-based filtering, and row-state filtering. Row-state filtering filters rows based on states such as rows that have been added, removed, and modified, to name a few.

To create a DataView object, you can programmatically create one of the supplied constructors or drag and drop it from the Windows Form designer toolbox within the Data tab onto your form. To associate the DataView with a table, set the Table property equal to a DataTable object. If you are using a typed DataSet , each table within the DataSet is a property, whereas tables within an untyped DataSet are located in the Tables collection.

After the DataSet object is filled from a data adapter, you can apply sorting and filtering to the DataView . To sort the table, pass a comma-delimited string of column names with an optional ASC or DESC modifier for each column to specify ascending or descending order for the Sort property of the DataView object. For instance, to sort the Products table of the Northwind database by CategoryID followed by ProductName in descending order, the Sort property appears as follows :

 dataView1.Sort = "CategoryID, ProductName DESC"; 

You use the RowFilter property to display a subset of data within the table. The property is also a string value and uses logical expressions using the same syntax as SQL queries. If the expression returns false for a given row, that row is not displayed in any controls that are bound to the DataView . A filter that only displays products within a certain CategoryID looks like this:

 dataView1.RowFilter = "CategoryID=3"; 

The RowStateFilter allows you to filter the rows of a table based on the current status in the DataSet . The possible values are contained within the DataViewRowState enumerated type and include such values as None , in which no rows are displayed; Unchanged , specifying rows that have not been modified; Added ; and Deleted . You can also use values that let you filter using data before it was modified such as Original and ModifiedOriginal , as well as their current values using Current and ModifiedCurrent . The following example is a culmination of all the DataView techniques described in this section. The application itself contains a DataGrid that is bound to the DataView itself rather than a DataSet . Because the DataView uses the DataSet as its source, any changes within the DataGrid get automatically propagated to the DataSet as before. Additionally, you use two ComboBox controls to modify the RowFilter and RowStateFilter properties of the DataView object. The RowFilter allows you to filter the list of products within the DataGrid by only including those products within a certain category as shown in Listing 14.3.

Listing 14.3 Controlling Views with Row Filters

 using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data; namespace _10_DataViews {     /// <summary>     /// Summary description for Form1.     /// </summary>     public class Form1 : System.Windows.Forms.Form     {         private System.Windows.Forms.DataGrid dgProducts;         private System.Data.SqlClient.SqlCommand sqlSelectCommand1;         private System.Data.SqlClient.SqlCommand sqlInsertCommand1;         private System.Data.SqlClient.SqlCommand sqlUpdateCommand1;         private System.Data.SqlClient.SqlCommand sqlDeleteCommand1;         private System.Data.SqlClient.SqlConnection sqlConnection1;         private System.Data.SqlClient.SqlDataAdapter sqlDataAdapter1;         private System.Data.SqlClient.SqlCommand sqlSelectCommand2;         private System.Data.SqlClient.SqlCommand sqlInsertCommand2;         private System.Data.SqlClient.SqlCommand sqlUpdateCommand2;         private System.Data.SqlClient.SqlCommand sqlDeleteCommand2;         private System.Data.SqlClient.SqlDataAdapter sqlDataAdapter2;         private _10_DataViews.ProductCategoriesDS productCategoriesDS1;         private System.Windows.Forms.Label label1;         private System.Data.DataView dataView1;         private System.Windows.Forms.ComboBox cbCategories;         private System.Windows.Forms.Label label2;         private System.Windows.Forms.ComboBox cbRowState;         private System.ComponentModel.Container components = null;         public Form1()         {             InitializeComponent();             // fill rowstate combo box items             foreach( string state in Enum.GetNames( typeof(DataViewRowState) ))             {                 cbRowState.Items.Add( state );             }             sqlDataAdapter1.Fill( productCategoriesDS1, "Products" );             sqlDataAdapter2.Fill( productCategoriesDS1, "Categories" );             // sort by prodcut name             dataView1.Sort = "ProductName ASC";         }         private void SetFilters()         {             int curCatID = Convert.ToInt32(cbCategories.SelectedValue);             dataView1.RowFilter = "CategoryID=" + curCatID;             if( cbRowState.SelectedIndex != -1 )                 dataView1.RowStateFilter = (DataViewRowState)                     Enum.Parse( typeof(DataViewRowState),                         cbRowState.SelectedItem.ToString(), true );             dgProducts.DataSource = dataView1;         }         /// <summary>         /// Clean up any resources being used.         /// </summary>         protected override void Dispose( bool disposing )         {             if( disposing )             {                 if (components != null)                 {                     components.Dispose();                 }             }             base.Dispose( disposing );         }         #region Windows Form Designer generated code         // ...         #endregion         /// <summary>         /// The main entry point for the application.         /// </summary>         [STAThread]         static void Main()         {             Application.Run(new Form1());         }         private void cbCDategories_SelectedIndexChanged(             object sender,             System.EventArgs e)         {             SetFilters();         }         private void Form1_Closing(             object sender,             System.ComponentModel.CancelEventArgs e)         {             if( productCategoriesDS1.HasChanges() )             {                 DialogResult result;                 result = MessageBox.Show( this,                     "Would you like to save your changes?",                     "Northwind Products", MessageBoxButtons.YesNoCancel,                     MessageBoxIcon.Question );                 if( result == DialogResult.Cancel )                 {                     e.Cancel = true;                     return;                 }                 else if( result == DialogResult.No )                 {                     return;                 }                 else                 {                     sqlDataAdapter1.Update( productCategoriesDS1 );                 }             }         }         private void cbRowState_SelectedIndexChanged(             object sender,             System.EventArgs e)         {             SetFilters();         }     } } 

Comments

You can probably just as easily perform this technique by using an SqlCommand or SELECT statement provided to the SqlDataAdapter object to perform sorting and filtering. For instance, if you want to create a DataTable within a DataSet that returns all the products sorted by name, you can use the following SELECT statement:

 "SELECT * From Products Order By ProductName ASC" 

The data is returned. However, what if in the same session, you want to view the products in descending order or to sort by a different column. You then have to recreate the DataSet and create another connection and subsequent data transfer to the server. The DataView class was designed to eliminate this type of data-transfer paradigm, allowing you to continue working in the disconnected scenario. Furthermore, in addition to sorting by multiple columns and filtering rows based on the result of an expression, you can also filter the rows based on the current status. If you only want to display the rows that have changed so you can, for instance, generate a change report, you can simply set the RowStateFilter property of the DataView object to ModifiedCurrent . You can then use the methods defined in the DataView class to extract the current data view. The following code demonstrates how to set the RowStateFilter to only return modified rows and how to read the DataView contents to display those rows:

 private void DisplayModifiedRows() {     dataView1.RowStateFilter = DataViewRowState.ModifiedCurrent;     foreach( DataRowView curRow in dataView1 )     {         for( int i = 0; i < dataView1.Table.Columns.Count; i++ )         {             Console.Write( curRow[i].ToString() + " " );         }         Console.WriteLine();     } }