Extreme ASP.NET
A Little Bit of Control for Your Controls
Rob Howard
Contents
Having worked for so many years designing and developing ASP.NET while at Microsoft, it's exciting now to have a venue in which to talk about it. In this new column, Extreme ASP.NET, I'll discuss and demonstrate time-tested techniques and approaches to implementing high-performance, reliable, secure, and user-friendly Web applications with ASP.NET.
Server Controls
When you're first learning to use ASP.NET, you'll quickly discover that there are various layers to the technology. The HttpRuntime is the lowest layer and has many similarities with the IIS ISAPI programming model. Using the HttpRuntime, you could actually build many technologies such as ASP, PHP, Cold Fusion, and so on.
The HttpRuntime is simply a request/response processing engine. The ASP.NET page model is then an implementation on top of the HttpRuntime—something most developers are more familiar with. Within the ASP.NET page model, you will then quickly find yourself using server controls to compose rich and complex page functionality using simple declarative statements.
ASP.NET controls, better known as server controls because they run on the server, are the developer's toolset for quickly writing Web applications. In fact, server controls are what ASP.NET page development is all about. Using them you can easily get great functionality for database access, calendars, textboxes, dropdown lists, and lots of other common composite Web functionality. Plus you can do it quickly through declarative programming.
The sooner you realize just how essential controls are to the ASP.NET page programming model, the better. For example, in ASP.NET 2.0 there are nearly 60 new server controls. The fact that the Page class is also a control indicates just how important server controls are to ASP.NET.
Both personally and professionally, I'm an advocate of writing as little code in a page as possible by encapsulating user interface functionality into server controls. If you download and examine Community Server (
www.communityserver.org) you'll find that 99 percent of the pages are containers for server controls. All UI behavior, layout, and other aspects of the interface are accessed strictly through server controls.
In the January 2005 issue of
MSDN®Magazine, I discussed 10 performance tips for writing better ASP.NET applications (see
ASP.NET: 10 Tips for Writing High-Performance Web Applications). One of those tips was to reduce the amount of data sent back and forth from the database while another was to batch multiple resultsets together to reduce the number of round-trips. In this month's column I'll look at combining those tips and exposing their functionality through a custom server control that handles paging.
The Need for Data Paging
There are two ways you can create a server control. One way is to take any ASP.NET page, make minor modifications to the code, and rename the extension from aspx to ascx. The user control can then be used declaratively within other pages. The second way is to crack open your favorite code editor and write a class that ultimately derives from the Control base class.
Writing compiled server controls is my preference. Compiled controls give you the greatest flexibility over what your control does. I can't possibly go into all the details and nuances of compiled server controls—for that I'd recommend you pick up the excellent book Developing Microsoft ASP.NET Server Controls and Components by Nikhil Kothari and Vandana Datye (Microsoft Press®, 2002).
One of the tips in my article concerned reducing time spent communicating with the database or "time spent on the wire." The most salient point this tip makes is that going back and forth to the database repeatedly is inefficient. Instead, a more efficient technique is to return multiple resultsets from the database when possible. This is accomplished using a stored procedure, and each resultset is processed using the DataReader's NextResult method:
// read first resultset
reader = command.ExecuteReader();
Supplier.DataSource = reader;
Supplier.DataBind();
// read next resultset
reader.NextResult();
Products.DataSource = reader;
Products.DataBind();
The second tip, which also used the multiple resultset tip, is to be smart about how much data to return from the database. Using a technique known as paging, resultsets are further reduced to only the data required to satisfy the request. However, to successfully page through the data you also have to know how many records are returned in the original query, thus the need to return a second resultset containing the total number of records.
Figure 1 shows a stored procedure that can be used to page through the Orders table in the sample Northwind database. The procedure first calculates the total number of records it has to return to retrieve the requested data. It sets the ROWCOUNT to this value, forcing SQL Server™ to return only this specified number of records. It then does some calculations on lower and upper bounds of the page. Next a temporary #PageIndex table is created with an IndexID identity column and an OrderID column. Note that if I were working with extremely large sets of data I could make some additional optimizations, such as adding a SQL index on the IndexID column.
Figure 1 Paging Stored Procedure
CREATE PROCEDURE northwind_OrdersPaged
(
@PageIndex int,
@PageSize int
)
AS
BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int
-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn
-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
IndexId int IDENTITY (1, 1) NOT NULL,
OrderID int
)
-- Insert into the temp table
INSERT INTO #PageIndex (OrderID)
SELECT
OrderID
FROM
Orders
ORDER BY
OrderID DESC
-- Return total count
SELECT COUNT(OrderID) FROM Orders
-- Return paged results
SELECT
O.*
FROM
Orders O,
#PageIndex PageIndex
WHERE
O.OrderID = PageIndex.OrderID AND
PageIndex.IndexID > @PageLowerBound AND
PageIndex.IndexID < @PageUpperBound
ORDER BY
PageIndex.IndexID
END
Next I insert into the temporary #PageIndex table the OrderID values from the Orders table. If I wanted to select only data that matched a specific criteria, I would add my WHERE clause, like I did here to get a specific data range. The number of records added to the #PageIndex table is limited based on the ROWCOUNT set previously. I then retrieve the total number of records available with SELECT COUNT(OrderID) FROM Orders. This is my first resultset returned from the stored procedure.
Finally, I SELECT the data I want from the Orders table by joining on the temporary #PageIndex table and controlling the returned results through the desired upper and lower bounds requests and ordering by the IndexID.
You might be wondering why I don't just use the OrderID or another identity column on the Orders table for accomplishing page selection. Well I can't do this for several reasons: first, there is no guarantee that the initial values in the identity column are contiguous (a row might have been deleted), and second, the numerical order will not be contiguous if a WHERE clause is used. The #PageIndex table builds a contiguous set of numerically ordered values based on how they were selected from the Orders table.
As you can see, this complicates the data access layer quite a bit, although it makes data access more efficient as the database grows. Requests execute through a stored procedure that takes a number of parameters, such as the page index, page number, and any other information required to create the resultset. Once returned, the resultset must be processed and data returned.
So you get the general idea—lots of work to get the desired result!
One thing you can do to simplify this data access functionality is create a server control to expose the UI required to page through data. Such a control would allow you to easily navigate to the first or last record with a single click or to walk through pages one at a time by clicking. The following shows the kind of UI I would expect to see:
Page 1 of 19 (373 items) 1 2 3 4 5 >> ... Last >>
There are two server controls used to generate the UI. The first is a server control called CurrentPage and the second is a control called Pager. Both are custom controls that ship with Community Server, the full source of which is available with the released version. Here I'm going to examine only the Pager control.
Using the Pager Control
The Pager control performs the following actions: it generates UI for easy navigation to the first or last page with a single click, it walks backwards and forwards through pages using numerical page numbers or forward/backward links, it displays the current page as plain text (not a link), and it raises a server-side event when a link button on the pager is clicked. Figure 2 shows a sample use of the Pager control used with a DataGrid.
Figure 2 Pager Control
<%@ Page language="cs" Codebehind="BetterPager.aspx.cs"
AutoEventWireup="false" Inherits="BlackBelt.Pager" %>
<%@ Register TagPrefix="cc1" Namespace="Controls" Assembly="Controls" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
<title>Pager</title>
<meta name="GENERATOR" Content="Microsoft Visual Studio .NET 7.1">
<meta name="CODE_LANGUAGE" Content="C#">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema"
content="http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<body>
<form id="Form1" method="post" runat="server">
<P>
<asp:DataGrid id="DataGrid1" runat="server"></asp:DataGrid></P>
<P>
<cc1:Pager id="Pager1" runat="server"></cc1:Pager></P>
</form>
</body>
</HTML>
Let's take a look at the Page_Load method and the event raised by the Pager control in this page—remember, ideally you would encapsulate all of this functionality in a separate control too, but for the purpose of this column I'll keep it simple:
public void Page_Load() {
if (!Page.IsPostBack)
Bind();
}
In the Page_Load I determine if I've already bound the data. If it's the first request, I'll call Bind to initially bind the DataGrid with default data and also set up the Pager (see Figure 3).
Figure 3 Bind to the Grid
private void Bind() {
SqlConnection connection =
new SqlConnection(ConfigurationSettings.AppSettings("Northwind"));
SqlCommand command =
new SqlCommand("northwind_OrdersPaged", connection);
SqlDataReader reader;
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@PageIndex", SqlDbType.Int).Value =
Pager1.PageIndex;
command.Parameters.Add("@PageSize", SqlDbType.Int).Value =
Pager1.PageSize;
try {
connection.Open();
reader = command.ExecuteReader();
reader.Read();
Pager1.TotalRecords = CType(reader(0), Integer);
reader.NextResult();
DataGrid1.DataSource = reader;
DataGrid1.DataBind();
} catch (Exception ex){
... // log exception
} finally {
connection.Close();
}
}
In the Bind procedure I create a connection to the database and attempt to execute a stored procedure. This particular procedure, as you saw in Figure 3, requires that the page index and the page size be set. These settings tell the procedure which page you want returned and how large each page is.
Next, I open the connection to the database. The first resultset returned tells me how many total records are available that meet the criteria of the clauses in the stored procedure query. In Figure 2 the procedure is relatively simple; a more complex procedure might filter data, sort, or order the resultset through other parameters—this is exactly what my team did for the Forums feature of Community Server.
After I call Bind, the DataGrid and the Pager are both bound and rendered. When the user of the paged grid decides to click on the Pager control to navigate forwards (or backwards) the following event is raised on the server:
void Index_Changed (sender As Object, e As EventArgs) {
Bind();
}
This calls the Bind method and rebinds the DataGrid using the PageIndex value that the user clicked on or selected from the Pager.
You might be wondering why I'm going through all of this complicated paging functionality when the DataGrid already has built-in paging. What you probably didn't know is that there is a small problem with the way paging works in the DataGrid. Let's say your data layer returns 1,000 records and you enable DataGrid paging with a page size of 25—meaning 25 records displayed at a time. The DataGrid accomplishes this by binding all 1,000 records and then discarding 975 of them. So, as you page through the data you'll first show records 1-25 discarding 26-1,000. When you click Next, records 26-50 would be shown while records 1-25 and 51-1,000 would be discarded. As long as the number of records returned is relatively small this is a fairly insignificant problem.
However, as the resultsets grow, the application will experience more and more serious performance problems because it has to spend more and more time communicating with the database—imagine the earlier case in which five million records are returned! Furthermore, the whole point of paging data is to be able to deal with so much returned data. The Pager control isn't necessary for all data access, but for paging through large sets of data it will make your data access more efficient.
Note that ASP.NET 2.0 provides support for data source-level paging. Using ObjectDataSource, you can write a SelectMethod that accepts parameters for a starting row index and the maximum number of rows to return. If you then enable paging in a connected DataGridView (not a DataGrid), the DataGridView will take advantage of the paging support in the data source. You can also configure a separate method (SelectCountMethod) to return the total number of rows in the resultset (regardless of the page size) so that DataGridView can use this number to calculate how to render pager UI links. In this case, the DataGridView asks the data source for just the data it needs in order to render the current page instead of asking for all of the data and throwing away rows.
Implementing the Pager Control
Now that you've seen how the Pager control works, let's look at its implementation. The Pager control derives from the ASP.NET Label control and uses the INamingContainer marker interface:
public class Pager : Label, INamingContainer {
As shown earlier, there are a couple of properties on the Pager that must be set. The first is the TotalRecords property. This property tells the control how many total records exist in the data source with which the Pager is associated. The second is PageSize. In the control there is a default value of 25. PageSize is required to calculate the total number of pages available in the CalculateTotalPages helper function (see
Figure 4).
Figure 4 Calculate Total Pages
public int CalculateTotalPages() {
int totalPagesAvailable;
if (TotalRecords == 0)
return 0;
// First calculate the division
totalPagesAvailable = TotalRecords / PageSize;
// Now do a mod for any remainder
if ((TotalRecords % PageSize) > 0)
totalPagesAvailable++;
return totalPagesAvailable;
}
Next, let's look at how the control renders itself. The Pager overrides both the CreateChildControls and Render methods. All of the event wire-up happens in CreateChildControls when the control tree is created. To ensure that the event is wired correctly, a link button is created for every page available. Later the Render method is called when it is time for the Pager to render its output. Within Render there exists logic to display the correct link buttons based on the current PageIndex and PageSize being used. For example, the RenderLast method is used to Render a link button with the text "Last", but only when the last page is not the current page:
void RenderLast (HtmlTextWriter writer) {
int totalPages = CalculateTotalPages();
if (((PageIndex + 3) < totalPages) && (totalPages > 5)) {
LiteralControl l = new LiteralControl(" ... ");
l.RenderControl(writer);
lastButton.RenderControl(writer);
}
}
The most complex logic is in the RenderPagingButtons method (see
Figure 5).
Figure 5 RenderPagingButtons
void RenderPagingButtons(HtmlTextWriter writer) {
int totalPages;
// Get the total pages available
totalPages = CalculateTotalPages();
// If we have <= 5 pages display all the pages and exit
if ( totalPages <= 5) {
RenderButtonRange(0, totalPages, writer);
} else {
int lowerBound = (PageIndex - 2);
int upperBound = (PageIndex + 3);
if (lowerBound <= 0)
lowerBound = 0;
if (PageIndex == 0)
RenderButtonRange(0, 5, writer);
else if (PageIndex == 1)
RenderButtonRange(0, (PageIndex + 4), writer);
else if (PageIndex < (totalPages - 2))
RenderButtonRange(lowerBound, (PageIndex + 3), writer);
else if (PageIndex == (totalPages - 2))
RenderButtonRange((totalPages - 5), (PageIndex + 2), writer);
else if (PageIndex == (totalPages - 1))
RenderButtonRange((totalPages - 5), (PageIndex + 1), writer);
}
}
As shown earlier, applications that use the Pager control must subscribe to an event raised by the Pager when a new page index is selected by the user:
public event System.EventHandler IndexChanged;
All the LinkButtons added to the PagerControl are wired to an internal PageIndex_Click event handler. Two things happen when an internal click event is raised. First, the PageIndex is set to the page selected by the user. The page index is stored as a CommandArgument of each LinkButton. Second, the IndexChanged event is raised and subscribers can handle the event. Here is the code that handles the internal PageIndex_Click event:
void PageIndex_Click(Object sender, EventArgs e) {
PageIndex = Convert.ToInt32(((LinkButton) sender).CommandArgument);
if (null != IndexChanged)
IndexChanged(sender, e);
}
The biggest shortcoming of the Pager control—and something I'll likely fix in a future version of the Community Server controls—is that it requires postbacks. Due to some logic paths, postbacks are required, and while it's technically possible for a search engine to follow postback links, Google, MSN® Search, and other popular search engines don't currently do so. In a future version we'll likely support standard links in the pager, too.
Using the Pager control combined with some smarter data access tips can help your ASP.NET application be more efficient when using the database. The Pager server control shows how you can encapsulate some complex functionality into a reusable server control that supports events. Controls make writing reusable functionality incredibly easy in ASP.NET.
Send your questions and comments for Rob to xtrmasp@microsoft.com.
Rob Howard is a founder of Telligent Systems, specializing in high-performance Web apps and knowledge management and collaboration systems. Previously, Rob was employed by Microsoft where he helped design the infrastructure features of ASP.NET 1.0, 1.1, and 2.0.