How to sort columns in an ASP.NET GridView if using a custom DataSource?

Question

I can't get my GridView to enable a user to sort a column of data when I'm using a custom SqlDataSource.

I have a GridView in which the code in the ASP reference to it in the HTML is minimal:

<asp:GridView id="grid" runat="server" AutoGenerateColumns="False" AllowSorting="True">
</asp:GridView>

In the code-behind I attach a dynamically-created SqlDataSource (the columns it contains are not always the same so the SQL used to create it is constructed at runtime). For example:

I set up the columns...

BoundField column = new BoundField();
column.DataField = columnName;
column.HeaderText = "Heading";
column.SortExpression = columnName;

grid.Columns.Add(column);

the data source...

SqlDataSource dataSource = new SqlDataSource(
    "System.Data.SqlClient",
    connectionString, 
    generatedSelectCommand);

then the gridview...

grid.DataSource = dataSource;
grid.DataKeyNames = mylistOfKeys;
grid.DataBind();

At the moment nothing happens when a user clicks on a column heading when I'd expect it to sort the column data. Anyone any ideas what I'm missing?

If there's a nicer way of doing this that would be helpful too as this looks messy to me!

Solution 1

First you need to add an event:

<asp:GridView AllowSorting="True" OnSorting="gvName_Sorting" ...

Then that event looks like:

protected void gvName_Sorting( object sender, GridViewSortEventArgs e )
{
    ...
    //rebind gridview
}

You basically have to get your data again.

You're right that it looks messy and there is a better way: ASP.Net MVC

Unfortunately that's a drastically different page model.

Solution 2

You could also just reassign the datasource.SelectCommand before the DataBind() call in the Sorting handler. Something like this:

protected void gvItems_Sorting(object sender, GridViewSortEventArgs e)
{
    GridView gv = (GridView)sender;
    SqlDataSource ds = (SqlDataSource)gv.DataSource;
    ds.SelectCommand = ds.SelectCommand + " order by " 
        + e.SortExpression + " " + GetSortDirection(e.SortDirection);
    gvItems.DataSource = ds;
    gvItems.DataBind();
}

string GetSortDirection(string sSortDirCmd)
{
    string sSortDir;
    if ((SortDirection.Ascending == sSortDirCmd))
    {
        sSortDir = "asc";
    }
    else
    {
        sSortDir = "desc";
    }
    return sSortDir;
}

I hope this help. Let me know if you need extra help to implement it.

Enjoy!

Comments

[Ben L]

At the moment I don't handle the sorting event at all and am leaving it to it's default behaviour. However, I have checked that within the sorting event the SortExpression and SortDirection are something sensible and not blank. The data source is re-bound to the grid on post back,

[Biri]

You should handle the event as Keith mentioned. With a standard SqlDataSource control it is done automatically, but in this case you should also take care of it by yourself.