Macromedia Dreamweaver 8 UNLEASHED [Electronic resources] نسخه متنی

Web Service and Database Interaction

So far, you've learned to use web services to return simple calculations as strings. 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 task lies in the fact that you can eliminate the arduous task of repeatedly making database calls through your various dynamic pages by allowing the web service to do all the work.

In this section, we'll build a web service that pulls data from the x_CompanyEvents table contained in the Dorknozzle database. Unlike previous sections where we used an ASP.NET web service to do all the work by exposing a familiar interface to both ASP.NET and ColdFusion applications, in this section you'll learn how to create two different types of web services using Dreamweaver. First, you'll create an ASP.NET web service that pulls data from the x_CompanyEvents table and returns the results to the ASP.NET application in the form of a DataSet; second, you'll create a ColdFusion component (CFC) that pulls data from the x_CompanyEvents table, exposes it as a web service, and then consumes the data using a ColdFusion web application.

The Company Events Web Service Using ASP.NET

One of the most flexible interfaces for exposing web services is ASP.NET. Using either VB.NET or C#, a developer can easily connect and issue a command to a database, fill a DataSet, and then return the results to an ASP.NET web application. The ASP.NET web application, accepting a DataSet, needs only to bind the results to a DataGrid. While we'll certainly get to that functionality in the next section, for now, let's focus on developing the web service. You can begin by following these steps:

<%@ Webservice %>
Imports System.Web.Services
Imports System.Data
Imports System.Data.OleDb
Public Class CompanyEventsService
Inherits System.Web.Services.WebService
Dim oConn As New OleDbConnection("Provider=Microsoft.Jet.OleDB.4.0;" & _
"Data Source=C:\Inetpub\wwwroot\DorknozzleASPX\Database\Dorknozzle.mdb")
Dim oDA As New OleDbDataAdapter("SELECT * FROM x_CompanyEvents", oConn)
Dim oDS As New DataSet
<WebMethod()> _
Public Function getCompanyEvents() As DataSet
oDA.Fill(oDS, "CompanyEvents")
Return oDS
End Function
End Class

Before we proceed any further, let's break down the code in hopes that you'll understand it better. Initially, we add the Webservice directive which defines the page as a web service and outlines the class as CompanyEventsService:

<%@ Webservice %>

Second, we import the necessary namespaces:

Imports System.Web.Services
Imports System.Data
Imports System.Data.OleDb

Importing these three namespaces gives our web service access to DataSet, OleDbConnection, OleDbCommand, and various other web service classes for use in our web service.

Next, we outline our class, inherit from the WebService class, instantiate a new instance of the OleDbConnection class, passing in the connection string as a parameter to the constructor, instantiate a new instance of the OleDbDataAdapter class, passing in the SQL statement and connection object as parameters into the constructor, and create a new instance of the DataSet class:

Public Class CompanyEventsService
Inherits System.Web.Services.WebService
Dim oConn As New OleDbConnection("Provider=Microsoft.Jet.OleDB.4.0;" & _
"Data Source=C:\Inetpub\wwwroot\DorknozzleASPX\Database\Dorknozzle.mdb")
Dim oDA As New OleDbDataAdapter("SELECT * FROM x_CompanyEvents", oConn)
Dim oDS As New DataSet

Next, we outline our web method, the public function named getCompanyEvents(), setting the return type as a DataSet, fill the DataSet, and return it:

<WebMethod()> _
Public Function getCompanyEvents() As DataSet
oDA.Fill(oDS, "CompanyEvents")
Return oDS
End Function

Lastly, we close the class by adding the following line:

End Class

To test the results, open a browser and enter the path

http://localhost/CompanyEventsService/companyeventsservice.asmx . Immediately, the web service testing application opens in the browser displaying the only web method, getCompanyEvents(), exposed by the web service.

Select the link. As occurred in the calculator example, you're redirected to a page that allows you to invoke the method. This time, however, because our method doesn't accept any parameters, text boxes don't appear. To see the results of the web service connecting to the database, issuing a command, and returning the results as a DataSet, click the Invoke button. Immediately, the results are returned similar to Figure 30.15.

It works! Our next step is to build the ASP.NET application that will consume the data.

NOTE

It's important to understand that the DataSet type is not a universal web service return type. DataSets are native to .NET and cannot be consumed in other server models such as ColdFusion, PHP, or even ASP. To make this universal, we'd have to return an array instead of a DataSet.

Consuming the Company Events Web Service in ASP.NET

Now that the web service has been created and we've tested it to make sure that it works, let's now build the ASP.NET web application that we'll use to consume the data that it returns. You can build the web application by following these steps:

<asp:DataGrid
id="dgCompanyEvents"
runat="server"
autogeneratecolumns="false">
<headerstyle Font-Bold="true"></headerstyle>
<columns>
<asp:BoundColumn 
DataField="Event"
HeaderText="Event" />
<asp:BoundColumn
DataField="Date"
HeaderText="Date" />
<asp:BoundColumn
DataField="Location"
HeaderText="Location" />
</columns>
</asp:DataGrid>

<script runat="server">
Sub Page_Load()
dim ces as new CompanyEventsService()
dgCompanyEvents.DataSource = ces.getCompanyEvents()
dgCompanyEvents.DataBind()
End Sub
</script>

Save your work and test the results in the browser by pressing F12. This time, the company events are pulled from the web service and presented within a DataGrid in the default.aspx page similar to Figure 30.18.

The Company Events Web Service Using ColdFusion and Components

As you have seen, creating web services in ASP.NET is fairly simple. The only downside to the ASP.NET server model is that you have to code the functionality by hand. Fortunately for you, there's a better way. Because Macromedia heavily promotes ColdFusion, creating web services using the ColdFusion server model is slightly more intuitive, and can be accomplished using the panels that appear in the Application panel group.

Creating a ColdFusion web service begins with ColdFusion Components or CFCs. The benefit to using CFCs is that they can be created as either standalone components or as publicly accessible web services. To explore this model further, follow these steps:

You're done! You've successfully created a ColdFusion web service. In the next section, we'll review the process of consuming the web service using a ColdFusion web application.

Consuming the Company Events Web Service in ColdFusion

With the web service created, you're now ready to add the code to the web application that will consume the data the web service exposes. To add this functionality, follow these steps:

<table width="100%" border="0" cellspacing="0" cellpadding="2">
<tr>
<td>Event</td>
<td>Date</td>
<td>Location</td>
</tr> 
<cfoutput query="aQuery">
<tr>
<td>#Event#</td>
<td>#Date#</td>
<td>#Location#</td>
</tr>
</cfoutput>
</table>

<cfinvoke webservice="http://localhost/
DorknozzleCFM/cfc/CompanyEvents.cfc?WSDL"
method="getCompanyEvents"
returnvariable="aQuery">
</cfinvoke>