How to sort columns in ASP.NET GridView when using custom DataSource?

Question

I cannot get the GridView to allow the user to sort the data column when I use my own SqlDataSource.

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

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

In encoding, I attach a dynamically created SqlDataSource (the columns contained in it are not always the same, so the SQL used to create it is created at runtime). For instance:

I set the columns ...

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

data source...

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

then gridview ...

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

Currently, nothing happens when the user clicks on the column header, when I expect him to sort the column data. Does anyone know what I am missing?

If there is a nicer way to do this, it will also be useful, as it looks messy for me!

Answer 1

First you need to add an event:

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

Then this event is as follows:

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

You basically need to get your data again.

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

Unfortunately, this is a very different page model.

Answer 2

You can also simply reassign the data source. Select the command before calling DataBind () in the sort handler. Something like that:

 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 more help to implement it.

Enjoy it!

Answer 3

I'm not sure about this, but if you use the standard SqlDataSource and you select the sort field according to this field, the SqlDataSource is again populated with data and it goes back to the grid. Thus, the sorting does not occur on the client side, and can also be performed only when the selectmethod SQLDataSource is not a DataReader.

When processing a sort event, recreate an SqlDataSource and bounce it into a GridView? Can you put the sort field and direction in the generated SelectCommand that you use? Or put it in the SortParameterName property for SQLDataSource?

I am absolutely sure that you need to bounce the SqlDataSource to the grid, and since you create it on the fly, you need to populate it again.

Answer 4

Better late than never?

Some supplement for China's offer, which is basically the right one.

In truth, you have to deal with sorting by the gridView_Sorting event. There is no need for a DataBind () GridView before, for example, in the Page_Load event. There you should call the GridView.Sort () method instead of .DataBind (). Here's how to do it:

 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, consider the gridView_Sorting event.

There you need to push the data source to the correct sort. GridView alone does not process (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 

There is no need to encode any sorting function in the data source, for example, passing sorting parameters to a stored procedure. All sorting is done in the above code snippets.

It’s also good that gridView.EnableViewState is switched to False, which makes the page much easier for network traffic and for the browser. This can be done because the grid is completely recreated whenever the page is sent back.

Have a nice day!

Martin