Re: Custom Paging in asp.net Datgrid Using Sql

Here is a solution to dynamically return a sort result set back.

CREATE TABLE #FileDownLoadJobs
(
RowID INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
[FileName] VARCHAR(255),
URL VARCHAR(255),
LastModified DATETIME
)

INSERT INTO #FileDownLoadJobs
SELECT 'file2', 'URL1', GETDATE()-5

DECLARE @ORDERBY VARCHAR(20)
SET @ORDERBY = 'url'
SELECT *
FROM #FileDownLoadJobs
WHERE DATEDIFF(dd, CONVERT( VARCHAR, LastModified, 101), GETDATE() ) <> 1
ORDER BY CASE WHEN @ORDERBY = 'URL' THEN URL
WHEN @ORDERBY = 'FILENAME' THEN [FILENAME]
END

If you need more information on deploying the let me know.


"Dev Geek Raj" <devgeekraj@xxxxxxxxx> wrote in message
news:%23aXGIPBRGHA.2300@xxxxxxxxxxxxxxxxxxxxxxx
All you devgeeks on .net,
Can you please help me on this.

I have a table ( tbl_emp).I allow users to search the record on this
table.
I have three filter conditions
Fname,Lname,Title.When user enters these values into Textboxes on
Asp.net page and click on search Button.
I Show the results of search on an ASP.NET datagrid.

Now my Database is huge ,it returns about 4 million records after
search,I can't use normal Paging because it gives me a Time out error.
I decided to Use Custom Paging.Here is my Logic
I have written A SP in SQL which will be used to Bind the grid.
This Sp_fillGrid has following Parameters.
1.Fname 2.LName 3.Title 4.StartIndex 5.endindex

Now When the first time grid is loaded after clicking on search button,
Sp_Fill builds a querry like
"Select Top [Pagesize of the grid] col 1,2....n from tbl_emp where
(Filter conditions) order by ID"

I pass Pagesize from The UI code to SQl SP.

I have 2 buttons : prev(<) and next(>) on grid.
When User clicks on Nextbutton,Sp_fill builds a querry like
"Select Top [Pagesize of the grid] col 1,2....n from tbl_emp where
(Filter conditions) and (pk_ID > [StartIndex])
order by Id"

pk_ID is the primary key of table.
StartIndex is the Value of Primary key of last record on the current
Page,which I pass to my SP.

Now,User can view a Prev Page only when he has visited that Page before
using Next Button.
My .Net code Stores(In the viewstate ) Id of the last record on the each
page Visited by clicking on Next Button.

So when user click on Prev Button ,My querry looks like this
"Select Top [Pagesize of the grid] col 1,2....n from tbl_emp where
(Filter conditions) and (pk_ID is betwen( [StartIndex,EndIndex)])
order by Id"

This used to work fine.
Now I have a new requirement on the grid,That I should be able to sort
records on the grid Using any of the column headers

So when User clicks on Fname column header..it sould sort by Fname.
So if i wnat to use sorting on column headers,My primary key gets
Jumbled.

Can anybody advice me or guide me to the solution of this.

Regards,
Raj(devgeekraj@xxxxxxxxx)





*** Sent via Developersdex http://www.developersdex.com ***


.