GridView with ObjectDataSource and Pagination

The title is self explanatory: I'm going to develop a GridView bound to an ObjectDataSource with pagination directly at the DataSource, not just on the Grid.


The difference of paginating directly on the data source and paginating at the Grid level, is that in the first case you'll only have the records that are shown in the grid loaded to memory. This means that for each page selected, the data source will get the next N results to be displayed. When you do that directly on the grid, every row will be loaded on the first fetch the data source will perform, and the grid will handle paginating and mantaining those records in memory for that.


First of all, let's create our table:

CREATE TABLE Customers
(
    CustomerId INT IDENTITY (1,1) PRIMARY KEY,
    FirstName VARCHAR(60) NOT NULL,
    LastName VARCHAR(60) NOT NULL,
    Age INT NOT NULL,
    Phone VARCHAR(8) NULL
)

Insert some data in it, about 15 Rows or so...


If you mark that the ObjectDataSource allows Paging (and you will), you have to implement a select method which takes two special integer parameters (startRowIndex and maximumRows) and a count method that returns an integer of how many records we have in our table. It's obvious enough that we need the count method to calculate how many pages we'll have, and the two special parameters to limit our result...

You could also change startRowIndex and maximumRows to whatever you'd like to with the properties StartRowIndexParameterName and MaximumRowsParameterName. However, if you change their names your select method must be changed too. You'll learn exactly why we need those parameters with the code below.


Now implement our Data Class, notice that I've created a constant called ConnStr just for demonstration purposes, you should get your Connection String from wherever you think is better.

[System.ComponentModel.DataObject(true)]
public class CustomerData
{

    [System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select, true)]
    public static DataTable GetCustomers(int maximumRows, int startRowIndex)
    {
        string strCommand = "SELECT * FROM ";
        strCommand += "(SELECT *, ROW_NUMBER() OVER (ORDER BY FirstName) as RowNum FROM Customers) as Sub ";
        strCommand += "WHERE RowNum BETWEEN @StartRow AND @MaximumRows";

        using (SqlDataAdapter sqlAdapter = new SqlDataAdapter(strCommand, new SqlConnection(CustomerData.ConnStr)))
        {
            using (DataTable dtRet = new DataTable())
            {
                try
                {
                    startRowIndex++;

                    sqlAdapter.SelectCommand.Parameters.Add("@StartRow", SqlDbType.Int).Value = startRowIndex;
                    sqlAdapter.SelectCommand.Parameters.Add("@MaximumRows", SqlDbType.Int).Value = (maximumRows + startRowIndex);

                    sqlAdapter.Fill(dtRet);

                    return dtRet;
                }
                catch (Exception)
                {
                    throw;
                }                   
            }                
        }
    }


    [System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select, false)]
    public static int GetCustomersCount()
    {
        using (SqlCommand sqlCommand = new SqlCommand("SELECT COUNT(*) FROM [Customers]", new SqlConnection(CustomerData.ConnStr)))
        {
            try
            {
                sqlCommand.Connection.Open();
                return (int)sqlCommand.ExecuteScalar();                    
            }
            catch (Exception)
            {                    
                throw;
            }
        }
    }
}

Take a better look at our SELECT command:

SELECT
    *
FROM
(
    SELECT
        *,
        ROW_NUMBER() OVER (ORDER BY FirstName) AS RowNum 
    FROM 
        Customers
) AS Sub
WHERE
    RowNum BETWEEN @StartRow AND @MaximumRows

Since our DataSource passes us "0" on StartRowIndex for our first page, we have to add 1 to it, because our column RowNum starts at 1. We then limit the records to start on StartRowIndex + 1 and end on MaximumRows + our StartRowIndex (already increased by one).

We can see that when we add our parameters to the SqlCommand object:

startRowIndex++; // Increment our start row number by one

sqlAdapter.SelectCommand.Parameters.Add("@StartRow", SqlDbType.Int).Value = startRowIndex;

sqlAdapter.SelectCommand.Parameters.Add("@MaximumRows", SqlDbType.Int).Value = (maximumRows + startRowIndex); // MaximumRow plus StartRowIndex is the number of our last row for the actual page

Ok, so after that we start designing our page's objects.

Heres our ObjectDataSource, with no big secrets:

<asp:ObjectDataSource 
    ID="dsCustomers" 
    runat="server" 
    TypeName="CustomerData" 
    SelectMethod="GetCustomers" 
    SelectCountMethod="GetCustomersCount" 
    EnablePaging="true"
/>

And this is our GridView, marked to auto generate our columns, just for demonstration purposes:

<asp:GridView
    ID="gridCustomers" 
    AllowPaging="True" 
    runat="server" 
    PageSize="5" 
    DataSourceID="dsCustomers"
    AutoGenerateColumns="true"
/>  

If you've done everything right, you should be good to go with an ugly ass GridView, showing a maximum of 5 rows on each page, ordered by our Customers' first name, like the images below:

Go on and customize the columns, add Insert / Edit / Delete functionality, and the grid's skin / theme.