Custom Paging in asp.net Datgrid Using Sql

[Dev Geek Raj]

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([email protected])

 

[Elton Wang]

After geting data sorce (DataTable or DataSet) from database server, you can
sort the data source in web server:

DataView dv = datatable.DefaultView;
dv.Sort = e.SortExpression;
datagrid = dv;
datagrid.DataBind();

HTH

 

[Dev Geek Raj]

Thanks .
But this solution will not work
As i have told you I can't fetch all of my records in Dataset and then
sort.

I will get and have got SQl timeout error if i try to fill in 4 million
records in dataset.

I want to use custom paging.

 

[TJ]

Why don't to save the PK before any sorting happens, so when you come
into the page save the last PK and then use that for Next or Prev
button.

 

[Elton Wang]

What I mean is in page sorting. Isn't it better?

 

[Dev Geek Raj]

TJ and Elton,

Thanks for your reply.

TJ,
If you read my initial post ,I was doing exactly what you have
replied(Saving Pk before sort).
But now since the requirement has changed ,it will not work.
The Stored procedure which returns the record ,Sors the records by Fname
and Lname.
So in this case Pk will be Jumbled.

Further,
User can click on any column header and sort.

Elton,

I'm not sure what you meant by "What I mean is in page sorting. Isn't it
better?"

can you please elaborate.

Once again,Thanks for your replies.I appreciate that

 

[David Londeck]

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.

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([email protected])

 

[Dev Geek Raj]

David,

Thanks for your reply.
I think i did not explained the problem clearly.My appologies for that.

But ,you have gone on completely different track.

Anyways i found out the solution and in few days (once i get some
breathing time ),I will be updating this.

Thanks to all of you for your replies.