How To Sort Columns In An Asp.net Gridview If Using A Custom Datasource?

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

Solution 1:

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

protectedvoidgvItems_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();
}

stringGetSortDirection(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!

Solution 2:

First you need to add an event:

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

Then that event looks like:

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

Solution 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:

ProtectedSub Page_Load(ByVal sender AsObject, ByVal e As System.EventArgs) HandlesMe.Load

    IfNot IsPostBack ThenMe.gridView.Sort(Request.QueryString("sortExpression"), Request.QueryString("sortDirection"))

    EndIfEndSub

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

ProtectedSub gridView_Sorting(ByVal sender AsObject, ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs) Handles gridView.Sorting
    If IsPostBack Then
        e.Cancel = TrueDim sortDir As SortDirection = SortDirection.Ascending
        If e.SortExpression = Me.Q_SortExpression AndMe.Q_SortDirection = SortDirection.Ascending Then
            sortDir = SortDirection.Descending
        EndIf
        RedirectMe(e.SortExpression, sortDir)
    ElseDim sortExpr AsString = 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()
    EndIfEndSub

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