.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

 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;     }   } } 

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.

 - <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

 <!-- 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> 

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).

Listing 25.5. NameDump.cfm

[View full width]

 <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#" /> 

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).

Figure 25.7. Dump of methods returned from Web service.

We must return the array of MessageElement objects by utilizing the get_any() method. Wrapping the xmlParse function around each array reference variable will expose the XML associated with the Schema and DiffGram data container. The DiffGram is a Microsoft proprietary format primarily used by the .NET Framework to serialize the contents of a DataSet.

Now that our references are in place, they are output in the example code to display their contents with <cfdump>. The results of the call to the UDF are also displayed. Figure 25.8 shows a snapshot of the collapsed structures and their descriptions.

Figure 25.8. Dump of result returned from UDF.

The chicken came before the egg somewhat in this last scenario, and you are probably asking yourself how these references are used in the UDF to create a structure of queries. Notice how the <cffunction> convertDotNetDataset is used to parse the return from the call to the Web service described earlier. A Structure is created, along with the references to the resulting XML, and the query objects are created and populated with just a few lines of code. Observe the use of xmlChildren and xmlAttributes to reference the returned columns and related data through the use of a nested loop.