Creating and Using a DataView Object

Creating and Using a DataView Object In this section, youll learn how to filter and sort rows with a DataView object. You create a DataView object using one of the following constructors
Nội dung trích xuất từ tài liệu:
Creating and Using a DataView ObjectCreating and Using a DataView ObjectIn this section, youll learn how to filter and sort rows with a DataView object. Youcreate 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. Thefollowing example creates and populates a DataTable named customersDT that containsrows 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];Lets say you want to filter the rows in CustomersDT to view just the customers in theUK. Your filter string expression would bestring filterExpression = Country = UK;Note Notice that UK is placed within single quotes. This is because UK is a string literal.Also, lets say you want to sort those rows by ascending CustomerID column values anddescending CompanyName column values. Therefore, your sort expression would bestring sortExpression = CustomerID ASC, CompanyName DESC;Note ASC sorts in ascending order. DESC sorts in descending order.Finally, lets say you wanted to view only the original rows in the DataView; youtherefore 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 passescustomersDT, filterExpression, sortExpression, and rowStateFilter to the DataViewconstructor:DataView customersDV = new DataView( customersDT, filterExpression, sortExpression, rowStateFilter );You can also create a DataView and set the Table, RowFilter, Sort, and RowStateFilterproperties 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 theDataRow objects stored in the underlying DataTable. The following example uses aforeach 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 numericposition specified by count. For example, myDataRowView[0] returns the value of theCustomerID column. Youll learn more about the DataRowView class later in the sectionThe DataRowView Class.Listing 13.1 shows a program that uses the previous code examples.Listing 13.1: USINGDATAVIEW.CS/* 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(); } }}Notice that the rows in customersDV are filtered to those for which the Country is UK,and the re ...