How To Use a Web Service as a Data Source for a Client Application in Visual C# .NET

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 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 C# Projects under Project Types, and then click ASP.NET Web Service under Templates.
4. In the Name text box, type csCustomer. In the Location text box, type the URL to your server. If you are using the local server, type http://localhost. Click OK.
5. On the View menu, click Code to switch to code view.
6. Add the following code to the top of the Code window, just after the other using statements:

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

7. Locate the following code:

   public Service1()
               {
                     //CODEGEN: The ASP.NET Web Services Designer requires this call.
                     InitializeComponent();
               }
   						

   After this code, add the following code:

   [WebMethod]
               public string CountWords(string Sentence)
               {
                     string[] Words = Sentence.Split(' ');
                     return "Your sentence contains " + Words.Length + " word(s).";
               }
   [WebMethod]
               public DataSet GetCustOrders(string IDMask)
               {
                     IDMask = IDMask.Replace("'", "''");
   //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.
   
   //Modify this connection string to use your SQL Server and log on information.
   SqlConnection con = new SqlConnection("server=<your server>;uid=<your user id>;
                                          pwd=<your Password>;database=northwind");
   
   //Open the Customers table to serve as the parent table.
   SqlDataAdapter daCust = new SqlDataAdapter("Select * From Customers 
                                               Where CustomerID Like '%" + IDMask + "%'", con);
   
   //Open the Orders table to serve as the child table.
   SqlDataAdapter daOrders = new SqlDataAdapter("Select * From Orders 
                                                 Where CustomerID Like '%" + IDMask + "%'", con);
   
   //Create a client-side DataSet to hold the Customers and Orders tables.
   DataSet ds=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;
   }
   					

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/csCustomer/Service1.asmx.
2. 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/csCustomer/Service1.asmx?op=GetCustOrders.
3. In the Text section of the GetCustOrders page, type AL in the Value text box next to the IDMask parameter.
4. 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/csCustomer/Service1.asmx/GetCustOrders?IDMask=AL.
5. Close the displayed Web pages.

Create the Client Application

1. In Visual Studio .NET, create a new Visual C# .NET 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. A dialog box appears that displays sources of Web references.
4. In the Add Web Reference dialog box, type the URL for your Web service (for example, http://localhost/csCustomer/Service1.asmx), press ENTER, and then click Add Reference. Notice that a new entry named Web Reference appears in Solution Explorer.
5. In the Visual C# project, double-click Button1 to open its Code window, and paste the following code into the Button1_Click event handler:

          //Use the Web Service that your Web server provides.
          localhost.Service1 MyService = 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.

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