GridView Binding, Pagination and Sorting using ObjectDataSource

ObjectDataSource :

ASP.NET ObjectDataSource control acts as a data interface for data bound controls

like GridView,FormView and DetailsView controls in multitier Web Application

Architectures.

It is similar to data source control, SqlDataSource. But SqlDataSource make use of 

two-tier application architecture where presentation layer directly communicates with data

layer. But ObjectDataSource is based on three-tier architecture by binding the data controls

to a middle business layer.

This control has several advantages if we utilize it efficiently. An example is

its pagination. In custom paging, it won't load the whole data and it will load

only each page's data from the database which is a great advantage in case of

huge data.

Below I will show an example to bind a GridView using ObjectDataSource :

HTML Code (.aspx) :

 <div>
        <asp:GridView ID="GridView1" runat="server" AllowPaging="True"   AutoGenerateColumns="False" CellPadding="4" DataSourceID="ObjectDataSourceAGENT" ForeColor="#333333" GridLines="None" DataKeyNames="AGN_SEQ" PageSize="3" AllowSorting="true">
            <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
            <Columns>
                <asp:BoundField DataField="AGN_ID" HeaderText="Agent ID" SortExpression="AGN_ID" />
                <asp:BoundField DataField="AGN_NAME" HeaderText="Name" SortExpression="AGN_NAME" />
                <asp:BoundField DataField="AGN_TEL" HeaderText="Telephone" SortExpression="AGN_TEL" />
                <asp:BoundField DataField="AGN_NO" HeaderText="Agent No." SortExpression="AGN_NO" />
            </Columns>
            <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
            <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
            <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
            <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
            <EditRowStyle BackColor="#999999" />
            <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
            <EmptyDataTemplate>
                No data found!
            </EmptyDataTemplate>
        </asp:GridView>
        <asp:ObjectDataSource ID="ObjectDataSourceAGENT" runat="server" EnablePaging="True"
            SelectCountMethod="GetDataCount" SelectMethod="GetAgentData" SortParameterName="sortParameter"
            TypeName="AgentBLL">
            <SelectParameters>
                <asp:Parameter Name="maximumRows" Type="Int32" />
                <asp:Parameter Name="startRowIndex" Type="Int32" />
                <asp:Parameter Name="sortParameter" Type="String" />
            </SelectParameters>
        </asp:ObjectDataSource>
    </div>

For Pagination, provide AllowPaging="True" in Gridview and also provide the
value of PageSize which represents the number of rows to be displayed per page. In ObjectDataSource provide EnablePaging="True" and pass parameters maximumRows
and startRowIndex in the SelectParameters. These parameters take automatically from PageSize and PageIndex of the GridView.

For Sorting, provide AllowSorting="true" in Gridview and in ObjectDataSource
provide SortParameterName="sortParameter" which has to be passed in
SelectParameters.

In ObjectDataSource, TypeName can be provided with business layer class that retrieves
the data. SelectMethod is the function name that retrieves data in business layer class
and SelectCountMethod is the function name that retrieves the count or the total
number of data.

Below is the code used for business layer class:

AgentBLL.cs inside App_Code :

using System;

using System.Data;

using System.Configuration;

using System.Collections.Generic;

public class AgentBLL

{

    AgentDAL DAL = new AgentDAL();

    public List<AgentObject> GetAgentData(int maximumRows, int startRowIndex, string sortParameter)

    {

        List<AgentObject> li = new List<AgentObject>();

        li = DAL.GetAgentData(maximumRows, startRowIndex, sortParameter);

        return li;

    }

    public int GetDataCount(int maximumRows, int startRowIndex, string sortParameter)

    {

        return DAL.GetDataCount(maximumRows, startRowIndex, sortParameter);

    }

}

Below is the code used for data layer class:

AgentDAL.cs inside App_Code :

using System;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

using System.Collections.Generic;

public class AgentDAL

{

    public List<AgentObject> GetAgentData(int maximumRows, int startRowIndex, string sortParameter)

    {

        List<AgentObject> li = new List<AgentObject>();

        string connectionString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;

        using (SqlConnection dbConnection = new SqlConnection(connectionString))

        using (SqlCommand dbCommand = new SqlCommand())

        {

            if (string.IsNullOrEmpty(sortParameter))

                sortParameter = "AGN_Seq";

            dbCommand.CommandText = "select * from (select ROW_NUMBER() OVER(ORDER BY " + sortParameter + ") AS RowNo,* from Agents) as AgentsWithRowno where RowNo>@startIndex and RowNo<=@startIndex+@maxRows";

            dbCommand.Connection = dbConnection;

            dbConnection.Open();

            dbCommand.Parameters.Add(new SqlParameter("@startIndex", startRowIndex));

            dbCommand.Parameters.Add(new SqlParameter("@maxRows", maximumRows));

            

            SqlDataReader dr = dbCommand.ExecuteReader();

            while (dr.Read())

            {

                li.Add(new AgentObject(dr["AGN_SEQ"].ToString().Trim(),

                    dr["AGN_NO"].ToString().Trim(), dr["AGN_NAME"].ToString().Trim(), dr["AGN_ID"].ToString().Trim(), dr["AGN_TEL"].ToString().Trim()));

            }

            dr.Close();

           

        }

        return li;

    }

    public int GetDataCount(int maximumRows, int startRowIndex, string sortParameter)

    {

        string connectionString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;

        using (SqlConnection dbConnection = new SqlConnection(connectionString))

        using (SqlCommand dbCommand = new SqlCommand())

        {

            dbCommand.CommandText = "select count(*) from Agents";

            dbCommand.Connection = dbConnection;

            dbConnection.Open();

            return (int)dbCommand.ExecuteScalar();

        }

    }

}

Below is the code used for object class:

AgentObject.cs inside App_Code :

using System;

using System.Data;

using System.Configuration;

public class AgentObject

{

    private string _AGN_SEQ;

    public string AGN_SEQ

    {

        get { return _AGN_SEQ; }

        set { _AGN_SEQ = value; }

    }

    private string _AGN_NO;

    public string AGN_NO

    {

        get { return _AGN_NO; }

        set { _AGN_NO = value; }

    }

    private string _AGN_NAME;

    public string AGN_NAME

    {

        get { return _AGN_NAME; }

        set { _AGN_NAME = value; }

    }

    private string _AGN_ID;

    public string AGN_ID

    {

        get { return _AGN_ID; }

        set { _AGN_ID = value; }

    }

    private String _AGN_TEL;

    public String AGN_TEL

    {

        get { return _AGN_TEL; }

        set { _AGN_TEL = value; }

    }

    public AgentObject(string AGN_SEQ, string AGN_NO, string AGN_NAME, string AGN_ID, string AGN_TEL)

    {

        _AGN_SEQ = AGN_SEQ;

        _AGN_NO = AGN_NO;

        _AGN_NAME = AGN_NAME;

        _AGN_ID = AGN_ID;

        _AGN_TEL = AGN_TEL;

    }

    public AgentObject()

    {

        //

        // TODO: Add constructor logic here

        //

    }

}

Output :

Output is as follows :

Clicking on header links gridview can be sorted.