Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

Sort Asp.net Gridview Columns with Dropdownlist Selection in C# using Order By in SQL Server

Mar 2, 2015
Introduction:

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.

Description:

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.

Design your aspx page like as shown below with dropdownlist and gridview


<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Sort Gridview Columns based on Dropdownlist Selection</title>
</head>
<body>
<form id="form1" runat="server">
<asp:DropDownList ID="ddlSort" runat="server" AutoPostBack="true"
onselectedindexchanged="ddlSort_SelectedIndexChanged">
<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:DropDownList>
<asp:GridView ID="gvShow" runat="server"></asp:GridView>
</form>
</body>
</html>

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

C# Code


using System;
using System.Data;
using System.Data.SqlClient;

Now write the following code in code behind


protected void ddlSort_SelectedIndexChanged(object sender, EventArgs e)
{
fillGrid();
}
private void fillGrid()
{
SqlConnection con= new SqlConnection("Data Source=SureshDasari; Initial Database=MySampleDB; Integrated Security=true");
string by = ddlSort.SelectedValue.ToString().Split('-')[0].ToString();
string order = ddlSort.SelectedValue.ToString().Split('-')[1].ToString();
string sql = "select * from [user] order by " + by + " " + order;
SqlDataAdapter da = new SqlDataAdapter(sql, con);
DataTable dt = new DataTable();
da.Fill(dt);
gvShow.DataSource = dt;
gvShow.DataBind();
}

I split the value of selected option into two part.

1.    Column Name
2.    Order

And then run the sql query over that to get the sort table. Get the attached files for better experience.

Happy coding...

Download Sample Attachment

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

6 comments :

Unknown said...
This comment has been removed by the author.
Unknown said...
This comment has been removed by the author.
Unknown said...

Hello,
Please do the correction in Third ListItem. please change the text of this listItem.

hhhhnkjh said...

Hhyh

Anonymous said...

ohoo....thks...

Unknown said...

Very useful code

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.