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:

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = '_Company_CustomSearch')
BEGIN
DROP  Procedure [dbo].[_Company_CustomSearch]
END

GO

CREATE PROCEDURE [dbo].[_Company_CustomSearch]
(
@CompanyId int   = null ,
@CompanyName nvarchar (150)  = null ,
@CompanyAddressTownCity nvarchar (200)  = null ,
@CompanyAddressState nvarchar (200)  = null ,
@CompanyAddressPostcode nvarchar (20)  = null ,
@OrderBy nvarchar (2000) = null ,
@PageIndex int = null ,
@PageSize int = null ,
@TotalRows int OUTPUT
)
AS

BEGIN

--
IF @PageIndex IS NULL
SET @PageIndex = 0

IF @PageSize IS NULL
SET @PageSize = 400
--
SET DATEFORMAT dmy

-- Build WHERE Clause
DECLARE @WhereClause [nvarchar] (2000)

SET @WhereClause = '1=1'

IF @CompanyId IS NOT NULL AND @CompanyId <> 0
SET @WhereClause = @WhereClause + ' AND [Company].CompanyId = ' + CAST(@CompanyId AS NVARCHAR(20))

IF @CompanyName IS NOT NULL AND LEN(@CompanyName) > 0
SET @WhereClause = @WhereClause + ' AND [Company].CompanyName LIKE ''' + @CompanyName + '%'''

IF @CompanyAddressTownCity IS NOT NULL AND LEN(@CompanyAddressTownCity) > 0
SET @WhereClause = @WhereClause + ' AND [Address].TownCity LIKE ''' + @CompanyAddressTownCity + '%'''

IF @CompanyAddressState IS NOT NULL AND LEN(@CompanyAddressState) > 0
SET @WhereClause = @WhereClause + ' AND [Address].State LIKE ''' + @CompanyAddressState + '%'''

IF @CompanyAddressPostcode IS NOT NULL AND LEN(@CompanyAddressPostcode) > 0
SET @WhereClause = @WhereClause + ' AND [Address].Postcode LIKE ''' + @CompanyAddressPostcode + '%'''

DECLARE @PageLowerBound int
DECLARE @PageUpperBound int

-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize

IF (@OrderBy is null or LEN(@OrderBy) <>
BEGIN
-- default order by to first column
SET @OrderBy = '[CompanyId]'
END

-- SQL Server 2005 Paging
declare @SQL as nvarchar(max)

-- get row count
SET @SQL = 'SELECT @TotalRows = COUNT(DISTINCT Company.CompanyId)'
SET @SQL = @SQL + ' FROM dbo.[Company]'
SET @SQL = @SQL + ' INNER JOIN [Address] ON [Address].AddressId = [Company].AddressId'

IF LEN(@WhereClause) > 0
BEGIN
SET @SQL = @SQL + ' WHERE ' + @WhereClause
END

EXEC sp_executesql @SQL, N'@TotalRows int out', @TotalRows out

-- Get Data
SET @SQL = 'WITH PageIndex AS ('
SET @SQL = @SQL + ' SELECT'
IF @PageSize > 0
BEGIN
SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
END

SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY [Company].' + @OrderBy + ') as RowIndex'

SET @SQL = @SQL + ', [Company].[CompanyId]'
SET @SQL = @SQL + ', [Company].[CompanyName]'
SET @SQL = @SQL + ', [Company].[AddressId]'
SET @SQL = @SQL + ', [Company].[CompanyUrl]'
SET @SQL = @SQL + ', [Company].[Telephone]'

SET @SQL = @SQL + ' FROM dbo.[Company]'
SET @SQL = @SQL + ' INNER JOIN [Address] ON [Address].AddressId = [Company].AddressId'

IF LEN(@WhereClause) > 0
BEGIN
SET @SQL = @SQL + ' WHERE ' + @WhereClause
END

-- Group By
SET @SQL = @SQL + ' GROUP BY '
SET @SQL = @SQL + ' [Company].[CompanyId]'
SET @SQL = @SQL + ', [Company].[CompanyName]'
SET @SQL = @SQL + ', [Company].[AddressId]'
SET @SQL = @SQL + ', [Company].[CompanyUrl]'
SET @SQL = @SQL + ', [Company].[Telephone]'

SET @SQL = @SQL + ' ) SELECT'
SET @SQL = @SQL + ' [CompanyId]'
SET @SQL = @SQL + ', [CompanyName]'
SET @SQL = @SQL + ', [AddressId]'
SET @SQL = @SQL + ', [CompanyUrl]'
SET @SQL = @SQL + ', [Telephone]'
SET @SQL = @SQL + ' FROM PageIndex'
SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)

IF @PageSize > 0
BEGIN
SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
END

SET @SQL = @SQL + ' ORDER BY ' + @OrderBy

-- Get Data
--PRINT @SQL
EXEC sp_executesql @SQL
PRINT @SQL

-- HACK (to get .NetTiers to gen the TList
IF USER_NAME() IS NULL
BEGIN
SELECT * FROM Company WHERE 1=0
RETURN
END

END

GO

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.