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

Introduction

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:

CREATE PROCEDURE spGetNews
@iPageIndex INT,
@iMaxRows INT,
@iCategory INT,
@strSearch VARCHAR(100)
AS
BEGIN
SET NOCOUNT ON;

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

IF OBJECT_ID (N'#TempNews',N'U') IS NOT NULL
DROP TABLE #TempNews

CREATE TABLE #TempNews(
intUniqueID INT PRIMARY KEY IDENTITY(1, 1),
dtPosted DATETIME,
strTitle VARCHAR(100),
strBody VARCHAR (5000),
strNewsCategory VARCHAR(50)
)

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

SELECT * FROM #TempNews
WHERE intUniqueID > @iStart
AND intUniqueID <= @iEnd

DROP TABLE #TempNews
END

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%">
<Columns>
<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>
<asp:BoundField DataField="strNewsCategory" HeaderText="Category">
<HeaderStyle HorizontalAlign="Center" Width="80px" />
<ItemStyle HorizontalAlign="Center" Width="80px" />
</asp:BoundField>
<asp:BoundField DataField="strTitle" HeaderText="Article Title">
</asp:BoundField>
<asp:HyperLinkField Text="View Complete Atricle">
<HeaderStyle Width="100px" />
<ItemStyle HorizontalAlign="Right" Width="100px" />
</asp:HyperLinkField>
</Columns>
<HeaderStyle BackColor="#CCCCCC" />
<AlternatingRowStyle BackColor="#dbdbfe" />
</asp:GridView>
<div id="Pager" runat="server">&nbsp;</div>
</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"
    connSql.Open()
    iTotal = cmdSql.ExecuteScalar
    connSql.Close()

    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
    cmdSql.Parameters.Add(prmParameter)

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

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

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

    connSql.Open()
    gvGrid.DataSource = cmdSql.ExecuteReader
    gvGrid.DataBind()
    connSql.Close()

    ' Paging
    If gvGrid.Rows.Count < 1 Then
        divPager.InnerHtml = ""
    Else
        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>"
                Else
                    divPager.InnerHtml += i.ToString
                End If
                If i < iPages Then divPager.InnerHtml += " | "
            Next
        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.