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!

Xyster

Answer 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.

Affricative

Answer 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!

Cusp

Answer 3

I'm not sure about this one, but if you use a standard SqlDataSource and you click on a field to sort according to that field, the SqlDataSource is populated again with the data and it is rebound to the grid. So the sorting does not happen on the client side and also can be done only when the selectmethod of the SQLDataSource is not DataReader.

When handling the sorting event, do you recreate the SqlDataSource and rebound it to the GridView? Can you put the sort field and direction to the generatedSelectCommand, which you use? Or put it to the SortParameterName property of the SQLDataSource?

I'm absolutely sure that you have to rebound the SqlDataSource to the grid, and since you create it on the fly, you have to populate it again.

Mascon

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, – Xyster

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. – Mascon

Answer 4

Better late than never?

Some addition for Keith's suggestion which is basically the right one.

Truth is, that you have to deal with sorting on gridView_Sorting event. There is no need to DataBind() the GridView earlier, for example in Page_Load event. There you should only call the GridView.Sort() method instead of .DataBind(). Here is how it goes:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    If Not IsPostBack Then

        Me.gridView.Sort(Request.QueryString("sortExpression"), Request.QueryString("sortDirection"))

    End If

End Sub

Next let's have a look on gridView_Sorting event.

There you have to push the datasource to the right sorting. GridView itself does not handle that (in this case at least).

Protected Sub gridView_Sorting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs) Handles gridView.Sorting
    If IsPostBack Then
        e.Cancel = True
        Dim sortDir As SortDirection = SortDirection.Ascending
        If e.SortExpression = Me.Q_SortExpression And Me.Q_SortDirection = SortDirection.Ascending Then
            sortDir = SortDirection.Descending
        End If
        RedirectMe(e.SortExpression, sortDir)
    Else
        Dim sortExpr As String = e.SortExpression + " " + IIf(e.SortDirection = SortDirection.Ascending, "ASC", "DESC")
        Dim dv As System.Data.DataView = Me.dsrcView.Select(New DataSourceSelectArguments(sortExpr))
        Me.gridView.DataSource = dv
        Me.gridView.DataBind()
    End If
End Sub

No need to code any sorting functionality in data source like passing sort parameters to stored procedure. All sorting takes place in the above pieces of code.

Moreover, it's good to have the gridView.EnableViewState switched to False which causes the page to be much lighter for the network traffic and for the browser as well. Can do that as the grid is entirely recreated whenever the page is post back.

Have a nice day!

Martin

Gunrunning