A Web Service for the Northwind Database

This section demonstrates how to build a Web service for an Access database. Running a Web service for an Access database turns it into a client/server system because the user never gets the database file, she merely submits commands to a Web service that manipulates the database file. You can dramatically decrease your network traffic with this use of Web services because the Access database file stays put on the Web service client. Only individual values or datasets pass back to clients from the Web service.

Overview of the Application

This chapter focuses on Web service design and deployment techniques. These techniques are essentially the same whether your Web service returns values from a database, does a computation, or performs some combination of the two. In addition, a majority of the book explained how to program these tasks . The point of this chapter is to show you how to package the code in a Web service and apply what you already learned in this book. This chapter concludes with the presentation of a Web service that focuses on the manipulation of an Access database ”the Northwind database.

The Web service we will examine in this section has the name OrderIDService. It provides three main functions. First, the Web service returns a value. A user inputs an OrderID, and the Web service looks up the Freight charge for the corresponding order. Second, the Web service returns a dataset. Specifically, the second function returns rows from the Order Details table that match a designated OrderID. This second function is very similar to the first one in that it is essentially a lookup task. The distinction is that the second function returns a collection of values instead of returning a single value. Because Access developers often work with a collection of values, this distinction is important. Third, the Web service computes a value: the sum of the extended price for the line items in an order.

As with many Web service applications, the sample application for this section consists of two projects. The OrderIDService project is a project built from an ASP.NET Web Service template. It consists of three function procedures ”one for each Web method ”and some related code, such as a module- level declaration and the ComputeDas1 Function procedure, which returns a DataSet object. The OrderIDServiceClient project is built on the Windows Application template. This project includes a Web reference that points at the OrderIDService project. A proxy variable pointing at the Web reference enables the client application to invoke the Web methods in the OrderIDService project. In addition, the OrderIDServiceClient project features a user interface through a Windows Forms instance, Form1 , which assists with the collection of input from users and the display of data to users.

The Web Service Project

The code for the Web service appears in the following listing. The attribute settings for the OrderIDService class assign a description and a name to override the default namespace for a Web service. Assigning a name helps to ensure the uniqueness of the namespace name, which is guaranteed not to be unique unless you change the default name of http://tempuri.org/ . The description helps to identify the functions of the Web service in the Web service s Test page. The Web Services Designer “generated code is not shown in this listing. Instead, a region marker signifies where this code goes in the listing. After the marker for the Web Services Designer “generated code, a module-level statement declares and instantiates an OleDbConnection object that points at the Northwind database. This Connection object appears in a couple of procedures for the Web service.

The three function procedures that appear first in the OrderIDService class implement a Web method. Two of the functions rely on a fourth function, ComputeDas1 , to implement their Web method.

The Freight Function procedure looks up the Freight column value for an order with an OrderID column value designated by a string passed to the function. The procedure begins by declaring and instantiating a new OleDbCommand object and then assigning its CommandText and Connection properties in a With End With statement. The assignment for the Connection property refers to the module-level Connection object. The CommandText property is assigned a SQL string that depends on a String expression that combines a String constant for part of the SQL string with a variable based on the str1 variable passed to the procedure. By invoking the ExecuteScalar method for the OleDbCommand object, the procedure stores in the dec1 variable the Freight column value for the order with the OrderID column value in str1 . A Return statement at the end of the procedure passes the value that has been looked up to the client application for the Web method.

The Items procedure returns a dataset with three column values for each line item from the Order Details table with an OrderID value matching the string passed to the Function procedure. The Items procedure does not compute the dataset that it returns. Instead, the Items procedure invokes the ComputeDas1 procedure, which supports both the Items procedure and the TotalPrice procedure (discussed in the next paragraph). The ComputeDas1 procedure starts with a SQL string assignment that selects the Quantity, the ProductName, and a computed term ”the ExtendedPrice for each line item matching an OrderID value specified by the str1 variable value. The procedure uses the SQL string along with the OleDbConnection object declared at the module level to construct a data adapter that the procedure in turn uses to populate the das1 dataset. The ComputeDas1 procedure passes the das1 dataset to the Items procedure, which passes the dataset to the client project for the Web service.

The TotalPrice procedure starts by computing a new instance of the das1 dataset based on the current value of the str1 String variable. Then, the TotalPrice procedure loops through the rows of the das1 dataset and aggregates the ExtendedPrice values in the dec1 variable that it returns.

 <System.Web.Services.WebService( _ Namespace:="http://CABXmlServices/OrderIDService", _ Description:="Freight, Items, and Total Price.")> _ Public Class OrderIDService Inherits System.Web.Services.WebService #Region " Web Services Designer Generated Code " Dim cnn1 As New OleDb.OleDbConnection( _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source = c:\Program Files\Microsoft " & _ "Office\Office10\Samples\Northwind.mdb") OrderIDService <WebMethod()> Public Function Freight( _ ByVal str1 As String) As Decimal Declare and instantiate a command object Dim cmd1 As New OleDb.OleDbCommand() Assign Connection and CommandText properties With cmd1 .Connection = cnn1 .CommandText = _ "SELECT Freight FROM Orders " & _ "WHERE OrderID = " & str1 End With Retrieve command cnn1. Open () Dim dec1 = cmd1.ExecuteScalar cnn1.Close() Return command result Return dec1 End Function <WebMethod()> Public Function Items( _ ByVal str1 As String) As Dataset Compute the dataset for an OrderID Dim das1 As Dataset = ComputeDas1(str1) Return dataset Return das1 End Function <WebMethod()> Public Function TotalPrice( _ ByVal str1 As String) As Decimal Compute the dataset for an OrderID Dim das1 As Dataset = ComputeDas1(str1) Loop through rows in dataset to compute total extended price Dim drw1 As DataRow Dim dec1 As Decimal For Each drw1 In das1.Tables(0).Rows dec1 += drw1(2) Next Return total price Return dec1 End Function Function ComputeDas1(ByVal str1 As String) As  Dataset  Define a SQL string to return Quantity, ProductName, and ExtendedPrice by line item for an order with a designated OrderID Dim str2 = _ "SELECT [Order Details].Quantity, ProductName, " & _ "[Order Details].UnitPrice*" & _ "[Order Details].Quantity*" & _ "(1-[Order Details].Discount) " & _ "AS ExtendedPrice " & _ "FROM [Order Details] INNER JOIN Products " & _ "ON [Order Details].ProductID = Products.ProductID " & _ "WHERE [Order Details].OrderID= " & str1 Fill das1 dataset with data adapter Dim dap1 As New OleDb.OleDbDataAdapter(str2, cnn1) Dim das1 As New Dataset() dap1.Fill(das1, "OrderDetails") Return dataset Return das1 End Function End Class 

Testing and Demonstrating the Web Service

After designing any Web service, it is a good idea to test-drive its operation with the built-in test page capabilities. This enables you to see how your code is operating. You might discover opportunities for fine-tuning your code or fixing logical errors. Beyond that, you can gain experience that helps you to design a client application for the Web service.

Figure 12-9 presents the initial test page for the OrderIDService Web service from within a Browse window in Visual Studio .NET. You can generate this dialog box by right-clicking the .asmx file for the Web service in Solution Explorer and choosing Build And Browse. The tab for the Browse window in Figure 12-9 shows the name for the Web service (or, more precisely, an abbreviation for it that fits on the tab). Just below the Web service name, the Description setting for the WebService attribute appears. Four hyperlinks appear in the initial test page. The Service Description link opens a window showing the .wsdl file describing the Web service. If you want to dig deeper into the XML describing the Web service, this is a great resource. Next, a separate link appears for each Web method. The links have names that correspond to the Function procedure implementing each Web method. Clicking the link for a Web method opens another dialog box containing text boxes for any input arguments to the Web method as well as an Invoke button for launching the Web method.

click to expand
Figure 12-9: The initial Test page for the OrderIDService Web service

As mentioned, the test page for all three Web methods is the same (unless they have different input arguments). The difference between the Web methods is in the response window that appears after a user clicks the Invoke button to launch a Web method. The response window shows the value or values returned by a Web method. For the OrderIDService Web service, both the Freight and TotalPrice Web methods have the same type of return: a single value with a Decimal data type. The difference is that the Freight Web method returns a looked-up value, but the TotalPrice Web method returns a computed value.

Figures 12-10 and 12-11 show the input and response windows for the Freight and TotalPrice Web methods, respectively. In both cases, the OrderID value is the same ”10248. The bottom window in each figure displays the return value. For example, if you open the Orders table in the Northwind database and look up the Freight column value for the row with an OrderID column value of 10248, the value is 32.38. The Freight Web method performs this operation for you. The bottom window in Figure 12-11 shows a value of 440. This is the sum of the extended price for each of the three line items in the Order Details table with an OrderID column value of 10248. The extended price value for a line item is itself the result of a computation based on the Quantity, UnitPrice, and Discount column values for a row in the Order Details table. The bottom windows in Figures 12-10 and 12-11 show the return value as an element in an XML document. Notice that the Namespace setting for the Web service appears as an attribute for the decimal tag denoting a return value. The Namespace name designates the Web service returning the value, and the decimal tag specifies the type for the data value. This XML markup is an inherent feature of a Web service.

click to expand
Figures 12-10: The input and response windows for the Freight Web method
click to expand
Figures 12-11: The input and response windows for the TotalPrice Web method

The Items Web method returns a dataset ”that is, a set of rows, each containing column values for Quantity, ProductName, and the computed result for the extended price of a line item. The number of rows in the dataset depends on the number of line items in an order. Because ADO.NET represents a dataset as an XML document, the return from the Items Web method is an XML document. Figure 12-12 shows an excerpt from the document for an input OrderID value of 10248. Notice that the document has two parts . The top part specifies the structure for the dataset, which includes elements for Quantity, ProductName, and ExtendedPrice. These elements designate the data type ( short , string , and double ) with an attribute from the xs namespace. The values for the first row in the dataset appear toward the bottom of Figure 12-12. Each row appears between opening and closing OrderDetails tags. Within these tags are more tags denoting the individual column values within a row. While the Test page returns the values in this format, you can program access to the dataset values just as you would any other dataset discussed in this book. The client application will illustrate how to control the display of the values in a ListBox control on a Windows Forms instance.

click to expand
Figure 12-12: An excerpt from the response window for the Items Web method with a OrderID value of 10248

Building a Client Application

Building a client application for a Web service such as the OrderIDService involves three steps. First, you create a Windows application or a Web application and add a Web reference pointing at the Web service that you want to invoke. Second, you create a user interface (for example, a Windows or Web form with controls) for collecting values, invoking one or more Web methods, and displaying any return values from the Web methods. Third, you develop code behind the form to invoke the Web methods and process and return values.

Creating a Windows Project with a Web Reference

In developing the client for this demonstration, I started by creating a new Windows application on a different computer than the one used to create the Web service. The client application is available in the sample material for this chapter as the OrderIDServiceClient project with its associated project folder. When you follow this strategy for creating a client, make sure that the client application runs from a computer with the .NET Framework installed. If you do not have a second computer with the .NET Framework installed for evaluation purposes, you have at least a couple of alternatives. First, build the Windows client application on the same computer as the Web service. Second, create an ASP.NET Web page on the same Web server as the Web service. Then, open the Web page from a second computer. The second computer does not need to have the .NET Framework installed to open and invoke the Web service through the Web page because the Web page runs on the Web server but displays in the browser on the second computer.

After creating a client application, add a Web reference to the OrderIDService Web service. Invoke the Project, Add Web Reference command to open the Add Web Reference dialog box. In my office, this Web service resides in the OrderIDService folder of the ccs1 Web server. Therefore, specify the startup file for the Web service in the Address box of the Add Web Reference dialog box and press Enter. I changed the name of the startup file from its default name of Service1.asmx to OrderIDService.asmx. In my office, the URL for this file is http://ccs1/OrderIDService/OrderIDService.asmx . After pressing Enter on the keyboard to input your designated address for the startup file in the Address box, click the Add Reference button when Visual Studio .NET enables it. If this is the first Web reference from the ccs1 Web server, Visual Studio .NET adds a Web service named ccs1 to Solution Explorer for the client application.

Design and Operational Views of the Web Service

The window on the left in Figure 12-13 displays the Design view of the Web service client, and the window on the right is an operational view of the client. TextBox1 is for specifying an OrderID. Button1 , Button2 , and Button3 invoke the Freight , Items , and TotalPrice Web methods, respectively. Label2 shows the return value in a sentence for either the Freight or TotalPrice Web methods, depending on whether Button1 or Button3 was clicked last. A click to Button2 , the Line Items button, populates ListBox1 with the Quantity, ProductName, and ExtendedPrice for each line item in the order. These line items are the subset of rows from the Order Details table that has OrderID values matching the entry in TextBox1 .

click to expand
Figure 12-13: Design and operational views of the client application for the OrderIDService Web service

The window on the right in Figure 12-13 shows Form1 from the OrderIDServiceClient Windows application. I entered 10248 in TextBox1 for an OrderID value, and I clicked the Line Items and Total Price buttons . Therefore, the line items in ListBox1 apply to the order with an OrderID value of 10248, and the label below TextBox1 ”Label2 ” displays the sum of the extended prices for the line items in the order. Clicking Button1 will change the contents of Label2 to show the freight charge for an order instead of the sum of the extended prices for the line items in an order.

The Code Behind Form1

The code behind Form1 for the client application consists of a module-level declaration that instantiates the proxy variable ( wsc1 ) for the Web service, a form load event procedure, and three short click event procedures for the buttons on the form. The Form1_Load procedure handles formatting issues. For example, this procedure assigns Text property values to the Button controls and Label1 . The Form1_Load procedure also specifies the Size and Location coordinates for ListBox1 . In addition, the Form1_Load procedure sets the Visible property for ListBox1 to False . Later, when a user clicks Button2 and invokes the Items Web method, the client application shows ListBox1 at the size and location designated in the Form1_Load procedure.

The Button1_Click and Button3_Click procedures have the same format. The Button1_Click procedure passes the Text property value of TextBox1 to the Web service pointed at by the wsc1 proxy variable. Button1_Click saves the return value from the Freight Web method in dec1 . Then, the procedure assigns a value to the Text property of the Label2 control. Similarly, the Button3_Click procedure invokes a Web method, in this case TotalPrice instead of Freight . Like Button1_Click , Button3_Click saves the return value in a Decimal data type ( dec1 ) and displays the return value from a Web method as the Text property value of the Label2 control.

The Button2_Click procedure passes the Text property of TextBox1 to the Items Web method of the Web service. The Items method returns a dataset. Therefore, the procedure saves the return object from the Web method in the das1 variable with a DataSet type. Then, the Button2_Click procedure invokes the Clear method for the Items collection of the ListBox1 control. The Items collection of a ListBox control reflects the items that display in the control. Next, the procedure declares a variable, drw1 , with a DataRow type. The sample code uses this variable to loop through the rows in the DataTable within the das1 dataset. For each row, the loop formats a line for the ListBox control and invokes the Add method for the Items collection of ListBox1 to add the line to the ListBox control.

 Dim wsc1 As New ccs1.OrderIDService() Private Sub Form1_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load Assign Text property values for buttons Button1.Text = "Freight" Button2.Text = "Line items" Button3.Text = "Total Price" Clear TextBox1 TextBox1.Clear() Format labels Label1.Text = "OrderID" Label2.Text = "" Label2.AutoSize = True Size and position ListBox1 relative to TextBox1 Dim int1 As Integer = TextBox1.Left + TextBox1.Width Dim int2 As Integer = int1 - 10 Dim int3 As Integer = TextBox1.Top + 100 ListBox1.Size = New System.Drawing.Size(int2, 100) ListBox1.Location = New System.Drawing.Point(10, 150) Format ListBox1 ListBox1.HorizontalScrollbar = True ListBox1.Visible = False End Sub Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Pass Freight Web method in OrderIDService the contents of TextBox1 and save return value in dec1 Dim dec1 As Decimal = wsc1.Freight(TextBox1.Text) Display returned Freight column value Label2.Text = "Freight is " & Format(dec1, "C") & "." End Sub Private Sub Button2_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button2.Click Dim das1 As DataSet = wsc1.Items(TextBox1.Text) ListBox1.Items.Clear() Dim drw1 As DataRow For Each drw1 In das1.Tables(0).Rows ListBox1.Items.Add(drw1(0).ToString & " of " & _ drw1(1) & " for " & Format(drw1(2), "c")) Next ListBox1.Visible = True End Sub Private Sub Button3_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button3.Click Compute Total Price for OrderID showing in TextBox1 Dim dec1 As Decimal = wsc1.TotalPrice(TextBox1.Text) Display summed Extended Price for line items in OrderID showing in TextBox1 Label2.Text = "Total price is " & Format(dec1, "C") & "." End Sub 
 


Programming Microsoft Visual Basic. NET for Microsoft Access Databases
Programming Microsoft Visual Basic .NET for Microsoft Access Databases (Pro Developer)
ISBN: 0735618194
EAN: 2147483647
Year: 2006
Pages: 111
Authors: Rick Dobson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net