justnbusiness

Friday, January 05, 2007

Dynamic sorting and paging

I ran into an interesting problem recently, trying to allow my EntityDataSource control to implement dynamic paging and sorting. There are some tricky ways to do either one fairly easily but to do both at the same time? Well let's just say it's not trivial. SO there might be a better way and I'd be happy to find out from others how to do this more simply but here is a snippet of how I got it to work (for sql2005):


ALTER PROCEDURE [dbo].[CompanyListFetchByRegion]
@RegionId int,
@StartIndex int = 0,
@MaxRows int = -1,
@SortField varchar(35) = null,
@SortOrder int = 0,
@TotalRows int = 0 output
AS
BEGIN

SET NOCOUNT ON;
DECLARE @Temp TABLE
(
TempId int IDENTITY(1,1) PRIMARY KEY,
[CompanyId] int,
[Name] varchar(50),
AddressId int,
Url varchar(128),
Phone varchar(16)
)

IF @SortOrder = 2
BEGIN
INSERT INTO @Temp ([CompanyId],[Name],AddressId,Url,Phone)
SELECT C.*
FROM [Company] AS C INNER JOIN [Address] AS A
ON C.AddressId=A.AddressId
WHERE A.[RegionId]=@RegionId
ORDER BY
CASE @SortField
WHEN 'Name' THEN cast (C.[Name] as sql_variant)
WHEN 'AddressId' THEN C.AddressId
WHEN 'Url' THEN cast (C.Url as sql_variant)
WHEN 'Phone' THEN C.Phone
ELSE C.[CompanyId]
END
DESC -- descending
END
ELSE -- Sort order == 1 or 0
BEGIN
INSERT INTO @Temp ([CompanyId],[Name],AddressId,Url,Phone)
SELECT C.CompanyId, C.[Name], C.AddressId, C.Url, C.Phone
FROM [Company] AS C INNER JOIN [Address] AS A
ON C.AddressId=A.AddressId
WHERE A.[RegionId]=@RegionId
ORDER BY
CASE @SortField
WHEN 'Name' THEN cast ([Name] as sql_variant)
WHEN 'AddressId' THEN C.AddressId
WHEN 'Url' THEN cast (Url as sql_variant)
WHEN 'Phone' THEN Phone
ELSE [CompanyId]
END
ASC -- ascending
END
-- Get the total row count, used for paging.
SET @TotalRows = (SELECT COUNT(TempId) FROM @Temp);
-- If paging is specified
IF (@MaxRows > -1)
BEGIN
SELECT * FROM @Temp
WHERE TempId >= @StartIndex
AND TempId < @StartIndex + @MaxRows + 1
END
-- Else return it all!
ELSE SELECT * FROM @Temp;
END

Good luck!

0 Comments:

Post a Comment

Links to this post:

Create a Link

<< Home