Adding Function Methods to a Web Service

Most often, your Web services will need to return values to the consuming applications. You return values from your Web services through Functions. This technique shows you how to include Functions in your Web service.

IT code added for this technique adds three Functions to the Web service. Two of those Functions return specific values for a tip and the third returns an HTML table that displays all the tips.

The first Function returns an HTML table that contains the titles for all the tips. The titles are displayed as links, with the intention that the developer would have another page that would display the text of the tip when the link is clicked.

That Function has this definition:

< WebMethod() > Public Function GetTipTable (RedirectPage as String, _ QueryStringVaraibleName as String) as String Dim DBConn as OleDbConnection Dim DBCommand As OleDbDataAdapter Dim DSData as New DataSet D im I as Long BConn = New OleDbConnection("PROVIDER=" _ & "Microsoft.Jet.OLEDB.4.0;" & DATA SOURCE=" & "c:/inetpub/wwwroot/TT/C14/" _ & "service/TipsDB.mdb;") DBCommand = New OleDbDataAdapter _ ("Select TipID, TipTitle From Tips " & "Order by TipTitle", DBConn) D BCommand.Fill(DSData, _ "AllTips") GetTipTable = "<Table>" F or I = 0 To DSData.Tables("AllTips").Rows.Count - 1 GetTipTable = GetTipTable _ & "<TR><TD>RedirectPage & "?" _ & QueryStringVaraibleName & "=" _ & DSData.Tables("AllTips"). Rows(I).Item("TipID") _ & """>" & DSData.Tables("AllTips"). Rows(I).Item("TipTitle") _ & "TD>TR>" ext etTipTable = GetTipTable & "Table >" function

ice that two parameters need to be passed into this procedure:

Method()> Public Function GetTipTable _ RedirectPage as String, _ ueryStringVaraibleName as String) as String

The first parameter is the path to the page that visitors should be taken to when they click on one of the title links. The second parameter is the name of the variable that the developer would like passed to that page when visitors click on the link. That variable is passed in the form of a query string.

In this procedure you will need these data variables:

Dim DBConn as OleDbConnection Dim DBCommand As OleDbDataAdapter Dim D SData as New DataSet You will also need this variable for a For code block: Dim I as Long You start by connecting to the Access database: DBConn = New OleDbConnection("PROVIDER=" _ & "Microsoft.Jet.OLEDB.4.0;" _ & DATA SOURCE=" & "c:/inetpub/wwwroot/TT/C14/" _ & "service/TipsDB.mdb;")

and you retrieve all the IDs and titles of the tips:

DBCommand = New OleDbDataAdapter _ ("Select TipID, TipTitle From Tips " _ & "Order by TipTitle", DBConn) DBCommand.Fill(DSData, _ "AllTips")

You return a value from the Function by assigning a value to the Function name. Here, it is initially set to the opening HTML Table tag:

GetTipTable = "< Table >"

You then start a loop, so that you can process each of the records that have been returned:

For I = 0 To DSData.Tables("AllTips").Rows.Count - 1

Within the loop, you append to the return value the title of the tip through an HTML Anchor tag:

GetTipTable = GetTipTable _ & "< TR >< TD >& RedirectPage & "?" _ & QueryStringVaraibleName & "=" & DSData.Tables("AllTips"). _ Rows(I).Item("TipID") _ & """>" & DSData.Tables("AllTips"). _ Rows(I).Item("TipTitle") _ & "<>< TR >"

Notice that the Anchor tag links to the page name passed in through the RedirectPage parameter and that the ID of the tip is passed into that page through the link's query string.

You then move on to process the next record:

Next

After the loop, you append the closing Table tag to the return value:

GetTipTable = GetTipTable & "< / Table >"

The second Function defined in this Web service would be used on a page that displays the contents of a tip. It returns the text of the tip's title:

< WebMethod() > Public Function GetTipTitle _ (TipID as Long) as String Dim DBConn as OleDbConnection Dim DBCommand As OleDbDataAdapter Dim DSData as New DataSet DBConn = New OleDbConnection("PROVIDER=" _ & "Microsoft.Jet.OLEDB.4.0;" _ & DATA SOURCE=" & "c:/inetpub/wwwroot/TT/C14/" & "service/TipsDB.mdb;") DBCommand = New OleDbDataAdapter ("Select TipTitle From Tips Where " & "TipID = " & TipID, DBConn) DBCommand.Fill(DSData, "CurrentTip") If DSData.Tables("CurrentTip").Rows.Count = 0 Then GetTipTitle = "" Else GetTipTitle = DSData.Tables("CurrentTip"). _ Rows(0).Item("TipTitle") End If End Function

Passed into the method through a parameter is the ID of the tip, whose title is to be returned:

(WebMethod()> Public Function GetTipTitle (TipID as Long) as String

You will need these data objects:

Dim DBConn as OleDbConnection Dim DBCommand As OleDbDataAdapter Dim DSData as New DataSet

You start by connecting to the database:

DBConn = New OleDbConnection("PROVIDER=" & "Microsoft.Jet.OLEDB.4.0;" "DATA SOURCE=" & "c:/inetpub/wwwroot/TT/C14/" _ & "service/TipsDB.mdb;")

and you retrieve from the database the title of the tip:

DBCommand = New OleDbDataAdapter _ ("Select TipTitle From Tips Where " & "TipID = " & TipID, DBConn) DBCommand.Fill(DSData, _ "CurrentTip")

You then check to see if a record was found based on the ID that was passed in: If DSData.Tables("CurrentTip").Rows.Count = 0 Then

If a record wasn't found, you return an empty string:

GetTipTitle = ""

Otherwise, the title of the tip is returned:

GetTipTitle = DSData.Tables("CurrentTip"). _ Rows(0).Item("TipTitle")

The other Function in this Web service returns the text of a tip, based on the ID of the tip passed That tip has this definition:

< WebMethod() > Public Function GetTipText _ (TipID as Long) as String Dim DBConn as OleDbConnection Dim DBCommand As OleDbDataAdapter Dim DSData as New DataSet DBConn = New OleDbConnection("PROVIDER=" & "Microsoft.Jet.OLEDB.4.0;" & "DATA SOURCE=" & "c:/inetpub/wwwroot/TT/C14/" _ & "service/TipsDB.mdb;") DBCommand = New OleDbDataAdapter ("Select TipText From Tips Where " & "TipID = " & TipID, DBConn) DBCommand.Fill(DSData, _ "CurrentTip") If DSData.Tables("CurrentTip").Rows.Count = 0 Then GetTipText = "" Else GetTipText = DSData.Tables("CurrentTip"). Rows(0).Item("TipText") End If End Function

Within this procedure, you connect to the Access database and attempt to retrieve the text of the tip. If a record with the ID passed in is not found, an empty string is returned. Otherwise, the text of the tip is returned.