How To Update Server Data Through a Web Service by Using ADO.NET and Visual Basic .NET (308056)

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

1. Start Visual Studio .NET.
2. Follow these steps to create a new Visual Basic 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 Basic Projects under Project Types, and then click ASP.NET Web Service under Templates.
   3. In the Location box, type the URL for your server and the project name, vbUpdateData (for example, http://localhost/vbUpdateData). The http://localhost portion of the URL runs the Web service on your local Web server. Click OK.
3. On the Service1.asmx.vb[Design] tab, switch to Code view. The Code window for the Web service appears.
4. At the top of the Code window, add the following Imports statement:

   'Use data access objects from the SqlClient namespace.
   Imports System.Data.SqlClient
   					

5. After the following code

   Public Class Service1
           Inherits System.Web.Services.WebService
   						

   add the following code:

   <WebMethod()> Public Function GetCustomers() As DataSet
   'Modify this Connection string to use your SQL Server and log on.
           Dim con As New SqlConnection("Server=witster;uid=sa;pwd=Password1;database=northwind")
           Dim daCust As New SqlDataAdapter("Select * From Customers", con)
           Dim ds As New DataSet()
           daCust.Fill(ds, "Cust")
           Return ds
       End Function
   
       <WebMethod()> Public Function UpdateCustomers(ByVal ds As DataSet) As DataSet
           Dim con As New SqlConnection("Server=witster;uid=sa;pwd=Password1;database=northwind")
           Dim daCust As New SqlDataAdapter("Select * From Customers", con)
           Dim cbCust As New SqlCommandBuilder(daCust)
           daCust.Update(ds, "Cust")
           Return ds
       End Function
   					

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

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

back to the top

Create the Client Application

1. In Visual Studio .NET, create a new Visual Basic 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 Name property of Button1 to Load, and then change the Name property of Button2 to Save.
4. On the Project menu, click Add Web Reference. Type the URL for your Web service (in this case, type http://localhost/vbUpdateData/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 Basic project, open the Code window for Button1. Add the following code into the Button1_Click (Load) event procedure:

     
           Dim MyService As 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:

           Dim MyService as New localhost.Service1()
           Dim ds as DataSet=DataGrid1.DataSource
           Dim dsChanges as DataSet=ds.GetChanges()
           If Not (dsChanges is Nothing) Then
                ds.Merge(MyService.UpdateCustomers(dsChanges), True)
           End If
   					

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 that states that you are breaking referential integrity on the server.

back to the top