An ASP. NET gridview control with custom paging (Technical)

I was working on a website and needed a way to display my log information in a gridview control but allow paging through a certain number of articles at a time so that if the user was viewing the complete archive, the page wouldn't be too long.

The default paging in the ASP. net gridview control does not scale well as it always Retrieves all the records from the database and then only displays the ones you want. I wanted to rather fetch only the records that I needed for the current page and let SQL so all the work so that the impact on my web application was less.

After some searching on the web I found varous articles but nothing that really worked for me so I decided to write my own. incidentally, I started by drawing the logic out on a piece of paper before just diving into the code and this really helped with my focus and made sure that I didn't waste time going in the wrong ction. now, let's get to the code...

Firstly, the structure of My SQL news article table is as follows:

The logid is an auto-incrementing identity column.

So in addition to the paging, I needed to be able to filter my recordsLogtable/logstores/logtimeAs well as search word (s) found in the title or body fields. To do this, I created a stored procedure that looks like this:

If object_id (n'SP _ getlogs', N'P') Is not null
 

 
Drop proc sp_getlogs
 

 
Go
 

 
Create proc sp_getlogs
 

 
@ Ipageindex int,
 

 
@ Imaxrows int,
 

 
@ Search varchar (1, 1000)
 

 
As
 

 
Begin
 

 
Set nocount on;
 

 
Declare @ istart int
 

 
Select @ istart = (@ ipageindex-1) * @ imaxrows
 

 
Declare @ iend int
 

 
Select @ iend = @ istart + @ imaxrows
 

 
If object_id (n'# Templogs', N'U') Is not null
 

 
Drop table # templogs
 

 
Create Table # templogs (
 

 
Intuniqueid int Primary Key Identity (1, 1 ),
 

Usercname nvarchar (100 ),
 

 
Logtable varchar (50 ),
 

 
Logstores varchar (50 ),
 

 
Logtime varchar (50 ),
 

 
Logcontent nvarchar (300 ))
 

 
Declare @ sqlstr varchar (1000)
 

 
Set @ sqlstr ='Insert # templogs select U. usercname, S. logtable, S. logstores, S. logtime, S. logcontent
 

 
From sys_log s
 

Inner join jc_userinfo U on u. userid = S. loguser
 

 
Where '+ @ search;
 

 
Print (@ sqlstr)
 

 
Exec (@ sqlstr)
 

 
Select * from # templogs
 

 
Where intuniqueid> @ istart
 

 
And intuniqueid <= @ iend
 

 
End
 

 
Go
 

Exec sp_getlogs 2, 5,'Logtable =''Jc_department'''
 

There are some eggache question here

1. In sysobjects

S	System Table
V	View
U	User table (including temporary tables)
Tr	Trigger

So, drop a temp table is

If object_id (n'# Templogs', N'U') Is not null
 

 
Drop table # templogs
 

2. Exec a variable is like this, don't forget the brackets.

 
Declare @ sqlstr varchar (1000)
 

 
Set @ sqlstr ='Insert # templogs select U. usercname, S. logtable, S. logstores, S. logtime, S. logcontent
 

 
From sys_log s
 

 
Inner join jc_userinfo U on u. userid = S. loguser
 

 
Where '+ @ search;
 

Print (@ sqlstr)
 

 
Exec (@ sqlstr)
 

The paramters I pass into the procdure are:

1. @ Ipageindex
2. @ Imaxrows
3. @ Search

The@ PageindexRefers to the current page of results that we are viewing.
The@ ImaxrowsRefers to the number of records displayed on each page-note that it is called Max rows because the last page may contain less than the others depending on the total number of records.
The@ SearchRefers to any search word (s) used to filter the results and is also optional.

The first thing we do is set the start and end values for our recordset based on the current page and the number of records to fetch. we will use these later to select only the relevant records to pass back to our web application.

Next we create a temprorary table with an auto-incrementing identity column-this is important as if records are deleted from our original news article table, the ID's will no longer be sequential-If, however, we insert our records into our temporary table, the ID's will be sequential and we can select between our start and end values.

We then select the appropriate records from our log table and insert these into our temporary table and finally, we select our results from our temporary table where the IDs are between our start and end values. after selecting them, we delete our temporary table from the memory.

So, now we have a SQL stored procedure that will return a set of results based on some filtering and paging parameters. How do we use this in our web application?

Here's what the source for my news. ASPX page looks like:

------------------------------------------------

------------------------------------------------

Note that I have two "Divs," one for the gridview control and another one called"Pager"That will display the page numbers for paging. (as an aside-checkDtpostedColumn, you will see that I have specified a date format andHtmlencode-If the htmlencode is not set to false, your formatting will not be applied)

Now that we have our grid, let's bind the data to it in our code behind page.

In the page_load event I call the following routine:

Fillnewsgrid ("news. aspx", gvnews, pager, imaxrows, icurrentpage, icategory, txtsearchtext. value)

This routine looks like this:

------------------------------------------------

------------------------------------------------

Lastly, I compile the link to view the complete article in The rowdatabound event:

There we go, that shocould get you started, please feel free to contact me if you need any balance getting this up and running on your site.

Tip:

When writing a stored procedure, you can use print (@ SQL) to check whether the last SQL statement is correct.