Building Portals, Intranets, and Corporate Web Sites Using Microsoft Servers

This section demonstrates a simple web service for retrieving an inventory level. This sample web service is created with the Microsoft .NET Framework and Microsoft Visual Studio .NET. You can apply this example to many other scenarios in which you need to consume a web service that makes an inquiry into a relational database table.

To begin, you need a computer that meets the prerequisites for creating an ASP.NET application. For more information about these prerequisites, see the Visual Studio documentation. You also need access to the Northwind demo database that is installed with SQL Server.

To create the web service:

1. Open Visual Studio .NET.

2. Select New Project from the File menu.

3. In the New Project dialog box, make the following selections (Figure 4.4) and click OK.

   • Project Types: Visual Basic Projects

   • Templates: ASP .NEW Web Service

   • Location: http://localhost/PortalServices

   • Name : PortalServices (this entry should appear automatically)

   Figure 4.4. Creating a Web Service Project

The Visual Studio .NET IDE creates a web application on your local machine and generates all of the files necessary for the creation and execution of the web service. You may want to give the Service1 class (Service1.asmx) that is created a more meaningful name. To do so, delete the file and create a new file with the name of your choice. Select the file from within the Solution Explorer, right-click, and select Delete from the popup menu. To then create a web service file:

1. Select Add Web Service from the Project menu.

2. In the Add New Item dialog box, name the web service InventoryService.asmx , and click OK.

Next, open the class, add the code for the sample web service, and build the project:

1. Select the ASMX file from within the Solution Explorer pane. Right-click and select View Code from the popup menu.

2. Add the code shown in Listing 4.3 to the class, below the HelloWorld web service example.

3. Select Build from the Build menu.

Listing 4.3. Sample Web Service

01: <WebMethod()> Public Function GetInventoryLevel _ 02: (ByVal SupplierID As Integer, _ 03: ByVal CriticalLevel As Integer) As DataSet 04: Dim ds As New DataSet() 05: Dim cn As New SqlConnection( _ 06: "User ID=sa;Data Source=.;Initial Catalog=Northwind;") 07: Dim com As New SqlCommand() 08: Dim stbSQL As New System.Text.StringBuilder("") 09: Try 10: stbSQL.Append("SELECT ProductID, ProductName, UnitsInStock _ FROM Products ") 11: stbSQL.AppendFormat("WHERE SupplierID = {0}", _ SupplierID.ToString) 12: stbSQL.AppendFormat(" AND UnitsInStock <= {0}", _ CriticalLevel.ToString) 13: com.CommandType = CommandType.Text 14: com.CommandText = stbSQL.ToString 15: com.Connection = cn 16: Dim ad As New SqlDataAdapter(com) 17: ad.Fill(ds, "InventoryItems") 18: Catch ex As Exception 19: Diagnostics.Debug.WriteLine(ex.ToString) 20: End Try 21: Return ds 22: End Function

In lines 01 to 03 of the code you added, you can find the function definition, which is divided into four parts :

1. Function attributes, which are new to the .NET languages, allow developers to define certain attributes before the definition of classes, member variables , and methods . In this case, the attribute <WebMethod()> defines the GetInventoryLevel() function as a web method. When the Common Language Runtime (CLR) sees this attribute while loading and compiling the ASMX file, it knows that this function needs to be exposed as part of a web service.

2. Function Name is the name that will be used to call the function/web service.

3. Variable/parameters are passed to the function.

4. A return variable, used to return the outcome of the function. In the case of web service development, it is important to make sure that the variable type can be serialized. In the sample web service, that variable type returned is a dataset, which is a serializable variable. Although the .NET framework will do most of the plumbing when it comes to serializable variables, there are numerous variable types that are not serializable and can therefore not be used to return values from a web service.

The remainder of the function is a call to the database to retrieve a dataset containing the products that currently have an inventory level equal to or lower than that specified in the function's parameters. Lines 04 to 08 define a number of variables that will be used during the function. Note that the connection string, defined on lines 05 and 06, may need to be modified depending on the configuration of SQL Server on your machine. The SQL statement is prepared on lines 10 to 12 by using a String Builder object. (The String Builder object is used to concatenate strings and has been optimized to perform this action.) Lines 13 “15 define the command that will be used to extract the data from the database and lines 16 and 17 make the call to the database and fill the dataset with the records that are returned from the database. On line 21, you can see that the function returns the dataset without having to perform any special handling of the dataset variable.

To run the sample web service, open Internet Explorer and navigate to localhost/PortalServices/InventoryService.asmx. Figure 4.5 shows the window that opens, which is generated automatically by ASP.NET using .NET reflection APIs. No work is required on the part of the developer.

On the generated page, there is a link to the GetInventoryLevel web service. Clicking that link opens a page from which you can call the GetInventoryLevel function, shown in Figure 4.6. This page, too, is automatically generated by ASP.NET and creates an impromptu user interface for testing the GetInventoryLevel web service. It outlines how to call the function using SOAP, HTTP/GET, and HTTP/POST, and it displays a general format of the message exchange so you know what to send and what to expect in return.

To test the GetInventoryLevel web service, set values for each of the variables and click the Invoke button to open a new browser window with the function's results (Figure 4.7).

The results from the service are in the format of a dataset serialized to XML format. These results can be consumed by any environment on any platform. For example, this web service could be included on a B2B (business-to-business) portal where users are identified as representatives of the supplier. A "portlet" within the portal could be configured to take the user's supplier identification and an inventory level, thus allowing the portal to display the current inventory level of products that are below the preset critical value.