Paging and sorting in a .NetTiers custom stored procedure

If you use .NetTiers as an Application Framework, you’ll have used custom stored procedures in cases where the generic Entity Service Find methods do not offer enough granularity. Specifically, if you have the need to join across database tables, you’ll find that a custom stored procedure is certainly the way to go. However, one of the biggest challenges facing developers is how to develop custom stored procedures for .NetTiers that support paging and sorting.

I have struggled to get a stored procedure that would not just provide the paging and sorting we need, but also would work with .NetTiers so that the correct methods would be generated. So therefore I have put this together to help others.

When .NetTiers generates the output code, it looks for custom stored procedures, based on a given format, and attempts to work out whether the SQL code is returning a list of known objects, or simply a dataset.

Searching, sorting and paging in combination are usually going to imply the use of dynamic SQL queries, but .NetTiers cannot determine the output from dynamically created queries by parsing the SQL code. Therefore, what you’ll find, when you first attempt to create a custom stored procedure, is a new method that returns void. Hence, .NetTiers did not expect any output to be returned from the procedure.

Paging in SQL 2005 is well documented, and with the use of PageIndex, ROW_Number() and RowIndex, you can quite easily perform effective and efficent paging against a 2005 database. For paging we need to know which page we are on, how many results there are, and how many pages of data are possible, given the current search criteria. Most important of all, is the total rows returned. Without this value, the ASP.NET Grid paging or ObjectDataSource / EntityDataSource cannot know how many pages are required to be shown.

My example involves two database tables:

  1. Company: Contain company information
  2. Address: Contains address information

The two tables are linked by the [Company].AddressId to the [Address].AddressId.

Our task is to return a list of companies, by searching on the Address fields. Example:

Return all Companies where the [Address].City=’London’

I have put together a download to demonstrate .NetTiers Custom Stored Procedure Paging and Sorting

This includes table / procedure create scripts, plus an example query. You’ll have to populate the tables yourself. The entire stored procedure is listed below:

If you have any questions or comments, please don’t hestitate to ask.