It will be good, if we fetch only those records which are required to display in that particular page. This article will help us to implement the custom paging with ObjectDataSource control and SqlServer 2005.
DataSource controls are new additions to ASP.Net 2.0 controls set that help in binding the data from database. Not every time, we can use the DataSet and DataTable object to hold the data from database. We will have our own business object and its associated collection object to hold the data in our projects. We can use ObjectDataSource control to bind database records that is fetched from database, if we have our own business objects. Another advantage of using ObjectDataSource control is, it will help us in implementing the custom paging mechanism very easily without the need to build page numbers separately i.e. we can still use inbuilt paging feature of GridView to display page numbers and we can use ObjectDataSource control to fetch the records that is required only for that page.

Configuring ObjectDataSource Control for Custom Paging

To configure the custom paging with ObjectDataSource control, we need to configure the following property.

EnablePaging

This property accepts a Boolean to enable paging with ObjectDataSource control.

SelectCountMethod

We need to specify the name of the method that can fetch the total number of records available in the database.

StartRowIndexParameterName

This property will accept the start row index of the record to fetch from database.

MaximumRowsParameterName

This property will accept the maximum number of rows that can be fetched at one time. This will be equivalent to page size.

SelectMethod

This property will accept the name of the method that fetches actual database record.

TypeName

This property needs to be configured with the class or type name that has the implementation of SelectCountMethod and SelectMethod.

In order to configure the ObjectDataSource control, we need to first implement the SelectCountMethod and SelectMethod. The following code snippet will have 2 methods called GetUserCount() and BindUsers(). The BindUsers()  method will take 2 parameters called startRowIndex and maximumRows to fetch the data from database. To make our understanding easier, the BindUsers() method will return a DataTable object. The return type can also be a List<User> object, where User is a business object.

public class UserDAO

{     

    public DataTable BindUsers(int startRowIndex, int maximumRows)

    {

        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Sql"].ConnectionString);       

        SqlCommand com = new SqlCommand("GetUsers", con);

        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@RowIndex", SqlDbType.Int, 4).Value = startRowIndex;

        com.Parameters.Add("@MaxRows", SqlDbType.Int, 4).Value = maximumRows;

        SqlDataAdapter ada = new SqlDataAdapter(com);

        DataTable dt = new DataTable();

        ada.Fill(dt);

        return dt;       

    }

    public int GetUserCount()

    {

        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Sql"].ConnectionString);

        con.Open();

        SqlCommand com = new SqlCommand("GetUserCount", con);

        SqlDataReader dr = com.ExecuteReader();

        int count = 0;

        while (dr.Read())

        {

            if (dr["UserCOUNT"] != null)

                int.TryParse(dr["UserCOUNT"].ToString(), out count);          

        }

        return count;

    }

}

Now, we can configure the ObjectDataSource control with these methods.

        <asp:ObjectDataSource ID="ObjectDataSource1" EnablePaging="true" 

        runat="server" SelectCountMethod="GetUserCount"

        SelectMethod="BindUsers" TypeName="UserDAO"

        StartRowIndexParameterName="startRowIndex" MaximumRowsParameterName="maximumRows">

         </asp:ObjectDataSource>

Next, we can implement the stored procedure that fetches the records only for that page by accepting startRowIndex and maximumRows as parameters.

CREATE PROC [dbo].[GetUsers]

(@RowIndex INT ,

@MaxRows INT)

AS

DECLARE @StartRow INT

DECLARE @EndRow INT

SET @StartRow = (@RowIndex+1)

SET @EndRow = @StartRow + @MaxRows

SELECT * FROM (

SELECT     FirstName, LastName, ROW_NUMBER() OVER (ORDER BY UserID) AS ROW

FROM         CD_Users) As NumberedUsers

WHERE ROW BETWEEN @StartRow AND @EndRow

The start row index parameter that will be returned from GridView control will be 0. Hence, we need to increment the row index parameter by 1 before querying the database.

Finally, we will configure the GridView control to accept the datasource control.

        <asp:GridView ID="gvUsers" runat="server" AllowPaging="True" DataSourceID="ObjectDataSource1" PageSize="2">

        </asp:GridView>

Thus, we have implemented the custom paging with the help of ObjectDataSource control and Sql Server 2005.

Practically, when we implement the custom paging there are chances that we should restrict the result set based on some filter condition. In other words, we can restrict the result set by some parameters. For example,

public DataTable BindUsers(string FirstName, string LastName, int startRowIndex, int maximumRows)

{

}

public int GetUserCount(string FirstName, string LastName)

{

}

These extra parameters can be configured from ObjectDataSource control SelectParameter collection.

       <asp:ObjectDataSource ID="ObjectDataSource1" EnablePaging="true" 

        runat="server" SelectCountMethod="GetUserCount"

        SelectMethod="BindUsers" TypeName="UserDAO"

        StartRowIndexParameterName="startRowIndex" MaximumRowsParameterName="maximumRows">      

         <SelectParameters>

        <asp:ControlParameter  ControlID="txtFirstName" Name="FirstName" Type="string"  />

        <asp:ControlParameter  ControlID="txtLastName" Name="LastName" Type="string"  />

        </SelectParameters>

         </asp:ObjectDataSource>

Finally, the stored procedure should be changed to accept the filter parameters.