Most of the ASP.Net developers would have used Gridview control in their applications. This article explains about sorting the gridview dynamically on multiple columns in different order in web applications. I am using Product table of AdventureWorks database for this sorting.
Our objective is to achieve the sorting order ListPrice desc, ReOrderPoint asc, Name desc on a gridview which fetches from product table – For this user would first click ListPrice column on gridview twice, ReOrderPoint column once and Name column twice to achieve this order. Clicking the column once would sort it in ascending order; clicking twice would sort it in descending order.
Sorting the Gridview involves the below steps:
- Set the AllowSorting attribute of Gridview Control to true
- Define the method for OnSorting event in Gridview – This method would be called when user clicks the header link to perform sorting.
- Define the SortExpression for the columns that you want sorting to be handled in gridview – This SortExpression (of type string)would be passed as parameter for Onsorting event method that we have defined in Step 2
- Load the data source based on the SortExpression and bind the data to the gridview . The sorting logic is handled here.
Enough of theory. Let’s fire up Visual studio and create a web application.
Before we do the sorting on gridview, let us pull up the data from the database and bind the data to the gridview. This approach of writing the code incrementally would help us find the issues at the early stage itself and resolve the issues as and when they arise.
Below Gridview is added to Webform
<asp:GridView ID="gv" runat="server" AutoGenerateColumns="false" > <Columns> <asp:BoundField DataField="ProductNumber" HeaderText="Product Number" /> <asp:BoundField DataField="Name" HeaderText="Name" /> <asp:BoundField DataField="ReorderPoint" HeaderText="ReorderPoint" /> <asp:BoundField DataField="ListPrice" HeaderText="ListPrice" /> <asp:BoundField DataField="MakeFlag" HeaderText="MakeFlag" /> </Columns> </asp:GridView>
In the code behind, I have created the below methods
- GetProducts – This method would query the database and returns the products available in the Product table of AdventureWorks database. This method creates the connection using the connection string defined in web.config file. The recommended way to write this data access method is to write it in Data Access Layer (preferably in separate project) and NOT in code behind. For the sake of simplicity and to concentrate on the problem (sorting the gridview) at hand, I wrote this at code behind.
private DataTable GetProducts(string orderClause)
{
string sqlQuery = "select Name,ProductNumber,ReorderPoint,ListPrice,MakeFlag from Production.Product" + orderClause;
string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["AdventureWorksContext"].ConnectionString;
DataTable dt = new DataTable();
try
{
SqlConnection conn = new SqlConnection(connectionString);
SqlDataAdapter da = new SqlDataAdapter(sqlQuery,conn);
da.Fill(dt);
&nbsp;
}
catch(Exception ex)
{
throw ex;
}
return dt;
}
- The below method would bind the products to the gridview
private void LoadProducts(string orderClause)
{
DataTable dtProducts = new DataTable();
dtProducts = GetProducts(orderClause);
if(dtProducts!=null)
{
gv.DataSource=dtProducts;
gv.DataBind();
}
}
- On Pageload event, I am calling this LoadProducts method.
protected void Page_Load(object sender, EventArgs e)
{
if(!IsPostBack)
{
LoadProducts(string.Empty);
}
}
When I run the application, the gridview would be populated with the product data
Let us come back to the problem of sorting the gridview.I have done the below changes to the gridview
- Set AllowSorting attribute = “true”
- Set OnSorting attribute = “gv_Sorting” – This method (gv_Sorting) would be called when user tries to perform sorting
- Set the SortExpression for all the columns that you want sorting.
And the resulting gridview is like below
<asp:GridView ID="gv" runat="server" AutoGenerateColumns="false" AllowSorting="true" OnSorting="gv_Sorting" DataKeyNames="ProductNumber"> <Columns> <asp:BoundField DataField="ProductNumber" HeaderText="Product Number" /> <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" /> <asp:BoundField DataField="ReorderPoint" HeaderText="ReorderPoint" SortExpression="ReorderPoint" /> <asp:BoundField DataField="ListPrice" HeaderText="ListPrice" SortExpression="ListPrice" /> <asp:BoundField DataField="MakeFlag" HeaderText="MakeFlag" SortExpression="MakeFlag" /> </Columns> </asp:GridView>
We need to preserve both the sorting expression and sorting order. I am going to write this sorting logic in separate class as it could be used across application.
The sorting logic is pretty much simple and is explained below
- We are maintaining 2 hash tables – one for storing the column info as the key on which sorting has to be performed and the direction(ascending or descending) as the value and another hashtable is for storing the order of the sorting columns
- If the column name is already present in hash table, it would change the sorting direction. i.,e For example if the gridview is earlier sorted by ListPrice column on ascending direction, now the direction would have updated as descending . If the ListPrice column is not present earlier, it would be added with asc direction in hash table.
- The order position of the column is updated as total number of columns for sorting + 1
- When the order by clause is generated to populate the data source, the hashtable containing position would be sorted by value and then we would get the direction of the column.
The source code is given below
[Serializable]
public class OrderBy
{
public Hashtable hshOrderExpressions { get; set; } // To store the column info on which gridview has to be sorted
public Hashtable hshOrderPosition { get; set; } //To store the sort order
&nbsp;
public OrderBy()
{
hshOrderExpressions = new Hashtable();
hshOrderPosition = new Hashtable();
}
public string GetOrderByClause()
{
string orderby = string.Empty;
try
{
if (hshOrderExpressions.Count > 0)
{
var orderbyClause = hshOrderPosition.Cast<DictionaryEntry>().OrderBy(entry => entry.Value);
foreach(DictionaryEntry clause in orderbyClause)
{
string key = Convert.ToString(clause.Key);
orderby += key + " " + hshOrderExpressions[key]+",";
}
&nbsp;
&nbsp;
}
if(orderby.Length>0)
{
orderby = " order by "+ orderby.Substring(0, orderby.Length - 1);
}
&nbsp;
}
catch(Exception ex)
{
throw ex;
}
return orderby;
}
&nbsp;
public bool AddOrderBy(string orderBy)
{
bool isAddedSuccessfully = false;
try
{
if(hshOrderExpressions.Count>0)
{
if(hshOrderExpressions[orderBy]!=null)
{
hshOrderExpressions[orderBy] = Convert.ToString(hshOrderExpressions[orderBy]) == "Asc" ? "Desc" : "Asc";
}
else
{
hshOrderExpressions[orderBy] = "Asc";
}
&nbsp;
hshOrderPosition[orderBy] = hshOrderPosition.Count + 1;
}
else
{
hshOrderExpressions[orderBy] = "Asc";
hshOrderPosition[orderBy] = 1;
}
isAddedSuccessfully = true;
&nbsp;
&nbsp;
}
catch(Exception ex)
{
throw ex;
}
return isAddedSuccessfully;
}
}
And I am using the OrderBy object in gv_Sorting method to generate the sorting expression to get it appended to select query. I am storing this OrderBy object in ViewState so that the information would not be lost in postbacks.
protectedvoid gv_Sorting(object sender, GridViewSortEventArgs e)
{
OrderBy objOrderBy = (OrderBy)ViewState["Sort"];
if(objOrderBy==null)
{
objOrderBy = newOrderBy();
}
objOrderBy.AddOrderBy(e.SortExpression);
ViewState["Sort"] = objOrderBy;
string order = objOrderBy.GetOrderByClause();
LoadProducts(order);
lblOrder.Text = order.Trim().Length > 0 ? "Order:" + order : "Order:No order";
}
Below is the flowchart to debug Gridview sorting issues
The source code of this solution can be found at https://github.com/mugiltsr/GridView_Sorting
If you like this article, please subscribe below so that I can send articles straight to your inbox.
[mc4wp_form]