Click here to Skip to main content
12,820,239 members (27,642 online)
Click here to Skip to main content
Add your own
alternative version


31 bookmarked
Posted 26 Jul 2007

A News Article List in an ASP.NET GridView Control With Custom Paging

, 26 Jul 2007 CPOL
Rate this:
Please Sign up or sign in to vote.
A list of news articles in an ASP.NET GridView control with custom paging.

Screenshot - screenshot.jpg


I was working on a website and needed a way to display my news articles 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 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 Server do all the work so that the impact on my web application is less.

After some searching on the web, I found various 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 direction. Let's get to the code...

Using the code

Firstly, the structure of my SQL News Article table is as follows:

  • UniqueID
  • NewsCategory
  • DatePosted
  • Title
  • Body
  • Display

The UniqueID is an auto-incrementing identity column and the Display column is a bit (True/False).

So, in addition to the paging, I needed to be able to filter my records by NewsCategory as well as search word(s) found in the Title or Body fields. To do this, I created a Stored Procedure that looks like this:

@iPageIndex INT,
@iMaxRows INT,
@iCategory INT,
@strSearch VARCHAR(100)

SELECT @iStart = (@iPageIndex - 1) * @iMaxRows
SELECT @iEnd = @iStart + @iMaxRows


dtPosted DATETIME,
strTitle VARCHAR(100),
strBody VARCHAR (5000),
strNewsCategory VARCHAR(50)

INSERT #TempNews 
SELECT N.dtPosted,
FROM tblNews N 
INNER JOIN tblNewsCategory C ON
C.intUniqueID = N.intNewsCategory
((@iCategory = 0) OR (intNewsCategory = @iCategory)) AND
((@strSearch = '') OR (strTitle LIKE '%' + @strSearch + '%')) OR
((@strSearch = '') OR (strBody LIKE '%' + @strSearch + '%')) AND
bitActive = 1 

WHERE intUniqueID > @iStart
AND intUniqueID <= @iEnd


The parameters I pass into the procedure are:

  • @iPageIndex
  • @iMaxRows
  • @iCategory
  • @strSearch

@PageIndex refers to the current page of results that we are viewing. @iMaxRows refers 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. @iCategory refers to the category to filter the results by, and is optional. @strSearch refers 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 temporary table with an auto-incrementing identity column - this is important because if records are deleted from our original news article table, the IDs will no longer be sequential - if, however, we insert our records into our temporary table, the IDs will be sequential and we can select between our start and end values.

We then select the appropriate records from our news articles 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:

<h3>Latest News</h3><br />
<div id="SiteNews" runat="server">
<asp:GridView ID="gvNews" runat="server" AutoGenerateColumns="False" 
  BorderColor="Black" BorderStyle="Solid" BorderWidth="1px" 
  GridLines="None" DataKeyNames="intUniqueID" Width="100%">
<asp:BoundField DataField="intUniqueID" Visible="False" />
<asp:BoundField DataField="dtPosted" 
  DataFormatString="{0:dd/MM/yyyy}" HtmlEncode="False" HeaderText="Posted">
<HeaderStyle HorizontalAlign="Center" Width="70px" />
<ItemStyle HorizontalAlign="Center" Width="70px" />
<asp:BoundField DataField="strNewsCategory" HeaderText="Category">
<HeaderStyle HorizontalAlign="Center" Width="80px" />
<ItemStyle HorizontalAlign="Center" Width="80px" />
<asp:BoundField DataField="strTitle" HeaderText="Article Title">
<asp:HyperLinkField Text="View Complete Atricle">
<HeaderStyle Width="100px" />
<ItemStyle HorizontalAlign="Right" Width="100px" />
<HeaderStyle BackColor="#CCCCCC" />
<AlternatingRowStyle BackColor="#dbdbfe" />
<div id="Pager" runat="server">&nbsp;</div>

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 - check the dtPosted column, you will see that I have specified a date format and HtmlEncode - if 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:

Private Sub FillNewsGrid(ByVal strPage As String, ByRef gvGrid As GridView, _
        ByRef divPager As HtmlGenericControl, ByVal iMaxRows As Integer, _
        Optional ByVal iCurrentPage As Integer = 1, _
        Optional ByVal iCategory As Integer = 0, _
        Optional ByVal strSearchText As String = "")
    If iCurrentPage = 0 Then iCurrentPage = 1

    Dim connSql As SqlConnection = New SqlConnection(strConnection)
    Dim cmdSql As SqlCommand = New SqlCommand
    cmdSql.Connection = connSql

    Dim iTotal As Integer = 0
    cmdSql.CommandText = "SELECT COUNT (*) FROM tblNews"
    iTotal = cmdSql.ExecuteScalar

    cmdSql = New SqlCommand
    cmdSql.CommandType = Data.CommandType.StoredProcedure
    cmdSql.CommandText = "spGetNews"
    cmdSql.Connection = connSql

    Dim prmParameter As SqlParameter

    prmParameter = New SqlParameter("@iPageIndex", Data.SqlDbType.Int)
    prmParameter.Direction = Data.ParameterDirection.Input
    prmParameter.Value = iCurrentPage

    prmParameter = New SqlParameter("@iMaxRows", Data.SqlDbType.Int)
    prmParameter.Direction = Data.ParameterDirection.Input
    prmParameter.Value = iMaxRows

    prmParameter = New SqlParameter("@iCategory", Data.SqlDbType.Int)
    prmParameter.Direction = Data.ParameterDirection.Input
    prmParameter.Value = iCategory

    prmParameter = New SqlParameter("@strSearch", Data.SqlDbType.VarChar, 100)
    prmParameter.Direction = Data.ParameterDirection.Input
    prmParameter.Value = strSearchText

    gvGrid.DataSource = cmdSql.ExecuteReader

    ' Paging
    If gvGrid.Rows.Count < 1 Then
        divPager.InnerHtml = ""
        Dim iPages As Integer = 0
        Dim iRemainder As Integer = 0
        iPages = iTotal / iMaxRows
        iRemainder = iTotal Mod iMaxRows
        If iRemainder > iMaxRows Then iPages += 1

        If iPages > 1 Then
            divPager.InnerHtml = "Page: "

            Dim i As Integer = 0
            For i = 1 To iPages
                If i <> iCurrentPage Then
                    divPager.InnerHtml += "<a href=""" & strPage & _
                                          "?pg=" & i & """>" & i & "</a>"
                    divPager.InnerHtml += i.ToString
                End If
                If i < iPages Then divPager.InnerHtml += " | "
        End If
    End If
End Sub

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

Protected Sub gvNews_RowDataBound(ByVal sender As Object, _
         ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) _
         Handles gvNews.RowDataBound
    If e.Row.RowType = DataControlRowType.DataRow Then
    ' Build link to full article
        e.Row.Cells(4).Text = "<a href=""NewsArticle.aspx?id=" & _
                              gvNews.DataKeys(e.Row.RowIndex)("intUniqueID") & _
                              "&pg=" & iCurrentPage & "&cID=" & _
                              iCategory & """>View complete article</a>"
    End If
End Sub

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


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Web Developer
South Africa South Africa
No Biography provided

You may also be interested in...

Comments and Discussions

QuestionCan I download the sample script? Pin
tinsuwan28-Aug-08 3:38
membertinsuwan28-Aug-08 3:38 
GeneralBug in the stored proc... Pin
mikvos26-Jul-07 20:55
membermikvos26-Jul-07 20:55 
GeneralRe: test reply Pin
afnbr1754-Aug-08 18:15
memberafnbr1754-Aug-08 18:15 
GeneralRe: test reply Pin
afnbr17511-Aug-08 18:11
memberafnbr17511-Aug-08 18:11 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170308.1 | Last Updated 27 Jul 2007
Article Copyright 2007 by mikvos
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid