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

Source code for the following post can be downloaded here.

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 ('Julie', 'Truter')
INSERT INTO friends(firstname, lastname) VALUES ('Eugene', '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.




Post/View comments
 

MS SQL Basics: Joins

There are three categories joins fall into, inner, outer and cross. Outer joins consist of three types, left, right and full.

Lets have a quick look at the various joins. (On the right we've got two datasets we'll be using in the examples.)

Table a
aID title
1 a1
2 a2
3 a3
4 a4
Table b
bID title aID
1 b1 1
2 b2 2
3 b3 NULL
4 b4 NULL

Inner Join

Return rows that match in both tables. (Default join) SELECT * FROM a INNER JOIN b ON a.aid = b.aid
title aID
a1 1
a2 2
aID bID title
1 1 b1
2 2 b2



Left Outer Join

Return rows from the table on the left of the expression even if they dont match with the table on the right. SELECT * FROM a LEFT OUTER JOIN b ON a.aid = b.aid
title aID
a1 1
a2 2
a3 3
a4 4
aID bID title
1 1 b1
2 2 b2
NULL NULL NULL
NULL NULL NULL
 



Right Outer Join

Return rows from the table on the right of the expression even if they dont match with the table on the left. SELECT * FROM a RIGHT OUTER JOIN b ON a.aid = b.aid
title aID
a1 1
a2 2
NULL NULL
NULL NULL
aID bID title
1 1 b1
2 2 b2
3 3 b3
4 4 b4
 



Full Outer Join

Return rows from both the left and right tables even if they don't match in either. (left + right join) SELECT * FROM a FULL OUTER JOIN b ON a.aid = b.aid
title aID
a1 1
a2 2
a3 3
a4 4
NULL NULL
NULL NULL
aID bID title
1 1 b1
2 2 b2
NULL NULL NULL
NULL NULL NULL
3 3 b3
4 4 b4
 



Cross Join

Join each row from the left with each row from the right. Known as a "carthesian product" SELECT * FROM a CROSS JOIN b or SELECT * FROM a, b
title aID
a1 1
a2 2
a3 3
a4 4
a1 1
a2 2
a3 3
a4 4
a1 1
a2 2
a3 3
a4 4
a1 1
a2 2
a3 3
a4 4
aID bID title
1 1 b1
1 1 b1
1 1 b1
1 1 b1
2 2 b2
2 2 b2
2 2 b2
2 2 b2
3 3 b3
3 3 b3
3 3 b3
3 3 b3
4 4 b4
4 4 b4
4 4 b4
4 4 b4
 







Post/View comments
 
First 16 17 18 19 20 21 22 23 24 25 Last / 42 Pages (83 Entries)