• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 29. XML Web Services > Web Service and Database Interaction

Web Service and Database Interaction

So far you've learned to use Web services to return simple calculations as strings. You've also learned to use third-party Web services to program to your benefit. This section goes beyond the traditional Web services paradigm and teaches you how to programmatically access database data through Web services. The beauty in this lies in the fact that you are able to eliminate the arduous task of repeatedly making database calls through your client application by allowing the Web service to do all the work. You will also learn how to force the Web method within the Web service to return a DataSet that you can program against on the client. To begin creating the Web service, follow the steps outlined next:

Open a new ASP.NET Visual Basic document in Dreamweaver and switch to code view. Immediately save your page as selectQuantity.asmx.

Write the following code:

<%@ WebService class="selectQuantity" %>
Imports System.Web.Services
Imports System.Data.OleDb
Public Class selectQuantity
Inherits System.Web.Services.WebService

Now create the new WebMethod and public function named selectQuantity as a DataSet. Pass in the ProductID as an Integer:

<WebMethod()> Public Function selectQuantity(ProductID As Integer)
     As System.Data.DataSet

Next, create a new DataSet, Connection, and DataAdapter object:

Dim dsQuantity As New System.Data.DataSet()
Dim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Dim objDA As OleDbDataAdapter

Now define the SQL statement, selecting the product from the database where the ProductID is equal to the ProductID that you are passing in as a parameter:

Dim strCmd As String = "SELECT * FROM Inventory
WHERE ProductID=" & ProductID

Create the new DataAdapter, passing in the SQL statement and connection object, and fill the DataAdapter:

objDA = New OleDbDataAdapter(strCmd, objConn)
objDA.Fill(dsQuantity, "Quantity")

Return the DataSet, end the function, and the class:

Return dsQuantity
End Function
End Class

The result is shown in Figure 29.24.

Figure 29.24. Define the new method for your Web service. This method accesses the database and returns a result as a DataSet object.

Save your work.

Now open the command prompt and use the CD command to switch to the folder where your .ASMX file is located. The result is shown in Figure 29.25.

Figure 29.25. Use the CD command to switch to the folder that contains the .ASMX file.

Type the following line to generate the new Visual Basic class file:

wsdl /l:vb http://localhost/WebStore/selectQuantity.asmx?WSDL

The result is shown in Figure 29.26.

Figure 29.26. Generate the new Visual Basic class file.

Now compile the class into an assembly .DLL by typing in the following code:

vbc /out:selectQuantity.dll /t:library /r:system.Web.dll,
services.dll,system.data.dll selectQuantity.vb

The result is shown in Figure 29.27.

Figure 29.27. Generate the new assembly.

Close the Command window and navigate to the folder where your files are located. Move the .DLL file into the Bin directory.

In Dreamweaver, create a new ASP.NET Visual Basic file and save it as selectQuantity.aspx.

Create a new form and populate the form with some Web controls as follows:

<form runat="server">
Check for stock on an item by its ID:<br>
<asp:textbox id="txtQuantity" runat="server" /><br>
<asp:button id="btnSubmit" runat="server"
OnClick="returnQuantity" Text="Check Quantity" />
<asp:label id="lblWeHave" runat="server" Visible="false"
text="We currently have the following amount in stock: " />
<asp:label id="lblResult" runat="server" Visible="false" />

The result will look similar to Figure 29.28.

Figure 29.28. Add some Web controls to the page so that the user can interact.

Next, add the proxy class from the Components panel by selecting Add Using Proxy Classes. Find the selectQuantity.dll within the Bin directory and click Open. Select OK. The new Web service will be added to the Components panel as shown in Figure 29.29.

Figure 29.29. Add the new Web service to the Components panel.

Now add the code as a subroutine that will pass the ProductID into the Web service as follows:

<script runat="server">
Sub returnQuantity(s As Object, e As EventArgs)

Create the new Web service object:

Dim aselectQuantity as new AppSettings.selectQuantity()

Create the new DataSet object, setting it equal to the result of the selectQuantity method:

Dim aDataSet as System.Data.DataSet =

Make the label controls visible:

lblWeHave.Visible = True
lblresult.Visible = True

Now make the text property of the result label equal to the first row of the Quantity item. This item is located within the quantity table of the DataSet:

lblResult.Text = aDataSet.Tables("Quantity").Rows(0).Item("Quantity")
End Sub

You can also instantiate the new service and method by dragging it in from the Components panel. It is important to note that this subroutine is being called by the button Web control. The result will look similar to Figure 29.30.

Figure 29.30. Add the code that interacts with the Web service.

Save your work and test the result in the browser.



Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint