Introduction
When making the transition from ASP to ASP.NET, you will discover that paging through database records has become both remarkably simple and more difficult at the same time. The DataGrid control has made it a breeze to create a Web page that allows the user to page through the records of a database query. However, the simplicity offered by the DataGrid's default paging mechanism comes at a cost of performance.
Essentially, the DataGrid offers two modes of paging support: default paging and custom paging. Default paging is the easier of the two to implement, and can be done with just a few lines of code. However, realize that the default paging method retrieves all of the data, but then displays only a small subset of data to be displayed on the current page. That is, every time a user navigates to a different page of the data, the DataGrid re-retrieves all of the data.
When dealing with a small amount of data, this behavior is not a major concern. For small datasets, the simplicity of default paging outweighs its inefficiency, but for large amounts of data, the unnecessary overhead of the default paging can be detrimental to the performance of the Web application and database.
As a solution for the default paging's inefficiency, the DataGrid also provides custom paging, which avoids the default paging's inefficiency by retrieving only that data that belongs that is to be displayed on the current page of data. As its name implies, the custom paging method requires you, the developer, to devise some way to select only those records that need to be displayed on a specific page. There are a number of techniques that can be used to employ custom paging, from stored procedures not unlike the one used in Paging Through Records Using a Stored Procedure, to more complicated SQL expressions. While custom paging is efficient, it requires more complicated programming from the developer.
As you can see, the ease-of-use offered by the DataGrid's paging functionality comes at the expense of either efficiency or development time. There's a larger problem lingering as well. What if you're not using the DataGrid control at all? Neither the Repeater nor the DataList has built-in paging. Obviously, depending on the particular needs of your ASP.NET page, you may not choose to use any of these controls. You may iterate through the results of a query programmatically, as in classic ASP. Simply put, if you don't use the DataGrid, you'll find that paging has actually become more difficult than in ASP. ADO.NET does not support the built-in paging properties and methods of ADO. You are forced to write a paging solution from the ground up.
In the following article, I'll explain how to do just this. The paging solution I'll present will avoid the problems inherent with the DataGrid's paging methods and will be control-independent. That is, the paging solution will work with any of the data controls or with no control at all. It will allow the user to sort records by any column, will not make any unrealistic/limiting assumptions about a table's primary key, and will efficiently retrieve the data required for each page. A screenshot of the paging system in action is shown below.
Database Configuration
At the top of code-behind class, I've stored the database parameters we'll be using throughout the code in variables:
Protected ConnString As String = "server=local;database=Test;Trusted_Connection=true"
Protected TableName As String = "Contacts"
Protected PrimaryKeyColumn As String = "ID"
Protected DefaultSortColumn As String = "DateAdded"
Protected ColumnsToRetrieve As String = "DateAdded,Email,LastName"
These variables will allow you to easily configure the code-behind class to work with different tables/columns. If you are using SQL Server, these are the only lines of code in the code-behind class that must be changed. If you are not using SQL Server, you will need to alter the class to use the namespace(s) and associated classes that pertain to your database.
With regard to the HTML portion, you will simply need to change the column headers and databound columns in the Repeater templates to account for the column names you wish to retrieve. For example, in Paging.aspx you will find the Repeater to have the following HeaderTemplate and ItemTemplate:
You will need to alter the HeaderTemplate and ItemTemplate so that it has column names from the data you are binding to the Repeater. (Of course, you can replace the Repeater with the DataList or DataGrid; the point of using the Repeater in Paging.aspx was to illustrate that this paging solution was not limited only to the DataGrid Web control.)
Examining the Page_Load() Event Handler
The remainder of this article examines the various event handlers and methods in the code-behind class. Let's start by examining the Page_Load event handler, which has three main tasks:
First, it retrieves all the primary key values from the table in question. This operation ensures the accuracy of the paging, as it provides an up-to-date count of the records available for paging:
At this point, you may be wondering, "Isn't this what the DataGrid's default paging does as well?" Actually, it's not. Remember that the DataGrid's default paging grabs all the data available, regardless of what page of data you are viewing. That is, it retrieves all the columns for every row. We're just retrieving one column from all the rows (a column that usually is just integers). Retrieving a single column's worth of data as opposed to every column's data is obviously far more efficient, particularly when dealing with tables with many columns of data. Furthermore, databases automatically index a table by its primary key, so selecting all of the values of just the primary key field(s) is very quick as a simple scan of the index can be performed without any table data accesses (if this makes no sense to you, don't worry! Just realize reading the values of a primary key is not an expensive operation).
After retrieving the primary key values, the Page_Load event handler uses a SqlDataReader to read the values into an ArrayList and then closes the database connection. This saves these values in a disconnected, easily manipulated format:
(For those of you unfamiliar with ArrayLists, they are a type of collection found in the .NET Framework that allows for array-style handling of data. Unlike traditional arrays, however, ArrayLists behave in a far more intuitive fashion and do not need to be "re-dimmed" as they grow. There is also a myriad of helpful ArrayList methods that allow for fast sorting and manipulation of the data contained within the collection. For more information on the ArrayList check out this tutorial.)
Why not just use a DataSet, as opposed to reading the SqlDataReader's data into an ArrayList? I've found the latter method to be a bit faster and user friendly. The DataSet involves a lot of complexity that we don't need for the code in question and a great deal of overhead that we don't want. In a way, storing the SqlDataReader's data in an ArrayList provides us with a stripped-down, efficient "dataset" that is perfect for our needs.
Finally, after creating the ArrayList, if the page has not been posted back, the Page_Load event handler calls the Paging() method, displaying the first page of records when Paging.aspx first loads:
No comments:
Post a Comment