Efficient Paging in GridView

In this post we will follow simple steps to implement efficient paging in gridview using ObjectDataSource. By default when we use ObjectDataSource with GridView to achieve paging, ObjectDataSource retrieves all the rows from the datasource, only displaying the rows for the current page while discarding all the remaining rows. In this post we will see how we can improve the process of paging by retrieving only those rows that are going to be displayed by the current page and not all the rows.

Designing DataBase
For the purpose of this example we suppose that we have a table 'Order' in our database containing fields Order DateTime, Tax and Shipping etc.

Writing Stored Procedures

We will write a SQL query that will only return the rows for current page instead of returning all rows from table 'Order'. For that purpose we will use the Row_Number function introduced in SQL Server 2005. Row_Number function returns sequential number of a row. We can write the query using Rw_Number function as follow:


Create Procedure [dbo].[GetOrdersList]
@CurrentIndex int,
@PageSize int

AS

SELECT Row_Number() over(order by OrderID) as Row ,[OrderID],[OrderDateTime],[Tax],[Shipping]
FROM ([dbo].[Orders])
WHERE Row between @CurrentIndex and (@CurrentIndex + @PageSize)

Above query will only return the number of rows equal to page size where index of the first row will be equal to current index.
But running this query will return an error that 'Row' is invalid column. This is because we cannot use the computed column 'Row' in the Where clause of the same query that computed the column because column is not yet available for Where clause.
The workaround for this is that first we will create a temporary table containing the computed column using CTE and then run the select query on that temporary table.


Create Procedure [dbo].[GetOrdersList]
@CurrentIndex int,
@PageSize int
AS
With TOrders AS (SELECT Row_Number() over(order by OrderID Desc) as Row ,[OrderID]
,[OrderDateTime]
,[CustomerID]
,[Tax]
,[Shipping]
FROM [MyCompany].[dbo].[Orders])
Select *
from TOrders
WHERE
Row between @CurrentIndex and (@CurrentIndex + @PageSize)

We will create a second stored procedre that will return the total number of rows in table 'Order'. Why we need that stored procedure , we will discuss it later on when setting properties of ObjectDataSource.


CREATE PROCEDURE dbo.getOrdersCount
AS
SELECT count(*) from Orders

Creating Data Access Layer

Before creating a Data Access Layer we will create a Data Transfer Object 'Order' that will be used to transfer 'Order' data across UI and Data Layer. For that we will add the file in App_Code folder. In this file we will create a public class 'Order' with properties matching to the fields in table 'Order'.


public class Order
{
DateTime _orderDateTime;
string _tax;
string _shipping;

public Order(DateTime orderDateTime,string tax,string shipping)
{
_orderDateTime = orderDateTime;
_tax = tax;
_shipping = shipping;
}
public DateTime OrderDate
{
get
{
return _orderDateTime;
}
set
{
_orderDateTime = value;
}
}
public string Tax
{
get
{
return _tax;
}
set
{
_tax = value;
}
}
public string Shipping
{
get
{
return _shipping;
}
set
{
_shipping = value;
}
}
}

Now for creating Data Access layer we will add the file in App_Code folder. In that file we will create a public class containing methods for accessing database. To get list of 'Orders' for the current page only we will add the following method in our DAL class.


public List getOrderList(int currentIndex,int pageSize)
{

List orderList = new List();

using(SqlConnection conn = new SqlConnection(getConnectionString()))
{

SqlCommand com = new SqlCommand();
com.Connection = conn;
com.CommandType = CommandType.StoredProcedure;
com.CommandText = "GetOrdersList";


SqlParameter param1 = new SqlParameter();
param1.ParameterName = "@CurrentIndex";
param1.SqlDbType = SqlDbType.Int;
param1.Value = currentIndex;
SqlParameter param2 = new SqlParameter();
param2.ParameterName = "@PageSize";
param2.SqlDbType = SqlDbType.Int;
param2.Value = pageSize;

com.Parameters.Add(param1);
com.Parameters.Add(param2);

conn.Open();


using (SqlDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{

orderList.Add(new Order((DateTime)reader["OrderDateTime"], reader["Tax"].ToString(), reader["Shipping"].ToString()));

}
reader.Close();

}
conn.Close();
return orderList;

}
}

The above method will return the list of orders, where each order is represented by DTO that we created above.
To get the total number of orders we will use the following method


public int getOrdersCount(int currentIndex, int pageSize)
{
int count = 0;
using (SqlConnection conn = new SqlConnection(getConnectionString()))
{
SqlCommand com = new SqlCommand();
com.Connection = conn;
com.CommandType = CommandType.StoredProcedure;
com.CommandText = "getOrdersCount";
conn.Open();
count = (int)com.ExecuteScalar();
}
return count;
}

We are passing the currentIndex and PageSize parameters to 'getTotalOrders' method although we are not using these parameters inside the method. This is because it is the requirement of ObjectDataSource to pass these parameters to the method.

Setting Properties of ObjectDataSource

To use paging with ObjectDataSource we need to set certain properties of ObjectDataSource.

• Set the 'EnablePaging' property of ObjectDataSource to true.
• Configure the ObjectDataSource to use the above created DAL class as a buisness object.
• Specify 'getOrderList' method in the SelectMethod property of Object DataSource.
• Specify 'getOrdersCount' in the SelectCountMethod property of ObjectDataSource.
• Set the StartRowIndexParameterName property to 'currentIndex' and MaximumRowsParameterName property of ObjectDataSource to 'pageSize'. These are names of the 'getOrderLists' method parameters that represent the starting row index and maximum rows value in the method.

Setting properties of GridView

• Set allowPaging property of GridView to true.
• Set the DataSourceID to the name of the ObjectDataSource.
• Set the page size equal to the number of rows you want to display per page.

Build the page and run your application. You will see the gridview with paging enabled and most important your object dataSource will retirve only the number of rows that is displayed on the page instead of retriving all the rows and then displaying only the rows that is required by the current page while discarding all the remaining rows.