Sorting in GridView

Sorting is kind of very useful information if we want to quickly analyze bunch of data based on columns. And being one of most used control to display data in ASP.NET, GridView can implement easily with few more line of codes (who doesn’t love to write more … right ?? )… And in today’s post I am showing how to add functionality to SORT data in GridView. Actually there are many ways to do it and use is kind of personal choice (I am not that deep into efficiency part, so not sure about it YET). Actually this method is kind of consolidation of different posts that I found on web world… so I don’t claim that it is an original invention… consider it as a bit amalgamation of different methods.

This is how it works (I have included the Code file in the end, also just to note I have used Visual Studio 2010 and AdventureWorks sample database which are free to use )…

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" AllowSorting="true"
         onsorting="GridView1_Sorting">
            <Columns>
                <asp:BoundField DataField="Name" HeaderText="Name" ReadOnly="True" 
                    SortExpression="Name" />
                <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
                <asp:BoundField DataField="BirthDate" HeaderText="BirthDate" 
                    SortExpression="BirthDate" DataFormatString="{0:d}" />
            </Columns>
        </asp:GridView>

Here, I have set AutoGenerateColumns=”False” … which will allow me to write my own setting of Columns which is kind of more flexible for example, I can set the way DATE column to only include date .. not the time, Or header of columns ! Also I have set AllowSorting=”true”, which will enable sorting. Now even though I have enabled sorting it will not sort data automatically !! we need to add some kind of event handler which will deal will SORT event which is ONSORTING.

In the code behind file, we have made few methods, first one is “bindGrid” which will do main work like connecting database and getting queried data. It is actually pretty simple process we are just getting data from database and storing that result into data adapter. Next is SortOrder class which is used for toggle between Ascending and Descending order of sorting.

  public void bindGrid(string sortExp, string sortDir)
        {
            SqlConnection conn1 = new SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorksConnectionString"].ToString());
            SqlDateTime dt = SqlDateTime.Null;
            SqlInt32 i = SqlInt32.Null;
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn1;
            conn1.Open();
            cmd.CommandText = "select (ct.LastName+','+ct.FirstName)Name, pad.City,e.BirthDate from HumanResources.Employee e "+
                                " join HumanResources.EmployeeAddress a on e.EmployeeID=a.EmployeeID " +
                                " join Person.Address pad on a.AddressID=pad.AddressID "
                                +" join Person.Contact ct on e.ContactID= ct.ContactID "+
                                    " Where City = @City";// Order By ct.LastName asc ";
            cmd.Parameters.Add("@City", SqlDbType.VarChar).Value = DropDownList1.SelectedValue;

            SqlDataAdapter da = new SqlDataAdapter(cmd);          
            DataSet ds = new DataSet();
            da.Fill(ds);
            DataView dv = new DataView();
            dv = ds.Tables[0].DefaultView;
            if (sortExp != string.Empty)
            {
                dv.Sort = string.Format("{0} {1}", sortExp, sortDir);
            }
            GridView1.DataSource = dv;
            GridView1.DataBind();
        }

        public string sortOrder
        {
            get
            {
                if (ViewState["sortOrder"].ToString() == "desc")
                {
                    ViewState["sortOrder"] = "asc";
                }
                else
                {
                    ViewState["sortOrder"] = "desc";
                } return ViewState["sortOrder"].ToString();
            }
            set
            {
                ViewState["sortOrder"] = value;
            }
        }

        protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
        {
            bindGrid(e.SortExpression, sortOrder);
        }

        protected void Button1_Click1(object sender, EventArgs e)
        {
            ViewState["sortOrder"] = "";
            bindGrid("", "");
        }

And now the easy part, just fireup the application and you will see that you have now functionality to sort the data simply by clicking on column names. I actually tried to put up/down arrows so user can see that in which direction the sorting has done, but so far I have not got any success  I am hoping that in time I will be able to get it done as well …

I have used THIS demo for post, just change the database server name and you are all set (assuming you aren’t using SQL Server Express, I have SQL Server Dev. edition)

That’s it for now…

It’s Just A Thought …