Lesson 05: Working with Disconnected Data - The DataSet and SqlDataAdapter(使用断开数据－－DataSet和SqlDataAdapter)

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

第五课：使用断开数据－－ DataSet 和 SqlDataAdapter

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

这节课解释如何使用断开数据，使用 DataSet 和 SqlDataAdapter 对象。这里这节课的目标：

• 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.
• 了解断开数据的需要
• 获得对于DataSet用来做什么的基本了解
• 学习如何使用SqlDataAdapter来找回和更新数据

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.

在第三课中，我们讨论了使用 Sqlcommand 对象的与数据源的完全连接操作模型。在第四课，我们学习了如何通过连接使用 SqlDataReader 快速的读取数据。这节课介绍如何通过使用 DataSet 和 SqlDataAdapter 来实现 SqlConnection 和 SqlDataReader 交互之间的事情。

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:

DataSet 是存储在内存中的数据，它保存了大量的表。 DataSet 只存储数据而并不与数据源发生交互。它通过 SqlDataAdapter 来管理与数据源的连接并给予我们非连接的行为。只有当需要的时候， SqlDataAdapter 打开连接并在完成任务后关闭它。比如，当 Data 填充数据的时候 SqlDataAdapter 执行以下的操作：

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

1．           打开连接

2．           将数据读入到 DataSet 中

3．           关闭连接

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

当更新 DataSet 时更新数据源，它执行以下的操作：

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

1．           打开连接

2．           将 DataSet 中的改变写入数据源

3．           关闭数据源

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.

在 Fill 和 Update 操作中间的时间，数据源的连接是断开的，你能够随便的使用 DataSet 对数据进行读写。这些是使用断开数据的机制。因为只有当需要的时候应用程序才连接，这样应用程序才更加的可扩展。

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.

一对场景描述了为什么你需要断开数据：人们使用网络连接并使 web 站点更加可扩展。考虑销售人员在旅行的时候需要客户数据。在最初的几天，他们将需要与主数据库同步以获得有效的最新信息。在这些天，它们将对已存在的用户数据进行修改，添加年用户，并且添加新的订单。这都是可以的，因为他们有相应的地区或者客户基础，而其它的用户不会改变相同的记录。在最后的几天中，销售人员将连接网络并把前晚上的改变的数据更新。

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.

另一个场景是使 web 站点更加可扩展。使用 SqlDataReader ，你必须在显示每一页的时候返回数据库读取记录。则需要对每一次页面装载重新读取数据库，这将在用户数量增加的时候引起麻烦。消除这种情况的一种办法是使用 DataSet ，它只更新一次并缓存数据。每一个对页面的查询都会检测缓存并显示它。这避免的数据库的连接，使你的应用程序更加有效率。

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.

上面的场景的异常包括你需要更新数据的情况。你必须抉择，基于你的场景中的数据如何被使用。当你的信息主要是读，则使用断开数据，但是当你需要更加动态的调用某些东西的时候，考虑其它的可选项（比如使用 SqlCommand 对象直接更新）。实际上，万事难料，但真正的指导方针应该是应用程序的需求，它将影响你的设计。

Creating a DataSet Object

创建 DataSet 对象

There isn't anything special about instantiating a DataSet.  You just create a new instance, just like any other object:

实例化 DataSet 没有任何特别的内容，你应该创建一个新的实例，就像其它对象一样：

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.

DataSet 构造函数并不需要参数。然而当你将数据序列化为 XML 的时候为了得到 DataSet 的名字却有额外的负担。因为在这个例子中并不需要，我就不提了。现在数据集为空并且你需要 SqlDataAdapter 来装载它。

Creating A SqlDataAdapter

创建 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 使用 SQL 命令和连接对象来读写数据。对它进行初始化使用 SQL 选择语句和连接对象。

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.

上面的代码创建了一个新的 SqlDataAdapter 对象 daCustomers,SQL 的 select 语句指明了将哪些数据读入数据集。连接对象 conn 应该已经被实例化，但不用打开。这是由 SqlDataAdapter 在调用 Fill 和 Update 方法的时候负责打开和关闭连接的。

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.

正如早先指明的， SqlDataAdapter 包含所有必需的与数据源交互的命令。代码显示了如何指定 select 语句，但是并没有显示 insert ， update 和 delete 语句。这些在初始化以后添加到 SqlDataAdapter 中。

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:

这里有两种方式添加 insert ， update 和 delete 命令：通过 SqlDataAdapter 属性或者通过 SqlCommandBuilder. 在这节课，我将介绍使用 SqlCommandBuilder 的简单方式。在接下来的课程中，我将介绍如何使用 SqlDataAdapter 属性，它将需要更多的工作但是会带来比 SqlCommandBuilder 更大的能力。下面是如何使用 SqlCommandBuilder 在 SqlDataAdapter 中添加命令：

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.

注意上面的代码中 SqlCommandBuilder 是使用一个 SqlDataAdapter 对象 daCustomers 作为参数的构造函数实例化的。这说明 SqlCommandBuilder 对哪一个 SalDataAdapter 添加命令。 SqlCommandBuilder 将读取 SQL 的 select 语句（在 SqlDataAdapter 被实例化的时候指明），推断 inser,update 和 delete 命令，并将新的命令分别赋值给 SqlDataAdapter 的 Insert ， Update 和 Delete 属性。

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 more tables or must do a stored procedure, it won't work.  I'll describe a work-around for these scenarios in future lessons.

正如我先前所说， SqlCommandBuilder 有限制性。它能在你对单独的表做简单的 select 语句的时候有效。然而，当需要连接两个以上的表或者必须执行一个存储过程的时候，它不会起作用。我将在以后的课程中描述这些场景的工作区。

Filling the DataSet

填充 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:

当具有一个 DataSet 和 SqlDataAdapter 实例以后，你需要填充数据集。下面是如何实现它，只要使用 SqlDataAdapter 的 Fill 方法：

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.

上面代码中的 Fill 方法具有两个参数： DataSet 对象和表名。 DataSet 必须在填充数据之前被实例化。第二个参数是将要在 DataSet 中创建的表的名字。你能够以你希望的任何名字对表命名。这要求能够用一个有意义的名字来标识表，以便以后使用。通常，我使用与数据库中表相同的名字来命名。然而，如果 SqlDataAdapter 的 select 命令包含多表连接，你将需要另外找一个有意义的名字。

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.

Fill 方法有另外一个重载的方式，它只接受 DataSet 一个参数。在这种情况中，对于第一个表的默认名字是“ Table1”。如果没有在Fill 方法中指定名字，每一个添加到 DataSet 中的表名数字都将会自增（ Table2 ， Table3 ， ….TableN ）。

Using the DataSet

使用 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:

DataSet 将能够与 ASP.NET 和 Windows Forms 的 DataGrid 控件相绑定。下面是将 DataSet 赋值给 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.

上面代码中我们做的第一件事情就是将 DataSet 赋值给 DataGrid 控件的 DataSource 属性。这让 DataGrid 知道它要绑定到那里，但是你将在 GUI 中得到一个 ’+’ 符号，因为 DataSet 能够保存多个表并且它允许你展开每一个有效的表。为了指定真正要使用哪个表，应该将 DataGrid 控件的 DataMember 属性设置为表的名字。在这个例子当中，我们设置为 Customers ，它与 SqlDataAdapter 的 Fill 方法中使用的第二个参数具有相同名字。这就是为什么我喜欢在 Fill 方法中指定表名字的原因，这样在后面的代码中就更具有可读性。

 

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.

在对数据做了修改以后，你将需要将改变写回给数据库。参考在这篇文章开始介绍的关于 update 的讨论，下面的代码展示了如何使用 SqlDataAdapter 的 Update 方法将改变传回数据库。

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.

上面的 Update 方法调用了最初填充 dsCustomers 数据集的 SalDataAdapter 实例。 Update 方法的第二个参数指定了从数据集中的哪个表来更新。这张表中包含一组被修改的记录，并且使用 SqlDataAdapter 的 Insert ， Update 和 Delete 属性包含的 SQL 语句来做数据库的修改。

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:

直到现在，你看到的代码片断需要实现断开数据管理。你真正需要的看看所有这些在一个应用程序如何实现。 Listing1 展示了从前面的代码断如何在程序中运作的，它已经简要的增强了本课的重点：

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.

Listing1 中的 InitData 方法包含需要建立 SqlDataAdapter 和 DataSet 的方法。注意不同数据对象是被定义在 class 级别的，这样它们能够在多个方法中使用， DataGrid 的 DataSource 属性在构造函数中设置。无论用户在何时点击更新按钮，在 btnupdateClicked 事件中的 Update 方法都被调用，将修改后的数据返回给数据库。

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.

DataSet 存有多张表，能够保存在内存中并能够重用。 SqlDataAdapter 使你能够填充 DataSet 并将更新返回给数据库。你不需要担心打开和关闭数据库的连接，因为 SqlDataAdapter 自动完成了。一个 SqlComandBuilder 基于 SqlDataAdapter 的 select 语句产生 insert,update 和 delete 命令。使用 SqlDataAdapter 的 Fill 方法对 DataSet 填充数据集。调用 SqlDataAdapter 的 Update 方法将改变返回给数据库。

I hope you enjoyed this lesson and welcome you to return to the next one in this series, Lesson 06:  Adding Parameters to Commands.

For further questions, you are welcome to participate in the C# Station Discussion Forums.

Your feedback is very important and I appreciate any constructive contributions you have.  Please feel free to contact me for feedback or comments you may have about this lesson.