Pages

Saturday, January 28, 2012

Sorting, Paging, Add, Update, Delete in GridView asp.net c#

Sorting, Paging, Add, Update, Delete in GridView asp.net c#
Introduction : In this article i will show full functional Gridview with Sorting, Paging , Add ,Update ,delete operation .The GridView control is used to display the values of a data source in a table. It is very useful asp.net control .Here I have written the code for Sorting , Paging , Update , Delete , Add with footer row . 
The Code for All operations in GridVeiw that i have written is very easy understand and easy to implement in you application .

Html Code For Gridview with Sorting, Paging , Add ,Update ,delete operation In Asp.net: 
<table>
    <tr>
        <td>
            <asp:Label ID="lblmsg" runat="server" ForeColor="Red"></asp:Label>
        </td>
    </tr>
    <tr>
        <td>
            <asp:GridView AllowSorting="True" AllowPaging="True" PageSize="3" ID="GVCity" runat="server"
                AutoGenerateColumns="False" OnRowCommand="GVCity_RowCommand" OnPageIndexChanging="GVCity_PageIndexChanging"
                OnSorting="GVCity_Sorting" BackColor="White" BorderColor="#CC9966" BorderStyle="None"
                BorderWidth="1px" CellPadding="4">
                <Columns>
                    <asp:TemplateField HeaderText="City Name" SortExpression="Name" FooterStyle-Width="200px"
                        ItemStyle-Width="200px">
                        <ItemTemplate>
                            <asp:Label ID="lblCityName" Text='<%#Bind("Name")%>' runat="server"></asp:Label>
                            <asp:Label ID="lblCityId" Visible="false" Text='<%#Bind("CityId")%>' runat="server"></asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:TextBox ID="txtEditCityName" Text='<%#Bind("Name")%>' runat="server"></asp:TextBox>
                            <asp:Label ID="lblCityIdEdit" Visible="false" Text='<%#Bind("CityId")%>' runat="server"></asp:Label>
                            <asp:RequiredFieldValidator ID="rfvEditCityname" ValidationGroup="Edit" runat="server"
                                SetFocusOnError="true" Display="Dynamic" ControlToValidate="txtEditCityName"
                                ErrorMessage="Please , Enter City Name."></asp:RequiredFieldValidator>
                        </EditItemTemplate>
                        <FooterTemplate>
                            <asp:TextBox ID="txtFooterCityName" runat="server"></asp:TextBox>
                            <asp:RequiredFieldValidator ID="rfvFooterCityname" ValidationGroup="Footer" runat="server"
                                SetFocusOnError="true" Display="Dynamic" ControlToValidate="txtFooterCityName"
                                ErrorMessage="Please , Enter City Name."></asp:RequiredFieldValidator>
                        </FooterTemplate>
                        <FooterStyle Width="200px"></FooterStyle>
                        <ItemStyle Width="200px"></ItemStyle>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="STD Code" SortExpression="STDCode" FooterStyle-Width="100px"
                        ItemStyle-Width="100px">
                        <ItemTemplate>
                            <asp:Label ID="lblSTDCOde" Text='<%#Bind("STDCode")%>' runat="server"></asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:TextBox ID="txtEditSTDCOde" Text='<%#Bind("STDCode")%>' runat="server"></asp:TextBox>
                        </EditItemTemplate>
                        <FooterTemplate>
                            <asp:TextBox ID="txtFooterSTDCOde" runat="server"></asp:TextBox>
                        </FooterTemplate>
                        <FooterStyle Width="100px"></FooterStyle>
                        <ItemStyle Width="100px"></ItemStyle>
                    </asp:TemplateField>
                    <asp:TemplateField>
                        <HeaderTemplate>
                            Actions
                        </HeaderTemplate>
                        <FooterTemplate>
                            <table cellpadding="2">
                                <tr>
                                    <td>
                                        <asp:ImageButton ID="imgBtnAdd" CssClass="imgButton" ImageUrl="~/Images/save.png"
                                            runat="server" CommandName="AddSave" ValidationGroup="Footer" ToolTip="Save"
                                            CausesValidation="true" />
                                        <asp:ImageButton ID="ImageBtnCancel" CssClass="imgButton" runat="server" ImageUrl="~/Images/cancel.gif"
                                            CommandName="AddCancel" ToolTip="Cancel" CausesValidation="false" />
                                    </td>
                                </tr>
                            </table>
                        </FooterTemplate>
                        <EditItemTemplate>
                            <table cellpadding="2">
                                <tr>
                                    <td>
                                        <asp:ImageButton ID="imgBtnEditSave" AlternateText="Save" CssClass="imgButton" runat="server"
                                            CommandName="EditSave" ToolTip="Save" ValidationGroup="Edit" CausesValidation="true"
                                            ImageUrl="~/Images/save.png" />
                                        <asp:ImageButton ID="ImageEditCancel" AlternateText="Cancel" CssClass="imgButton"
                                            runat="server" CommandName="EditCancel" CausesValidation="false" ImageUrl="~/Images/cancel.gif"
                                            ToolTip="Cancel" />
                                    </td>
                                </tr>
                            </table>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <table cellpadding="2">
                                <tr>
                                    <td>
                                        <asp:ImageButton ID="imgLanEdit" ImageUrl="~/Images/edit.gif" CssClass="imgButton"
                                            runat="server" CommandName="CityEdit" ToolTip="Edit" />
                                        <asp:ImageButton ID="ImgBtnDelete" ImageUrl="~/Images/delete.gif" CssClass="imgButton"
                                            runat="server" CommandName="CityDelete" ToolTip="Delete" />
                                    </td>
                                </tr>
                            </table>
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
                <FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />
                <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" />
                <PagerStyle BackColor="#FFFFCC" ForeColor="#330099" HorizontalAlign="Center" />
                <RowStyle BackColor="White" ForeColor="#330099" />
                <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="#663399" />
                <SortedAscendingCellStyle BackColor="#FEFCEB" />
                <SortedAscendingHeaderStyle BackColor="#AF0101" />
                <SortedDescendingCellStyle BackColor="#F6F0C0" />
                <SortedDescendingHeaderStyle BackColor="#7E0000" />
            </asp:GridView>
        </td>
    </tr>
    <tr>
        <td>
            <asp:Button ID="imgExperiencetInsert" runat="server" Text="Insert Record" OnClick="imgExperiencetInsert_Click" />
        </td>
    </tr>
</table>
C# Code For Gridview with Sorting, Paging , Add ,Update ,delete operation In Asp.net: 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

namespace HamidSite
{
    public partial class GridviewExample : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            lblmsg.Text = "";
            if (!IsPostBack)
            {
                BindGridView();
            }
        }
        private string SortDirection
        {
            get
            {
                if (ViewState["SortDirection"] != null)
                    return (String)ViewState["SortDirection"];
                else return "ASC";
            }
            set
            {
                ViewState["SortDirection"] = value;
            }
        }
        private string SortExpression
        {
            get
            {
                if (ViewState["SortExpression"] != null)
                    return ViewState["SortExpression"].ToString();
                else return null;
            }
            set
            {
                ViewState["SortExpression"] = value;
            }
        }
        private void BindGridView()
        {
            try
            {
                DataTable Dt = GetDataTable("Select Name ,stdcode ,Cityid from citytable");
                DataView DV = Dt.DefaultView;

                if (SortExpression != null)
                {
                    DV.Sort = SortExpression + " " + SortDirection;
                }

                GVCity.DataSource = DV;
                GVCity.DataBind();
                if (DV.Count == 0)
                {
                    imgExperiencetInsert.Visible = false;
                }
                else
                {
                    imgExperiencetInsert.Visible = true;
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
        private DataTable GetDataTable(string Query)
        {
            DataSet Ds = new DataSet();
            try
            {
                string strCon = @"Data Source=Servername;Initial Catalog=Test;Integrated Security=True;";  //Conntection String
                SqlConnection Con = new SqlConnection(strCon);
                SqlDataAdapter Da = new SqlDataAdapter(Query, Con);
                Da.Fill(Ds);
            }
            catch (Exception) { }
            return Ds.Tables[0];
        }
        private int ExecuteQuery(string Query)
        {
            int RowAffected = 0;
            try
            {
                string strCon = @"Data Source=Servername;Initial Catalog=Test;Integrated Security=True;";  //Conntection String
                SqlConnection Con = new SqlConnection(strCon);
                Con.Open();
                SqlCommand cmd = new SqlCommand(Query, Con);
                RowAffected = (int)cmd.ExecuteNonQuery();
                Con.Close();
            }
            catch (Exception) { }
            return RowAffected;
        }
        /// 
        /// Edit , Update , Delete , Cancel in Gridview using RowCommand in c# Code.
        ///         
        protected void GVCity_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            switch (e.CommandName)
            {
                case "AddCancel":
                    GVCity.EditIndex = -1;
                    imgExperiencetInsert.Enabled = true;
                    GVCity.ShowFooter = false;
                    BindGridView();
                    break;
                case "AddSave":

                    GridViewRow grvAdd = ((GridViewRow)((DataControlFieldCell)((ImageButton)e.CommandSource).Parent).Parent);
                    TextBox txtFooterCityName = (TextBox)grvAdd.FindControl("txtFooterCityName");
                    TextBox txtFooterSTDCOde = (TextBox)grvAdd.FindControl("txtFooterSTDCOde");

                    string InsertQuery = "Insert into cityTable(Name,STDCode) Values('" + txtFooterCityName.Text.Trim() + "','" + txtFooterSTDCOde.Text.Trim() + "')";
                    if (ExecuteQuery(InsertQuery) > 0)
                    {
                        lblmsg.Text = "City Inserted Successfully";
                        imgExperiencetInsert.Enabled = true;
                    }

                    GVCity.ShowFooter = false;
                    BindGridView();

                    break;
                case "CityDelete":
                    GridViewRow grv = ((GridViewRow)((DataControlFieldCell)((ImageButton)e.CommandSource).Parent).Parent);

                    if (grv != null)
                    {
                        Label lblCityId = (Label)grv.FindControl("lblCityId");
                        String DeleteQuery = "Delete from cityTable where cityid =" + lblCityId.Text;
                        
                        if (ExecuteQuery(DeleteQuery) > 0)
                        {
                            lblmsg.Text = "City Deleted Successfully";

                        }
                        BindGridView();
                        break;
                    }

                    break;
                case "CityEdit":
                    GridViewRow grvEdit = ((GridViewRow)((DataControlFieldCell)((ImageButton)e.CommandSource).Parent).Parent);
                    GVCity.EditIndex = grvEdit.RowIndex;
                    BindGridView();
                    break;

                case "EditSave":
                    GridViewRow grvSaveEdit = ((GridViewRow)((DataControlFieldCell)((ImageButton)e.CommandSource).Parent).Parent);

                    TextBox txtEditCityName = (TextBox)grvSaveEdit.FindControl("txtEditCityName");
                    TextBox txtEditSTDCOde = (TextBox)grvSaveEdit.FindControl("txtEditSTDCOde");
                    Label lblCityIdEdit = (Label)grvSaveEdit.FindControl("lblCityIdEdit");

                    string queryUpdate = "update cityTable set Name = '" + txtEditCityName.Text.Trim() + "', STDCode ='" + txtEditSTDCOde.Text.Trim() + "' where cityid=" + lblCityIdEdit.Text;
                    if (ExecuteQuery(queryUpdate) > 0)
                    {
                        lblmsg.Text = "City Updated Successfully";
                        imgExperiencetInsert.Enabled = true;
                    }
                    
                    GVCity.EditIndex = -1;
                    BindGridView();

                    break;
                case "EditCancel":
                    GVCity.EditIndex = -1;
                    BindGridView();
                    break;
            }
        }
        protected void imgExperiencetInsert_Click(object sender, EventArgs e)
        {
            imgExperiencetInsert.Enabled = false;
            GVCity.ShowFooter = true;
            GVCity.EditIndex = -1;
            BindGridView();
        }
        protected void GVCity_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            GVCity.PageIndex = e.NewPageIndex;
            BindGridView();
        }
        /// 
        /// Gridview Sorting Code in c#
        ///        
        protected void GVCity_Sorting(object sender, GridViewSortEventArgs e)
        {
            if (SortExpression != null)
            {
                if (SortExpression == e.SortExpression && SortDirection == "ASC")
                    SortDirection = "DESC";
                else
                    SortDirection = "ASC";
            }
            SortExpression = e.SortExpression;
            BindGridView();
        }
    }
}


Related Other posts

11 comments:

  1. Good job ,

    this code helped me a lot.This example covers most of operation in gridview .

    Thnx

    ReplyDelete
  2. Nice code

    thank you

    ReplyDelete
  3. This is really a nice exapmle. Only thing missing is that if a user wants to save all the records at once on a single button click with a button lick insert record button.

    ReplyDelete
  4. Hi!

    This is nice article. There are some other article related to this topic

    http://mindstick.com/Articles/38ca3305-6346-467e-b03b-a536737ff623/?Insert%20Delete%20Update%20in%20Gr#dvHeaderText

    http://www.aspdotnet-suresh.com/2011/02/how-to-inserteditupdate-and-delete-data.html

    http://www.google.co.in/url?sa=t&rct=j&q=&esrc=s&source=web&cd=2&cad=rja&ved=0CDoQFjAB&url=http%3A%2F%2Fwww.codeproject.com%2FArticles%2F417693%2FInsert-Update-Delete-in-ASP-NET-Gridview-DataSourc&ei=6xZkUdTFA4iHrAeOtoCIAg&usg=AFQjCNGkz7WZ58dJhaEFYkshVe6GYrhwNA&bvm=bv.44990110,d.bmk

    ReplyDelete
  5. Ola puedes compartir tu proyceto

    ReplyDelete
  6. No encuentro donde esta el Dt.DefaultView;

    ReplyDelete
  7. Nice! you are sharing such helpful and easy to understandable blog. i have no words for say i just say thanks because it is helpful for me.



    Dot Net Training in Chennai | Dot Net Training in anna nagar | Dot Net Training in omr | Dot Net Training in porur | Dot Net Training in tambaram | Dot Net Training in velachery



    ReplyDelete