Lesson 05: Working with Disconnected Data - The DataSet and SqlDataAdapter

This lesson explains how to work with disconnected data, using the DataSet and SqlDataAdapter objects. Here are the objectives of this lesson:

• Understand the need for disconnected data.
• Obtain a basic understanding of what a DataSet is for.
• Learn to use a SqlDataAdapter to retrieve and update data.

Introduction

In Lesson 3, we discussed a fully connected mode of operation for interacting with a data source by using the SqlCommand object. In Lesson 4, we learned about how to read data quickly an let go of the connection with the SqlDataReader. This Lesson shows how to accomplish something in-between SqlConnection and SqlDataReader interaction by using the DataSet and SqlDataAdapter objects.
A DataSet is an in-memory data store that can hold numerous tables. DataSets only hold data and do not interact with a data source. It is the SqlDataAdapter that manages connections with the data source and gives us disconnected behavior. The SqlDataAdapter opens a connection only when required and closes it as soon as it has performed its task. For example, the SqlDataAdapter performs the following tasks when filling a DataSet with data:

1. Open connection
2. Retrieve data into DataSet
3. Close connection

and performs the following actions when updating data source with DataSet changes:

1. Open connection
2. Write changes from DataSet to data source
3. Close connection

In between the Fill and Update operations, data source connections are closed and you are free to read and write data with the DataSet as you need. These are the mechanics of working with disconnected data. Because the applications holds on to connections only when necessary, the application becomes more scalable.

A couple scenarios illustrate why you would want to work with disconnected data: people working without network connectivity and making Web sites more scalable. Consider sales people who need customer data as they travel. At the beginning of the day, they'll need to sync up with the main database to have the latest information available. During the day, they'll make modifications to existing customer data, add new customers, and input new orders. This is okay because they have a given region or customer base where other people won't be changing the same records. At the end of the day, the sales person will connect to the network and update changes for overnight processing.
Another scenario is making a Web site more scalable. With a SqlDataReader, you have to go back to the database for records every time you show a page. This requires a new connection for each page load, which will hurt scalability as the number of users increase. One way to relieve this is to use a DataSet that is updated one time and stored in cache. Every request for the page checks the cache and loads the data if it isn't there or just pulls the data out of cache and displays it. This avoids a trip to the database, making your application more efficient.
Exceptions to the scenario above include situations where you need to update data. You then have to make a decision, based on the nature of how the data will be used as to your strategy. Use disconnected data when your information is primarily read only, but consider other alternatives (such as using SqlCommand object for immediate update) when your requirements call for something more dynamic. Also, if the amount of data is so large that holding it in memory is impractical, you will need to use SqlDataReader for read-only data. Really, one could come up with all kinds of exceptions, but the true guiding force should be the requirements of your application which will influence what your design should be.

Creating a DataSet Object

There isn't anything special about instantiating a DataSet. You just create a new instance, just like any other object:
DataSet dsCustomers = new DataSet();
The DataSet constructor doesn't require parameters. However there is one overload that accepts a string for the name of the DataSet, which is used if you were to serialize the data to XML. Since that isn't a requirement for this example, I left it out. Right now, the DataSet is empty and you need a SqlDataAdapter to load it.

Creating A SqlDataAdapter

The SqlDataAdapter holds the SQL commands and connection object for reading and writing data. You initialize it with a SQL select statement and connection object:
SqlDataAdapter daCustomers = new SqlDataAdapter(
    "select CustomerID, CompanyName from Customers", conn);
The code above creates a new SqlDataAdapter, daCustomers. The SQL select statement specifies what data will be read into a DataSet. The connection object, conn, should have already been instantiated, but not opened. It is the SqlDataAdapter's responsibility to open and close the connection during Fill and Update method calls.
As indicated earlier, the SqlDataAdapter contains all of the commands necessary to interact with the data source. The code showed how to specify the select statment, but didn't show the insert, update, and delete statements. These are added to the SqlDataAdapter after it is instantiated.
There are two ways to add insert, update, and delete commands: via SqlDataAdapter properties or with a SqlCommandBuilder. In this lesson, I'm going to show you the easy way of doing it with the SqlCommandBuilder. In a later lesson, I'll show you how to use the SqlDataAdapter properties, which takes more work but will give you more capabilities than what the SqlCommandBuilder does. Here's how to add commands to the SqlDataAdapter with the SqlCommandBuilder:
SqlCommandBuilder cmdBldr = new SqlCommandBuilder(daCustomers);
Notice in the code above that the SqlCommandBuilder is instantiated with a single constructor parameter of the SqlDataAdapter, daCustomers, instance. This tells the SqlCommandBuilder what SqlDataAdapter to add commands to. The SqlCommandBuilder will read the SQL select statement (specified when the SqlDataAdapter was instantiated), infer the insert, update, and delete commands, and assign the new commands to the Insert, Update, and Delete properties of the SqlDataAdapter, respectively.
As I mentioned earlier, the SqlCommandBuilder has limitations. It works when you do a simple select statement on a single table. However, when you need a join of two or mor tables or must do a stored procedure, it won't work. I'll describe a work-around for these scenarios in future lessons.

Filling the DataSet

Once you have a DataSet and SqlDataAdapter instances, you need to fill the DataSet. Here's how to do it, by using the Fill method of the SqlDataAdapter:
daCustomers.Fill(dsCustomers, "Customers");
The Fill method, in the code above, takes two parameters: a DataSet and a table name. The DataSet must be instantiated before trying to fill it with data. The second parameter is the name of the table that will be created in the DataSet. You can name the table anything you want. Its purpose is so you can identify the table with a meaningful name later on. Typically, I'll give it the same name as the database table. However, if the SqlDataAdapter's select command contains a join, you'll need to find another meaningful name.
The Fill method has an overload that accepts one parameter for the DataSet only. In that case, the table created has a default name of "table1" for the first table. The number will be incremented (table2, table3, ..., tableN) for each table added to the DataSet where the table name was not specified in the Fill method.

Using the DataSet

A DataSet will bind with both ASP.NET and Windows forms DataGrids. Here's an example that assigns the DataSet to a Windows forms DataGrid:
dgCustomers.DataSource = dsCustomers;
dgCustomers.DataMember = "Customers";
The first thing we do, in the code above, is assign the DataSet to the DataSource property of the DataGrid. This lets the DataGrid know that it has something to bind to, but you will get a '+' sign in the GUI because the DataSet can hold multiple tables and this would allow you to expand each available table. To specify exactly which table to use, set the DataGrid's DataMember property to the name of the table. In the example, we set the name to Customers, which is the same name used as the second parameter to the SqlDataAdapter Fill method. This is why I like to give the table a name in the Fill method, as it makes subsequent code more readable.

Updating Changes

After modifications are made to the data, you'll want to write the changes back to the database. Refer to previous discussion in the Introduction of this article on update guidance. The following code shows how to use the Update method of the SqlDataAdapter to push modifications back to the database.
daCustomers.Update(dsCustomers, "Customers");
The Update method, above, is called on the SqlDataAdapter instance that originally filled the dsCustomers DataSet. The second parameter to the Update method specifies which table, from the DataSet, to update. The table contains a list of records that have been modified and the Insert, Update, and Delete properties of the SqlDataAdapter contain the SQL statements used to make database modifications.

Putting it All Together

Until now, you've seen the pieces required to implement disconnected data managment. What you really need is to see all this implemented in an application. Listing 1 shows how the code from all the previous sections is used in a working program that has been simplified to enhance the points of this lesson:

Listing 1: Implementing a Disconnected Data Management Strategy

using System;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Windows.Forms;

class DisconnectedDataform : Form
{
 private SqlConnection  conn;
 private SqlDataAdapter daCustomers;

 private DataSet  dsCustomers;
 private DataGrid dgCustomers;

 private const string tableName = "Customers";

 // initialize form with DataGrid and Button
 public DisconnectedDataform()
 {
  // fill dataset
  Initdata();

  // set up datagrid
  dgCustomers = new DataGrid();
  dgCustomers.Location = new Point(5, 5);
  dgCustomers.Size = new Size(  
            this.ClientRectangle.Size.Width - 10,  
            this.ClientRectangle.Height - 50);
  dgCustomers.DataSource = dsCustomers;
  dgCustomers.DataMember = tableName;

  // create update button
  Button btnUpdate = new Button();
  btnUpdate.Text = "Update";
  btnUpdate.Location = new Point(
   this.ClientRectangle.Width/2 - btnUpdate.Width/2,
   this.ClientRectangle.Height - (btnUpdate.Height + 10));
  btnUpdate.Click += new EventHandler(btnUpdateClicked);

  // make sure controls appear on form
  Controls.AddRange(new Control[] { dgCustomers, btnUpdate });
 }

 // set up ADO.NET objects
 public void Initdata()
 {
  // instantiate the connection
  conn = new SqlConnection(
   "Server=(local);DataBase=Northwind;Integrated Security=SSPI");
  
  // 1. instantiate a new DataSet
  dsCustomers = new DataSet();

  // 2. init SqlDataAdapter with select command and connection
  daCustomers = new SqlDataAdapter(
   "select CustomerID, CompanyName from Customers", conn);

  // 3. fill in insert, update, and delete commands
  SqlCommandBuilder cmdBldr = new SqlCommandBuilder(daCustomers);
  
  // 4. fill the dataset
  daCustomers.Fill(dsCustomers, tableName);
 }

 // Update button was clicked
 public void btnUpdateClicked(object sender, EventArgs e)
 {
  // write changes back to DataBase
  daCustomers.Update(dsCustomers, tableName);
 }

 // start the Windows form
 static void Main()
 {
  Application.Run(new DisconnectedDataForm());
 }
 }

The Initdata method in Listing 1 contains the methods necessary to set up the SqlDataAdapter and DataSet. Notice that various data objects are defined at class level so they can be used in multiple methods. The DataGrid's DataSource property is set in the constructor. Whenever a user clicks the Update button, the Update method in the btnUpdateClicked event handler is called, pushing modifications back to the database.

Summary

DataSets hold multiple tables and can be kept in memory and reused. The SqlDataAdapter enables you to fill a DataSet and Update changes back to the database. You don't have to worry about opening and closing the SqlConnection because the SqlDataAdapter does it automatically. A SqlCommandBuilder populates insert, update, and delete commands based on the SqlDataAdapter's select statement. Use the Fill method of the SqlDataAdapter to fill a DataSet with data. Call the SqlDataAdapter's Update method to push changes back to a database.