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....

 

               

Difference between stored procedure and function

Difference between stored procedure and function

1) Procedure can return zero or n values whereas function can return one value which is mandatory.
2) Procedures can have input, output parameters for it whereas functions can have only input parameters.
3) Procedure allows select as well as DML statement in it whereas function allows only select statement in it.
4) Functions can be called from procedure whereas procedures cannot be called from function.
5) Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
6) We can go for transaction management in procedure whereas we can't go in function.
7) Procedures cannot be utilized in a select statement whereas function can be embedded in a select statement.

Ajax Cascading DropDownList With Database Example in GridView

There are several cases when you have two or three dropdowns in gridview and want second one (and third one) to be populated based on selection of first or second dropdownlist.

I've used Ajax cascading dropdownlist in EditItemTemaplete of GridView for updation of records in grid by fetching data from database to populate dropdowns,I've also implemented ajax auto complete extender textbox in it to edit name field.

Make sure you have created ajax enabled website and installed ajax toolkit and ajax web extensions properly.

using System.Collections.Generic;

using System.Configuration;

using System.Data.SqlClient;

using AjaxControlToolkit;

using System.Collections.Specialized;

/// <summary>

/// Summary description for CascadingDropDown

/// </summary>

[WebService(Namespace = "http://tempuri.org/")]

[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

[System.Web.Script.Services.ScriptService()]

public class CascadingDropDown : System.Web.Services.WebService

{

//Create global Connection string

string strConnection = ConfigurationManager.ConnectionStrings

["dbConnectionString"].ConnectionString;

public CascadingDropDown () {

//Uncomment the following line if using designed components 

//InitializeComponent(); 

}

/// <summary>

/// WebMethod to populate country Dropdown

/// </summary>

/// <param name="knownCategoryValues"></param>

/// <param name="category"></param>

/// <returns>countrynames</returns>

[WebMethod]

public CascadingDropDownNameValue[] GetCountries

(string knownCategoryValues, string category)

{

//Create sql connection and sql command

SqlConnection con = new SqlConnection(strConnection);

SqlCommand cmd = new SqlCommand();

cmd.Connection = con;

cmd.CommandType = System.Data.CommandType.Text;

cmd.CommandText = "Select * from Country";

//Create dataadapter and fill the dataset

SqlDataAdapter dAdapter = new SqlDataAdapter();

dAdapter.SelectCommand = cmd;

con.Open();

DataSet objDs = new DataSet();

dAdapter.Fill(objDs);

con.Close();

//create list and add items in it 

//by looping through dataset table

List<CascadingDropDownNameValue> countryNames

= new List<CascadingDropDownNameValue>();

foreach (DataRow dRow in objDs.Tables[0].Rows)

{

string countryID = dRow["CountryID"].ToString();

string countryName = dRow["CountryName"].ToString();

countryNames.Add(new CascadingDropDownNameValue

(countryName, countryID));

}

return countryNames.ToArray();

}

[WebMethod]

public CascadingDropDownNameValue[] GetCities

(string knownCategoryValues, string category)

{

int countryID;

//this stringdictionary contains has table with key value

//pair of cooountry and countryID

StringDictionary countryValues =

AjaxControlToolkit.CascadingDropDown.

ParseKnownCategoryValuesString(knownCategoryValues);

countryID = Convert.ToInt32(countryValues["Country"]);

SqlConnection con = new SqlConnection(strConnection);

SqlCommand cmd = new SqlCommand();

cmd.Connection = con;

cmd.CommandType = System.Data.CommandType.Text;

cmd.Parameters.AddWithValue("@CountryID", countryID);

cmd.CommandText =

"Select * from City where CountryID = @CountryID";

SqlDataAdapter dAdapter = new SqlDataAdapter();

dAdapter.SelectCommand = cmd;

con.Open();

DataSet objDs = new DataSet();

dAdapter.Fill(objDs);

con.Close();

List<CascadingDropDownNameValue> cityNames =

new List<CascadingDropDownNameValue>();

foreach (DataRow dRow in objDs.Tables[0].Rows)

{

string cityID = dRow["CityID"].ToString();

string cityName = dRow["CityName"].ToString();

cityNames.Add(new CascadingDropDownNameValue

(cityName, cityID));

}

return cityNames.ToArray();

}

}

The complete html source is like this

<%@ Page Language="C#" EnableEventValidation="false"

AutoEventWireup="true" CodeFile="Default.aspx.cs"

Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"

"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

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

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

<title>Untitled Page</title>

</head>

<body>

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

<asp:ScriptManager ID="ScriptManager1" runat="server">

<Services>

<asp:ServiceReference Path="AutoComplete.asmx" />

<asp:ServiceReference Path="CascadingDropDown.asmx" />

</Services>

</asp:ScriptManager>

<asp:UpdatePanel ID="UpdatePanel1" runat="server">

<ContentTemplate>

<asp:GridView ID="GridView1" runat="server"

AutoGenerateColumns="False"

DataSourceID="SqlDataSource1"

OnRowUpdating="GridView1_RowUpdating">

<Columns>

<asp:CommandField ShowEditButton="True" />

<asp:TemplateField HeaderText="ID" SortExpression="ID">

<ItemTemplate>

<asp:Label ID="lblID" runat="server"

Text='<%#Eval("ID") %>'>

</asp:Label>

</ItemTemplate>

<EditItemTemplate>

<asp:Label ID="lblID" runat="server"

Text='<%#Bind("ID") %>'>

</asp:Label>

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Name"

SortExpression="Name">

<ItemTemplate>

<asp:Label ID = "lblName" runat="server"

Text='<%#Eval("Name") %>'>

</asp:Label>

</ItemTemplate>

<EditItemTemplate>

<asp:TextBox ID="txtName" runat="server"

Text='<%#Bind("Name") %>' >

</asp:TextBox>

<ajaxToolkit:AutoCompleteExtender

runat="server"

ID="autoComplete1"

TargetControlID="txtName"

ServicePath="AutoComplete.asmx"

ServiceMethod="GetCompletionList"

MinimumPrefixLength="1"

CompletionInterval="10"

EnableCaching="true"

CompletionSetCount="12" />

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Country"

SortExpression="Country">

<ItemTemplate>

<asp:Label ID="lblCountry" runat="server"

Text='<%#Eval("Country") %>'>

</asp:Label>

</ItemTemplate>

<EditItemTemplate>

<asp:DropDownList ID="ddlCountry" runat="server">

</asp:DropDownList>

<ajaxToolkit:CascadingDropDown

ID="CascadingDropDown1"

runat="server"

Category="Country"

TargetControlID="ddlCountry"

PromptText="-Select Country-"

LoadingText="Loading Countries.."

ServicePath="CascadingDropDown.asmx"

ServiceMethod="GetCountries">

</ajaxToolkit:CascadingDropDown>

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="City"

SortExpression="City">

<ItemTemplate>

<asp:Label ID="lblCity" runat="server"

Text='<%#Eval("City") %>'>

</asp:Label>

</ItemTemplate>

<EditItemTemplate>

<asp:DropDownList ID="ddlCity" runat="server"

OnSelectedIndexChanged="ddlCity_SelectedIndexChanged">

</asp:DropDownList><br />

<ajaxToolkit:CascadingDropDown

ID="CascadingDropDown2"

runat="server"

Category="City"

TargetControlID="ddlCity"

ParentControlID="ddlCountry"

PromptText="-Select City-"

LoadingText="Loading Cities.."

ServicePath="CascadingDropDown.asmx"

ServiceMethod="GetCities">

</ajaxToolkit:CascadingDropDown>

</EditItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

</ContentTemplate>

</asp:UpdatePanel>

<div>

<asp:SqlDataSource ID="SqlDataSource1" runat="server"

ConnectionString="<%$ ConnectionStrings:dbConnectionString %>"

SelectCommand="SELECT [ID], [Name], [City],[Country]

FROM [Location]"

UpdateCommand="Update Location set [Name] = @Name,

[City] = @City,[Country] = @Country

where ID = @ID">

<UpdateParameters>

<asp:Parameter Name="Name" />

<asp:Parameter Name="ID" />

<asp:Parameter Name="Country"/>

<asp:Parameter Name="City"/>

</UpdateParameters>

</asp:SqlDataSource>

</div>

</form> 

</body>

</html>

Finally write this code in code behind of aspx page to update record

protected void GridView1_RowUpdating

(object sender, GridViewUpdateEventArgs e)

{

//Find dropdown to get selected Item text  

DropDownList ddlGridCountry = (DropDownList)

GridView1.Rows[e.RowIndex].FindControl("ddlCountry");

string strCountry =

ddlGridCountry.SelectedItem.Text.ToString();

DropDownList ddlGridCity = (DropDownList)

GridView1.Rows[e.RowIndex].FindControl("ddlCity");

string strCity =

ddlGridCity.SelectedItem.Text.ToString();

SqlDataSource1.UpdateParameters.Clear();

SqlDataSource1.UpdateParameters.Add

("Country", strCountry);

SqlDataSource1.UpdateParameters.Add("City", strCity);

}

Hope this will helps you.