Working with Data 25 in ASP.net 2.0: increasing paging efficiency when large data levels _ self-study process

Introduction

As we discussed in previous tutorials, pagination can be done in two ways:

1. Default paging – You only use the Enable paging of the smart tag with the data Web control selected; However, when you are browsing the page, although you see only a small portion of the data, ObjectDataSource will always read all the data

2. Custom paging – improves performance by reading only the part of the data that users need to browse from the database. Obviously this method requires you to do more work.

The default paging feature is very attractive, because you just have to select a checkbox to do it. But it reads all the data every time, which is not appropriate in the case of large amounts of data or multiple concurrent users. In such cases, we must make the system better performance by customizing pagination.

One of the key points of customizing pagination is to write a query that returns just the data you need. Fortunately, Microsoft SQL Server 2005 provides a new keyword that lets you write queries that read the data that you need. In this tutorial, We will learn how to use this new keyword of Microsoft SQL Server 2005 to implement custom paging in the GridView. The interface for customizing pagination and default paging looks the same, but when you move from one page to another, the efficiency is a few orders of magnitude.

Note: The performance boost for customizing paging depends on the amount of data and the load on the database. At the end of this tutorial, we'll use data to illustrate the performance benefits of customizing pagination.

First step: Understanding the process of customizing pagination

When you are paging the data, the data that is displayed on the page depends on which page is requested and how many are displayed per page. For example, imagine that we give 81 product pagination, 10 per page. When we look at the first page, we need product 1 to product 10. When we browse the second page, What we need is product 11 to product 20, and so on.

For pages that need to read what data and pagination, there are three related variables:

1.Start row index– The index of the first row of data displayed in the page; This value can be obtained by adding 1 to the page's index by the number of entries shown on each page. For example, if a page displays 10 data, the first page (index 0), the first row index is 0 * + 1, or 1; For the second page (index 1), the index of the first row is 1 * 10 + 1, or 11.

2.Maximum rows– The maximum number of records displayed per page. It is called "maximum" rows because the last page displays less data than the page size. For example, when 81 is displayed with 10 records per page, the last page, which is the Nineth page, contains only one record. The number of record bars that are not displayed by the page is greater than the value of maximum Rows.

The 3.Total record count– displays the total number of bars for the data. You don't need to know what data the page displays, but the total number of records affects pagination. For example, if you are paging 81 product records, 10 pages per page, then the total number of pages is 9.

For the default paging, Start Row index is obtained by adding 1 to the page index and the number of records per page. Maximum rows is the number of records per page. When you use the default paging, regardless of which page of data is rendered, all the data is read, and the index of each row is known, so that you get the Start Row index becomes worthless. Moreover, the total number of records can be obtained through the total number of DataTable.

Custom paging returns only the maximum rows record starting at start Row Index. Here are two places to note:

1. We must associate the entire paging data with a ROW index to return the required data from the specified start Row index.

2. We need to provide the total number of pages used for paging data.

In the next two steps we will write the SQL related to the above two points. In addition, we will complete the corresponding methods in the Dal and BLL.

Step Two: Returns the total number of records that require paging

Before we learn how to return the data needed to display the page, let's take a look at how to get the total number of data. Because this information is needed to configure the interface. We use the SQL Count aggregate function to implement this. Returns the total number of record bars in the Products table, we can use the following statement:

SELECT COUNT (*) from the products

We add a method in the DAL to return this information. This method is named Totalnumberofproducts (), which executes the SQL statement above.

Open the Northwind.xsd in the App_code/dal folder. Then in the designer, right-click the ProductsTableAdapter and select Add Query. As we have learned in previous tutorials, this will allow us to add a new DAL method, This method executes the specified SQL or stored procedure when invoked. As in the previous TableAdapter method, add a SQL statement to this.

In the next form we can specify which SQL to create. Because the query returns only a value –products the total number of records for the table – we select "Select which returns a Singe value".

The next step is to write the SQL statement.

Finally, the method is named Totalnumberofproducts.

After clicking, a totalnumberofproducts method is added to the DAL. The value returned by this method can be null, and the count statement always returns a Non-null value.
We also need to add a method to the BLL. Open the Productsbll class file and add a Totalnumberofproducts method, and this method simply calls the DAL's Totalnumberofproducts method.

public int totalnumberofproducts ()
{return
 adapter.totalnumberofproducts (). GetValueOrDefault ();
}

The Totalnumberofproducts method of the Dal returns a nullable integral type, and the Totalnumberofproducts method that requires the PRODUCTSBLL class returns a standard integer. Call the GetValueOrDefault method, If an nullable integer is empty, the default value is returned, 0.

Step three: Return the required data records

The next step is to create a method that accepts start Row Index and maximum Rows in the Dal and BLL, and then returns the appropriate records. Let's take a look at the SQL statements that we need. The challenge is to assign an index to the entire paging record to return from the Start row Maximum Records number of Records of Index start.

If there is already a column in the database table as an index, then everything will be very simple. We first think of the ProductID field in the products list to meet this condition, the first product ProductID is 1, the second is 2, And so on. However, when a product is deleted, the sequence leaves an interval, so this method does not work.

There are two ways to correlate the entire page-splitting data with a row index.

1. Using the new features of SQL Server 2005 's row_number () keyword–sql Server 2005, it allows records to be sorted in a certain order, with each record and a level-related level that can be used as row index for each record.

2. The SET ROWCOUNT statement using Set ROWCOUNT–SQL server can be used to specify how many records need to be processed; Table variables is a table-type T-SQL variable, similar to temporary tables. This method is available in both Microsoft SQL Server 2005 and SQL Server 2000 (the Row_number () method can only be used in SQL Server 2005).

The idea is to create a table variable for the paging data, which has an identity column that is the primary key. This requires that each record of pagination be in the table variable and one row index (through the identity column) Connected. Once the table variable is generated, the SELECT statement that connects the database table is executed to obtain the required records. SET rowcount is used to limit the number of records placed in a table variable.

When the value of the SET ROWCOUNT is specified as start Row Index plus maximum rows, the efficiency of this method depends on the number of pages requested. For comparison to the previous page-for example, to start a few pages-this method works very well. But for a page close to the end, the efficiency of this method is almost the same as the default paging.

This tutorial uses row_number () to implement custom paging. If you need to know more about table variables and SET ROWCOUNT technology, see a more efficient methods for paging through Large result Se Ts.

Use the following statement to associate a level with each record returned by using Row_number ():

SELECT columnlist,
 row_number () over (Orderbyclause) from
tablename

Row_number () Returns a value that represents the rank of each record according to the specified sort. For example, we can use to view the rank of each product sorted by price (descending):

SELECT ProductName, UnitPrice,
 row_number () over (order by UnitPrice DESC) as Pricerank
from

Figure 5 is the result of running the above code in Visual Studio. Note that product is sorted by price, with one level per line.

Note: Row_number () is only one of many levels of functionality in SQL Server 2005. To learn more about Row_number (), including other row-level features, see returning ranked Results with Microsoft SQL Server 2005.

SQL Server sorts the results when using the order by column name (UnitPrice) in the over clause. To increase performance during large data query, you can add a nonclustered index to the column you are sorting. More performance Considerations Reference ranking functions and performance in SQL Server 2005.

The level information returned by Row_number () cannot be used directly in the WHERE clause. In the select from the back, you can return Row_number () and use it in the WHERE clause. For example, The following statement uses a from after select to return the result of Productname,unitprice, and Row_number (), and then uses a WHERE clause to return the product of price rank between 11 and 20.

Select Pricerank, ProductName, UnitPrice
from
 (select ProductName, UnitPrice,
 row_number () UnitPrice DESC) as Pricerank from Products
 ) as Productswithrownumber
WHERE Pricerank BETWEEN One and 20

Further, we can return the data for the pages given start Row Index and maximum Rows according to this method.

Select Pricerank, ProductName, UnitPrice
from
 (select ProductName, UnitPrice,
 row_number () UnitPrice DESC) as Pricerank from Products
 ) as Productswithrownumber
WHERE pricerank > <i> Startrowindex</i> and
 Pricerank <= (<i>StartRowIndex</i> + <i>MaximumRows</i>)

Note: As we'll see later in this tutorial, the startRowIndex provided by ObjectDataSource starts at 0, and Row_number () begins with a value of 1. So, The WHERE clause return will strictly return those records with Pricerank greater than startrowindex and less than startrowindex+maximumrows.

We already know how to return data for a particular page with Row_number () based on the given start Row Index and maximum Rows. Now we need to implement it in the Dal and the BLL.

We first have to decide what sort to rank according to. We use the alphabetical order of the product name here. This means that we are not able to implement the sorting function at the same time. In the following tutorial, we will learn how to implement this functionality.

In the front we use SQL statement to create the Dal method. But the T-SQL parser in the visual Stuido used by TableAdapter Wizard does not recognize row_number with over syntax () method. So we want to create this DAL method with stored procedures. Select Server Explorer (ctrl+alt+s) from the View menu to expand the Northwnd.mdf node. Right-click the stored procedure and select Add a new stored procedure (see Figure 6).

This stored procedure takes two integer input parameters-@startRowIndex and @maximumrows-and is sorted by Row_number () in ProductName fields. Returns records that are greater than @startrowindex and less than or equal to @startrowindex+ @maximumRows. Add the following code to the stored procedure and save it.

CREATE PROCEDURE dbo.GetProductsPaged
(
 @startRowIndex int,
 @maximumRows int
)
AS
 SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit,
 UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued,
 CategoryName, SupplierName
FROM
 (
 SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit,
 UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued,
 (SELECT CategoryName
 FROM Categories
 WHERE Categories.CategoryID = Products.CategoryID) AS CategoryName,
 (SELECT CompanyName
 FROM Suppliers
 WHERE Suppliers.SupplierID = Products.SupplierID) AS SupplierName,
 ROW_NUMBER() OVER (ORDER BY ProductName) AS RowRank
 FROM Products
 ) AS ProductsWithRowNumbers
WHERE RowRank > @startRowIndex AND RowRank <= (@startRowIndex + @maximumRows)

After you create the stored procedure, take a moment to test it. Right-click the stored procedure named getproductspaged in Server Explorer, select Execute. Visual Studio lets you enter parameters, @startRowIndex and @maximumrows (see Figure 7). Enter a different value to see what the result is.

After you enter the value of the parameter, you will see the result. The result of Figure 8 is the result of a value of 10 for two parameters.

After the stored procedure is complete, we can create the ProductsTableAdapter method. Open the Northwind.xsd, right-click ProductsTableAdapter, and select Add Query. Choose to use a stored procedure that already exists.

The next step is to select the stored procedure to invoke. Select Getproductspaged from the dropdown list.

The next step is to select the data type returned by the stored procedure: A table value, a single value, and no value. Select a table value because the getproductspaged returns more than one record.

Finally, the method is named. Like the previous method, select Fill a DataTable and return a DataTable, named Fillpaged for the first, and getproductspaged for the second.

In addition to creating a Dal method to return products for a particular page, we need to do the same in BLL. Like the Dal method, the BLL getproductspaged method takes two integer input parameters, namely start Row Index and maximum Rows and returns the records in the specified range. Create this method in Productsbll, just call the Dal getproductspaged.

[System.ComponentModel.DataObjectMethodAttribute (System.ComponentModel.DataObjectMethodType.Select, false)]
Public northwind.productsdatatable getproductspaged (int startrowindex, int maximumrows)
{
 return Adapter.getproductspaged (startRowIndex, maximumrows);
}

You can take any name for the parameters of the BLL method. But we'll see right away that choosing to use startRowIndex and maximumrows will make it much easier to configure ObjectDataSource.
Step Fourth: Use custom paging configuration ObjectDataSource

After creating the BLL and Dal methods, we can prepare to create a GridView to use custom paging. Open the efficientpaging.aspx in the PagingAndSorting folder, add a GridView, Then use ObjectDataSource to configure it. In our previous tutorials, we typically use the GetProducts method of the PRODUCTSBLL class to configure ObjectDataSource. But this time, We use the Getproductspaged method. GetProducts returns all products and getproductspaged only returns a specific record.

We want to create a read-only GridView, so select (None) in the Insert, UPDATE, and delete tab Drop-down list.

The ObjectDataSource Wizard then lets us select the values for the input parameters startRowIndex and maximumrows of the Getproductspaged method. Select None in Source.

After completing the ObjectDataSource Wizard, the GridView creates a BoundField or CheckBoxField for each product field. You can cut the GridView at will. 's appearance. I chose here to show only ProductName, CategoryName, SupplierName, QuantityPerUnit, and UnitPrice boundfields. Select support Paging in smart tags, The GridView and ObjectDataSource markings should look similar to the following:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
 DataKeyNames="ProductID" DataSourceID="ObjectDataSource1" AllowPaging="True">
 <Columns>
 <asp:BoundField DataField="ProductName" HeaderText="Product"
 SortExpression="ProductName" />
 <asp:BoundField DataField="CategoryName" HeaderText="Category"
 ReadOnly="True" SortExpression="CategoryName" />
 <asp:BoundField DataField="SupplierName" HeaderText="Supplier"
 SortExpression="SupplierName" />
 <asp:BoundField DataField="QuantityPerUnit" HeaderText="Qty/Unit"
 SortExpression="QuantityPerUnit" />
 <asp:BoundField DataField="UnitPrice" DataFormatString="{0:c}"
 HeaderText="Price" HtmlEncode="False" SortExpression="UnitPrice" />
 </Columns>
</asp:GridView>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
 OldValuesParameterFormatString="original_{0}" SelectMethod="GetProductsPaged"
 TypeName="ProductsBLL">
 <SelectParameters>
 <asp:Parameter Name="startRowIndex" Type="Int32" />
 <asp:Parameter Name="maximumRows" Type="Int32" />
 </SelectParameters>
</asp:ObjectDataSource>

	
  

		
   

	
  

If you browse the page through a browser, you will find that the GridView is not visible.

Because the getproductspaged startrowindex and maximumrows parameters in ObjectDataSource are 0, no records are returned by SQL so the GridView is invisible.

We need to make the ObjectDataSource configuration a custom paging to fix the problem. The following steps can be done with this:

1. Set the EnablePaging property of ObjectDataSource to true – this means that two parameters must be passed to SelectMethod method: a specified start Row Index ( StartRowIndexParameterName), a specified maximum Rows (maximumrowsparametername).

2. Set the StartRowIndexParameterName and MaximumRowsParameterName properties of the ObjectDataSource –startrowindexparametername And the MaximumRowsParameterName property is passed to Selecmethod with input parameters that customize the paging. The default parameter name is Startindexrow and Maximumrows, which is why you use these to name the parameters when you create the Getproductspaged method in BLL. If you use other parameter names-like startindex and maxrows– You will have to set ObjectDataSource StartRowIndexParameterName and MaximumRowsParameterName (startindex and MaxRows) accordingly.

3. Set the name of the method that ObjectDataSource SelectCountMethod property to return the total number of paging records (Totalnumberofproducts)- The Totalnumberofproducts method that calls the PRODUCTSBLL class returns the total number of records. ObjectDataSource needs this information to properly display the page.

4. Removal of startRowIndex and maximumrows <asp:Parameter> elements from ObjectDataSource's declaration-when ObjectDataSource is configured through the wizard, Visual Studio automatically added two <asp:Parameter> elements to the parameters of the Getproductspaged method. When set enablepaging to True, these parameters are automatically passed, and if they are preserved in the declaration code, Then ObjectDataSource will attempt to pass 4 parameters to getproductspaged and 2 parameters to totalnumberofproducts. If you don't remove <asp:Parameter>, When browsing the page, you will get an error message like this: "ObjectDataSource ' ObjectDataSource1 ' could not find a Non-generic method ' Totalnumberofproducts ' that has Parameters:startrowindex, maximumrows. "

After these changes are made, the ObjectDataSource Declaration code should look similar to the following:

<asp:objectdatasource id= "ObjectDataSource1" runat= "Server"
 oldvaluesparameterformatstring= "original_{0}" Typename= "PRODUCTSBLL"
 selectmethod= "getproductspaged" enablepaging= "True"
 selectcountmethod= " Totalnumberofproducts ">
</asp:ObjectDataSource>

Note that the enablepaging and SelectCountMethod properties have been set,<asp:parameter> removed. Figure 16 is the Properties window.

When you're done, browse the page. You will see that 10 items are listed alphabetically. Every time you flip a page, look at it. There is no difference for the user, because the custom paging can be displayed with a large amount of data.

Note: When customizing pagination, the page count returned by the ObjectDataSource SelectCountMethod method exists in the view state of the GridView. Other variable –pageindex,editindex, Selectedindex,datakeys collection, etc.-all exist in control. The state is independent of the EnableViewState property of the GridView. Because PageCount values exist in ViewState during postback, you need to open the GridView view when you have links to the previous page on your page. State (if you do not have this link, you can disable view state).

A page link will cause Postback,gridview to update the PageIndex property. The GridView assigns pageindex a value less than PageCount. If view state is disabled, The value of PageCount is lost when postback, and pageindex is assigned a maximum integer value. Then the GridView calculates PageCount row according to the pagesize multiply starting OverflowException exception occurs when index.

Perform custom paging and sorting

The sort field we are using to customize pagination is written when the getproductspaged stored procedure is created. There is a checkbox in the GridView smart tag with the enable sorting, unfortunately, Adding the sort function in the previous work only sorts the records of the current page. For example, to view the first page of data in descending order, the first page of the product is reversed. See figure 18,carnarvon Tigers became the first record, The 71 records that followed it were ignored. Sorted with only the data displayed on the first page.

This occurs because the BLL getproductspaged method returns the data before it is sorted. The Auricular method returns only the records for a particular page. For the right sort, we need to upload the sort expression to the Getproductspaged method, Sorts before returning data for a particular page. We will complete this function in the later tutorial.

Perform custom paging and deletion

If you open the GridView deletion feature, you will find that the GridView disappears when you delete the last record of the last page, not the correct value of pageindex. Open the Delete in the GridView we created above to view this bug. To the last page (Nineth page) , because we have 81 records, each page shows 10, so you will only see a record, delete this record.

When the default paging occurs, the GridView automatically jumps to page eighth, which is what we want. However, in custom pagination, the GridView appears. The reason for this is somewhat beyond the scope of this tutorial, and you can see deleting the last Page from a GridView with Custom paging. Simply because of the point delete, the GridView works by this step:

Deletes a record.

Obtain records according to the given pageindex and pagesize.

Check pageindex to ensure that there are no more pages than the data source. If so, the pageindex of the GridView will be automatically reduced.

The record obtained using the second step is bound to the appropriate page of the GridView.

The root of the problem is the second step, when you get the displayed record, the pageindex that you use is still the pageindex of the last page. Therefore, no records are returned. In the third step the GridView determines the total number of pages that the PageIndex property is greater than the data source ( Because the last piece of data on the last page is deleted, the pageindex is reduced by 1. In step fourth, the GridView attempts to bind the data obtained in the second step as a data source, but without any data, Therefore the displayed GridView is missing. The default page does not have this problem because in the second step or all the data returned.

We can modify this in two ways. The first is to create an event for the rowdeleted event of the GridView handler
To determine how many records are in the delete page, if there is only one, then this is definitely the last one, we need to subtract 1 for pageindex. Of course we want to modify the value of pageindex only after the deletion succeeds. We need to judge whether the E.exception attribute is empty.

This method works because it modifies the value of the pageindex between the first and second steps. So the correct records will be returned in the second step. See the following code:

protected void gridview1_rowdeleted (object sender, Gridviewdeletedeventargs e)
{
 //If We just deleted the last Ro W in the GridView, decrement the PageIndex
 if (e.exception = null && GridView1.Rows.Count = 1)
 //We jus T deleted the last row
 Gridview1.pageindex = Math.max (0, gridview1.pageindex-1);
}

Alternatively, create an event handler for the ObjectDataSource rowdeleted event, and set the AffectedRows property to 1. After you delete a record in the first step (before the second step), if one or more rows of records are affected, The GridView updates the value of the pageindex. However, ObjectDataSource does not set the affectedrows, so this step is not performed. We need to manually set the AffectedRows if the delete operation is successful. See the following code:

protected void objectdatasource1_deleted (
 object sender, ObjectDataSourceStatusEventArgs e)
{
 //If we get Back a Boolean value from the Deleteproduct method and it ' s true,
 //Then we successfully deleted the product. Set affectedrows to 1
 if (E.returnvalue is bool && ((bool) e.returnvalue) = = True)
 e.affectedrows = 1;
}

This code can be found in Efficientpaging.aspx's Code-behind class.

Compare the performance of default and custom paging

Since custom paging returns the data that you want, and the default paging returns all of the data, it is clear that custom paging is more efficient than the default paging. But how much is the performance improvement? What are the performance advantages of changing from a default paging to a custom page?

Unfortunately, there is no uniform answer. The advantages of performance depend on many factors, the most important being the number of paging records, the load of the database, and the channel of communication between the Web server and the database. For some small tables, the difference in performance is negligible. The difference is obvious for tables with thousands of rows of data. .

One of our custom paging in asp.net 2.0 the with SQL Server 2005 article contains some performance tests that compare the two paging technologies, and the tables used have about 50,000 records. In the test, I tested the SQL Time to execute a query in the server (using SQL Profiler) and asp.net pages (using ASP.net ' s tracing features). Note that this is a test result for a single user in my development environment, so there is no simulation of the load on a typical web site and the results are not scientific.

	Avg. Duration (sec)	Reads
Default Paging–sql Profiler	1.411	383
Custom Paging–sql Profiler	0.002	29
Default paging–asp.net Trace	2.379	N/A
Custom paging–asp.net Trace	0.029	N/A

As you can see, getting a specific page of data on average is less 354 reads and is done in a short time. In the page, custom paging is 1/100 of the time that the default paging takes. You can see more test information and code in my article, and you can download the test database and test it again in your environment.

Summarize

The default paging is very easy to implement – you only need to select the Enable paging in the smart tag on the control checkbox– but the convenience is the loss of performance. When the default paging occurs, all data is returned, regardless of which page is requested, even if only a small portion is displayed. To improve performance, ObjectDataSource provides an alternative paging feature – custom paging.

Custom paging solves the default paging performance problem by getting only the data that needs to be displayed, but it is more cumbersome to use. First, query statements that request specific data must be correct and valid. This can be done in a number of ways. In this tutorial we use the row_number of SQL Server 2005 to rank the results and then return the data in a specific range. Second, we need to add a method to get the total number of records that require paging. After creating the Dal and BLL method, we also need to configure ObjectDataSource so that it can get the total number of records that require paging and pass the correct values for row Index and maximum Rows to BLL.

Although it takes a series of actions to use custom paging, it is far less straightforward than default paging. But in the case of large amounts of data it is necessary. By displaying only the data you need, customizing paging saves a lot of time and reduces the burden on your database.

I wish you a happy programming!

Author Introduction

Scott Mitchell, with six asp/asp. NET book, is the founder of 4GuysFromRolla.com, has been applying Microsoft Web technology since 1998. Scott is an independent technical consultant, trainer, writer, recently completed a new book to be published by Sams Press, proficient in asp.net 2.0 within 24 hours. His contact email is mitchell@4guysfromrolla.com, or he can contact him through his blog http://scottonwriting.net/.