Creating and Using a DataView Object

Creating and Using a DataView Object

In this section, you'll learn how to filter and sort rows with a DataView object. You create a DataView object using one of the following constructors:

 DataView() DataView(DataTable myDataTable) DataView(DataTable myDataTable, string filterExpression, string sortExpression, DataViewRowState rowState) 

where

  • myDataTable specifies the DataTable that your DataView is associated with. Your DataView will read the rows from this DataTable. The Table property of your DataView is set to myDataTable.

  • filterExpression specifies a string containing the expression you want to filter the rows by. The RowFilter property of your DataView is set to filterExpression.

  • sortExpression specifies a string containing the expression you want to sort the rows by. The Sort property of your DataView is set to sortExpression.

  • rowState specifies an additional filter to apply to the rows; rowState filters by the DataRowView-State of the DataViewRow objects in your DataView. The RowStateFilter of your DataView is set to rowState.

Before you create a DataView, you first need a DataTable from which to read rows. The following example creates and populates a DataTable named customersDT that contains rows from the Customers table:

 SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText =   "SELECT CustomerID, CompanyName, Country " +   "FROM Customers"; SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); mySqlDataAdapter.SelectCommand = mySqlCommand; DataSet myDataSet = new DataSet(); mySqlConnection.Open(); mySqlDataAdapter.Fill(myDataSet, "Customers"); mySqlConnection.Close(); DataTable customersDT = myDataSet.Tables["Customers"]; 

Let's say you want to filter the rows in CustomersDT to view just the customers in the UK. Your filter string expression would be

 string filterExpression = "Country = 'UK'"; 

Note 

Notice that UK is placed within single quotes. This is because UK is a string literal.

Also, let's say you want to sort those rows by ascending CustomerID column values and descending CompanyName column values. Therefore, your sort expression would be

 string sortExpression = "CustomerID ASC, CompanyName DESC"; 

Note 

ASC sorts in ascending order. DESC sorts in descending order.

Finally, let's say you wanted to view only the original rows in the DataView; you therefore set your row state filter to DataViewRowState.OriginalRows:

 DataViewRowState rowStateFilter = DataViewRowState.OriginalRows; 

Note 

The default is DataViewRowState.CurrentRows, which includes rows in your DataView for which the DataViewRowState is Unchanged, Added, and ModifiedCurrent.

The following example creates a DataView object named customersDV and passes customersDT, filterExpression, sortExpression, and rowStateFilter to the DataView constructor:

 DataView customersDV =   new DataView(     customersDT, filterExpression, sortExpression, rowStateFilter   ); 

You can also create a DataView and set the Table, RowFilter, Sort, and RowStateFilter properties individually. For example:

 DataView customersDV = new DataView(); customersDV.Table = customersDT; customersDV.RowFilter = filterExpression; customersDV.Sort = sortExpression; customersDV.RowStateFilter = rowStateFilter; 

A DataView stores rows as DataRowView objects, and the rows are read from the DataRow objects stored in the underlying DataTable. The following example uses a foreach loop to display the DataRowView objects in the customersDV DataView:

 foreach (DataRowView myDataRowView in customersDV) {   for (int count = 0; count < customersDV.Table.Columns.Count; count++)   {     Console.WriteLine(myDataRowView[count]);   }     Console.WriteLine(""); } 

Note that myDataRowView[count] returns the value of the column at the numeric position specified by count. For example, myDataRowView[0] returns the value of the CustomerID column. You'll learn more about the DataRowView class later in the section "The DataRowView Class."

Listing 13.1 shows a program that uses the previous code examples.

Listing 13.1: USINGDATAVIEW.CS

start example
 /*   UsingDataView.cs illustrates the use of a DataView object to   filter and sort rows */ using System; using System.Data; using System.Data.SqlClient; class UsingDataView {   public static void Main()   {     SqlConnection mySqlConnection =       new SqlConnection(         "server=localhost;database=Northwind;uid=sa;pwd=sa"       );     SqlCommand mySqlCommand = mySqlConnection.CreateCommand();     mySqlCommand.CommandText =       "SELECT CustomerID, CompanyName, Country " +       "FROM Customers";     SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();     mySqlDataAdapter.SelectCommand = mySqlCommand;     DataSet myDataSet = new DataSet();     mySqlConnection.Open();     mySqlDataAdapter.Fill(myDataSet, "Customers");     mySqlConnection.Close();     DataTable customersDT = myDataSet.Tables["Customers"];     // set up the filter and sort expressions     string filterExpression = "Country = 'UK'";     string sortExpression = "CustomerID ASC, CompanyName DESC";     DataViewRowState rowStateFilter = DataViewRowState.OriginalRows;     // create a DataView object named customersDV     DataView customersDV = new DataView();     customersDV.Table = customersDT;     customersDV.RowFilter = filterExpression;     customersDV.Sort = sortExpression;     customersDV.RowStateFilter = rowStateFilter;     // display the rows in the customersDV DataView object     foreach (DataRowView myDataRowView in customersDV)     {       for (int count = 0; count < customersDV.Table.Columns.Count; count++)       {         Console.WriteLine(myDataRowView[count]);       }       Console.WriteLine("");     }   } } 
end example

Notice that the rows in customersDV are filtered to those for which the Country is UK, and the resulting rows are then sorted by CustomerID. The output from this program is as follows:

 AROUT Around the Horn UK BSBEV B's Beverages UK CONSH Consolidated Holdings UK EASTC Eastern Connection UK ISLAT Island Trading UK NORTS North/South UK SEVES Seven Seas Imports UK 




Mastering C# Database Programming
Mastering the SAP Business Information Warehouse: Leveraging the Business Intelligence Capabilities of SAP NetWeaver
ISBN: 0764596373
EAN: 2147483647
Year: 2003
Pages: 181

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net