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!

Answer 1

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!