When using paging with data-bound controls, performance issues can arise when accessing large amounts of data. By default, all rows needed for the data-bound control will be acquired even if paging is only exposing a small subset of these rows per page index. Utilizing an ObjectDataSource (ODS) and enabling paging functionality within the ODS allows a specified amount of rows to be accessed from the database per page index. Setting the ODS to use paging requires a few extra properties to be utilized within the ODS: EnablingPaging, MaximumRowsParameterName, StartRowIndexParameterName, and SelectCountMethod to be specific.
In this example, I am utilizing the following ODS:
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" EnablePaging="True" SelectCountMethod="GetDepartmentCount" SelectMethod="GetDepartments" TypeName="TempBLL" MaximumRowsParameterName="maximumRows" StartRowIndexParameterName="startRowIndex">
The ODS points to the following class:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
[System.ComponentModel.DataObject]
public class TempBLL
{
[System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select,
true)]
public DataTable GetDepartments(int startRowIndex, int maximumRows)
{
using (SqlConnection connection = new SqlConnection("Data Source=localhost;Initial
Catalog=AdventureWorks;Trusted_Connection=Yes"))
{
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "HumanResources.uspGetDepartments";
command.Parameters.Add(new SqlParameter("@FirstRow", SqlDbType.Int));
command.Parameters["@FirstRow"].Value = startRowIndex + 1;
command.Parameters.Add(new SqlParameter("@MaxRows", SqlDbType.Int));
command.Parameters["@MaxRows"].Value = maximumRows;
DataSet result = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(result);
HttpContext.Current.Items["DepartmentRowCount"] = (int)result.Tables[1].Rows[0][0];
return result.Tables[0];
}
}
public int GetDepartmentCount()
{
return (int)HttpContext.Current.Items["DepartmentRowCount"];
}
}
When using paging within the ODS, the method specified in the SelectMethod property must, at the least, have a parameter to indicate the starting row index and a parameter to indicate how many rows should be returned per page. The names of these parameters are set in the StartRowIndexParameterName and MaximumRowsParameterName properties of the ODS respectively.
The purpose of the customized SelectMethod is to provide a mechanism by which only a pre-determined number of rows will be returned from the database per page index. The ODS will take care of passing in the appropriate arguments for the two custom parameters. The “maximumRows” parameter is determined from the data-bound control’s PageSize property and the “startRowIndex” parameter automatically increases based on the currently selected page index. For example, if you only want 5 rows to show up per page on a GridView, simply set the GridView’s PageSize property to 5 and the ODS will automatically send the value 5 into the SelectMethod’s parameter that is named in the MaximumRowsParameterName property. This value can then be used to limit the amount of rows being grabbed from the database (as I did with the uspGetDepartments stored procedure).
The SelectCountMethod property points to the second method within TempBLL.cs. This method lets the data-bound control know the total amount of rows so it can display the correct number of page links. This is necessary because the paging enabled ODS will only be returning a subset of the total rows. I am utilizing the HttpContext in order to prevent two round-trips to the database since the SelectCountMethod is called after the SelectMethod. I originally got the HttpContext idea from Fritz Onion.
I am using SQL Server 2005 in this example, so I am able to use the ROW_NUMBER() function in my T-SQL to grab the appropriate group of rows per page index:
CREATE PROCEDURE [HumanResources].[uspGetDepartments]
@FirstRow int,
@MaxRows int
AS
BEGIN
WITH OrderedDepartments AS
(
SELECT d.DepartmentID, d.[Name], ROW_NUMBER() OVER (ORDER BY d.[Name]) AS
'RowNumber'
FROM HumanResources.Department d
)
SELECT od.DepartmentID, od.[Name]
FROM OrderedDepartments od
WHERE RowNumber BETWEEN @FirstRow AND (@FirstRow + @MaxRows) - 1
SELECT COUNT(*) FROM HumanResources.Department
END
With this stored procedure, I am able to get the proper subset of rows and the total row count. The multiple result sets are placed within distinct tables when the stored procedure is called in the SelectMethod. This allows the SelectMethod to grab the subset of rows needed for the current page index and retrieve the total row count in one database call.