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

This step-by-step article demonstrates how to use a Web service to receive and to update data from a database by using aDataSet 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.

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 Northwinddatabase. The Northwind database is included with Microsoft SQL Server.

Create the Web Service

1. Start Visual Studio .NET.
2. Follow these steps to create a new Visual C# ASP.NET Web Service project:
   1. On the File menu, point to New, and then click Project.
   2. In the New Project dialog box, click Visual C# Projects under Project Types, and then click ASP.NET Web Serviceunder Templates.
   3. 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.
3. 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.
4. At the top of the Code window, add the following using statement:

   //Use data access objects from the SqlClient namespace.
   using System.Data.SqlClient;
        

5. 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("server=servername;uid=login;
   pwd=password;database=northwind");
              SqlDataAdapter daCust = new SqlDataAdapter("Select * From Customers", con);
              DataSet ds = new DataSet();
              daCust.Fill(ds, "Cust");
              return ds;
          }
   
          [WebMethod]
          public DataSet UpdateCustomers(DataSet ds)
          {
               SqlConnection con = new SqlConnection("server=servername;uid=login;
   pwd=password;database=northwind");
               SqlDataAdapter daCust = new SqlDataAdapter("Select * From Customers", con);
               SqlCommandBuilder cbCust = new SqlCommandBuilder(daCust);
               daCust.Update(ds, "Cust");
               return ds;
           }
        

6. Modify the SqlConnection string to properly connect to the computer that is running SQL Server.

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.
2. On the Service1 Web page, click GetCustomers. A Web page is displayed that includes details about the GetCustomersWeb method.
3. Close the Web pages.

Create the Client Application

1. In Visual Studio .NET, create a new Visual C# Windows Application project. By default, Form1 is added to the project.
2. Add two Button controls and one DataGrid control to Form1. By default, Button1, Button2, and DataGrid1 are added to the project.
3. Change the Text property of Button1 to Load, and then change the Text property of Button2 to Save.
4. On the Project menu, click Add Web Reference. Type the URL for your Web service (for example, typehttp://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.
5. 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 = "Cust";
        

6. Switch to Form view.
7. 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);
           }
        

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.