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:
UniqueIDNewsCategoryDatePostedTitleBodyDisplay
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"> </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()
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
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.
