If you have ever had to display large a dataset on the web, you will soon realize they can bring your application to a halt. A solution to this problem is paging. Paging allows smaller results set to be returned and displayed on the client, which greatly improves performance.
Introduction
For this example I will user Pariveda’s custom Pagable/Sortable repeater control which will soon be included in the Pariveda.Web dll. I will demonstrate how to:
- Create a Stored Procedure that allows for Paging and Sorting
- Write a Business Controller to call the stored procedure
- Add the custom control to a web page
- Write code to hook into the controls events in order to call the Stored Procedure and return the proper result set
Create the Stored Procedure
I used the infamous Northwind database for this example. I will select Customer records based on the current page and sort information from the control.
First, you must declare parameters that will be needed for paging:
CREATE PROCEDURE dbo.Northwind_CustomersGetPaged
@startRow int
, @endRow int
, @sortColumn varchar(100)
, @sortDirection char
Next, either a Table Variable or Temp Table is needed to hold you queried results. In this example I have chosen to use a Table Variable. If you use a temp table make sure to drop it at the end of the procedure. The Row ID will be used to determine which rows (Page) of data to return. This is based on the information passed in from the control that I will discuss later.
DECLARE @results TABLE
(
rowId int identity
,CustomerID nchar(5)
,CompanyName nvarchar(40)
,Address nvarchar(60)
,City nvarchar(15)
,Phone nvarchar(24)
)
After we have declared the results table we must populate it with data based on the sort criteria received from the control. Notice the order by is a case statement. This is done in order to transform the @sortColumn and @sortDirection into a SQL order by statement.
INSERT INTO @results
(
CustomerID
,CompanyName
,Address
,City
,Phone
)
SELECT
CustomerID
,CompanyName
,Address
,City
,Phone
FROM
dbo.Customers
ORDER BY
CASE
WHEN @sortColumn = 'Customer ID' and @sortDirection = 'D' then CustomerID
WHEN @sortColumn ='Company Name' and @sortDirection = 'D' then CompanyName
WHEN @sortColumn ='Address' and @sortDirection = 'D' then Address
WHEN @sortColumn ='City' and @sortDirection = 'D' then City
WHEN @sortColumn ='Phone' and @sortDirection = 'D' then Phone
END DESC,
CASE
WHEN @sortColumn = 'Customer ID' and @sortDirection = 'A' then CustomerID
WHEN @sortColumn ='Company Name' and @sortDirection = 'A' then CompanyName
WHEN @sortColumn ='Address' and @sortDirection = 'A' then Address
WHEN @sortColumn ='City' and @sortDirection = 'A' then City
WHEN @sortColumn ='Phone' and @sortDirection = 'A' then Phone
END ASC,
CompanyName
Finally, 2 results sets will need to be returned; one with a total count (“Virtual Count”) of all records that meet our criteria, and another with only the rows for the current page of the control.
SELECT count(1) AS NumRows FROM @results
SELECT * FROM @results
WHERE rowID >= @startRow
AND rowID <= @endRow
Writing the Business Controller
Calling the procedure is pretty straight forward. For the example the stored procedure only accepts the Paging parameters, but you could add any additional parameters for your particular application. One quick note, I used Microsoft’s Application Blocks to access the database. The first results set returns the total number of customers and the next results set contains the specific customer data for the given page. Both result sets are read into a Customer Object which is returned to the UI where it will be bound to the paging control.
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using PagedRepeaterExample.DataObject.Customer;
namespace PagedRepeater.Business
{ /// <summary>
/// Summary description for CustomerController.
/// </summary>
public class CustomerController
{private static readonly string CONNECTION_STRING =
ConfigurationSettings.AppSettings.Get("ConnectionString");
public CustomerController()
{ }
public static Customers GetCustomers(int startRow,
int endRow,
string sortColumn,
string sortDirection)
{ Customers customers = new Customers();
CustomerCollection customerColleciton = new CustomerCollection();
SqlParameter[] parameters = new SqlParameter[]
{ new SqlParameter("@startRow", startRow), new SqlParameter("@endRow", endRow), new SqlParameter("@sortColumn", sortColumn), new SqlParameter("@sortDirection", sortDirection) };
try
{using(SqlDataReader reader =
SqlHelper.ExecuteReader(CONNECTION_STRING,CommandType.StoredProcedure,
"NorthWind_CustomersGetPaged",parameters))
{ while(reader.Read())
{ customers.VirtualCount = (int)reader["NumRows"];
}
if(reader.NextResult())
{ while(reader.Read())
{ Customer customer = new Customer();
customer.CustomerId = reader["CustomerId"].ToString();
customer.CompanyName = reader["CompanyName"].ToString();
customer.City = reader["City"].ToString();
customer.Address = reader["Address"].ToString();
customer.Phone = reader["Phone"].ToString();
customerColleciton.Add(customer);
}
}
}
}
catch(Exception ex)
{ return null;
}
customers.CustomerCollection = customerColleciton;
return customers;
}
}
}
Add the Control
Adding the control to the page is similar to adding a regular repeater control. The first thing is to add a page directive for the Pariveda.Web dll.
<%Register TagPrefix="pvs" Assembly="Pariveda.Web" namespace="Pariveda.Web.UI.WebControls"%>
The next step is to add the custom control to the page. The “IsSortable” property of the repeater should be set to True and the “PageControllerContainerId” should be set to the id of the Place holder located in the header template. The Place holder is used to hold the paging controls and can be place in either the Header Template of the Footer Template. The SortLinkButton’s are used, of course, for sorting. The only thing to watch with these is to make sure that the SortType property matches the Order By clause in the stored procedure.
<form id="Form1" method="post" runat="server">
<table width="100%">
<pvs:PagedRepeater id="rptCustomers" Runat="server"
IsSortable="true"
PagerControllerContainerId="CustomerPagerControls">
<HEADERTEMPLATE>
<TR >
<TD colspan="5" align="right">
<asp:PlaceHolder id="CustomerPagerControls" Runat="server"></asp:PlaceHolder>
</TD>
</TR>
<TR bgcolor="Gainsboro">
<TD >
<pvs:SortLinkButton ID="Sortablelinkbutton1"
Runat="server"
SortType="Customer ID"
AscImage="images/ArrowAsc.gif"
DecImage="images/ArrowDec.gif">Customer ID</pvs:SortLinkButton>
</TD>
<TD>
<pvs:SortLinkButton ID="Sortablelinkbutton2"
Runat="server"
SortType="Company Name"
AscImage="images/ArrowAsc.gif"
DecImage="images/ArrowDec.gif">Company Name</pvs:SortLinkButton>
</TD>
<TD>
<pvs:SortLinkButton ID="Sortlinkbutton1"
Runat="server"
SortType="Address"
AscImage="images/ArrowAsc.gif"
DecImage="images/ArrowDec.gif">Address</pvs:SortLinkButton>
</TD>
<TD>
<pvs:SortLinkButton ID="Sortlinkbutton2"
Runat="server"
SortType="City"
AscImage="images/ArrowAsc.gif"
DecImage="images/ArrowDec.gif">City</pvs:SortLinkButton>
</TD>
<TD>
<pvs:SortLinkButton ID="Sortlinkbutton3"
Runat="server"
SortType="Phone"
AscImage="images/ArrowAsc.gif"
DecImage="images/ArrowDec.gif">Phone</pvs:SortLinkButton>
</TD>
</TR>
</HEADERTEMPLATE>
<ITEMTEMPLATE>
<TR>
<TD><%# DataBinder.Eval(Container.DataItem,"CustomerId")%> </TD>
<TD><%# DataBinder.Eval(Container.DataItem,"CompanyName")%> </TD>
<TD><%# DataBinder.Eval(Container.DataItem,"Address")%> </TD>
<TD><%# DataBinder.Eval(Container.DataItem,"City")%> </TD>
<TD><%# DataBinder.Eval(Container.DataItem,"Phone")%> </TD>
</TR>
</ITEMTEMPLATE>
<FOOTERTEMPLATE>
<tr>
<td></td>
</tr>
</FOOTERTEMPLATE>
</pvs:PagedRepeater>
</table>
</form>
Hook into the events
The final step is to hook in the events of the control.
First, add the proper event handlers to the code and set the default paging properties. RowsPerPageIncrement determines the increment in the values located in the Rows per Page drop down list that is added to the Place holder mentioned above. Min and Max rows should be fairly self explanatory.
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
this.rptCustomers.ReSorted += new EventHandler(rptSelectedMedical_ReSorted);
this.rptCustomers.RowsPerPageChanged +=
new EventHandler(rptCustomers_RowsPerPageChanged);
this.rptCustomers.PageIndexChanged +=
new Pariveda.Web.UI.WebControls.PagedRepeaterPageChangedHandler
(rptCustomers_PageIndexChanged);
this.rptCustomers.RowsPerPageIncrement = 5;
this.rptCustomers.MaxRowsPerPage = 50;
this.rptCustomers.MinRowsPerPage = 5;
base.OnInit(e);
}
Next we will add code to each of these events. The RowsPerPageChanged event occurs when a user changes the value in the Rows Per Page drop down list. The PageIndexChanged event is raised any time the user request a new page of data. The Resorted event occurs any time a SortLinkButton with in the control is clicked.
private void rptCustomers_RowsPerPageChanged(object sender, EventArgs e)
{ BindCustomers();
}
private void rptCustomers_PageIndexChanged(object sender,
Pariveda.Web.UI.WebControls.PagedRepeaterPageChangedEventArgs e)
{ rptCustomers.CurrentPageIndex = e.NewPageIndex;
BindCustomers();
}
private void rptSelectedMedical_ReSorted(object sender, EventArgs e)
{ BindCustomers();
}
Finally the BindCustomers Method will determine the Start and End rows along with the Sort Column and Sort Direction and bind the results to the data repeater.
private void BindCustomers()
{ Customers customers = new Customers();
int startRow = 0;
int endRow = 0;
int rowsPerPage = rptCustomers.PageSize;
int pageIndex = rptCustomers.CurrentPageIndex + 1;
string sortColumn = rptCustomers.SortType;
string sortDirection = rptCustomers.IsAscending ? "A" : "D";
endRow = rowsPerPage * pageIndex;
startRow = endRow - (rowsPerPage - 1);
customers = CustomerController.GetCustomers(startRow,
endRow,
sortColumn,
sortDirection);
rptCustomers.DataSource = customers.CustomerCollection;
rptCustomers.VirtualItemCount = customers.VirtualCount;
rptCustomers.DataBind();
}
In conclusion
This example demonstrates how to use a custom web control to perform database sorting and paging in order to increase application performance by reducing the size of results sets. Full source code for this example is available upon request, so feel free to email me at Justin.finch@parivedasolutions.com .