Here I will explain how to sort gridview columns based on dropdownlist selection in asp.net using Order By clause in SQL Server. Whenever we are trying to sort a gridview or Repeater through the dropdownlist we used to add either switch case or if else condition. So there will be too many conditions as the list of the dropdownlist increases and every time you have to change in code behind (adding conditions or delete conditions). While doing one of my projects I found its solutions myself and going to share with you.
Previously we have discussed about Sorting Columns in Repeater Control in Asp.net using C#, How to show the up and down arrows during sorting of column in gridview using asp.net and today we will show how to sort gridview columns based on dropdownlist selection in asp.net using Order By clause in SQL Server.
All of us know how to use ORDER BY clause in SQL Server code for example simple query like as shown below
select * from <table_name> order by <column_name> asc/desc
Here the column_name and order are variable, and others are same. So if it possible to pass the value of column_name with order (asc or desc) to the sql query then our problem will be solved.
I did the same thing here. I pass the column name and order from dropdownlist selection to code behind by post back method and in code behind with the help of just one single query (no if else conditions used) I get the ordered table. Let's see how.
<title>Sort Gridview Columns based on Dropdownlist Selection</title>
<form id="form1" runat="server">
<asp:DropDownList ID="ddlSort" runat="server" AutoPostBack="true"
<asp:ListItem Value="name-asc" Text="Name Asc"></asp:ListItem>
<asp:ListItem Value="name-desc" Text="Name Desc"></asp:ListItem>
<asp:ListItem Value="salary-asc" Text="Salary Desc"></asp:ListItem>
<asp:ListItem Value="salary-desc" Text="Salary Desc"></asp:ListItem>
<asp:GridView ID="gvShow" runat="server"></asp:GridView>
Here you can see I am sending the value of column_name and the order with the dropdownlist value. That's the trick not to use multiple if else condition or switch case.
Now with the C# code. It’s pretty simple.
After completion of aspx page write the add following namespaces in codebehind
Now write the following code in code behind
protected void ddlSort_SelectedIndexChanged(object sender, EventArgs e)
private void fillGrid()
SqlConnection con= new SqlConnection("Data Source=SureshDasari; Initial Database=MySampleDB; Integrated Security=true");
string by = ddlSort.SelectedValue.ToString().Split('-').ToString();
string order = ddlSort.SelectedValue.ToString().Split('-').ToString();
string sql = "select * from [user] order by " + by + " " + order;
SqlDataAdapter da = new SqlDataAdapter(sql, con);
DataTable dt = new DataTable();
gvShow.DataSource = dt;
I split the value of selected option into two part.
1. Column Name
And then run the sql query over that to get the sort table. Get the attached files for better experience.
Download Sample Attachment