The C# Station ADO.NET Tutorial
by Joe Mayo, 8/30/04, updated 1/12/09
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:
- Open connection
- Retrieve data into DataSet
- Close connection
and performs the following actions when updating data source with DataSet changes:
- Open connection
- Write changes from DataSet to data source
- 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.
I hope you enjoyed this lesson and welcome you to return to the next one in this
series, Lesson 06: Adding Parameters to Commands.
