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:

1.	Start by creating a new folder within C:\Inetpub\wwwroot called CompanyEventsService.
2.	In Dreamweaver, create a new document by choosing the New option from the File menu. Choose the ASP.NET VB option from the Dynamic Page category and click Create.
3.	Immediately save your file as companyeventsservice.asmx, saving it to the newly created folder C:\Inetpub\wwwroot\CompanyEventsService.
4.	Switch to Code view, remove the existing code, and replace it with the following code: <%@ 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
5.	Save your work.

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.

Figure 30.15. The company events are returned in XML format.

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:

1.	With the DorknozzleASPX site selected in the Site Files panel, open the default.aspx page.
2.	Open the Components panel by choosing the Components option from the Window menu.
3.	Click the Add Component (+) button and select the Add Using WSDL option from the submenu. The Add Using WSDL dialog appears.
4.	In the Add Using WSDL dialog box, enter the path http://localhost/CompanyEventsService/companyeventsservice.asmx?WSDL and click OK. The CompanyEventsService option appears in the Components panel list similar to Figure 30.16. Figure 30.16. The CompanyEventsService appears in the Components list.
5.	Select the CompanyEventsService.dll file that appears in your defined site's list and drag it into the Bin directory. You'll receive errors when you run the page if this step is not performed.
6.	We'll need to remove the existing company events table and replace it with a DataGrid. You can do this by selecting the company events table and pressing Delete.
7.	With your cursor still blinking in the cell, switch to Code view and add the DataGrid tag as follows: <asp:DataGrid 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>
8.	Scroll to the top of the page and enter a new code declaration block, a Page_Load() subroutine, and the functionality that binds the web service to the DataGrid as follows: <script runat="server"> Sub Page_Load() dim ces as new CompanyEventsService() dgCompanyEvents.DataSource = ces.getCompanyEvents() dgCompanyEvents.DataBind() End Sub </script> The result of the code addition resembles Figure 30.17. Figure 30.17. Add the code that binds the web service to the DataGrid.

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.

Figure 30.18. The company events data appears in the DataGrid.

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:

1.	Start by creating a new folder in C:\Inetpub\wwwroot\DorknozzleCFM called cfc.
2.	Open the index.cfm file.
3.	Open the Components panel by choosing the Components option from the Window menu.
4.	In the Components panel, select the CF Components option from the Components Type menu.
5.	Click the Add Component (+) button. The Create Component dialog appears.
6.	In the Name text box in the Component section, enter the value CompanyEvents. Browse to the C:\Inetpub\wwwroot\DorknozzleCFM\cfc folder in the Component Directory File field. The result appears similar to Figure 30.19. Figure 30.19. Give your component a name and a directory to save to.
7.	Switch to the Functions section. Click the Add Function (+) button to create a new function. Assign the value getCompanyEvents in the Name text box, select the remote option from the Access menu, and choose the query option from the Return Type menu. The results appear similar to Figure 30.20. Figure 30.20. Give your function a name, an Access type, and a Return type.
8.	Click OK. The dialog closes and the CompanyEvents.cfc file opens complete with the necessary code to get you started (see Figure 30.21). Figure 30.21. The new CompanyEvents.cfc file opens complete with the necessary code to get you started.
9.	Switch to the Bindings panel and select the Recordset option from the Add (+) menu. The Recordset dialog appears.
10.	Enter the name rsCE in the Name text box.
11.	Make sure that the getCompanyEvents function is selected from the Function menu.
12.	Choose the connDorknozzle option from the Data Source menu.
13.	Choose the x_CompanyEvents option from the Table menu.
14.	Click the Selected radio button from the Columns radio button group and highlight only the Event, Date, and Location fields.
15.	Click OK. Again, the necessary code is added to the CompanyEvents.cfc file similar to Figure 30.22. Figure 30.22. The appropriate code is added to the CompanyEvents.cfc file.

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:

1.	Open the index.cfm page if it's not already open.
2.	Select the existing company events table and delete it.
3.	With your cursor still highlighted in the cell, switch to Code view and add the following code: <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>
4.	Scroll to the top of the page and place your cursor at the top-leftmost position on the page. From the Components panel, expand the CompanyEventsService node, and click, hold, and drag out the query getCompanyEvents function, placing it into the position where your cursor was. The web service call is added as follows: <cfinvoke webservice="http://localhost/ DorknozzleCFM/cfc/CompanyEvents.cfc?WSDL" method="getCompanyEvents" returnvariable="aQuery"> </cfinvoke>
5.	Save your work.

To test the functionality in the browser, press F12. As you can see from Figure 30.23, the company events data is presented in the table.

Figure 30.23. The company events data appears in the table.