Published:
Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.
Putting It All Together
Now that we have all the pieces lets put them together.
<cfcomponent>
<cffunction name="calcPageRows" access="public" output="false" returntype="struct">
<cfargument name="page" type="numeric" required="true">
<cfargument name="pagesize" type="numeric" required="true">
<cfset var rowStruct = structNew()>
<cfset rowStruct.startRow = (arguments.page * arguments.pageSize)-(arguments.pageSize - 1)>
<cfset rowStruct.endRow = (arguments.page * arguments.pageSize)>
<cfreturn rowStruct />
</cffunction>
<cffunction name="queryToCFGrid" access="public" output="false" returntype="struct">
<cfargument name="theQuery" required="true" type="query">
<cfset var returnStruct = structNew()>
<cfset returnStruct.totalRowCount = theQuery.totalRows>
<cfset returnStruct.query = theQuery>
<cfreturn returnStruct />
</cffunction>
<cffunction name="getUsersForCFGrid" access="remote" output="false" returntype="struct">
<cfargument name="page" type="numeric" required="true">
<cfargument name="pagesize" type="numeric" required="true">
<cfargument name="sortCol" type="string" required="true" default="">
<cfargument name="sortDir" type="string" required="true" default="">
<cfset var returnQuery = "">
<cfset var rowLimits = calcPageRows(arguments.page,arguments.pagesize)>
<cfstoredproc procedure="dbo.getUsers_paged" datasource="#request.dsn#">
<cfprocparam cfsqltype="cf_sql_varchar" value="#arguments.sortCol#">
<cfprocparam cfsqltype="cf_sql_varchar" value="#arguments.sortDir#">
<cfprocparam cfsqltype="cf_sql_integer" value="#rowLimits.startRow#">
<cfprocparam cfsqltype="cf_sql_integer" value="#rowLimits.endRow#">
<cfprocresult name="returnQuery">
</cfstoredproc>
<cfreturn queryToCFGrid(returnQuery)>
</cffunction>
</cfcomponent>
So there you have it, a more efficient way of doing pagination with ColdFusion and databases. I know it may seem like a lot of work to do considering how easy ColdFusion makes paging, but really all you have to do is write the paging sql as I'm giving you the code for the helper methods here.
Reader Comments