Paging: ASP.net (C#) and SQL 2005/8

October 19, 2009 by Christoff Truter C#   ASP.NET  

Paging data is one of those basic things most, if not every developer will have to do from time to time.
In the following approach, we're going to use a ListView, DataPager and an ObjectDataSource to achieve this. Equally one can use a DataView and simply use it with an ObjectDataSource - I personally prefer the ListView, simply because I feel its a more flexible control.

Lets have a look at the ObjectDataSource first:

<asp:ObjectDataSource ID="obsFriends" runat="server" 
	SelectCountMethod="CountFriends"
	SelectMethod="ViewFriends" TypeName="friends" EnablePaging="true">
</asp:ObjectDataSource>

Notice that we'll need a "SelectCountMethod", this method returns a count of the total rows that needs paging, the "SelectMethod" is the method that returns the actual rows.

TypeName is the class (along with its namespaces) where the ObjectDataSource can find these methods.

That class will look something like the following:

using System;
using System.Data;
using System.Configuration;
using System.ComponentModel;
using System.Data.SqlClient;

[DataObject()]
public class friends
{
    [DataObjectMethod(DataObjectMethodType.Select)]
    public static DataTable ViewFriends(Int32 maximumRows, Int32 startRowIndex)
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Default"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("ViewFriends", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@maximumRows", maximumRows);
                command.Parameters.AddWithValue("@startRowIndex", startRowIndex);
                using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                {
                    DataTable dt = new DataTable();
                    adapter.Fill(dt);
                    return dt;
                }
            }
        }
    }

    [DataObjectMethod(DataObjectMethodType.Select)]
    public static Int32 CountFriends()
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Default"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("CountFriends", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                connection.Open();
                return Convert.ToInt32(command.ExecuteScalar());
            }
        }
    }
}

There are a few things you'll notice in the preceding snippet, attributes like DataObjectMethod & DataObject - these attributes are simply there to make these methods visible in the control designers, eg Configure Data Source in design view.

Also, you'll notice we need to create two stored procedures ViewFriends and CountFriends. ViewFriends require two parameters, maximumRows and startRowIndex, these parameters automatically become required the second we set EnablePaging in our object to true.

Now is probably a good time to create some data.

Lets create a little table, where we insert the first and last names of friends.

CREATE TABLE [dbo].[friends](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[firstname] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[lastname] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_friends] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Populate it with some data.

insert into friends(firstname, lastname) values ('Jurgens', 'Truter')
insert into friends(firstname, lastname) values ('Gerhardt', 'Stander')
insert into friends(firstname, lastname) values ('Pam', 'Nizar')
insert into friends(firstname, lastname) values ('Alexander', 'Mehlhorn')
insert into friends(firstname, lastname) values ('Roland', 'Cooper')
insert into friends(firstname, lastname) values ('Loren', 'Stevens')
insert into friends(firstname, lastname) values ('Edward', 'Anderson')
insert into friends(firstname, lastname) values ('Wayne', 'Kleynhans')

The ViewFriends stored procedure will look like this, notice we use a CTE to aid us in our paging process - this works in SQL 2005 and greater.

CREATE PROCEDURE [dbo].[ViewFriends]
	@maximumRows AS INT, 
	@startRowIndex AS INT
AS
BEGIN
	WITH entries AS 
	( 
		SELECT ROW_NUMBER() OVER (ORDER BY id ASC) AS ROW, id, firstname, lastname
		FROM friends
	)
 
	SELECT firstname, lastname
	FROM entries 
	WHERE ROW BETWEEN 
		@startRowIndex+1 AND (@startRowIndex + @maximumRows)
END

And the CountFriends like this:

CREATE PROCEDURE CountFriends
AS
BEGIN
	SELECT COUNT(*) FROM friends
END

Getting back to our frontend, our pager will look something like this:

<asp:DataPager runat="server" ID="dpFriends" PagedControlID="lvFriends" PageSize="3">
	<Fields>
		<asp:NumericPagerField ButtonType="Link" />
	</Fields>
</asp:DataPager>

Notice PagedControlID, this is the control we'll be paging, and PageSize is the number of rows that must be displayed.

Finally, our ListView will look like this:

<asp:ListView ID="lvFriends" DataSourceID="obsFriends" runat="server" ItemPlaceholderID="phFriends">
	<LayoutTemplate>
		<table>
			<tr style="background-color: Black; color: White">
				<td>
					Firstname
				</td>
				<td>
					Lastname
				</td>
			</tr>
			<asp:PlaceHolder ID="phFriends" runat="server"></asp:PlaceHolder>
		</table>
	</LayoutTemplate>
	<ItemTemplate>
		<tr>
			<td>
				<%# Eval("firstname") %>
			</td>
			<td>
				<%# Eval("lastname") %>
			</td>
		</tr>
	</ItemTemplate>
</asp:ListView>

DataSourceID refers to our ObjectDataSource, ItemPlaceholderID to the control within our LayoutTemplate thats going to be substituted, with the values generated within the ItemTemplate.