How to sort using GridView and ObjectDataSource?

Question

I have a GridView with an ObjectDataSource and I want to be able to sort it.

Paging works correctly, however Sorting gives me an exception:

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

How do I enable sorting on the server side?

(i.e. gridView.EnableSortingAndPagingCallbacks must remains false)

Answer 1

Set the gridView.AllowSorting property to true. From here the grid should allow you to sort data automatically on postback if you are using an object that implements IBindingList. However, since that is most likely not the case, you should take TheTXI's advice above and handle the sorting event yourself. Either wire the GridView.Sorting event in the codebehind, like so:

gridView.Sorting += new GridViewSortEventHandler(gridView_Sorting); 

Handle the sorting inside the gridView_Sorting method, which should look like this:

private void gridView_Sorting(object sender, GridViewSortEventArgs e)
{
     //Sorting logic here
}

Also, you can wire the event on the page itself using OnSort="gridView_Sorting" attached to the control.

Remember, since you are setting gridView.EnableSortingAndPagingCallbacks to false, this will not be immediately fired when the user tries to sort, it instead will wait for the postback to the server.

I hope this helps!

EDIT:

Since ObjectDataSource seems to be the middleman of choice, here is a brief explanation of wiring that for sorting as well. Use the following in your page (The full example can be found here on the MSDN, near the bottom):

<asp:GridView ID="TestGridView" runat="server" DataSourceID="ObjectDataSourceTest"
        AllowSorting="True">
    </asp:GridView>
    <asp:ObjectDataSource ID="ObjectDataSourceTest" runat="server" 
        SelectMethod="SelectMethod" 
        TypeName="Samples.AspNet.CS.SortingData" 
        SortParameterName="sortExpression">
    </asp:ObjectDataSource>

Instead of actually using the gridView.Sorting event, you'll be jumping over to the ObjectDataSource to take care of the sorting. Once the sort is triggered it should call the method found in SelectMethod in your code behind. Then, inside SelectMethod, you would handle the rebuilding of your GridView object, which would look like:

public void SelectMethod(string sortExpression)
{
     //Rebuild gridView table if necessary, same method used in 
     //on a postback, and retrieve data from the database. Once
     //completed sort the data with:

     gridView.Sort(sortExpression, SortDirection.(Ascending or Descending))
}

Answer 2

You can use LINQ for this just use OrderBy on the selected column, do this:

    public static List<YourDataObject> GetSortedData(string orderBy)
    {
        List<YourDataObject> sortedDataList = new List<YourDataObject>();

        switch (orderBy)
        {
            case "Col1": sortedEmployeeList = GetDefaultObjects().OrderBy(x => x.Col1).ToList();
                break;
            case "Col2": 
                         //Do this for all columns
            default:
                sortedEmployeeList = GetDefaultObjects();
                break;
        }

        return sortedEmployeeList;
    }

This is done in the Select method of the object data source.

In the GridView add sort keys in it. And add SelectParameter in the object data source a `orderBy'

<asp:GridView AllowSorting="true" DataSourceID="objDsAllObjects" ....>
<Columns>
         <asp:BoundField SortExpression="Col1"/>
         <!-- Do this for all columns -->
</Columns>
</asp:GridView >

<asp:ObjectDataSource ID="objDsAllObjects" SortParameterName="orderBy" runat="server" 
SelectMethod="GetAllEmployees" TypeName="YourObjectClass"></asp:ObjectDataSource>

It worked for me without changing the DAL layer and stored procedures.

Answer 3

I am using Linq2Sql and a ObjectDataSource and it does Paging and Sorting very well.

I implemented a Class to be used as the ObjectDataSource. It has a Select and a Count method calling my business layer which uses Linq2SQL queries to retrieve data from the DB. The select methods gets the first item index, page size and the sort expression as parameters automatically.

public List<EntityClass> Select(int startIndex, int pageSize, string sortBy) {}
public int Count() {}

In the ASPX, the DataSource is configured like this:

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"  
     SelectMethod="Select" EnablePaging="true"
     StartRowIndexParameterName="startIndex" 
     MaximumRowsParameterName="pageSize"
     SortParameterName="sortBy" SelectCountMethod="Count" >   
</asp:ObjectDataSource>

The Select and the Count method use Linq queries to retrieve the data from the DB. I use the Skip(), Take() and Orderby() methods. For the OrderBy to accept a string sort expression I use DynamicLinq There is not much to code, Databinding, paging and Sorting are automatically working.

Answer 4

Pass SortExpression in method of Data Access Layer that calls StoredProcedure and write below way SP to handle sorting on SQL. This way you can improve the performance of your sorting.

Database SP:

Select ROW_NUMBER() OVER(ORDER BY   '+@sortExpression +' ) as RowNum
,* from (SELECT CUSTOMERID,  
LEDGERDESCRIPTION,  
CustomerDescription as CustomerName
WHERE REGIONID ='''+@RegionID+''')t
order by RowNum'