Use your favorite text editor to create a new, blank file. Then add the @ WebService directive, the Imports statements, and the class definition shown here:

<%@ WebService Language="VB" Class="ContosoAdSvc" %>
Imports System
Imports System.DateTime
Imports System.Web.Services
Imports System.Data
Imports System.Data.OleDb
Imports Microsoft.VisualBasic

Public Class ContosoAdSvc
    Inherits WebService
' Code to implement this class will appear here.
End Class

Specifying the class name ContosoAdSvc in the @ WebService directive tells ASP.NET the name of the class that will provide the Web service methods.

Web services don’t automatically import all the standard namespaces that ASP.NET pages do. This limitation accounts for the large number of import statements you see in the preceding code example.

The Inherits WebService statement that follows the ContosoAdSvc definition refers to an optional class of base features for Web services. Any Web service that needs access to common ASP.NET objects like Server, Application, and Session needs to inherit this class.

Replace the comment in the previous step with code that declares an OleDbConnection named conClasf and a subroutine named OpenDb that opens this connection:

Public Class ContosoAdSvc
    Inherits WebService
  Dim conClasf As OleDbConnection

  Sub OpenDb()
    conClasf = New OleDbConnection( _
      "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & _
      Server.MapPath("db/classified.mdb") & ";")
    conClasf.Open
  End Sub
' Code for additional methods will go here.
End Class

Note that this code doesn’t retrieve the database location from a Session variable, as recent chapters have done; Web services normally don’t have Session objects, and creating them is a great nuisance.

Between the End Sub statement and the comment you just coded, define a WebMethod function named FindAds that accepts a string argument named astrFind and returns an array of strings. This function should

None of this involves any new concepts, so here’s the code, all in one piece. Recall that the <WebMethod> tag identifies functions and subroutines that should be available through the XML Web service.

<WebMethod> Public Function _
    FindAds(astrFind As String) As String()
  Dim cmdClasf As OleDbCommand
  Dim rdrMembers As OleDbDataReader
  Dim strSQL As String
  Dim strMatchList As String = ""
  Dim strMatches() As String
  Dim strNoMatches() As String = {""}

  If Trim(astrFind) = "" Then
    Return strNoMatches
    Exit Function
  End If
   strSQL = _
       "SELECT * "& _
         "FROM ads "& _
       "WHERE description LIKE '%" & astrFind & "%' "& _
         "AND (startdate <= #" & Date.Today() & "#) "& _
         "AND (enddate   >= #" & Date.Today() & "#) "& _
    "ORDER BY adid "
  OpenDb()
  cmdClasf = New OleDbCommand(strSQL, conClasf)
  rdrMembers = cmdClasf.ExecuteReader

  Do While rdrMembers.Read
    strMatchList &= "," & rdrMembers.Item("adid")
  Loop

  If strMatchList <> "" Then
    strMatchList = Mid(strMatchList,2)
  End If
  strMatches = Split(strMatchList, ",")
  conClasf.Close
  Return strMatches
End Function

This function satisfies the first requirement for the Web service: returning a list of ID values for ads with a given substring in the description field.

The second requirement is for a method that returns four fields for a given ad ID: a Found indicator and the Headline, Description, and AskingPrice fields from the database. To provide an object that contains these four fields, add the following statements immediately after the function you created in the preceding step.

Public Class AdInfo
  Public Found As String
  Public Headline As String
  Public Description As String
  Public AskingPrice As Decimal
End Class

Next, define a WebMethod function named GetInfo that accepts an Integer argument named intAdId and returns an AdInfo object. This function should

Very little of this involves any new concepts, so here again is the code, all in one piece:

<WebMethod(CacheDuration:=5)> _
  Public Function GetInfo(intAdId As Integer) As AdInfo
    Dim cmdClasf As OleDbCommand
    Dim rdrMembers As OleDbDataReader
    Dim strSQL As String
    Dim adiCurrent As AdInfo = New AdInfo()

    strSQL = _
      "SELECT * "& _
        "FROM ads "& _
       "WHERE adid = "& intAdId & ""& _
         "AND (startdate <= #" & Date.Today() & "#) "& _
         "AND (enddate   >= #" & Date.Today() & "#) "

    OpenDb()
    cmdClasf = New OleDbCommand(strSQL, conClasf)
    rdrMembers = cmdClasf.ExecuteReader

    If rdrMembers.Read Then
      adiCurrent.Found = True
      adiCurrent.Headline = rdrMembers.Item("headline")
      adiCurrent.Description =   _
                              rdrMembers.Item("description")
      adiCurrent.AskingPrice =  _
                              rdrMembers.Item("askingprice")
    Else
      adiCurrent.Found = False
      adiCurrent.Headline = "Not found."
      adiCurrent.Description = "Not Found."
      adiCurrent.AskingPrice = 0
    End If
    conClasf.Close
    return adiCurrent
  End Function

The expression (CacheDuration:=5) inside the <WebMethod> tag tells the Web server to cache responses for up to five seconds. This instruction saves the Web service from looking up the same ad twice in any five-second period; instead, the second and all subsequent requests will receive the same results as the first.

Save the file in the ch14svc folder, assigning it a name of adsvc.asmx.

Within the ch14svc folder, create a subfolder named db. Copy the classified.mdb file from Chapter 13 into this subfolder.

Open the ch14svc/adsvc.asmx file in your browser, taking care to specify an HTTP URL. This should display a test page like the one on page 483. Test the page as before and verify that you’re getting reasonable results from both the FindAds and GetInfo methods.

Create the source code for the Web service proxy. In other words, open a command window and run the wsdl program as shown here. It’s all one line and you’ll need to adjust the server name and path so that they conform to your environment.

wsdl /language:vb http://collar/webdbpgm/ch14svc/
adsvc.asmx?WSDL

Because the name of the Web Service class in the adsvc.asmx file is ContosoAdSvc, this command will create a file named ContosoAdSvc.vb.

Compile the ContosoAdSvc.vb file from step 1 into a library named ContosoAdSvc.dll. To do so, run the following command. It’s all one line.

vbc /t:library /out:ContosoAdSvc.dll /r:system.dll
 /r:system.web.dll /r:System.web.services.dll
 /r:system.Xml.dll ContosoAdSvc.vb

Copy the ContosoAdSvc.dll file from step 2 into the ch14c/bin folder.

Open your favorite HTML editor, create a new, blank page, and display the HTML for editing.

Add a standard @ Page directive, an @ Register directive for the standard page banner, and an @ Import directive to the System.Data namespace at the top of the page. Then add a code declaration block, a title, and any page-level formatting you want. The top of the page should now look like this:

<%@ Page Language="vb" Debug="true" Explicit="True"%>
<%@ Register TagPrefix="webdbpgm" TagName="banner"
             src="banner.ascx" %>
<%@ Import Namespace="System.Data"%>
<script runat="server">
' Program code will go here.
</script>
<html>
<head>
<title>Web Service Query</title>
<link rel="stylesheet" type="text/css" href="normal.css">
</head>

Add a user control to display the page banner, a Web form, a Literal Web server control named litErrMsg, and a DataGrid named grdAds to the <body> section of the Web page. Here’s the code for these elements; arrange them any way you want.

<webdbpgm:banner runat="server" />

<form runat="server">
</form>

<asp:literal id="litErrMsg" EnableViewState="false"
     runat="server" />

<asp:DataGrid id="grdAds" visible="false"
     EnableViewState="false" runat="server" />

Add an HtmlInputText control named txtFind and an HtmlInputButton named btnSub inside the Web form you created in the previous step, using the new code shown here in blue.

<form runat="server">
<input type="text" id="txtFind" size="20"
       runat="server" />
<input type="submit" id="btnSub" value="Submit"
       runat="server" />
</form>

Replace the comment in step 2 with a Page_Load subroutine. If a postback has occurred and the text box is empty, the subroutine should display an error message. If a postback has occurred and the text box contains data, the code should run another subroutine named SearchForAd. Here’s the required code:

Sub Page_Load(sender As Object, e As EventArgs)
  If Page.IsPostBack Then
    If txtFind.Value = "" Then
      litErrMsg.Text = "Enter an Ad ID."
    Else
      SearchForAd()
    End If
  End If
End Sub

Immediately after the End Sub statement you coded in the previous step, define a subroutine named SearchForAd and containing these declarations:

Sub SearchForAd()
  Dim cadSvc As ContosoAdSvc
  Dim strAdIds() As String
  Dim dtbAds As DataTable
  Dim rowAds As DataRow
  Dim intPos As Integer
  Dim intAdId As Integer
'   Program code will go here.
End Sub

The cadSvc variable will load an instance of the ContosoAdSvc proxy class. The strAdIds array will receive a list of ad IDs from the Web service’s FindAds method. The dtbAds variable will point to a data table that contains information about each found ad; rowAds will temporarily point to each new row in that data table. The intPos variable will step through the strAdIds array one element at a time, and the intAdId variable will contain the current ad ID based on each subscript.

Replace the comment in the previous step with code that creates an instance of the ContosoAdSvc proxy class, calls the Web service’s FindAds method using the txtFind text box value as an argument, and then exits the subroutine if the FindAds method returns an empty array or an empty first element. Here’s the required code:

cadSvc = New ContosoAdSvc
strAdIds = cadSvc.FindAds(txtFind.Value)
If (Ubound(strAdIds) < 0 ) OrElse (strAdIds(0) = "") Then
  litErrMsg.Text = "No matches found."
  Exit Sub
End If

Next, create a new data table object. Name the table it contains ads and store a pointer to the new object in the dtbAds variable. Add columns named AdId, Headline, Description, and AskingPrice. The first three columns will contain strings, and the AskingPrice column will contain a Decimal value. Here’s the required code:

dtbAds = New DataTable("ads")
dtbAds.Columns.Add("AdId", _
      Type.GetType("System.String"))
dtbAds.Columns.Add("Headline", _
      Type.GetType("System.String"))
dtbAds.Columns.Add("Description", _
      Type.GetType("System.String"))
dtbAds.Columns.Add("AskingPrice", _
      Type.GetType("System.Decimal"))

Follow the code from the previous step with a loop that iterates through each element in the strAdIds array. Here’s the code:

For intPos = 0 to Ubound(strAdIds)
' Code to process each ad ID will go here
Next

For each ad ID in the strAdIds array, convert the ID to an integer and use it to call the Web service’s GetInfo method. If the Web service sets the Found property to True, create a new row for the rowAds data table, set the new row’s AdId property to the current ad ID value, and set its Headline, Description, and AskingPrice rows to the values from the Web service’s GetInfo method. Finally, add the new row to the dtbAds data table. To do all this, replace the comment in step 9 with the code shown here:

For intPos = 0 to Ubound(strAdIds)
  intAdId = Cint(strAdIds(intPos))
  If cadSvc.GetInfo(intAdId).Found Then
    rowAds = dtbAds.NewRow()
    rowAds("AdId") = intAdId
    rowAds("Headline") = _
                     cadSvc.GetInfo(intAdId).Headline
    rowAds("Description") = _
                     cadSvc.GetInfo(intAdId).Description
    rowAds("AskingPrice") = _
                     cadSvc.GetInfo(intAdId).AskingPrice
    dtbAds.Rows.Add(rowAds)
  End If
Next

Now that the data table contains all the required data, make the grdAds data grid visible, establish the dtbAds data table as the grid’s data source, and bind the data into the data grid. The statements in blue should follow the Next statement from step 9.

Next
grdAds.Visible = True
grdAds.DataSource = dtbAds
grdAds.DataBind

Save the file in the ch14c folder, giving it the name adclient.aspx.

Copy the banner.ascx file and the normal.css file (if you’re using it) from the ch13 folder to the ch14c folder.

Copy the images folder from the ch13 folder to the ch14c folder.

Load the adclient.aspx page into your browser, taking care to specify an HTTP URL. Enter a text string that exists within the description of at least one ad and then click the Submit button. You should receive a display similar to the one on page 497.