Using DataSet Objects

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 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.

A variety of syntaxes exists that you can use to retrieve data. Given a DataTable variable named dt that refers to the data from the Customers table, for example, either of these statements would retrieve the value in the first column of the first row of data in the DataTable object:

 dt.Rows[0][0] dt.Rows[0]["CustomerID"]

Strongly Typed DataSet Objects

The sytaxes used for retrieving data in the previous section were all late bound. That is, the .NET Framework doesn't know until runtime that a column name (such as CustomerID ) is valid. ADO.NET also facilitates the creation of strongly typed DataSet objects. In a strongly typed DataSet object, columns actually become properties of the row, enabling you to write an early-bound version of the data-retrieval expression:

 dt.Rows[0].CustomerID

In addition to being faster than the late-bound syntaxes, the early-bound syntax has the added advantage of making column names show up in IntelliSense tips as you type code.

Anytime you use the Generate DataSet link in the Properties window for a SqlDataAdapter object, Visual Studio .NET builds a strongly typed DataSet object. You can also build strongly typed DataSet objects using the XSD designer.

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. Take the following steps to learn how to build a DataSet object that contains multiple DataTable objects:

Open Visual Studio .NET and create a new blank solution named 315C07 at c:\inetpub\ wwwroot \ExamCram . (You might need to change the directory based on your configuration.)
Add a new Visual C# ASP.NET Web Application project at the following location: http://localhost/ExamCram/315C07/Example7_1 .
Use the Properties window to change the pageLayout property of the DOCUMENT object from GridLayout to FlowLayout for the Web form.
Place two DataGrid controls ( dgCustomers and dgOrders ) on the form. Set the DataKeyField property of the dgCustomers control to CustomerID .
Click the Property Builder hyperlink beneath the Properties window for the dgCustomers control. Select the Columns section of the dgCustomers Properties dialog box. In the Available Columns list, expand the node for Button Column. Select the Select item and then click the > button to move this item to the Selected Columns list. Click OK.
Switch to Code view and add the following using directive:
```
 using System.Data.SqlClient; 
```

Attach an event handler to the ItemCommand event of the dgCustomers control and enter the following code in the class definition:

 DataSet ds = new DataSet(); private void Page_Load(object sender, System.EventArgs e) {     // Initialize the DataSet     LoadData();     // And show the data on the user interface     dgCustomers.DataSource = ds;     dgCustomers.DataMember = "Customers";     dgCustomers.DataBind(); } private void dgCustomers_ItemCommand(object source, system.Web.UI.WebControls.DataGridCommandEventArgs e) {     // Create a DataView showing orders for the selected customer     DataView dv = new DataView(ds.Tables["Orders"]);     dv.RowFilter = "CustomerID = '" +         dgCustomers.DataKeys[e.Item.ItemIndex] + "'";     // And show the data on the user interface     dgOrders.DataSource = dv;     dgOrders.DataBind();     // shift the focus to the dgOrders control     RegisterStartupScript("select",     "<script language = 'JavaScript'>dgOrders.focus();</script>"); } private void LoadData() {    // Create a SqlConnection and a DataSet    SqlConnection cnn = new SqlConnection(@"Data Source=(local)\NetSDK;"       + "Initial Catalog=Northwind; Integrated Security=SSPI");     // Add the customers data to the DataSet     SqlCommand cmdCustomers= cnn.CreateCommand();     cmdCustomers.CommandType = CommandType.Text;     cmdCustomers.CommandText = "SELECT * FROM Customers";     SqlDataAdapter daCustomers = new SqlDataAdapter();     daCustomers.SelectCommand = cmdCustomers;     daCustomers.Fill(ds, "Customers");     // Add the Orders data to the DataSet     SqlCommand cmdOrders = cnn.CreateCommand();     cmdOrders.CommandType = CommandType.Text;     cmdOrders.CommandText = "SELECT * FROM Orders";     SqlDataAdapter daOrders = new SqlDataAdapter();     daOrders.SelectCommand = cmdOrders;     daOrders.Fill(ds, "Orders");     // Add Relation     DataRelation relCustOrder = ds.Relations.Add("CustOrder",         ds.Tables["Customers"].Columns["CustomerID"],         ds.Tables["Orders"].Columns["CustomerID"]); }

Set the project as the startup project and run the application. The program loads both database tables into the DataSet object and then displays the customer information on the DataGrid control.
Click the Select hyperlink for one of the rows; this posts the form back to the server. First, the Load event of the page fires, loading the DataSet object and initializing the dgCustomers control. Then the ItemCommand event of the DataGrid fires. In the handler for this event, the code retrieves the key from the selected row and uses this key to build a DataView object containing orders for the selected customer.

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.

Filter expressions are essentially SQL WHERE clauses constructed according to these rules:

Column names containing special characters (such as space) should be enclosed in square brackets.
String constants should be enclosed in single quotation marks.
Date constants should be enclosed in pound signs.
Numeric expressions can be specified in decimal or scientific notation.
Expressions can be created using AND , OR , NOT , parentheses, IN , LIKE , comparison operators, and arithmetic operators.
The + operator is used to concatenate strings.
Either * or % can be used as a wild card to match any number of characters. Wild cards can be used only at the start or end of strings.
Columns in a child table can be referenced with the expression Child.Column . If the table has more than one child table, you use the expression Child(RelationName).Column to choose a particular child table.
The Sum , Avg , Min , Max , Count , StDev , and Var aggregates can be used with child tables.
Supported functions include CONVERT , LEN , ISNULL , IIF , and SUBSTRING .

If you don't specify a sort order in the Select() method, the rows are returned in primary key order or in the order of addition if the table doesn't have a primary key. You can also specify a sort expression that consists of one or more column names and the keyword ASC or DESC to specify an ascending or descending sort. For example, this is a valid sort expression:

 Country ASC, CompanyName DESC

This expression sorts first by country, in ascending order, and then by company name within each country, in descending order.

Finally, you can also select DataRow objects according to their current states by supplying one of the DataViewRowState constants. Table 7.11 lists these constants.

Table 7.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 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 as shown in the following code segment:

 DataView dv = new DataView(ds.Tables["Customers"]); dv.RowFilter = "Country = 'France'"; dv.Sort = "CompanyName ASC"; dgCustomers.DataSource = dv; dgCustomers.DataBind();

By setting the RowFilter , Sort , and RowStateFilter properties of the DataView object, you can control which rows are available in the DataView object and the order in which they are presented. I didn't use the RowStateFilter property in the previous code segment. RowStateFilter allows you to select, for example, only rows that have been changed since the DataTable object was loaded. The RowStateFilter property can be set to any one of the DataViewRowState constants listed previously in Table 7.11.