.NET Web Service (Returning a Complex Type)
Passing simple types between .NET and ColdFusion is useful, but in most real-world situations complex types provide benefits that seasoned ColdFusion developers have become accustomed tosuch as querying a database and displaying results with a minimal amount of code. Whether you're a seasoned ColdFusion developer or not, the next example will have you retrieving .NET DataSet objects into ColdFusion and sending ColdFusion queries to .NET.In this first example of a complex-type scenario, we will be querying a Microsoft Access database and storing the result in an ADO.NET DataSet. A DataSet object in its most complex form stores a memory-resident representation of a relational database; that is, a collection of tables including queried data from various data stores. DataSets work very similarly to storing multiple ColdFusion queries inside of a Structure. Our example will not be enforcing any referential integrity within the DataSet. We will be using the DataSet object to store two result sets from two different SQL SELECT statements. The end result of this example will be a ColdFusion Structure containing multiple queries. We will achieve this by parsing the resulting XML from the .NET Web service and storing the data as such.By analyzing the NameService.asmx.cs (Listing 25.3) code, we notice that there is one method defined: GetNames.
Listing 25.3. NameService.asmx.cs
The method GetNames accepts two string parameters, returning an object of type DataSet. This is clear-cut when analyzing the C# code. The waters muddy a bit when analyzing the WSDL that is generated from the service. Go to http://localhost/WebServices1/NameService.asmx?wsdl in the browser and analyze the resulting xml output. The key ingredient to look for in the WSDL XML is the <wsdl:operation> or <operation> element. This element defines the functions (methods) within the Web service. The <wsdl:message> or <message> element defines the input and output details. See Listing 25.4 for snippets of the WSDL generated for the NameService.asmx.
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;
using System.Web;
using System.Web.Services;
using System.Web.UI.WebControls;
namespace WebServices1
{
public class NameService : System.Web.Services.WebService
{
private DataSet namesDataSet; //Declare the Dataset object
//NameService class constructor
public NameService()
{
//CODEGEN: This call is required by the ASP.NET Web Services Designer
InitializeComponent();
}
#region Component Designer generated code
//Required by the Web Services Designer
private IContainer components = null;
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
}
/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose( bool disposing )
{
if(disposing && components != null)
{
components.Dispose();
}
base.Dispose(disposing);
}
#endregion
// The GetNames() example service returns a DataSet of names
[WebMethod]
public DataSet GetNames(string sFilter1, string sFilter2)
{
//Assign a SQL statement with wildcard filter to string variables
string sFilterWild1 = sFilter1 + "%'";
string sFilterWild2 = sFilter2 + "%'";
string sqlStr = "SELECT * FROM [names] "
+ "WHERE name LIKE '";
string sqlStr1 = sqlStr + sFilterWild1;
string sqlStr2 = sqlStr + sFilterWild2;
//Create an OleDbConnection object
OleDbConnection namesConn = new OleDbConnection(@"Provider="
+ @"Microsoft.Jet.OLEDB.4.0; Data Source=C:\Inetpub\wwwroot\"
+ @"WebServices1\Names.mdb;");
//Create a DataSet object
namesDataSet = new DataSet("TheDataSet");
try
{
namesConn.Open();
//Using the OleDbDataAdapter execute the query
OleDbDataAdapter namesAdapter = new OleDbDataAdapter();
//Define the command
namesAdapter.SelectCommand = new OleDbCommand(sqlStr1,namesConn);
//Add the Table 'names'to the dataset
namesAdapter.Fill(namesDataSet,"TheDataSet");
//Add second command result
namesAdapter.SelectCommand = new OleDbCommand(sqlStr2,namesConn);
namesAdapter.Fill(namesDataSet,"TheDataSet");
namesAdapter.Dispose();
}
catch(Exception e)
{
Debug.WriteLine("Error in connecting! "+e.ToString(), "Error");
}
finally
{
//Close the OleDbConnection
namesConn.Close() ;
namesConn.Dispose();
}
return namesDataSet;
}
}
}
- <portType name="NameServiceHttpGet">
- <operation name="GetNames">
<input message="s0:GetNamesHttpGetIn" />
<output message="s0:GetNamesHttpGetOut" />
</operation>
</portType>
- <message name="GetNamesHttpGetIn">
<part name="sFilter1" type="s:string" />
<part name="sFilter2" type="s:string" />
</message>
- <message name="GetNamesHttpGetOut">
<part name="Body" element="s0:DataSet" />
</message>
Listing 25.4. List of methods in NameService.asmx
A ColdFusion user-defined function (UDF) was written to encapsulate the code required to consume a .NET DataSet returned from NameService.asmx (see Listing 25.5).
<!-- List of functions (methods) -->
<wsdl:portType name="NameServiceHttpGet">
<wsdl:operation name="GetNames">
<wsdl:input message="tns:GetNamesHttpGetIn" />
<wsdl:output message="tns:GetNamesHttpGetOut" />
</wsdl:operation>
</wsdl:portType>
<!-- List of input parameters -->
<wsdl:message name="GetNamesHttpGetIn">
<wsdl:part name="sFilter1" type="s:string" />
<wsdl:part name="sFilter2" type="s:string" />
</wsdl:message>
<!-- Output definition -->
<wsdl:message name="GetNamesHttpGetOut">
<wsdl:part name="Body" element="tns:DataSet" />
</wsdl:message>
Listing 25.5. NameDump.cfm
[View full width]
First let's focus on the code listed below the UDF. We initially need to define the two arguments to pass to GetNames. This is done by creating a Structure to hold the two arguments. Next, the <cfinvoke> tag is used to define the webservice, method, argumentcollection, and returnvariable associated with the Web service. A <cfdump> is used to display the result, containing the array of methods returned by the call to the Web service (Figure 25.7).
<cffunction name="convertDotNetDataset"
returnType="struct">
<cfargument name="dataset" required="true">
<!--- Local Variables --->
<cfset var result = structNew() />
<cfset var aDataset = dataset.get_any() />
<cfset var xSchema = xmlParse(aDataset[1]) />
<cfset var xTables = xSchema["xs:schema"]
["xs:element"]["xs:complexType"]["xs:choice"] />
<cfset var xData = xmlParse(aDataset[2]) />
<cfset var xRows = xData["diffgr:diffgram"]
["TheDataSet"] />
<cfset var tableName = " />
<cfset var thisRow = " />
<cfset var i = " />
<cfset var j = " />
<!--- Create Queries --->w
<cfloop from="1" to="#arrayLen(xTables.xmlChildren)#" index="i">
<cfset tableName = xTables.xmlChildren[i].xmlAttributes.name />
<cfset xColumns = xTables.xmlChildren[i].xmlChildren[1].xmlChildren[1].xmlChildren/>
<cfset result[tableName] = queryNew(") />
<cfloop from="1" to="#arrayLen(xColumns)#" index="j">
<cfset queryAddColumn(result[tableName], xColumns[j].xmlAttributes.name, arrayNew(1)) />
</cfloop>
</cfloop>
<!--- Populate Queries --->
<cfloop from="1" to="#arrayLen(xRows.xmlChildren)#" index="i">
<cfset thisRow = xRows.xmlChildren[i] />
<cfset tableName = thisRow.xmlName />
<cfset queryAddRow(result[tableName], 1) />
<cfloop from="1" to="#arrayLen(thisRow.xmlChildren)#" index="j">
<cfset querySetCell(result[tableName], thisRow.xmlChildren[j].xmlName, thisRow.xmlChildren[j].xmlText, result[tableName].recordCount) />
</cfloop>
</cfloop>
<cfreturn result>
</cffunction>
<!--- Create arguments for .NET Web Service(Web Method) --->
<cfset args = StructNew()>
<cfset args.sFilter1 = "j">
<cfset args.sFilter2 = "s">
<!--- Invoke the .NET Web Service(Web Method) --->
<cfinvoke webservice="http://localhost/WebServices1/NameService.asmx?wsdl"
method="GetNames"
argumentcollection="#args#"
returnvariable="result">
<cfset aDataset = result.get_any() />
<cfset xSchema = xmlParse(aDataset[1]) />
<cfset xData = xmlParse(aDataset[2]) />
<!--- Convert result to CF queries --->
<cfset result = convertDotNetDataset(result) />
<!--- Display --->
<cfdump var="#xData#" /><cfabort />
<cfdump var="#result#" />
Figure 25.7. Dump of methods returned from Web service.

Figure 25.8. Dump of result returned from UDF.
