How to sort data in gridview by clicking column header

In this article I am going to show how to sort Gridview Data in Ascending and Desending oreder by clicking on Column Header with the up and Down icons in header.

For this firstly you need to bind Gridview with a Datasource. Set allowsorting property true and set sortexpression property to to respective datafield name.
Here is the Html Code for the GirdView.

<asp:GridView ID="grd1" runat="server" Width="100%" BorderColor="#CFDDEE" BorderWidth="1px" BorderStyle="Solid"
        Font-Names="Verdana,Geneva,sans-serif" Font-Size="11px"
        AutoGenerateColumns="False" onsorting="grd1_Sorting" AllowSorting="true"
        onrowdatabound="grd1_RowDataBound">
<Columns>
    <asp:BoundField DataField="ID" HeaderText=" User ID">
    </asp:BoundField>
    <asp:BoundField DataField="User_Title" HeaderText="User Title" SortExpression="User_Title">
    </asp:BoundField>
    <asp:BoundField DataField="Role" HeaderText="Role" SortExpression="Role">
    </asp:BoundField>
    <asp:BoundField DataField="User_Name" HeaderText="User Name" SortExpression="User_Name">
    </asp:BoundField>
</Columns>
</asp:GridView>

Create Public Property of SortDirection type and store direction in ViewState.
    public SortDirection dir
    {
        get
        {
            if (ViewState["dirState"] == null)
            {
                ViewState["dirState"] = SortDirection.Descending;
            }
            return (SortDirection)ViewState["dirState"];
        }
        set
        {
            ViewState["dirState"] = value;
        }

    }
protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
             ViewState["SortDirection"] = "Desc";
            gvDetails.DataSource = BindGridView();
            gvDetails.DataBind();
        }

    }
private DataTable BindGridView()
    {
        DataTable dtGrid = new DataTable();
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        string strSelect = "SELECT ID,User_Name,User_Title,Role FROM User_Master_Tbl";
        SqlCommand cmd = new SqlCommand(strSelect, con);
        SqlDataAdapter dAdapter = new SqlDataAdapter(cmd);
        dAdapter.Fill(dtGrid);
        Session["DataTable"] = dtGrid;
        return dtGrid;
    }
protected void grd1_Sorting(object sender, GridViewSortEventArgs e)
    {
        DataTable dt = new DataTable();
        dt = (DataTable)Session["DataTable"];
        string sortingDirection = string.Empty;
        if (dir == SortDirection.Ascending)
        {
            dir = SortDirection.Descending;
            sortingDirection = "Desc";
        }
        else
        {
            dir = SortDirection.Ascending;
            sortingDirection = "Asc";
        }
        DataView sortedView = new DataView(dt);
        sortedView.Sort = e.SortExpression + " " + sortingDirection;
        ViewState["SortDirection"] = sortingDirection;
        grd1.DataSource = sortedView;
        grd1.DataBind();
    }
protected void grd1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        string imgAsc = @" <img src='image/AscArrow.png' border='0' title='Ascending' />";
        string imgDes = @" <img src='image/DscArrow.png' border='0' title='Descending' />";
        if (e.Row.RowType == DataControlRowType.Header)
        {
            for (int i = 0; i < e.Row.Cells.Count; i++)
            {
                LinkButton lnkbtn = (LinkButton)e.Row.Cells[i].Controls[0];
                {
                    if (ViewState["SortDirection"] != null)
                    {
                        if (ViewState["SortDirection"].ToString() == "Asc")
                        {
                            lnkbtn.Text += imgAsc;
                        }
                        else
                            lnkbtn.Text += imgDes;
                    }
                    else
                    {
                        lnkbtn.Text += imgDes;
                    }
                }
            }
        }
    }
Here are the Up/Down icons:

Hope this will help you.

Import Data from Excel file to Sql Database Table

Here I am going to show you the step by step process to import data from Excel to SQL Server.There are many cases where you need to import data from Excel file to your SQL Databse Table. This code to import data from Excel to SQL table does not contain any type of validations.

We have a sample Excel file Customer.xlsx of customer details from which we have to import data.

Customer_ID	Customer_Name	Customer_Email	Customer_Mobile
10001	Nitin Kumar	nitin.kumar@acb.com	9877889995
10002	Sumit Garg	sumit.garg@acb.com	4353536254
10003	Shiv Sagar	shiv.sagar@acb.com	7634347464
10004	Raju Chaudhary	raju.chaudhary@acb.com	8766542677
10005	Manoj Kumar	manoj.kumar@acb.com	7786365338

  Step 1:

 Crate a Sample table Customer_Master .         

create table Customer_Master
(

Customer_ID int,
Customer_Name nvarchar(20),
Customer_Email nvarchar(50),

Customer_Mobile nvarchar(10)

)   

Step 2:

Now create a page upload_customer.aspx. 

 

<html xmlns="http://www.w3.org/1999/xhtml">

<head id="Head1" runat="server">

<title></title>

</head>

<body>

<form id="form1" runat="server">

<div>

<table>

<tr>

<td>

<span style="color: Red">*</span>Select Excel File:

</td>

<td>

<asp:FileUpload ID="fileuploadExcel" runat="server" />

</td>

</tr>

<tr>

<td></td>

<td>

<asp:Button ID="btnupload" runat="server" Text="Upload" onclick="btnupload_Click"  />

</td>

</tr>

</table>

</div>

</form>

</body>

</html>

 

Step 3:

Here is the code in customer_upload.aspx.cs.

using System;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Office.Core;
using System.IO;
using System.Text;
using System.Data.OleDb;

public partial class customer_upload: System.Web.UI.Page

{

 if (!Page.IsPostBack)

{

 

}

protected void btnupload_Click(object sender, EventArgs e) 

{

OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(@"~/Upload/" + hdnfield2.Value) + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"");
        OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
        OleDbDataAdapter oda = new OleDbDataAdapter(ocmd);
        ocmd.CommandTimeout = 0;
        oconn.Open(); 

OleDbDataReader odr = ocmd.ExecuteReader();

while(ode.Read())

{

 string CustomerID = odr[0].ToString().Trim();      
 string CustomerName = odr[1].ToString().Trim();      
 string CustomerEmail = odr[2].ToString().Trim();     
 string CustomerMobile = odr[3].ToString().Trim();
 //Here using this method we are inserting the data into the database
 insertdataintosql(CustomerID, CustomerName, CustomerEmail, CustomerMobile);
}
oconn.Close();
}

public void insertdataintosql(string CustomerID, string CustomerName, string CustomerEmail, string CustomerMobile)
 {
        //inserting data into the Sql Server
        SqlConnection conn = new SqlConnection("Data Source=.\\sqlexpress;
    AttachDbFileName=|DataDirectory|exceltosql.mdf;Trusted_Connection=yes");
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandText = "insert into Customer_Master(Customer_ID,Customer_Name,Customer_Email,Customer_Mobile) values(@CustomerID,@CustomerName,@CustomerEmail,@CustomerMobile)";
        cmd.Parameters.Add("@CustomerID", SqlDbType.Int).Value = Convert.ToInt32(CustomerID);
        cmd.Parameters.Add("@CustomerName", SqlDbType.NVarChar).Value = CustomerName;
        cmd.Parameters.Add("@CustomerEmail", SqlDbType.NVarChar).Value = CustomerEmail;
        cmd.Parameters.Add("@CustomerMobile", SqlDbType.NVarChar).Value = CustomerMobile;      
        cmd.CommandType = CommandType.Text;
        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();
    }

}

I hope this will help you. Enjoy coding....