How To Add Paging to a GridView

Paging is not an MM .NET-specific feature, but we provide instructions in this section to help you implement standard ASP.NET paging using best practices.

Step 1: Page-Enabling Your Business Object

The first step in getting paging to work in your application is to create a method on your business object that retrieves a single page of records. To work with the ASP.NET ObjectDataSource control, this method needs to maximum rows per page and the starting index of the first record on the page to be displayed. As a best practice, we recommend that this method calls a stored procedure that performs the actual paging logic.

There are a variety of ways you can implement paging logic in a stored procedure. We present one methodology described in the following web log:

http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx

To implement this methodology, follow these steps:

  1. Create the following stored procedure in your SQL Server database:

    CREATE PROCEDURE ReturnPage(@Select varchar(1000), @OrderBy varchar(1000), 
                            @StartRow int, @EndRow int)

AS

BEGIN

declare @ColList varchar(2000);
declare @Where varchar(2000);
declare @i int;  
declare @i2 int;
declare @tmp varchar(1000);
declare @dec varchar(1000);
declare @f varchar(100);
declare @d varchar(100);
declare @Symbol char(2);
declare @SQL varchar(5000);
declare @Sort varchar(1000);

set @Sort = @OrderBy + ', '
set @dec = ''
set @Where  = ''
set @SQL = ''
 
set @i = charindex(',' , @Sort)

while @i != 0

 begin

  set @tmp = left(@Sort,@i-1)
  set @i2 = charindex(' ', @tmp)
  set @f = ltrim(rtrim(left(@tmp,@i2-1)))
  set @d = ltrim(rtrim(substring(@tmp,@i2+1,100)))
  set @Sort = rtrim(ltrim(substring(@Sort,@i+1,100)))
  set @i = charindex(',', @Sort)
  set @symbol = case when @d = 'ASC' then '>' else '<' end + 
                case when @i=0 then '=' else '' end

  set @dec = @dec + 'declare @' + @f + ' sql_variant; '
  set @ColList = isnull(replace(replace(@colList,'>','='),'<','=') + ' and ','') +
                 @f + @Symbol + ' @' + @f
  set @Where = @Where + ' OR (' + @ColList + ') '
  set @SQL = @SQL + ', @' + @f + '= ' + @f

 end

set @SQL = @dec + ' ' +
           'SET ROWCOUNT ' + convert(varchar(10), @StartRow) + '; ' +
           'SELECT ' + substring(@SQL,3,7000) + ' from (' + @Select + ') a ORDER BY ' +
           @OrderBy + '; ' + 'SET ROWCOUNT ' + 
           convert(varchar(10), 1 + @EndRow - @StartRow) + '; ' +
           'select * from (' + @Select + ') a WHERE ' + 
           substring(@Where,4,7000) + ' ORDER BY ' + @OrderBy + '; SET ROWCOUNT 0;'

exec(@SQL)

END

  2. Add the following method to your project's ABusinessObject class (or company-level business object if you have one):

    In C#:

    /// <summary>
/// Retrieves a page of data based on the specified parameters
/// Constraints:
/// 1 - The ASC/DESC designation is required for each column in the @OrderBy argument, 
///     unlike in a regular SELECT, in which ASC is the default 
/// 2 - All column names in the @OrderBy argument must consist of only 1 word; 
///		even using [ ] around a column name will not work
/// 3 - Sort columns must form a unique constraint for the resultset; 
///		you cannot sort only by “Customer Name“ if it is not always unique for each row -- 
///		you would need to include “CustomerID“  as a secondary sort
/// 4 - There should not be any Null values in the @OrderBy columns
/// </summary>
/// <param name="selectCommand">SELECT command (e.g. "SELECT contactTitle, City, CustomerID FROM customers"</param>
/// <param name="tableName">Name of the DataTable into which results are stored</param>
/// <param name="orderBy">Order BY clause (e.g. "'ContactTitle ASC, City DESC, CustomerID ASC"</param>
/// <param name="maximumRows">Maximium number of rows in the data page</param>
/// <param name="startRowIndex">Starting row index</param>
/// <returns>DataTable containing the result set</returns>
public virtual DataTable GetDataPage(string selectCommand, string tableName, string orderBy, int maximumRows, int startRowIndex)
{
	int EndRow = startRowIndex + maximumRows;
	startRowIndex++;

	this.GetDataSet("ReturnPage", tableName, CommandType.StoredProcedure,
		this.CreateParameter("@Select", selectCommand),
		this.CreateParameter("@OrderBy", orderBy),
		this.CreateParameter("@StartRow", startRowIndex),
		this.CreateParameter("@EndRow", EndRow));

	return this.DataSet.Tables[TableName];
}

    And in VB .NET:

    ''' <summary>
''' Retrieves a page of data based on the specified parameters
''' Constraints:
''' 1 - The ASC/DESC designation is required for each column in the @OrderBy argument, 
'''     unlike in a regular SELECT, in which ASC is the default 
''' 2 - All column names in the @OrderBy argument must consist of only 1 word; 
'''		even using [ ] around a column name will not work
''' 3 - Sort columns must form a unique constraint for the resultset; 
'''		you cannot sort only by “Customer Name“ if it is not always unique for each row -- 
'''		you would need to include “CustomerID“  as a secondary sort
''' 4 - There should not be any Null values in the @OrderBy columns
''' </summary>
''' <param name="selectCommand">SELECT command (e.g. "SELECT contactTitle, City, CustomerID FROM customers"</param>
''' <param name="tableName">Name of the DataTable into which results are stored</param>
''' <param name="orderBy">Order BY clause (e.g. "'ContactTitle ASC, City DESC, CustomerID ASC"</param>
''' <param name="maximumRows">Maximium number of rows in the data page</param>
''' <param name="startRowIndex">Starting row index</param>
''' <returns>DataTable containing the result set</returns>
Public Overridable Function GetDataPage(ByVal selectCommand As String, ByVal tableName As String, ByVal orderBy As String, ByVal maximumRows As Integer, ByVal startRowIndex As Integer) As DataTable
    Dim EndRow As Integer = startRowIndex + maximumRows
    startRowIndex = startRowIndex + 1

    Me.GetDataSet("ReturnPage", tableName, CommandType.StoredProcedure, _
     Me.CreateParameter("@Select", selectCommand), _
     Me.CreateParameter("@OrderBy", orderBy), _
     Me.CreateParameter("@StartRow", startRowIndex), _
     Me.CreateParameter("@EndRow", EndRow))

    Return Me.DataSet.Tables(tableName)

End Function

  3. Open the source code for the business object you are using to fill the GridView, and add a method that calls the new GetDataPage() method. In a later step you will add an ObjectSource control to the web form that will use this method to fill a page of data in the GridView.

    For example, in C#:

    /// <summary>
/// Gets a page of orders data
/// </summary>
/// <param name="maximumRows">Maximum rows in the data page</param>
/// <param name="startRowIndex">Starting Row index</param>
/// <returns>DataTable containing page of data</returns>
public DataTable GetDataPage(int maximumRows, int startRowIndex)
{
	return this.GetDataPage("SELECT * FROM Orders", this.TableName,
		"OrderID ASC", maximumRows, startRowIndex);
}

    And in VB .NET:

    ''' <summary>
''' Gets a page of orders data
''' </summary>
''' <param name="maximumRows">Maximum rows in the data page</param>
''' <param name="startRowIndex">Starting Row index</param>
''' <returns>DataTable containing page of data</returns>
Public Overloads Function GetDataPage(ByVal maximumRows As Integer, ByVal startRowIndex As Integer) As DataTable

    Return Me.GetDataPage("SELECT * FROM Orders", Me.TableName, _
     "OrderID ASC", maximumRows, startRowIndex)

End Function

  4. Add a new method to your business object that returns the TOTAL count of items to be displayed in all pages of the GridView. In a later step you will add an ObjectDataSource control to the web page which will use this method to determine how many page links to display in the GridView.

    For example, in C#:

    /// <summary>
/// Gets the total number of Orders
/// </summary>
/// <param name="maximumRows">Maximum rows in the data page</param>
/// <param name="startRowIndex">Index of the first row on the page</param>
/// <returns>Total number of orders</returns>
public int GetTotalNumberOfOrders(int maximumRows, int startRowIndex)
{
	return (int)this.ExecScalar("SELECT COUNT(*) FROM Orders");
}

    And in VB .NET:

    ''' <summary>
''' Gets the total number of Orders
''' </summary>
''' <param name="maximumRows">Maximum rows in the data page</param>
''' <param name="startRowIndex">Index of the first row on the page</param>''' <returns>Total number of orders</returns>
Public Function GetTotalNumberOfOrders(maximumRows As Integer, startRowIndex As Integer) As Integer

    Return CType(Me.ExecScalar("SELECT COUNT(*) FROM Orders"), Integer)

End Function

  5. Rebuild your business object project and make sure there are no errors.

Step 2: Add an Object Data Source to the Web Form

  1. Select the Web Form in design mode that contains the GridView you want to page-enable

  2. From the Visual Studio Toolbox Data tab, drag an ObjectDataSource and drop it on your web form.

  3. With the ObjectDataSource's smart tag selected, click the Configure Data Source link:

  4. In the Configure Data Source dialog, uncheck the Show only data components check box, then in the Choose your business object list, select the business object you page-enabled in the previous step:

  5. Click the Next button and in the Choose a method combo box, select the data page retrieval method of your business object. For example:

  6. Click Next to display the Define Parameters step, then click Finish to accept the parameter defaults.

  7. With the ObjectDataSource selected, go to the Properties window and set the EnablePaging property to True.

  8. In the Properties Window, set the SelectCountMethod property to the name of the method on your business object that returns a total count of the items to be displayed in the GridView (for example, GetTotalNumberOfOrders.

    Notice the StartRowIndexParameterName property is set to startRowIndex and the MaximumRowsParameterName property is set to maximumRows by default--these are the names we gave to the parameters in our business object methods (we knew in advance what the ObjectDataSource control was expecting <s>). If you use different parameter names in your GetDataPage() method, then change these properties accordingly--otherwise you can accept the defaults.

  9. With the ObjectDataSource selected, go to the Properties Window, and click the Events button (the one with the lightning bolt). Double-click the Selected event which creates a handler method in the code-behind file. Add the following code to the handler method which saves the retrieved page of data in a session variable.

    In C#:

    protected void ObjectDataSource2_Selected(object sender, ObjectDataSourceStatusEventArgs e)
{
	DataTable dt = e.ReturnValue as DataTable;
	if (dt != null)
	{
		Session["dtOrders"] = dt;
	}
}

    And in VB .NET:

    Protected Sub ObjectDataSource1_Selected(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.ObjectDataSourceStatusEventArgs) Handles ObjectDataSource1.Selected
	
	If TypeOf e.ReturnValue Is DataTable Then
            Dim dt As DataTable = CType(e.ReturnValue, DataTable)
            Session("dtOrders") = dt
      End If

End Sub

Step 3: Connecting your GridView to the ObjectDataSource

  1. Select your GridView in design mode, then go to the properties window and set the AllowPaging property to True.

  2. Select the GridView's DataSourceID property. From the combo box, select the ObjectDataSource you just configured. If you see the following message, click No so you don't delete your existing GridView columns:

  3. Set the BindingSource property to the business object used to filled the DataGridView. The ObjectDataSource is used to fill the GridView with data, but the business object specified in the BindingSource property is used for updating and deleting records in the GridView.


    Note: You must register the BindingSource business object with the web form in order for your GridView to work properly at run time. For details, see the Help topic Using Business Objects in Web Forms.


  4. By default, ten items are displayed at a time in a GridView page. If you want to change this number, set the GridView's PageSize property accordingly.

Step 4: Setting up the GridView for Editing

If you want the GridView to be read-only (no in-place editing), then you're done. Otherwise, follow these steps for allowing edits in the GridView. These steps assume you have already added Edit, Update, Cancel, and Delete buttons as described in the Help topic Enabling Editing in mmGridView.

  1. Select your GridView in design mode, go to the Properties window, and click the Events button (the button with the lightning bolt). Double-click the following events in the Properties window to create handlers for each event:

    • RowCancelingEdit
    • RowDeleting
    • RowEditing
    • RowUpdating

  2. Create handlers for each of the methods following the examples listed here.

    In C#:

    protected void grdOrders_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
	this.grdOrders.EditIndex = -1;
	this.BindControl(this.grdOrders);
}
	
protected void grdOrders_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
	// Retrieve the previous Order DataTable
	DataTable dtOrders = (DataTable)Session["dtOrders"];
	// Tell the business object to delete the specified row
	this.oOrder.Delete(dtOrders.DataSet, e.RowIndex);
	// Rebind the GridView
	this.ObjectDataSource1.Select();
	this.grdOrders.DataBind();
	// Prevent the ObjectDataSource Cancel method from firing
	e.Cancel = true;
}

protected void grdOrders_RowEditing(object sender, GridViewEditEventArgs e)
{
	this.grdOrders.EditIndex = e.NewEditIndex;
	this.BindControl(this.grdOrders);
}

protected void grdOrders_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
	// Get the item to be updated
	this.grdOrders.EditIndex = e.RowIndex;
	// Retrieve the previous Order DataTable
	DataTable dtOrders = (DataTable)Session["dtOrders"];
	// Save the DataSet (the GridView automatically binds back)
	this.Save(this.oOrder, dtOrders.DataSet);
	// Reset the edited item and rebind the GridView
	this.grdOrders.EditIndex = -1;
	// Prevent the ObjectDataSource Cancel method from firing
	e.Cancel = true;
}

    And in VB .NET:

    Protected Sub grdOrders_RowCancelingEdit(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCancelEditEventArgs) Handles grdOrders.RowCancelingEdit
    Me.grdOrders.EditIndex = -1
    Me.BindControl(Me.grdOrders)
End Sub

Protected Sub grdOrders_RowDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewDeleteEventArgs) Handles grdOrders.RowDeleting
    '' Retrieve the previous Order DataTable
    Dim dtOrders As DataTable = CType(Session("dtOrders"), DataTable)
    '' Tell the business object to delete the specified row
    Me.oOrder.Delete(dtOrders.DataSet, e.RowIndex)
    '' Rebind the GridView
    Me.ObjectDataSource1.Select()
    Me.grdOrders.DataBind()
    '' Prevent the ObjectDataSource Cancel method from firing
    e.Cancel = True
End Sub

Protected Sub grdOrders_RowEditing(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewEditEventArgs) Handles grdOrders.RowEditing
    Me.grdOrders.EditIndex = e.NewEditIndex
    Me.BindControl(Me.grdOrders)
End Sub

Protected Sub grdOrders_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles grdOrders.RowUpdating
    '' Get the item to be updated
    Me.grdOrders.EditIndex = e.RowIndex
    '' Retrieve the previous Order DataTable
    Dim dtOrders As DataTable = CType(Session("dtOrders"), DataTable)
    '' Save the DataSet (the GridView automatically binds back)
    Me.Save(Me.oOrder, dtOrders.DataSet)
    '' Reset the edited item and rebind the GridView
    Me.grdOrders.EditIndex = -1
    '' Prevent the ObjectDataSource Cancel method from firing
    e.Cancel = True
End Sub


© (c) 2026 Oak Leaf Enterprises, Inc., 1996-2026 • Updated: 04/26/18
Comment or report problem with topic