The C# Station ADO.NET Tutorial
by Joe Mayo, 8/30/04
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 data base 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 data base 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 data base, 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 data base. 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 data base.
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 data base 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 data base.
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
data base. 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 data base.
I hope you enjoyed this lesson and welcome you to return to the next one in this
series, Lesson 06: Adding Parameters to Commands.
Your feedback and constructive contributions are welcome. Please feel free
to contact me for feedback or comments you may have about this lesson.
Copyright © 2000-2008 C# Station, All Rights Reserved
