Creating and Using a DataView Object

Chia sẻ: Tuan Nghia | Ngày: | Loại File: PDF | Số trang:5

Thêm vào BST

Báo xấu

113
lượt xem 11
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

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

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: 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 /* 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 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