Designing the Web Service

To help you see how XML Web Services can work in this type of scenario, the rest of this chapter will show you how to build Web Services that allow two trading partners to interact. Our first fictitious company is called Jackson-Lauren Distributing Company (JLD), and it acts as distributor of a variety of products. JLD has a number of dealers around the country, including our second fictitious company, Elise's Emporium (EE). Elise's Emporium maintains a Web site that has a complete product listing, as well as information as to whether each product is in stock or back-ordered, based on information provided by JLD.

You'll be building the following software for both EE and JLD:

• A basic Web site to show products available from Elise's Emporium, including description, pricing, and availability

• A Web Service at JLD to allow customers to inquire about current stock level on an item

• A Web service at JLD to provide a full list of products available for order, allowing client stores to populate their databases on a routine basis

All the Web Services offered by JLD will include authentication to make sure that only customers use these services. We'll be handling security through the application and a SOAP header. You'll be supplying a username and password in this header, which is parsed by the .NET Framework and made available to your application. You could also add Secure Sockets Layer (SSL) to add an extra layer of protection for your data because if you don't, information is passing across the Internet unencrypted.

Building the JLD Database

For a database platform, we'll be using SQL Server 2000. If you're using a database other than SQL Server 7.0 or 2000, refer to the .NET documentation for information on how to configure your database connections.

To make this scenario more realistic, I've created two databases, one for each company. These tables will include just the fields that are relevant to our application. In real life, the tables you're building, such as the tblCustomers table on the JLD database, would have quite a bit more information.

To get started, create a database for the JLD application. In the sample code, this database is named WSCh12_JLD.

In the JLD database, we have to create the following tables:

• tblCustomers Includes user and password information for each customer of JLD

• tblProducts List of products, prices, and stock levels

The SQL script to create these tables is shown in Listing 12.1.

Listing 12.1. JLDScript.sql—Script to Create Database Tables

CREATE TABLE [dbo].[tblCustomers] (
      [pkCustomerID] [int] IDENTITY (1, 1) NOT NULL ,
      [Name] [varchar] (80) NOT NULL ,
      [UserID] [varchar] (20) NOT NULL ,
      [Password] [varchar] (20) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblProducts] (
      [pkProductID] [int] NOT NULL ,
      [Name] [varchar] (80) NOT NULL ,
      [Description] [varchar] (240) NULL ,
      [RetailPrice] [money] NOT NULL ,
      [DealerPrice] [money] NOT NULL ,
      [Inventory] [int] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblCustomers] WITH NOCHECK ADD
      CONSTRAINT [PK_tblCustomers] PRIMARY KEY  CLUSTERED
      (
              [pkCustomerID]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblProducts] WITH NOCHECK ADD
      CONSTRAINT [PK_tblProducts] PRIMARY KEY  CLUSTERED
      (
              [pkProductID]
      )  ON [PRIMARY]
GO
						

After you have built the database tables, you should add some test data. I'd suggest a couple of customers and between 5 and 10 products. Vary the stock levels and prices so that the store you build will look somewhat realistic.

Creating the Database Class

Database programming in ADO .NET requires a few more objects and more lines of code than you may be used to. If you have the code encapsulated inside another object, you can concentrate on the business logic you are trying to implement. The Database class you're going to build here is specifically designed to communicate with SQL Server (7.0 or 2000) and handle the most commonly used operations you'll need.

The code for the Database class is shown in Listing 12.2. You can add this class into your Web Service project. In the sample code, this file is called Database.vb.

Listing 12.2. Database.vb—Class File for the Database Class

Imports System.Data.SqlClient
Imports System.Configuration

Public Class Database
  Private m_cnDB As SqlConnection

  '
  ' This constructor reads the application configuration
  ' file (Web.config for web applications) for a string
  ' called ConnectionString. If it's not there, an exception
  ' is thrown. Otherwise, the connection is made.
  '

  Public Sub New()
    Dim strConn As String
    strConn = ConfigurationSettings.AppSettings("ConnectionString")
    If strConn = "" Then
      Throw New Exception("Connection string not found " _
        & "in application configuration file.")
    Else
      m_cnDB = New SqlConnection(strConn)
      m_cnDB.Open()
    End If
  End Sub

  '
  ' This constructor accepts a connection string as input
  ' and makes a connection to that SQL Server.
  '

  Public Sub New(ByVal ConnectionString As String)
    m_cnDB = New SqlConnection(ConnectionString)
    m_cnDB.Open()
  End Sub

  '
  ' In case there are other objects that need the live
  ' connection, make it available through a read-only
  ' property.
  '

  Public ReadOnly Property Connection() As SqlConnection
  Get
    Return m_cnDB
  End Get
  End Property

  '
  ' Run a query that returns records in the form
  ' of a SqlDataReader.
  '

  Public Function GetDataReader(ByVal SQL As String, _
    Optional ByVal blnSkipRead As Boolean = False) As SqlDataReader

    Dim cmdQuery As New SqlCommand()
    Dim dr As SqlDataReader
    cmdQuery.Connection = m_cnDB
    cmdQuery.CommandText = SQL
    cmdQuery.CommandType = CommandType.Text
    dr = cmdQuery.ExecuteReader
    If Not blnSkipRead Then dr.Read()
    Return dr
  End Function

  '
  ' Run a query that returns records in the form
  ' of a DataSet.
  '

  Public Function GetDataSet(ByVal SQL As String) As DataSet
    Dim da As New SqlDataAdapter(SQL, m_cnDB)
    Dim ds As New DataSet("Results")
    da.Fill(ds)
    Return ds
  End Function

  '
  ' Close the database connection.
  '

  Public Sub Close()
    m_cnDB.Close()
  End Sub
End Class
						

Starting at the top of the listing, you have a class-level private variable called m_cnDB. This variable will hold the SqlConnection object after it is created. The other methods in the class use this object to perform their database actions.

The class has two constructors—one that takes no arguments and one that takes a connection string. The one that doesn't take arguments reads the ConnectionString variable from the web.config file located in the Web application's directory. The ConnectionString is located in the AppSettings section, as follows:

... rest of Web.config file ...
  </system.web>
  <appSettings>
    <add key="ConnectionString"
       value="server=(local);database=WSCh12_JLD;UID=user;PWD=pass;" />
 </appSettings>

</configuration>

Using the web.config file to hold the ConnectionString makes it much easier to change when necessary. You don't have to recompile any objects; instead, you just edit this text file to change the connection information. Be sure to change the user ID and password as necessary.

After the database connection has been made, the calling object has three options for using it. The first is a property that returns the current SqlConnection object. This feature allows you to use a live SqlConnection object in controls that require it, while still keeping the logic encapsulated in the Database class. The second option returns a DataSet generated by a SQL statement, and the third option returns a SqlDataReader object. Because of a restriction in the SqlConnection object, you can only have one SqlDataReader open at any time.

When the caller is done using the Database object, a Close method closes the connection.

You'll be using the Database class throughout the Web Service to handle all database interaction.

Creating the SecurityToken Class

Because JLD doesn't want just anyone to be able to get wholesale pricing information, a username and password will be required to obtain information via this Web Service. The username and password will be sent for each request of this Web Service. You could make these additional parameters on the call to the Web Service, but instead, you'll be creating a SOAP header that will take care of this information. If the header values are not provided, the Web Service will not run. There is a great deal more involved with SOAP, so we'll just be focusing on this one aspect for now. Refer to the MSDN documentation for more information on what SOAP provides.

The data put into the SOAP header will be stored in a class called SecurityToken. The code for the SecurityToken class is shown in Listing 12.3.

Listing 12.3. SecurityToken.vb—Class File for SecurityToken Class

Public Class SecurityToken
  Inherits System.Web.Services.Protocols.SoapHeader
  Public UserName As String
  Public Password As String

  Public Function Verify(ByVal db As Database) As Boolean
    Dim ds As DataSet

    Try
      ds = db.GetDataSet("sp_RetrieveCustomer '" & UserName _
        & "', '" & Password & "'")
      Return (ds.Tables.Count > 0)
    Catch e As Exception
      Return False
    End Try

  End Function

End Class

We start the class by declaring two public properties—UserName and Password. The application calling this Web Service will supply data for each of these properties. The second piece of code you need to write is the Verify routine, which checks the supplied username and password against the database. It calls a stored procedure called sp_RetrieveCustomer, which is essentially a query saved to the database. If you're not familiar with stored procedures, refer to the SQL Server Books Online that will show you how to create one. The code for this stored procedure is as follows:

CREATE PROCEDURE dbo.sp_RetrieveCustomer
@UserID varchar(20),
@Password varchar(20)
AS
SELECT COUNT(*) As Result
FROM tblCustomers
WHERE Upper(UserID) = Upper(@UserID)
AND Upper(Password) = Upper(@Password)

This stored procedure accepts the username and password and determines if there is a customer matching that combination in the database. In this stored procedure, the Upper function is converting both the database field and the input data to uppercase letters, effectively making it non-case–sensitive. If you want your application to use case-sensitive passwords, you can remove the call to the Upper function here.

Building the Web Service

With the helper objects complete, it's time to write the code for the Web Service. This code will go into your .asmx file, which is called CustomerService.asmx in the sample code. The code for the Web Service is shown in Listing 12.4.

Listing 12.4. CustomerService.asmx— Creating the CustomerService Web Service

Imports System.Web.Services
Imports System.Web.Services.Protocols

<WebService(Namespace:="http://www.jld.ncs")> _
Public Class CustomerService
  Inherits System.Web.Services.WebService
  Public SecurityHeader As SecurityToken

#Region " Web Services Designer Generated Code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Web Services Designer.
        InitializeComponent()
        SecurityHeader = New SecurityToken()
        'Add your own initialization code after the InitializeComponent() call

    End Sub

    'Required by the Web Services Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Web Services Designer
    'It can be modified using the Web Services Designer.
    'Do not modify it using the code editor.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        components = New System.ComponentModel.Container()
    End Sub

    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        'CODEGEN: This procedure is required by the Web Services Designer
        'Do not modify it using the code editor.
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub
#End Region
  <WebMethod(), SoapHeaderAttribute("SecurityHeader", _
    Direction:=SoapHeaderDirection.In, Required:=True)> _
  Public Function GetInventoryLevel(ByVal intProductID As Integer) As Integer

    Dim db As New Database()
    Dim ds As DataSet
    Dim dr As DataRow
    If Not SecurityHeader.Verify(db) Then
      Throw New Exception("Invalid authentication token.")
      Exit Function
    End If
    ds = db.GetDataSet("sp_RetrieveProduct " & intProductID.ToString())
    If ds.Tables(0).Rows.Count > 0 Then
      dr = ds.Tables(0).Rows(0)
      Return Integer.Parse(dr("Inventory"))
    Else
      Throw New Exception("Invalid product ID.")
    End If
    db.Close()

  End Function

  <WebMethod(), SoapHeaderAttribute("SecurityHeader", _
    Direction:=SoapHeaderDirection.In, Required:=True)> _
  Public Function GetProducts() As DataSet
    Dim db As New Database()
    Dim ds As DataSet

    If Not SecurityHeader.Verify(db) Then
      Throw New Exception("Invalid authentication token.")
      Exit Function
    End If

    ds = db.GetDataSet("sp_RetrieveProducts")
    Return ds
    db.Close()

  End Function
End Class

The first thing to do is to include the System.Web.Services.Protocols assembly, which provides access to the special features of SOAP that you'll need for the security features you'll be using with this Web Service.

In the declaration of the class, you use the WebService directive just before the Class keyword. This identifies the class as a Web Service to the .NET Framework. In this example, a fake URL is used as the namespace name, but in practice, you would put the real URL here.

When you start declaring the class, you need a public variable of type SecurityToken. This variable will be filled by the .NET Framework after the security token has been read from the SOAP header sent by the calling application. You'll see how this is done in the section “Building the Product List Page,” when we call the Web Service from another application.

In Listing 12.4, the code between the #Region and #End Region markers is automatically generated, so don't type it into the page. However, you do need to add this line (shown in boldface in the previous listing) following the call to InitializeComponent:

SecurityHeader = New SecurityToken()

This creates a new instance of the SecurityToken class so that security verification can take place. Because the variable is declared at the class level, you only need to instantiate this object once for the life of the Web Service.

The first method you need to write is the GetInventoryLevel method. After authenticating the username and password against the database, this method accepts a product ID number and returns the current stock level for that item. It uses the sp_RetrieveProduct stored procedure, shown in the following code:

CREATE PROCEDURE dbo.sp_RetrieveProduct
@ProductID int
AS
SELECT *
FROM tblProducts
WHERE pkProductID = @ProductID

This stored procedure may seem a bit trivial, but it's a good habit to get into because stored procedures give you performance benefits whenever you use them.

The data returned from this stored procedure is put into a DataSet object, but in this case, the only value we need is the current stock represented in the Inventory field. If no rows are returned, an exception is generated indicating that the product ID doesn't exist in the database.

The second method you need to write returns the current list of products in the database. This method follows the same pattern as the last—authenticate the username and password and then retrieve the data. In this case, the returned data is sent back as a DataSet instead of just a single value. The calling application will be able to read this data from the Web Service into a DataSet locally and manipulate it. The stored procedure being called here, named sp_RetrieveProducts, is as follows:

CREATE PROCEDURE dbo.sp_RetrieveProducts
AS
SELECT *
FROM tblProducts
ORDER BY Name

Because we're using the SOAP header here to pass a username and password, the normal test page generated by Visual Studio .NET for a Web Service will display but not allow you to test the method. If you want to test this code now, you can do the following:

• Change the WebMethod declaration above each method declaration to look as follows:

  <WebMethod()> _
  Public Function GetProducts() As DataSet
  

• Comment out the lines that call the Verify method on the SecurityToken object.

Making these two changes will allow you to test the Web Service prior to building the Web site in the next section. When the test page comes up, you can use any of the methods exposed by the Web Service. For the GetProduct method, you can use any of the product ID values stored in the database that you created.