Using DataSet Objects


Team-Fly

	Developing XML Web Services and Server Components with Visual C#™ .NET and the .NET Framework, Exam Cram™ 2 (Exam 70-320) By Amit Kalani, Priti Kalani
	Table of Contents

	Chapter 2. Consuming and Manipulating DataSets

Before you can do anything with data in a DataSet object, you have to get that data into the DataSet object somehow. In general, you can follow this four-step process to move data from the database to a DataSet object:

Create a SqlConnection object to connect to the database.
Create a SqlCommand object to retrieve the desired data.
Assign the SqlCommand object to the SelectCommand property of a SqlDataAdapter object.
Call the Fill() method of the SqlDataAdapter object to retrieve data from the database and populate the DataSet object with the data.

Here, you don't have to explicitly call the Open() and Close() methods of the SqlConnection object. Instead, the Fill() method makes those calls when it needs the data. Doing this not only cuts down on the amount of code you need to write, but also improves the scalability of your application by keeping the SqlConnection object open for the shortest possible period of time.

Using DataSet Objects with Multiple Tables

By using DataAdapter objects, you can connect a single DataSet object to more than one table in the SQL Server database. You can also define DataRelation objects to represent the relationships between the DataTable objects in the DataSet object, as shown in the following code segment:

 DataSet ds = new DataSet(); //Add a Customers table to the DataSet SqlDataAdapter daCustomers =       new SqlDataAdapter("SELECT * FROM Customers", conn); daCustomers.Fill(ds, "Customers"); // Add the Orders data to the DataSet SqlDataAdapter daOrders =     new SqlDataAdapter("SELECT * FROM Orders", conn); daOrders.Fill(ds, "Orders"); // Add Relation, CustOrders is the name of the relation DataRelation relCustOrder = ds.Relations.Add("CustOrder",       ds.Tables["Customers"].Columns["CustomerID"],       ds.Tables["Orders"].Columns["CustomerID"]);

Finding and Sorting Data in DataSet Objects

Using the Select() method of the DataTable object is a convenient way to find particular DataRow objects in the DataTable object. For example, the following code returns all customers in Brazil:

 DataRow[] adr = ds.Tables["Customers"].Select("Country = 'Brazil'");

The Select() method of the DataTable object constructs an array of DataRow objects based on up to three factors: a filter expression, sort expression, and state constant.

A filter expression is similar to the expression used with the WHERE clause of a SQL statement. The sort expression consists of one or more column names and the keywords ASC or DESC to specify an ascending or descending sort. For example, this expression sorts first by country, in ascending order, and then by company name within each country, in descending order:

 Country ASC, CompanyName DESC

You can also select DataRow objects according to their current states by supplying one of the DataViewRowState constants. Table 2.11 lists these constants.

Table 2.11. DataViewRowState Constants
Constant	Meaning
Added	Specifies new rows that have not yet been committed
CurrentRows	Specifies all current rows, whether they are unchanged, modified, or new
Deleted	Specifies deleted rows
ModifiedCurrent	Specifies modified rows
ModifiedOriginal	Specifies original data from modified rows
None	Specifies no rows
OriginalRows	Specifies original data, including rows that have been modified or deleted
Unchanged	Specifies rows that have not been changed

You can quickly create an array that holds all the content of a DataTable object by calling the Select() method with no parameters, like this:

 DataRow[] adr = dt.Select();

You can also sort and filter data using a DataView object. The DataView object has the same structure of rows and columns as a DataTable object, but it also lets you specify sorting and filtering options. Typically, you create a DataView object by starting with a DataTable object and specifying options to include a subset of the rows in the DataTable object. Follow these steps to learn how to use a DataView object to sort and filter data:

Add a new Visual C# Web Application project (Example2_2) to the current solution.
Place a Button control (btnGetCustomers) and a DataGrid control (dgCustomers) on the form.
Switch to code view and add the following using directive:
```
 using System.Data.SqlClient; 
```

Double-click the Button control and enter this code to handle its Click event:

 private void btnGetCustomers_Click(object sender, System.EventArgs e) {    // Create a SqlConnection    SqlConnection cnn = new SqlConnection(      @"Data Source=(local)\NetSDK;"      + "Initial Catalog=Northwind;Integrated Security=SSPI");    // Set up the DataAdapter and fill the DataSet    SqlDataAdapter da = new SqlDataAdapter              ("SELECT * FROM Customers", cnn);    DataSet ds = new DataSet();    da.Fill(ds, "Customers");    // Create a DataView based on the Customers DataTable    DataView dv = new DataView(ds.Tables["Customers"]);    dv.RowFilter = "Country = 'France'";    dv.Sort = "CompanyName ASC";    dgCustomers.DataSource = dv;    dgCustomers.DataBind(); }

Set the project as the startup project, run the application, and click the button. The DataGrid control displays only the customers from France, sorted in ascending order by the CompanyName column.

The DataView.ApplyDefaultSort property is used to automatically create a sort order, in ascending order, based on the primary key of the table. This property applies only when the table has a primary key defined and the DataView.Sort property is null or an empty string.


Team-Fly

Top

Using DataSet Objects