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.
Paging: ASP.net (C#) and SQL 2005/8
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.
|
