DotNet DataSet To CF Structure of Queries

UPDATE: I don’t want to get comments or hate mail saying that I stole this code from Joe and called it my own. I give Joe credit and I even emailed him about it.

I have no idea why Joe Rinehart never submitted this little gem to CFLIB.org (probably because it takes 6 months for your submission to show up). Any who, this is function that he wrote a while back that will take a DotNet Dataset and convert it to a CF structure of queries. I corrected some bugs (it would bomb if there was no record returned) and updated it (it now automatically converts xs:dateTime columns to CF datetime) and submitted it to CFLib. Hopefully someday it will make it into a library there, but for the mean time you can download it below.

I just want to say that Joe Rinehart deserves all the credit for this one. Thanks Joe!!!

====================================================================

<!— convertDotNetDataset —>
<cffunction name=”convertDotNetDataset” access=”public” returnType=”struct” output=”false”
hint=”takes a .Net dataset and converts it to a CF structure of queries”>
<cfargument name=”dataset” required=”true”>
<cfset var Local = StructNew()>
<cfset Local.result = structNew() />
<cfset Local.aDataset = arguments.dataset.get_any() />
<cfset Local.xSchema = xmlParse(Local.aDataset[1]) />
<cfset Local.xData = xmlParse(Local.aDataset[2]) />
<!— keeps track of which columns are xs:dateTime (1999-02-16T00:00:00.0000000-05:00) columns, so we can convert them —>
<cfset Local.DTColumns = StructNew()>

<!— Create Queries —>
<cfset Local.xTables = Local.xSchema[“xs:schema”][“xs:element”][“xs:complexType”][“xs:choice”] />
<cfloop from=”1″ to=”#arrayLen(Local.xTables.xmlChildren)#” index=”Local.i”>
<cfset Local.tableName = Local.xTables.xmlChildren[Local.i].xmlAttributes.name />
<cfset Local.xColumns = Local.xTables.xmlChildren[Local.i].xmlChildren[1].xmlChildren[1].xmlChildren/>
<cfset Local.result[Local.tableName] = queryNew(“”) />
<cfloop from=”1″ to=”#arrayLen(Local.xColumns)#” index=”Local.j”>
<cfif Local.xColumns[Local.j].xmlAttributes.type EQ “xs:dateTime”>
<cfset StructInsert(Local.DTColumns, Local.xColumns[Local.j].xmlAttributes.name, “”)>
</cfif>
<cfset queryAddColumn(Local.result[Local.tableName], Local.xColumns[Local.j].xmlAttributes.name, arrayNew(1)) />
</cfloop>
</cfloop>

<!— see if there are any row of data, if not exit —>
<cfif NOT StructKeyExists(Local.xData[“diffgr:diffgram”], “NewDataSet”)>
<cfreturn Local.result>
</cfif>

<!— Populate Queries —>
<cfset Local.xRows = Local.xData[“diffgr:diffgram”][“NewDataSet”] />
<cfloop from=”1″ to=”#arrayLen(Local.xRows.xmlChildren)#” index=”Local.i”>
<cfset Local.thisRow = Local.xRows.xmlChildren[Local.i] />
<cfset Local.tableName = Local.thisRow.xmlName />
<cfset queryAddRow(Local.result[Local.tableName], 1) />
<cfloop from=”1″ to=”#arrayLen(Local.thisRow.xmlChildren)#” index=”Local.j”>
<cfset Local.RowData = Local.thisRow.xmlChildren[Local.j].xmlText>
<cfset Local.ColumnName = Local.thisRow.xmlChildren[Local.j].xmlName>
<!— if this column is a xs:dateTime column, convert —>
<cfif StructKeyExists(Local.DTColumns, Local.ColumnName)>
<cfset Local.RowData = ReReplace(Local.RowData, “(\d{4})-(\d{2})-(\d{2})T(\d{2}):(\d{2}):(\d{2,2}).*”, “\2/\3/\1 \4:\5:\6”)>
</cfif>
<cfset querySetCell(Local.result[Local.tableName], Local.ColumnName, Local.RowData, Local.result[Local.tableName].recordCount) />
</cfloop>
</cfloop>

<cfreturn Local.result>
</cffunction>