Creating and Using Datasets

Creating and Using Datasets

The DataSet object lies at the heart of ADO.NET. Datasets are roughly analogous to recordsets in earlier versions of ADO. However, the dataset greatly extends and expands upon the concept of the recordset. Datasets are used to maintain local copies of the data, completely disconnected from the database.

To create an instance of the DataSet object, call the DataSet constructor, as shown in the following code. You can use the base constructor, which takes no parameters. You can also call the overloaded version of this constructor, which takes the name of the dataset as its parameter.

DataSet ds = new DataSet();
DataSet ds = new DataSet("myDataSet");

It’s the job of the .NET data providers to fill the DataSet object with the appropriate data and to provide the interface to the database for that data. It’s the function of the DataSet object to hold that data locally while the application is working on it and to maintain the data, including all changes, additions, and deletions. Any changes are then sent to the database through the DataAdapter object when its Update method is called. DataAdapter is covered in more detail later in this chapter, in the section “Using a DataAdapter Object to Populate a Dataset.”

Data in the DataSet object is organized in a set of tables. These tables are built by the DataTable object. Using these tables, the DataSet object can hold a number of different sets of data, which might or might not be related. Each DataTable object consists of DataRow and DataColumn objects that hold the data.

After you’ve filled the dataset with your data, you’ll need to parse through the data, retrieving the data you want. You can easily loop through the rows and columns in the dataset, as shown here:

foreach(DataRow row in ds.Tables[0].Rows)
    foreach(DataColumn col in row.Table.Columns)
        this.textBox1.Text = this.textBox1.Text 
            + row[col].ToString() + "\t";
    this.textBox1.Text = this.textBox1.Text + "\n";

As you parse through the data, you might need to make changes to it. You can access the data values through the DataRow and DataColumn objects, assigning new values as necessary, as shown here:

foreach(DataRow row in ds.Tables[0].Rows)
    row[1] = "NewValue";
Adding Records

You can add new records to the dataset by creating a DataRow object using the DataTable object’s NewRow method, as shown in the following code. This method will create an empty row with the same structure as other rows in the table. Values can be assigned to each of the columns in the row, and the newly created row can then be added to the Rows collection of the DataTable object.

DataRow row = ds.Tables[0].NewRow();
row["au_id"] = "123-45-6789";
row["au_lname"] = "MyLastName";
row["au_fname"] = "MyFirstName";
Detecting “Dirty” Data

At times, you might need to determine whether any changes have been made to the current dataset that haven’t been accepted or committed to the dataset. These changes can include data that’s been modified, added, or deleted from the dataset that hasn’t been accepted into the dataset. You can detect these changes by using the HasChanges method, which returns true if changes have been made but not accepted. You can then retrieve the DataRow objects that have changes by calling the GetChanges method, as shown here:

private void UpdateDataSet (DataSet ds)
        DataSet dirtyDS = ds.GetChanges();
        //Take action.



Accepting Changes

Whenever you make changes to the dataset, the data is flagged as having been changed. The data changes can be retrieved and manipulated fairly easily, as you’ve seen. When you’re sure that the changes made are correct and can be accepted into the current dataset, you can call the AcceptChanges method of the DataSet object, as shown here:


The DataTable and DataRow objects also have the AcceptChanges method, which allows you to commit all the changes made to the DataTable and DataRow objects. This is useful when you want to accept just the changes in a particular table or just a specific row of a table.

Accepting the changes to the dataset doesn’t save those changes back to the database. Because the dataset maintains the data locally, changes you make to the data aren’t automatically saved to the database. To save the data in the data store, you call the Update method of the DataAdapter object.

Binding to Data Components

With the new .NET components, you can easily bind many controls in both Windows Forms and Web Forms to a dataset so that the applications will show the contents of the current dataset in those controls.

The DataGrid control is an example of a control that can show the contents of multiple DataTable objects and multiple data rows in each table. In the grid, you can assign the dataset to which the grid will be bound to the DataSource property of the DataGrid control, as shown in the following code. You can then assign a DataTable object within that dataset to the DataMember property of the DataGrid control. If you don’t provide a value for the data source, the grid will display each of the DataTable objects in the dataset and allow the user to select the desired table.

dataGrid1.DataSource = ds;
dataGrid1.DataMember = "authors";

The other controls that are derived from the System.Windows.Forms.Control class can also be bound to a dataset. In these cases, you add the data binding to the DataBindings collection of the control, as shown here:

textBox1.DataBindings.Add(new System.Windows.Forms.Binding("Text", 
    ds, "authors.au_lname"));

The Binding class referenced here is used to bind a property of a Windows Forms control to a field in a dataset. To perform a similar task in building an ASP.NET application, you can use the System.Web.UI.DataBinding class. Fortunately, for the standard controls, these tasks are taken care of automatically.

Persisting Data

When you’re working with the dataset, you can save that data locally to an XML file so that the information can be persisted beyond the current session. This technique might be used if you have users who will retrieve data from the database, go off line, and then work with the database while traveling.

You save the data to an XML file using the WriteXML method of the DataSet object and specifying the file to which the data will be saved, as shown here:


When you restart the session, you can read that data back into the dataset using the ReadXML method.


Part III: Programming Windows Forms