Hi,
Would anyone like to share an example of a custom stored procedure that:
- Supports paging, and
- Returns a TList not a dataset, and
- INNER JOINS on one or more sub tables so a WHERE search can target the sub tables
I have the following, which is an exact copy of the same generated GetPaged SP for a table called "Company", but with two INNER JOINS across tables that are related by foreign keys:
ALTER PROCEDURE [dbo].[_Company_SearchGetPaged]
(
@WhereClause varchar (2000) = null ,
@OrderBy varchar (2000) = null ,
@PageIndex int = null ,
@PageSize int = null
)
AS
BEGIN
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) < 1)
BEGIN
-- default order by to first column
SET @OrderBy = '[CompanyID]'
END
-- SQL Server 2005 Paging
declare @SQL as nvarchar(4000)
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 ' + @OrderBy + ') as RowIndex'
SET @SQL = @SQL + ', [Company].[CompanyID]'
SET @SQL = @SQL + ', [Company].[AccountID]'
SET @SQL = @SQL + ', [Company].[CompanyName]'
SET @SQL = @SQL + ', [Company].[ContactName]'
SET @SQL = @SQL + ', [Company].[DateCreated]'
SET @SQL = @SQL + ', [Company].[PhoneNumber]'
SET @SQL = @SQL + ', [Company].[Website]'
SET @SQL = @SQL + ', [Company].[EmailAddress]'
SET @SQL = @SQL + ', [Company].[AddressID]'
SET @SQL = @SQL + ', [Company].[Active]'
SET @SQL = @SQL + ' FROM dbo.[Company]'
SET @SQL = @SQL + ' INNER JOIN [Subscription] ON [Subscription].CompanyID = [Company].CompanyID'
SET @SQL = @SQL + ' INNER JOIN [Tag] ON [Tag].TagID = [Subscription].TagID'
IF LEN(@WhereClause) > 0
BEGIN
SET @SQL = @SQL + ' WHERE ' + @WhereClause
END
SET @SQL = @SQL + ' ) SELECT'
SET @SQL = @SQL + ' [CompanyID],'
SET @SQL = @SQL + ' [AccountID],'
SET @SQL = @SQL + ' [CompanyName],'
SET @SQL = @SQL + ' [ContactName],'
SET @SQL = @SQL + ' [DateCreated],'
SET @SQL = @SQL + ' [PhoneNumber],'
SET @SQL = @SQL + ' [Website],'
SET @SQL = @SQL + ' [EmailAddress],'
SET @SQL = @SQL + ' [AddressID],'
SET @SQL = @SQL + ' [Active]'
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
exec sp_executesql @SQL
-- get row count
SET @SQL = 'SELECT COUNT(*) as TotalRowCount'
SET @SQL = @SQL + ' FROM dbo.[Company]'
SET @SQL = @SQL + ' INNER JOIN [Subscription] ON [Subscription].CompanyID = [Company].CompanyID'
SET @SQL = @SQL + ' INNER JOIN [Tag] ON [Tag].TagID = [Subscription].TagID'
IF LEN(@WhereClause) > 0
BEGIN
SET @SQL = @SQL + ' WHERE ' + @WhereClause
END
exec sp_executesql @SQL
END
The generated method that .netTiers generates returns a void:
public
virtual void SearchGetPaged( System.String whereClause, System.String orderBy, System.Int32? pageIndex, System.Int32? pageSize, int start, int pageLength)
How on earth do you get the data out from this method??? I'm able to do the following, but can't assign the result to TList<Company> and bind the data to the GridView because the method does't return anything:
DataRepository
.CompanyProvider.SearchGetPaged("Tag.Name LIKE '%software%'", null, GridViewSearch.PageIndex, GridViewSearch.PageSize);
Any suggestions, or even better code examples from people who have solved this problem are most welcome!
Please help, I'm really stuck. Should I be using a view instead for this kind of thing? I can get this to work without the paging, but with 1000's of records a lack of paging is going to cause me some serious problems!
Thanks in advance.
Ben