Paging and caching large record sets Support

Today we look at viewing large record sets, and how to optimise this process both for the person doing the viewing and for the server processing the page. To handle the former, we'll use a technique known as paging, and for the latter, caching. This caching is not the client side browser caching we've all come to know and hate; this caching is done entirely on the server.

 

The code supplied is half the length of the Dreamweaver-generated code, and is hugely beneficial if you have a site where users look through large lists of data without amending it.

Advertisement DMXzone Paginator ASP

Add the coolest page navigation to your site and make browsing through large lists of items or tables easy and quick. Choose from 22 different styles, used by many sites such as Digg, Yahoo, Flickr and much more, to fit perfectly with your design and display the total number of items.

All paging styles are fully CSS based, so you can always fine tune the colors and borders to your site design.

 

 

3b. Retrieve and cache data from database

If the cache is nonexistent or invalid, we create our recordset as before.

    var ordersRS = Server.CreateObject("ADODB.Recordset");
     ordersRS.ActiveConnection = MM_PagingSample_STRING;
     ordersRS.Source = "SELECT OrderDate, Client, Value, Products FROM Orders ORDER BY OrderDate ASC";
     ordersRS.CursorType = 0;
     ordersRS.CursorLocation = 2;
     ordersRS.LockType = 1;
     ordersRS.Open();

We then define an empty array (or numbered list) container and loop through the recordset:

    orders = new Array();
     while ( !ordersRS.EOF ) {

This next bit of code displays JavaScript's amazing flexibility when it comes to representing data. We are able to define arbitrary containers (objects) and assign values to named properties of these objects. Using the formatting functions defined above for the Order Date and Value columns, and String for the rest, we set named properties for the order object.

     order = new Object();
     order.OrderDate = FormatShortDate( new Date( ordersRS( "OrderDate" ) ) );
     order.Client = String( ordersRS( "Client" ) );
     order.Value = FormatCurrency( parseFloat( ordersRS( "Value" ) ) );
     order.Products = String( ordersRS( "Products" ) );

Doing this is vitally important! If we simply assigned from the recordset column directly to the object property, it would attempt to store a reference to that particular row/column combination in the recordset object. This means that once we've closed the recordset, that reference will be broken, which is not a good thing for a caching implementation!

We then use the Array.push() method to add the new order object to the end of the array, and move to the next record in the recordset. That finalises the loop. After the loop, now that we have transformed all the data, we close the recordset, and add a reference to the array to the Session:

     orders.push( order );
     ordersRS.moveNext;
     }
     ordersRS.Close();
     Session( "Orders" ) = orders;

And caching is done! Next, we have to determine whether or not we need to page this data.