Microsoft KB Archive/310143

= How To Update Server Data Through a Web Service by Using ADO.NET and Visual C# .NET =

Article ID: 310143

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft ADO.NET 1.1
 * Microsoft ADO.NET 1.0
 * Microsoft Visual C# .NET 2003 Standard Edition
 * Microsoft Visual C# .NET 2002 Standard Edition

-



This article was previously published under Q310143



For a Microsoft Visual Basic .NET version of this article, see 308056.

For a Microsoft Visual J# .NET version of this article, see 320634.

This article refers to the following Microsoft .NET Framework Class Library namespace:
 * System.Data.SqlClient

IN THIS TASK
SUMMARY
 * Requirements
 * Create the Web Service
 * Test the Web Service
 * Create the Client Application
 * Test the Client Application

REFERENCES



SUMMARY
This step-by-step article demonstrates how to use a Web service to receive and to update data from a database by using a DataSet object. This article also demonstrates how to reference the Web service in a client application and how to display the returned DataSet in a DataGrid control so that you can update that data and send the updates back to the server.

NOTE: You can only use the method in this article for single-table updates.

back to the top

Requirements
The following list outlines the recommended hardware, software, network infrastructure, and service packs that are required:
 * Microsoft Windows 2000 Professional, Windows 2000 Server, Windows 2000 Advanced Server, or Windows NT 4.0 Server
 * Microsoft Visual Studio .NET

This article assumes that you are familiar with the following topics:
 * Visual Studio .NET
 * ADO.NET fundamentals and syntax
 * ASP.NET fundamentals and syntax

The code samples in this article use http://localhost as the Web server. Additionally, the code samples use the Northwind database. The Northwind database is included with Microsoft SQL Server.

back to the top

Create the Web Service
 Start Visual Studio .NET. Follow these steps to create a new Visual C# ASP.NET Web Service project:  On the File menu, point to New, and then click Project. In the New Project dialog box, click Visual C# Projects under Project Types, and then click ASP.NET Web Service under Templates. In the Location box, type the URL for your server and the project name, csUpdateData (for example, http://localhost/csUpdateData). The http://localhost portion of the URL runs the Web service on your local Web server. Click OK.  On the Service1.asmx.cs[Design] tab, right-click the page, and then click View Code to switch to Code view. The Code window for the Web service appears.</li>  At the top of the Code window, add the following using statement: //Use data access objects from the SqlClient namespace. using System.Data.SqlClient; </li>  After the following code public Service1 {           //CODEGEN: This call is required by the ASP.NET Web Services Designer InitializeComponent; } add the following code: [WebMethod] public DataSet GetCustomers {          SqlConnection con = new SqlConnection(&quot;server=servername;uid=login; pwd=password;database=northwind&quot;); SqlDataAdapter daCust = new SqlDataAdapter(&quot;Select * From Customers&quot;, con); DataSet ds = new DataSet; daCust.Fill(ds, &quot;Cust&quot;); return ds; }

[WebMethod] public DataSet UpdateCustomers(DataSet ds) {           SqlConnection con = new SqlConnection(&quot;server=servername;uid=login; pwd=password;database=northwind&quot;); SqlDataAdapter daCust = new SqlDataAdapter(&quot;Select * From Customers&quot;, con); SqlCommandBuilder cbCust = new SqlCommandBuilder(daCust); daCust.Update(ds, &quot;Cust&quot;); return ds; }                   </li> Modify the SqlConnection string to properly connect to the computer that is running SQL Server.</li></ol>

back to the top

Test the Web Service

 * 1) Press F5 to compile and to run the Web service. A Web page is displayed in which you can interact with the Web service from within Microsoft Internet Explorer.

Note that the URL of the returned page is http://localhost/csUpdateData/Service1.asmx.
 * 1) On the Service1 Web page, click GetCustomers. A Web page is displayed that includes details about the GetCustomers Web method.
 * 2) Close the Web pages.

back to the top

Create the Client Application
 In Visual Studio .NET, create a new Visual C# Windows Application project. By default, Form1 is added to the project.</li> Add two Button controls and one DataGrid control to Form1. By default, Button1, Button2, and DataGrid1 are added to the project.</li> Change the Text property of Button1 to Load, and then change the Text property of Button2 to Save .</li> On the Project menu, click Add Web Reference. Type the URL for your Web service (for example, type http://localhost/csUpdateData/Service1.asmx ), press ENTER, and then click Add Reference. The entry for the newly added Web reference appears View menu of Solution Explorer.</li>  In the Visual C# project, open the Code window for Button1. Add the following code to the Button1_Click (Load) event procedure: localhost.Service1 MyService = new localhost.Service1; dataGrid1.DataSource = MyService.GetCustomers; dataGrid1.DataMember = &quot;Cust&quot;; </li> Switch to Form view.</li>  Open the Code window for Button2. Add the following code into the Button2_Click (Save) event procedure: localhost.Service1 MyService = new localhost.Service1; DataSet ds = (DataSet) dataGrid1.DataSource; DataSet dsChanges = ds.GetChanges; if (dsChanges != null) {           ds.Merge(MyService.UpdateCustomers(dsChanges), true); }                   </li></ol>

back to the top

Test the Client Application

 * 1) Press F5 to compile and to run the client application.
 * 2) Notice that initially DataGrid1 is empty. Click Load. Note that DataGrid1 now displays the Customer records.
 * 3) In DataGrid1, modify some of the data, and then click Save.

NOTE: Do not change the key field. If you change the key field, you receive an error message, which states that you are breaking referential integrity on the server.

back to the top

<div class="references_section">