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:

   1:  IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = '_Company_CustomSearch')

   2:      BEGIN

   3:          DROP  Procedure [dbo].[_Company_CustomSearch]

   4:      END

   5:   

   6:  GO

   7:   

   8:  CREATE PROCEDURE [dbo].[_Company_CustomSearch]

   9:  (

  10:      @CompanyId int   = null ,

  11:      @CompanyName nvarchar (150)  = null ,

  12:      @CompanyAddressTownCity nvarchar (200)  = null ,

  13:      @CompanyAddressState nvarchar (200)  = null ,

  14:      @CompanyAddressPostcode nvarchar (20)  = null ,

  15:      @OrderBy nvarchar (2000) = null ,

  16:      @PageIndex int = null ,

  17:      @PageSize int = null ,

  18:      @TotalRows int OUTPUT

  19:  )

  20:  AS

  21:   

  22:  BEGIN

  23:   

  24:  --

  25:  IF @PageIndex IS NULL

  26:      SET @PageIndex = 0

  27:      

  28:  IF @PageSize IS NULL

  29:      SET @PageSize = 400

  30:  --

  31:  SET DATEFORMAT dmy

  32:   

  33:  -- Build WHERE Clause

  34:  DECLARE @WhereClause [nvarchar] (2000)

  35:   

  36:  SET @WhereClause = '1=1'

  37:   

  38:  IF @CompanyId IS NOT NULL AND @CompanyId <> 0

  39:      SET @WhereClause = @WhereClause + ' AND [Company].CompanyId = ' + CAST(@CompanyId AS NVARCHAR(20))

  40:      

  41:  IF @CompanyName IS NOT NULL AND LEN(@CompanyName) > 0

  42:      SET @WhereClause = @WhereClause + ' AND [Company].CompanyName LIKE ''' + @CompanyName + '%'''

  43:      

  44:  IF @CompanyAddressTownCity IS NOT NULL AND LEN(@CompanyAddressTownCity) > 0

  45:      SET @WhereClause = @WhereClause + ' AND [Address].TownCity LIKE ''' + @CompanyAddressTownCity + '%'''

  46:   

  47:  IF @CompanyAddressState IS NOT NULL AND LEN(@CompanyAddressState) > 0

  48:      SET @WhereClause = @WhereClause + ' AND [Address].State LIKE ''' + @CompanyAddressState + '%'''

  49:   

  50:  IF @CompanyAddressPostcode IS NOT NULL AND LEN(@CompanyAddressPostcode) > 0

  51:      SET @WhereClause = @WhereClause + ' AND [Address].Postcode LIKE ''' + @CompanyAddressPostcode + '%'''

  52:   

  53:  DECLARE @PageLowerBound int

  54:  DECLARE @PageUpperBound int

  55:   

  56:  -- Set the page bounds

  57:  SET @PageLowerBound = @PageSize * @PageIndex

  58:  SET @PageUpperBound = @PageLowerBound + @PageSize

  59:   

  60:  IF (@OrderBy is null or LEN(@OrderBy) <>
  61:  BEGIN
  62:  -- default order by to first column
  63:  SET @OrderBy = '[CompanyId]'
  64:  END
  65:   
  66:  -- SQL Server 2005 Paging
  67:  declare @SQL as nvarchar(max)
  68:   
  69:  -- get row count
  70:  SET @SQL = 'SELECT @TotalRows = COUNT(DISTINCT Company.CompanyId)'
  71:  SET @SQL = @SQL + ' FROM dbo.[Company]'
  72:  SET @SQL = @SQL + ' INNER JOIN [Address] ON [Address].AddressId = [Company].AddressId'
  73:   
  74:  IF LEN(@WhereClause) > 0
  75:  BEGIN
  76:  SET @SQL = @SQL + ' WHERE ' + @WhereClause
  77:  END
  78:   
  79:  EXEC sp_executesql @SQL, N'@TotalRows int out', @TotalRows out
  80:   
  81:  -- Get Data
  82:  SET @SQL = 'WITH PageIndex AS ('
  83:  SET @SQL = @SQL + ' SELECT'
  84:  IF @PageSize > 0
  85:  BEGIN
  86:  SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
  87:  END
  88:   
  89:  SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY [Company].' + @OrderBy + ') as RowIndex'
  90:   
  91:  SET @SQL = @SQL + ', [Company].[CompanyId]'
  92:  SET @SQL = @SQL + ', [Company].[CompanyName]'
  93:  SET @SQL = @SQL + ', [Company].[AddressId]'
  94:  SET @SQL = @SQL + ', [Company].[CompanyUrl]'
  95:  SET @SQL = @SQL + ', [Company].[Telephone]'
  96:   
  97:  SET @SQL = @SQL + ' FROM dbo.[Company]'
  98:  SET @SQL = @SQL + ' INNER JOIN [Address] ON [Address].AddressId = [Company].AddressId'
  99:   
 100:  IF LEN(@WhereClause) > 0
 101:  BEGIN
 102:  SET @SQL = @SQL + ' WHERE ' + @WhereClause
 103:  END
 104:   
 105:  -- Group By
 106:  SET @SQL = @SQL + ' GROUP BY '
 107:  SET @SQL = @SQL + ' [Company].[CompanyId]'
 108:  SET @SQL = @SQL + ', [Company].[CompanyName]'
 109:  SET @SQL = @SQL + ', [Company].[AddressId]'
 110:  SET @SQL = @SQL + ', [Company].[CompanyUrl]'
 111:  SET @SQL = @SQL + ', [Company].[Telephone]'
 112:   
 113:  SET @SQL = @SQL + ' ) SELECT'
 114:  SET @SQL = @SQL + ' [CompanyId]'
 115:  SET @SQL = @SQL + ', [CompanyName]'
 116:  SET @SQL = @SQL + ', [AddressId]'
 117:  SET @SQL = @SQL + ', [CompanyUrl]'
 118:  SET @SQL = @SQL + ', [Telephone]'
 119:  SET @SQL = @SQL + ' FROM PageIndex'
 120:  SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
 121:   
 122:  IF @PageSize > 0
 123:  BEGIN
 124:  SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
 125:  END
 126:   
 127:  SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
 128:   
 129:  -- Get Data
 130:  --PRINT @SQL
 131:  EXEC sp_executesql @SQL
 132:  PRINT @SQL
 133:      
 134:  -- HACK (to get .NetTiers to gen the TList
 135:  IF USER_NAME() IS NULL
 136:  BEGIN
 137:      SELECT * FROM Company WHERE 1=0
 138:      RETURN
 139:  END
 140:   
 141:  END
 142:   
 143:  GO

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