Author: Steven Neiland
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.

1 2 3

Related Blog Postings

What Do You Think?

Reader Comments

Post a Comment

Comment Etiquette:

  • Please keep comments on-topic.
  • Please do not post unrelated questions or large chunks of code.
  • Please do not engage in flaming/abusive behaviour.
  • Comments that contain or appear to be advertisments, will not be published.
  • Comments that appear to be created for the purpose of linkbuilding to commercial sites will be removed.

We are all adults here so play nice.

*
*



Archives Blog Listing

Tag Listing

Learn CF In A Week

Treehouse

 
Fork me on GitHub