Microsoft KB Archive/308054

From BetaArchive Wiki

Article ID: 308054

Article Last Modified on 3/22/2006


APPLIES TO

  • Microsoft ADO.NET 1.1
  • Microsoft .NET Framework 1.1 Service Pack 1
  • Microsoft Visual Basic .NET 2003 Standard Edition
  • Microsoft Visual Basic .NET 2002 Standard Edition


This article was previously published under Q308054

For a Microsoft Visual C# .NET version of this article, see 308495.

For a Microsoft JScript .NET version of this article, see 308497.


This article refers to the following Microsoft .NET Framework Class Library namespace:

  • System.Data.SqlClient

IN THIS TASK

Summary

References

SUMMARY

This step-by-step article demonstrates how to create and test a Web service that returns a DataSet object to a client. This article also demonstrates how to reference the Web service in a client application and display the returned DataSet in a DataGrid control.

The sample code in this article:

  • Permits the user to select a subset of the records by typing a character string into a text box on the form.
  • Uses the String.Replace method to demonstrate proper escaping of literal SQL statement parameters.
  • Demonstrates how to pass parameters into the Web service, and also how to retrieve data.
  • Demonstrates how to link parent and child records in a single DataSet, and also how to bind to the grid to allow for hierarchical navigation.

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

Create the Web service

  1. Start Microsoft Visual Studio .NET.
  2. On the File menu, point to New, and then click Project.
  3. In the New Project dialog box, click Visual Basic Projects under Project Types, and then click ASP.NET Web Service under Templates.
  4. In the Name text box, type vbCustomer. In the Location text box, type the URL to your server. If you are using the local server, type http://localhost. Click OK.
  5. Switch to Code view.

  6. Add the following code to the top of the Code window:

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

  7. Locate the following code:

    Public Class Service1
        Inherits System.Web.Services.WebService

    After this code, add the following code:

    <WebMethod()> Public Function GetCustOrders(ByVal IDMask As String) As DataSet
'IDMask is the Customer ID that the client submits.

'Replace single quotation marks with two single quotation marks
'so that all single quotation marks in the CustomerID are parsed correctly.
IDMask = IDMask.Replace("'", "''")

'Modify this connection string to use your SQL Server and log on information.
Dim con As New SqlConnection("Server=server;uid=login;pwd=password;database=northwind")

'Open the Customers table to serve as the parent table.
Dim daCust As New SqlDataAdapter("Select * From Customers Where CustomerID Like '%" _
                                 & IDMask & "%'", con)

'Open the Orders table to serve as the child table.
Dim daOrders As New SqlDataAdapter("Select * From Orders Where CustomerID Like '%" _
                                   & IDMask & "%'", con)

'Create a client-side DataSet to hold the Customers and Orders tables.
Dim ds As New DataSet()

'Explicitly open the connection to allow explicit closing.
con.Open()

'Fill the DataSet with the Customers table and the Orders table.
daCust.Fill(ds, "Cust")
daOrders.Fill(ds, "Orders")

'Explicitly close the connection - do not wait for garbage collection.
con.Close()

'Relate Customers to Orders.
ds.Relations.Add("CustOrd", ds.Tables!Cust.Columns!CustomerID, _
                            ds.Tables!Orders.Columns!CustomerID)

'The relationship is Orders nested in Customers.
ds.Relations(0).Nested = True

'Return the DataSet to the client.
Return ds

End Function
  8. Modify the SqlConnection string as appropriate for your environment.

Test the Web service

  1. Press the F5 key to compile and run the Web Service. A Web page is returned that permits you to interact with the Web Service from Microsoft Internet Explorer.


Note that the URL of the returned page is http://localhost/vbCustomer/Service1.asmx.

  1. On the Service1 Web Page, click GetCustOrders. A Web page is returned that displays details about the GetCustOrders Web method.


Note that the URL of the returned page is http://localhost/vbCustomer/Service1.asmx?op=GetCustOrders.

  1. In the Text section of the GetCustOrders page, type AL in the Value text box next to the IDMask parameter.
  2. Click Invoke. A Web page is returned that displays the results of the GetCustOrders Web method as a hierarchical XML document.


Note that the URL of the returned page is http://localhost/vbCustomer/Service1.asmx/GetCustOrders?IDMask=AL.

  1. Close the displayed Web Pages.

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 one TextBox control, one Button control, and one DataGrid control to Form1. TextBox1, Button1, and DataGrid1 are added to the project by default.
  3. On the Project menu, click Add Web Reference. Follow these steps in the Add Web Reference dialog box:
    1. Click Web References on Local Server. A Web page that lists the available services is displayed. If your Web service does not appear in this list, see the Troubleshooting section of this article.
    2. In the Add Web Reference dialog box, click http://localhost/vbCustomer/Service1.asmx. A page is displayed that lists links to the service's contract and documentation.
    3. Click Add Reference. To view the added Web references, click Solution Explorer on the View menu.

  4. In the Visual Basic project, double-click Button1 to open its Code window, and paste the following code into the Button1_Click event procedure:

            'Use the Web Service that your Web server provides.
        Dim MyService As New localhost.Service1()
        'Invoke the public WebMethod that returns a DataSet.
        'Bind the DataGrid to the returned DataSet.
        DataGrid1.DataSource = MyService.GetCustOrders(TextBox1.Text)
        DataGrid1.DataMember = "Cust"

Test the client application

  1. Press the F5 key to compile and run the client application.
  2. Type AL in the text box.
  3. Click Button1. Notice that DataGrid1 displays the Customer records that contain "AL" in the CustomerID field.
  4. In DataGrid1, click the plus sign (+) next to ALFKI to display the CustOrd relation that you defined in the Web method.
  5. Click CustOrd to display the Orders that are related to the Customers by CustomerID ALKFI.

Troubleshooting

When you try to add the Web reference to the client application, you may not see your new Web service in the list of Available References.

To work around this problem, manually type the URL for your Web service. For example, in the URL box at the top of the Add Web Reference dialog box, add the following URL:

http://localhost/vbCustomer/Service1.asmx


REFERENCES

For more information about Web services, see the "Creating and Accessing Web Services Walkthroughs" topic in the Visual Studio .NET Online Help documentation.

Keywords: kbhowtomaster kbsqlclient kbsystemdata kbdatabinding KB308054



Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.


Retrieved from ‘https://www.betaarchive.com/wiki/index.php?title=Microsoft_KB_Archive/308054&oldid=380104’
the Creative Commons 3.0 ShareAlike (except the Microsoft KB Archive).
Powered by MediaWiki