C# GridView Sorting/Paging w/o a DataSourceControl DataSource

written by Ryan Olshan on Friday, May 23 2008

If you set AllowPaging="true" or AllowSorting="true" on a GridView control without using a DataSourceControl DataSource (i.e. SqlDataSource, ObjectDataSource), you will run into the following errors:

When changing the page on the GridView control:

The GridView 'GridViewID' fired event PageIndexChanging which wasn't handled.

When clicking a column name to sort the column on the GridView control:

The GridView 'GridViewID' fired event Sorting which wasn't handled.

As a result of not setting the DataSourceID property of the GridView to a DataSourceControl DataSource, you have to add event handlers for sorting and paging.

Click here to download the below code
Click here to test script below live

<%@ Page Language="C#" %>
 
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
 
<script runat="server">   
    private void PopulatePublishersGridView()
    {
        string connectionString = AccessConnectionString();
        OleDbConnection accessConnection = new OleDbConnection(connectionString);
 
        string sqlQuery = "SELECT [PubID], [Name], [Company Name], [Address], [City], [State], [Zip], [Telephone], [Fax], [Comments] FROM Publishers ORDER BY [Name] ASC;";
 
        OleDbCommand accessCommand = new OleDbCommand(sqlQuery, accessConnection);
 
        OleDbDataAdapter publishersDataAdapter = new OleDbDataAdapter(accessCommand);
        DataTable publishersDataTable = new DataTable("Publishers");
        publishersDataAdapter.Fill(publishersDataTable);
 
        int dataTableRowCount = publishersDataTable.Rows.Count;
 
        if (dataTableRowCount > 0)
        {
            gridViewPublishers.DataSource = publishersDataTable;
            gridViewPublishers.DataBind();
        }
    }
 
    private string AccessConnectionString()
    {
        string accessDatabasePath = Server.MapPath("~/App_Data/biblio.mdb");
        return String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};", accessDatabasePath);
    }
 
    private string GridViewSortDirection
    {
        get { return ViewState["SortDirection"] as string ?? "ASC"; }
        set { ViewState["SortDirection"] = value; }
    }
 
    private string GridViewSortExpression
    {
        get { return ViewState["SortExpression"] as string ?? string.Empty; }
        set { ViewState["SortExpression"] = value; }
    }
 
    private string GetSortDirection()
    {
        switch (GridViewSortDirection)
        {
            case "ASC":
                GridViewSortDirection = "DESC";
                break;
 
            case "DESC":
                GridViewSortDirection = "ASC";
                break;
        }
 
        return GridViewSortDirection;
    }
 
    protected void gridViewPublishers_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        gridViewPublishers.DataSource = SortDataTable(gridViewPublishers.DataSource as DataTable, true);
        gridViewPublishers.PageIndex = e.NewPageIndex;
        gridViewPublishers.DataBind();
    }
 
    protected DataView SortDataTable(DataTable dataTable, bool isPageIndexChanging)
    {
        if (dataTable != null)
        {
            DataView dataView = new DataView(dataTable);
            if (GridViewSortExpression != string.Empty)
            {
                if (isPageIndexChanging)
                {
                    dataView.Sort = string.Format("{0} {1}", GridViewSortExpression, GridViewSortDirection);
                }
                else
                {
                    dataView.Sort = string.Format("{0} {1}", GridViewSortExpression, GetSortDirection());
                }
            }
            return dataView;
        }
        else
        {
            return new DataView();
        }
    }
 
    protected void gridViewPublishers_Sorting(object sender, GridViewSortEventArgs e)
    {
        GridViewSortExpression = e.SortExpression;
        int pageIndex = gridViewPublishers.PageIndex;
        gridViewPublishers.DataSource = SortDataTable(gridViewPublishers.DataSource as DataTable, false);
        gridViewPublishers.DataBind();
        gridViewPublishers.PageIndex = pageIndex;
    }
 
    protected void Page_Load(object sender, EventArgs e)
    {
        PopulatePublishersGridView();
    }
 
</script>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>GridView Sorting/Paging without a DataSourceControl DataSource</title>
</head>
<body>
    <form id="form" runat="server">
        <div>
            <asp:GridView ID="gridViewPublishers" AllowPaging="true" AllowSorting="true" AutoGenerateColumns="false"
                EmptyDataText="No records found" PagerSettings-Mode="NumericFirstLast" PageSize="25"
                OnPageIndexChanging="gridViewPublishers_PageIndexChanging" OnSorting="gridViewPublishers_Sorting"
                runat="server">
                <AlternatingRowStyle BackColor="LightGray" />
                <HeaderStyle BackColor="Gray" Font-Bold="true" Font-Names="Verdana" Font-Size="Small" />
                <PagerStyle BackColor="DarkGray" Font-Names="Verdana" Font-Size="Small" />
                <RowStyle Font-Names="Verdana" Font-Size="Small" />
                <Columns>
                    <asp:BoundField DataField="PubID" HeaderText="Publisher ID" SortExpression="PubID" />
                    <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
                    <asp:BoundField DataField="Company Name" HeaderText="Company Name" SortExpression="Company Name" />
                    <asp:BoundField DataField="Address" HeaderText="Address" SortExpression="Address" />
                    <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
                    <asp:BoundField DataField="State" HeaderText="State" SortExpression="State" />
                    <asp:BoundField DataField="Zip" HeaderText="Zip" SortExpression="Zip" />
                    <asp:BoundField DataField="Telephone" HeaderText="Telephone" SortExpression="Telephone" />
                    <asp:BoundField DataField="Fax" HeaderText="Fax" SortExpression="Fax" />
                    <asp:BoundField DataField="Comments" HeaderText="Comments" SortExpression="Comments" />
                </Columns>
            </asp:GridView>
        </div>
    </form>
</body>
</html>

Comments

Daniel Ballinger on on 5.28.2008 at 12:55 AM

Do you always need to toggle the sort direction in GetSortDirection?

What if e.SortExpression differs from GridViewSortExpression? I.e. the user has selected a different column, so the sort order shouldn't change.
Reece on on 6.18.2008 at 5:37 AM

I believe you should store both the sort expression and the direction in the viewstate.

If the sort epxression is nothing or does not equal e.sortexpression, then the direction should be set to ascending. Otherwise reverse the sort direction.
Jacob Huizenga on on 6.19.2008 at 10:05 AM

Is there a way to do this when using a generic list (List<>) of objects as DataSource for the GridView?
Anonymous Tom on on 6.22.2008 at 11:53 AM

Wow, thanks for this posting. We do VB.NET development, but the logic has really helped me to understand.
siddu on on 6.24.2008 at 2:49 PM

HOW TO: Using updating / inserting/deleting on GridView w/o a DataSourceControl DataSource
siddu on on 6.24.2008 at 2:49 PM

HOW TO: Using sorting / paging on GridView w/o a DataSourceControl DataSource
Jake on on 6.25.2008 at 10:53 AM

Thank you for a wonderful post! This was most helpful.

In my case, I modified your code only slightly and moved it to a base page class so that all pages using a datagrid can use it without the need to put the code on each page.

Granted, you have to add event handlers to each page, but they simply call the base page event handler.

Great work!
Nato Castro on on 6.29.2008 at 12:30 PM

Hi great article, but I have an issue:

If I do the following:

if(!IsPostBack)

{

DisplayData();

}

the sorting and paging doesn't work. I tried to put ViewState to a session to save the sort direction, but still doesn't work

Any idea?

Thanks,

Nato
Rodrigo Peiro on on 7.04.2008 at 1:54 AM

Ryan,

I want to thank you for such a great article and for your tremendous contributions to the .NET community. Your article was extremely helpful and works like a charm on my application.

I always have a very hard time trying to manipulate parameters when using a DataSourceControl. They are great since they provide native paging and sorting without having to write a single line of code, but when you want to manipulate your gridView, let say filter it like a search engine it's really a pain in the neck. Maybe it's just me because of my limited experience and exposure to DataSourceControls.

I would appreciate if you can provide us an article about how to bind a gridView to a SqlDataSource or ObjectDataSource and be able to filter and change parameters source (sometimes you want assign to the value of a parameter the text of a textbox, but sometimes you want the value of that same parameter to be the text of a dropdownlist item).

Definitely a wonderful article.

Thanks again.

Rodrigo
Draco on on 7.17.2008 at 1:47 PM

Outstanding piece of work. I made a couple of mods myself and put it all in an AJAX panel; worked way slick.

Many Thanks.

I tried to add the solution I did to the asp.net forumn but it was locked.

I always do my best to pass along excellent solutions like this one. I'll pass this one along too.

Most cool.
Mauricio Avella G. on on 7.17.2008 at 6:22 PM

Gracias por tu colaboracion, me ayudo mucho lo de la paginacion del gridview...
Chuleeberry on on 7.25.2008 at 6:34 AM

Hello,I am Programmer from Thailand

I use Linq Datasource

Example

using (SkillgoDataContext skillGo = new SkillgoDataContext())

{

gvHolding.DataSource = skillGo.SelectHoldingByAccNumber(Decimal.Parse(ddlHolding.SelectedValue));

gvHolding.DataBind();

}

when i put your code in my page . at gridViewPublishers_Sorting Event I found that

gvHolding.DataSource is return null Value although i bound DataSource in SelectedIndexChanged event of Dropdownlist

.It is not DataTable Type

Thank you
Chuleeberry on on 7.25.2008 at 6:35 AM

Hello,I am Programmer from Thailand

I use Linq Datasource

Example

using (SkillgoDataContext skillGo = new SkillgoDataContext())

{

gvHolding.DataSource = skillGo.SelectHoldingByAccNumber(Decimal.Parse(ddlHolding.SelectedValue));

gvHolding.DataBind();

}

when i put your code in my page . at gridViewPublishers_Sorting Event I found that

gvHolding.DataSource is return null Value although i bound DataSource in SelectedIndexChanged event of Dropdownlist

.It is not DataTable Type

Thank you
bertelsenbo on on 8.04.2008 at 8:51 AM

Great job.

However I need to make this work with a custom ObjectDataSource. I(we?) have to convert the method SortDataTable to handle a generic List of objects instead of using a DataTable. The problem lies within the use of DataView, there has to be a sortable substitute.

Keep up the good job.

Regards

/BoB
Txomin on on 8.22.2008 at 8:01 AM

Hi Ryan,

It's a good article. However I think there's a performance issue with it.

Imagine that the query that you run has 1500 records, and in the gridview you display 15 records per page. So, everytime you sort the grid or you change of page, SortDataTable is called, which would return the 1500 records, when just 15 are being displayed in the grid. Which means that SortDataTable returns 1485 records that are not necessary. I've been trying to work out a solution to sort the performance issue but I've not been succesfull.

I'd appreciate if anybody could give me a hand with it.

Thanks,
Mior Tajjul on on 9.08.2008 at 6:17 AM

Hi Ryan

This is a good article. I have a question on Page_Load event. If i add a button the aspx file. In BUTTON event Click i call to "PopulatePublishersGridView" (instead in Page_load event), result displayed in the grid view. When i click second page, "GridView1.DataSource" has value "nothing". Any work-around on this..

Thanks
Vik on on 9.19.2008 at 1:37 PM

Hi Ryan,

I used your code for sorting and it worked perfect but the only isssue i had is i should click the header on the grid twice for the sorting to take place, in the first click the pages refreshes and on the second click sorting takes place. Could you please suggest me how to overcome this problem.

Thanks