本分步指南介绍如何从 Web 服务检索
数据集 (DataSet) 对象,如何将 DiffGram 更新发送到 Web 服务,以及如何将更新的 DiffGram 合并回客户端
数据集。该过程包括下列步骤:
1. | 客户端应用程序从 Web 服务中检索一个数据集对象,该数据集有两个具有“父-子”关系的数据表对象。父数据表将“标识/自动编号”列作为主键。 |
2. | 在客户端应用程序中,用户可以添加、删除、修改父记录和子记录。新的父记录收到一个本地生成的临时主键值。 |
3. | 客户端应用程序将这些更改作为 DiffGram 发送回 Web 服务。 |
4. | Web 服务更新数据库,为新的父记录检索实际主键值,然后将更改的键值传播给子记录。 |
5. | 客户端应用程序收到来自 Web 服务的更新值,然后将更新值合并回本地数据集。 |
有关如何使用 Web 服务更新单个表的其他信息,请单击下面的文章号,查看 Microsoft 知识库中相应的文章:
310143
(http://support.microsoft.com/kb/310143/EN-US/) HOW TO:Update Server Data Through a Web Service by Using ADO.NET and Visual C# .NET
要求
<script type="text/javascript">loadTOCNode(2, 'summary');</script>下面的列表概括了推荐的硬件、软件、网络结构以及所需的 Service Pack:
• | Microsoft Windows 2000 Professional、Windows 2000 Server、Windows 2000 Advanced Server 或 Windows NT 4.0 Server |
• | Microsoft Visual Studio .NET |
本文假定您熟悉下列主题:
• | Visual Studio .NET |
• | ADO.NET 基础知识和语法 |
• | ADO.NET 基础知识和语法 |
本文中的代码示例将 http://localhost 用作 Web 服务器。另外,这些代码示例将使用
Northwind 数据库。
Northwind 数据库包括在 Microsoft SQL Server 中。
创建 Web 服务
<script type="text/javascript">loadTOCNode(2, 'summary');</script>
1. | 若要创建新的 Visual C# ASP.NET Web Service 项目,请按照下列步骤操作:
a. | 启动 Visual Studio .NET。 | b. | 在文件菜单上,指向新建,然后单击项目。 | c. | 单击项目类型下的 Visual C# 项目,然后单击模板下的 ASP.NET Web Service。 | d. | 在位置框中,默认位置显示为 http://localhost/WebService1。键入您的服务器的 URL(例如,http://localhost 在您的本地 Web 服务器上运行 Web 服务)。将 WebService1替换为 CSharpUpdateData。在位置框中的 URL 应该显示如下:
http://localhost/CSharpUpdateData
| e. | 若要关闭新建项目对话框,请单击确定。 | |
2. | 在 Service1.asmx.cs[Design] 页上,切换到“代码”视图。您会注意到该 Web 服务的“代码”窗口将出现。 |
3. | 在“代码”窗口的顶部,添加下面的 using 语句: using System.Data;
using System.Web.Services;
using System.Data.SqlClient;
|
4. | 将下面的内容添加到类 Service1 的实现中: [WebMethod]
public DataSet GetData()
{
SqlConnection conn = new SqlConnection ("server=vcdb02;uid=sa;pwd=ricka;database=northwind");
//Pull back the recent orders for the parent rows.
SqlDataAdapter daOrder = new SqlDataAdapter("SELECT * FROM Orders WHERE OrderDate >= '05/01/1998'",conn);
//Get only the appropriate child rows for the parent rows.
SqlDataAdapter daDetails = new SqlDataAdapter("SELECT * FROM [Order Details] WHERE OrderID in ( SELECT OrderID FROM Orders WHERE OrderDate >= '05/01/1998')",conn);
DataSet ds = new DataSet();
try
{
//Fill DataSet, and then set DataRelation to move through the DataGrid.
conn.Open();
daOrder.FillSchema(ds,SchemaType.Mapped,"Orders");
daOrder.Fill(ds,"Orders");
daDetails.FillSchema(ds,SchemaType.Mapped,"Details");
daDetails.Fill(ds,"Details");
ds.Relations.Add("OrdDetail", ds.Tables["Orders"].Columns["OrderID"], ds.Tables["Details"].Columns["OrderID"]);
DataColumn dc = ds.Tables["Orders"].Columns["OrderID"];
dc.AutoIncrement = true;
dc.AutoIncrementSeed = -1;
dc.AutoIncrementStep = -1;
}
catch(SqlException ex)
{
Console.Write (ex.Message.ToString ());
Console.Write(ex.InnerException.ToString ());
}
return ds;
}
[WebMethod]
public DataSet UpdateData(DataSet ds)
{
SqlConnection conn = new SqlConnection ("server=vcdb02;uid=sa;pwd=ricka;database=northwind");
//Pull back the recent orders for the parent rows.
SqlDataAdapter daOrders = new SqlDataAdapter("SELECT * FROM Orders WHERE OrderDate >= '05/01/1998'",conn);
//Get only the appropriate child rows for the parent rows.
SqlDataAdapter daDetails = new SqlDataAdapter("SELECT * FROM [Order Details] WHERE OrderID in ( SELECT OrderID FROM Orders WHERE OrderDate >= '05/01/1998')",conn);
try
{
conn.Open();
// Get commands for the Orders table.
// Reselect record after insert to get new Identity value.
// You must get the schema, which you did in GetData(), before you get commands;
// otherwise, the Command builder tries to insert new rows, based
// on the Identity column.
SqlCommandBuilder cb = new SqlCommandBuilder(daOrders);
daOrders.DeleteCommand = cb.GetDeleteCommand();
daOrders.UpdateCommand = cb.GetUpdateCommand();
daOrders.InsertCommand = cb.GetInsertCommand();
daOrders.InsertCommand.CommandText = String.Concat(daOrders.InsertCommand.CommandText, "; Select * From Orders Where OrderID = @@IDENTITY");
//UpdateRowSource tells the DataAdapter that there will be a re-selected record.
daOrders.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
//cb = null;
// Get commands for the Order Details table.
// Must set the QuotePrefix and QuoteSuffix;
// otherwise, the CommandBuilder does not put brackets ([])
// around the table name.
SqlCommandBuilder cb1 = new SqlCommandBuilder(daDetails);
cb1.QuotePrefix = "[";
cb1.QuoteSuffix = "]";
daDetails.DeleteCommand = cb1.GetDeleteCommand();
daDetails.InsertCommand = cb1.GetInsertCommand();
daDetails.UpdateCommand = cb1.GetUpdateCommand();
// Create a new DataAdapter based on the original one to prevent the
// CommandBuilder from modifying the SQL statements,
// specifically the custom InsertCommand.
// You do not need this if you roll your own commands and parameters
// or if you use the Visual Tools to do it.
SqlDataAdapter daOrd2 = new SqlDataAdapter();
daOrd2.DeleteCommand = daOrders.DeleteCommand;
daOrd2.InsertCommand = daOrders.InsertCommand;
daOrd2.UpdateCommand = daOrders.UpdateCommand;
// Use a delegate to prevent AcceptChanges from occurring on Deletes and Inserts.
// This is for a limitation of the DataAdapter; see Q313540.
daOrd2.RowUpdated += new SqlRowUpdatedEventHandler(OnOrd1RowUpdated);
daDetails.RowUpdated += new SqlRowUpdatedEventHandler(OnDetailsRowUpdated);
daDetails.Update(GetDeletedRows(ds.Tables["Details"]));
daOrd2.Update(GetDeletedRows(ds.Tables["Orders"]));
DataRow [] dsArray = ds.Tables["Orders"].Select("", "", DataViewRowState.ModifiedCurrent);
daOrd2.Update(ds.Tables["Orders"].Select("", "", DataViewRowState.ModifiedCurrent));
daDetails.Update(ds.Tables["Details"].Select("", "", DataViewRowState.ModifiedCurrent));
daOrd2.Update(ds.Tables["Orders"].Select("", "", DataViewRowState.Added));
ds.EnforceConstraints = false;
daDetails.Update(ds.Tables["Details"].Select("","", DataViewRowState.Added));
ds.EnforceConstraints = true;
conn.Close();
}
catch(SqlException ex)
{
Console.Write (ex.Message.ToString ());
Console.Write(ex.InnerException.ToString ());
}
return ds;
}
protected static void OnOrd1RowUpdated(object sender, SqlRowUpdatedEventArgs args)
{
if(args.StatementType == StatementType.Insert || args.StatementType == StatementType.Delete )
args.Status = UpdateStatus.SkipCurrentRow;
}
protected static void OnDetailsRowUpdated(object sender, SqlRowUpdatedEventArgs args)
{
if(args.StatementType == StatementType.Insert )
{
// Do not allow the AcceptChanges to occur on this row.
args.Status = UpdateStatus.SkipCurrentRow;
// Get the current, actual primary key value so that you can plug it back
// in after you get the correct original value that was generated for the child row.
int currentkey = (int)args.Row["OrderID"];
//GetParentRow("OrdDetail")("OrderID", DataRowVersion.Current)
// This is where you get a correct original value key that is stored to the child row.
// You pull the original, pseudo key value from the parent, plug it in as the child row's primary key
// field, and then accept changes on it. Specifically, this is why you turned off EnforceConstraints.
args.Row["OrderID"] = args.Row.GetParentRow("OrdDetail",DataRowVersion.Original)["OrderID"];
args.Row.AcceptChanges();
// Store the actual primary key value in the foreign key column of the child row.
args.Row["OrderID"] = currentkey;
}
if(args.StatementType == StatementType.Delete )
args.Status = UpdateStatus.SkipCurrentRow;
}
private DataRow [] GetDeletedRows(DataTable dt)
{
DataRow [] dr ;
if(dt == null)
return null;
dr = dt.Select("","",DataViewRowState.Deleted );
if(dr.Length ==0 || dr[0] != null)
return dr;
// Workaround:
// With a remoted DataSet, Select returns the array elements
// that are filled with Nothing/null instead of DataRow objects.
for(int i=0; i < (int)dt.Rows.Count; i++)
{
if(dt.Rows[i].RowState ==DataRowState.Deleted )
dr[i]=dt.Rows[i];
}
return dr;
}
|
5. | 修改 SqlConnection 字符串以连接到运行 SQL Server 的服务器。 |
测试 Web 服务
<script type="text/javascript">loadTOCNode(2, 'summary');</script>
1. | 按 F5 键以编译并运行 Web 服务。您会注意到返回一个 URL 为 http://localhost/CSharpUpdateData/Service1.asmx 的 Web 页。在此 Web 页中,您可以通过 Microsoft Internet Explorer 与 Web 服务进行交互。 |
2. | 在 Service1.asmx Web 页上,单击 GetData。注意,将返回一个 Web 页,此页显示关于 GetCustomers Web 方法的详细信息。 |
3. | 关闭 Web 页。 |
创建客户端应用程序
<script type="text/javascript">loadTOCNode(2, 'summary');</script>
1. | 若要创建新的 Visual C# Windows 应用程序项目,请按照下列步骤操作:
a. | 在 Visual Studio .NET 中的文件菜单中,指向新建,然后单击项目。 | b. | 在新建项目对话框中,单击项目类型下的 Visual C# 项目,然后单击模板下的 Windows 应用程序。默认情况下,Form1 将添加到该项目中。 | |
2. | 将按钮控件和 DataGrid 控件从工具箱拖到 Form1。默认情况下,这些控件分别被命名为 Button1 和 DataGrid1 。 |
3. | 将 Button1 的 Name 属性更改为 btnSave,然后将 Button1 的 Text 属性更改为 Save。 |
4. | 在项目菜单上,单击添加 Web 引用。键入您的 Web 服务的 URL(本例中,键入 http://localhost/CSharpUpdateData/Service1.asmx),按 ENTER 键,然后单击添加引用。注意,此 Web 引用项将出现在“解决方案资源管理器”的“查看”菜单上。 |
5. | 将下面的代码添加到 Form1 类上以声明数据集的窗体级成员: private System.Data.DataSet ds;
|
6. | 双击该窗体以切换到“代码”视图,您就会注意到 Visual Studio .NET 创建了 Form1_Load 方法。将下列代码添加到 Form1_Load 方法: localhost.Service1 sv = new localhost.Service1();
ds = sv.GetData ();
dataGrid1.DataSource = ds;
dataGrid1.DataMember = "Orders";
|
7. | 切换到“窗体”视图。 |
8. | 打开保存按钮的“代码”窗口,然后将下面的代码添加到 BtnSave_Click (Save) 事件过程中: localhost.Service1 sv = new localhost.Service1();
DataSet MyChangedRows;
dataGrid1.DataMember = "";
dataGrid1.DataSource = null;
//Pull out only what you must send over the wire.
MyChangedRows = ds.GetChanges();
MyChangedRows = sv.UpdateData(MyChangedRows);
//You must accept changes on the DataSet because of a known problem. See Q313540.
ds.AcceptChanges();
ds.EnforceConstraints = false;
//Merge in the parent rows first and then the child rows.
ds.Merge(MyChangedRows.Tables["Orders"], false, MissingSchemaAction.Ignore);
ds.Merge(MyChangedRows.Tables["details"], false, MissingSchemaAction.Ignore);
//Accept changes that you have made to the DataSet.
ds.AcceptChanges();
//Turn on the integrity constraints that you turned off earlier.
ds.EnforceConstraints = true;
dataGrid1.DataSource = ds;
dataGrid1.DataMember = "Orders";
|
测试客户端应用程序
<script type="text/javascript">loadTOCNode(2, 'summary');</script>
1. | 按 F5 键以编译并运行该客户端应用程序。 |
2. | 在 DataGrid1 中,修改一些数据,然后单击保存。
备注:不要更改关键字段。如果更改了关键字段,将收到错误消息,表明您破坏了服务器上的引用完整性。 |
3. | 向现有的父行添加子行。注意,该子行将自动接收正确的外键值。 |
4. | 对新的父行和新的子行添加下列值,然后单击保存。注意看下列键值:
• | Orders.CustomerID=RATTC | • | Orders.EmployeeID=1 | • | Orders.OrderDate=2/2/2002 | • | Details.ProductID=1 | • | Details.UnitPrice=18 | • | Details.Quantity=1 | • | Details.Discount=0 | |
有关其他信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
310143
(http://support.microsoft.com/kb/310143/EN-US/) HOW TO:Update Server Data Through a Web Service by Using ADO.NET and Visual C# .NET
313483
(http://support.microsoft.com/kb/313483/EN-US/) INFO:Roadmap for ADO.NET DataAdapter Objects