This code will not work if you have Hot Fix 2 installed.
Data table to query object – 3792 rows with 5 columns
~~about 12-13 Seconds
BACKGROUND: We are using .NET to call all appropriate data
into ADO DataSets to ensure our ability to persist data even if the
database is taken offline. We are using a CFGRID to display the
data. We are using flash remoting to call the component that
returns the query and then puts the query into the data provider
for the grid. The ColdFusion code and the C # Code are attached
below for a little better clarification of our problem and initial
solution.
Initially we tried to use autoconversion and were getting
nothing due to timeout errors. We then turned off autoconversion
and started explicitly calling dotNetToCFType(dataTable) with the
same result. We then moved the entire application to a much more
robust server and started getting results. On the new server, this
process was taking approximately 49000 ms to return. We also
encountered errors when we allowed .net to build the datatable and
return it to us, so we started building the .net datatable in
Coldfusion and allowing the dll to fill the table for us. In our
actual model, it is several dll’s that manage a lot more than
just the datagrid, so where you see discrepancies in variable
passing we missed something in the code rewrite for you.
Total Time Avg Time Template
12563 ms 12563 ms C:\Inetpub\wwwroot\Portal\F1.cfm
12500 ms 12500 ms CFC[
C:\Inetpub\wwwroot\Portal\personnel\personnel.cfc | getList2() ]
from C:\Inetpub\wwwroot\Portal\personnel\personnel.cfc
0 ms 0 ms CFC[ C:\Inetpub\wwwroot\Portal\application.cfc |
onRequestEnd(/portal/F1.cfm) ] from
C:\Inetpub\wwwroot\Portal\application.cfc
0 ms 0 ms CFC[ C:\Inetpub\wwwroot\Portal\application.cfc |
onRequestStart(/portal/F1.cfm) ] from
C:\Inetpub\wwwroot\Portal\application.cfc
0 ms STARTUP, PARSING, COMPILING, LOADING, & SHUTDOWN
12563 ms TOTAL EXECUTION TIME
COLDFUSION CODE
APPLICATION.strportallib =
“c:\windows\microsoft.net\framework\v2.0.50727\system.data.dll,C:\Inetpub\wwwroot\Portal\Bin\PortalInterfaces.dll,C:\Inetpub\wwwroot\Portal\Bin\PortalCFInterop.dll”
<cffunction name="getGridItems" returntype="array"
access="remote">
<!--- format = [name , Header Text, Display(y/n)] --->
<cfset gridItems = arrayNew(2)>
<cfset gridItems[1] = ["nIndex", "Record Index",
"no"]>
<cfset gridItems[2] = ["strLastName", "Last Name",
"yes"]>
<cfset gridItems[3] = ["strFirstName", "First Name",
"yes"]>
<cfset gridItems[4] = ["strMiddleName", "Middle Name",
"yes"]>
<cfset gridItems[5] = ["strSocialSecurityNumber", "Social
Security Number", "yes"]>
<cfreturn gridItems>
</cffunction>
<cffunction name="getList" returntype="query"
access="remote">
<!--- By declaring the variables with keyword var, the
function will free up the memory that was being used for these
items and eliminate data persistence through caching. --->
<cfset var gridItems = "">
<cfset var dtPersonnel = "">
<cfset var queryPersonnel = "">
<cfset var nFormID = "">
<cfset var dtSelectTemp = "">
<cfset var newRow = "">
<cfset var arCellValue = "">
<!--- We have to make a call to get the list of grid
Items for this portlet. --->
<cfset gridItems = getGridItems()>
<!--- We have to instantiate the datatable that will be
passed to the dll to be filled. --->
<cfset dtPersonnel =
createObject(".net","System.Data.DataTable","#APPLICATION.strPortalLib#").init()>
<!--- We have to instantiate the Coldfusion query object
that will be filled by the datatable methods. --->
<cfset queryPersonnel = queryNew("gPersonnelGUID,
strLastName, strFirstName, strMiddleName,
strSocialSecurityNumber")>
<!--- This call fills the table based on the users portal
Token. See C# code below. Once the process is finalized, all uses
of the returned datatable will be in try-catch/if-then blocks to
handle empty datasets. --->
<cfset nFormID =
APPLICATION.ifcPortal.GetFormID(dtPersonnel)>
<!--- This extracts all rows from the datatable into an
array. --->
<cfset dtSelectTemp = dtPersonnel.Select()>
<!--- This command prepares the query to be filled.
--->
<cfset newRow = queryAddRow(queryPersonnel,
#arrayLen(dtSelectTemp)# )>
<!--- This set of commands loops through the Row array.
--->
<cfloop from="1" to="#arrayLen(dtSelectTemp)#"
index="i">
<!--- This gets all the column data for one row. --->
<cfset arCellValue = dtSelectTemp
.Get_ItemArray()>
<!--- This set of commands loops through the column
array. --->
<cfloop from="1" to="#arrayLen(arCellValue)#" index="x"
>
<!--- This set of commands Writes the values to the
correct column and row in the array. --->
<cfset QuerySetCell(queryPersonnel, "#gridItems[1]#",
"#arCellValue.stringValue()#", i)>
</cfloop>
</cfloop>
<!--- This returns the coldfusion query object. --->
<cfreturn queryPersonnel>
</cffunction>
C # Code
public bool GetGridData( DataTable tableGridData )
{
if( tableGridData.Columns.Count != 0 )
{
return false;
}
// We can't pass the CF-provided Table but can fill it
// using its member methods; create a temp table for
// library call, then fill the destination table before
return
DataTable tableTempGrid;
try
{
tableTempGrid = new DataTable( );
}
catch(Exception exceptNew )
{
return false;
}
if( tableTempGrid == null )
{
return false;
}
// This will fill the DataTable based on session context
if( !m_mgrData.FillGrid( ref tableTempGrid ) )
{
return false;
}
int nRows = tableTempGrid.Rows.Count;
if( nRows < 1 )
{
m_strLastError = "GetGridData received NULL Results
DataTable";
return false;
}
// We now have a source table to copy
// Fill provided object
tableGridData.TableName = "PersonnelGrid";
tableGridData.Columns.Add( "nIndex" );
tableGridData.Columns.Add( "strLastName" );
tableGridData.Columns.Add( "strFirstName" );
tableGridData.Columns.Add( "strMiddleName" );
tableGridData.Columns.Add( "strSocialSecurityNumber" );
for( int i = 0; i < nRows; i++ )
{
DataRow rowNew = tableGridData.NewRow( );
DataRow rowSource = tableTempGrid.Rows[ i ];
rowNew[ "nIndex" ] = i;
rowNew[ "strLastName" ] = rowSource[ "strLastName" ];
rowNew[ "strFirstName" ] = rowSource[ "strFirstName" ];
rowNew[ "strMiddleName" ] = rowSource[ "strMiddleName" ];
rowNew[ "strSocialSecurityNumber" ]
= rowSource[ "strSocialSecurityNumber" ];
tableGridData.Rows.Add( rowNew );
}
// If we get here, we're golden!
return true;
}
1
Reply
AdChoices