Guruji Point - Code You Want To Write

  • Home
  • Asp.Net
  • WCF
  • SQL
  • Type Script
  • AngularJS
  • Tech News
  • Blog
  • Earn Online

Wednesday, 13 April 2016

Insert, Edit,Update, Delete (CRUD) Operation in Grid View

 gridview     No comments   

Introduction

In this article I will explain how to perform CRUD operation in gridview using asp.net.

Description

In my last Update i  talked about Bind Grid using DataSet and here in this i do the same for binding and for Insert, Edit,Update, Delete operation we have to use the gridView events. Always we need to use GridView events to perform  Insert, Edit,Update, Delete operations. 
I have used some of gridview events those are 

  1) Onrowcancelingedit

  2) Onrowediting
  3) Onrowupdating
  4) Onrowcancelingedit
   5) Onrowdeleting

Before switching in Asp.Net part we need to design our Database first. Now take a look on my database design 



SQL 
Create Database Employee
Use Employee

Create table EmpDetail( UserId int Identity(1,1), UserName varchar(50),
City varchar(50),  Designation varchar(50))

On Aspx WebForm write down the following code

ASPX Page

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
    <style type="text/css">
        .Gridview {
            font-family: Verdana;
            font-size: 10pt;
            font-weight: normal;
            color: black;
        }
    </style>
</head>
<body>
    <form id="form2" runat="server">
        <div>
            <asp:GridView ID="gvDetails" DataKeyNames="UserId,UserName" 
      runat="server" AutoGenerateColumns="false" CssClass="Gridview"
        HeaderStyle-BackColor="#61A6F8"
                ShowFooter="true" HeaderStyle-Font-Bold="true"
            HeaderStyle-ForeColor="White"
                OnRowCancelingEdit="gvDetails_RowCancelingEdit"
                OnRowDeleting="gvDetails_RowDeleting" 
               OnRowEditing="gvDetails_RowEditing"
                OnRowUpdating="gvDetails_RowUpdating"
                OnRowCommand="gvDetails_RowCommand">
          <Columns>
                    <asp:TemplateField>
                        <EditItemTemplate>
                            <asp:ImageButton ID="imgbtnUpdate" 
                         CommandName="Update" runat="server" 
                                ImageUrl="~/Images/update.jpg" 
            ToolTip="Update" Height="20px" Width="20px" />
 
             <asp:ImageButton ID="imgbtnCancel" runat="server" 
CommandName="Cancel" ImageUrl="~/Images/Cancel.jpg" ToolTip="Cancel" 
Height="20px" Width="20px" />
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:ImageButton ID="imgbtnEdit" 
CommandName="Edit" runat="server" ImageUrl="~/Images/Edit.jpg" 
ToolTip="Edit" Height="20px" Width="20px" />
                            <asp:ImageButton ID="imgbtnDelete" 
 CommandName="Delete" Text="Edit" runat="server" 
ImageUrl="~/Images/delete.jpg" ToolTip="Delete" Height="20px"
 Width="20px" />
                        </ItemTemplate>
                        <FooterTemplate>
                            <asp:ImageButton ID="imgbtnAdd" runat="server" 
ImageUrl="~/Images/AddNewitem.jpg" CommandName="AddNew" Width="30px" 
Height="30px" ToolTip="Add new User"
                                 ValidationGroup="validaiton" />
                        </FooterTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="UserName">
                        <EditItemTemplate>
                            <asp:Label ID="lbleditusr" runat="server"
 Text='<%#Eval("Username") %>' />
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label ID="lblitemUsr" runat="server" 
Text='<%#Eval("UserName") %>' />
                        </ItemTemplate>
                        <FooterTemplate>
                      <asp:TextBox ID="txtftrusrname" runat="server" />
                   <asp:RequiredFieldValidator ID="rfvusername"
 runat="server" ControlToValidate="txtftrusrname" 
  Text="*"  ValidationGroup="validaiton" />
                        </FooterTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="City">
                        <EditItemTemplate>
                        <asp:TextBox ID="txtcity" runat="server" 
                 Text='<%#Eval("City") %>' />
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label ID="lblcity" runat="server" 
                  Text='<%#Eval("City") %>' />
                        </ItemTemplate>
                        <FooterTemplate>
                            <asp:TextBox ID="txtftrcity" runat="server" />
                            <asp:RequiredFieldValidator ID="rfvcity"
                     runat="server" ControlToValidate="txtftrcity"
                  Text="*" ValidationGroup="validaiton" />
                        </FooterTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Designation">
                        <EditItemTemplate>
                            <asp:TextBox ID="txtDesg" runat="server" 
       Text='<%#Eval("Designation") %>' />
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label ID="lblDesg" runat="server" 
           Text='<%#Eval("Designation") %>' />
                        </ItemTemplate>
                        <FooterTemplate>
                            <asp:TextBox ID="txtftrDesignation" 
                   runat="server" />
                            <asp:RequiredFieldValidator ID="rfvdesignation"
             runat="server" ControlToValidate="txtftrDesignation"
          Text="*" ValidationGroup="validaiton" />
                        </FooterTemplate>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>
        </div>
        <div>
            <asp:Label ID="lblresult" runat="server"></asp:Label>
        </div>
    </form>
</body>
</html>


After creating design switch to code behind (.CS) Page and write following code

.CS Page

SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=EmpDetail;Integrated Security=True");
protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindEmployeeDetails();
        }
    }
    protected void BindEmployeeDetails()
    {
        con.Open();
        SqlCommand cmd = new SqlCommand("Select * from EmpDetail", con);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        con.Close();
        if (ds.Tables[0].Rows.Count > 0)
        {
            gvDetails.DataSource = ds;
            gvDetails.DataBind();
        }
        else
        {
            ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
            gvDetails.DataSource = ds;
            gvDetails.DataBind();
            int columncount = gvDetails.Rows[0].Cells.Count;
            gvDetails.Rows[0].Cells.Clear();
            gvDetails.Rows[0].Cells.Add(new TableCell());
            gvDetails.Rows[0].Cells[0].ColumnSpan = columncount;
            gvDetails.Rows[0].Cells[0].Text = "No Records Found";
        }
    }
    protected void gvDetails_RowEditing(object sender, GridViewEditEventArgs e)
    {
        gvDetails.EditIndex = e.NewEditIndex;
        BindEmployeeDetails();
    }
    protected void gvDetails_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        int userid = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Value.ToString());
        string username = gvDetails.DataKeys[e.RowIndex].Values["UserName"].ToString();
        TextBox txtcity = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtcity");
        TextBox txtDesignation = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtDesg");
        con.Open();
        SqlCommand cmd = new SqlCommand("update EmpDetail set City='" + txtcity.Text + "',Designation='" + txtDesignation.Text + "' where UserId=" + userid, con);
        cmd.ExecuteNonQuery();
        con.Close();
        lblresult.ForeColor = Color.Green;
        lblresult.Text = username + " Details Updated successfully";
        gvDetails.EditIndex = -1;
        BindEmployeeDetails();
    }
    protected void gvDetails_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        gvDetails.EditIndex = -1;
        BindEmployeeDetails();
    }
    protected void gvDetails_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        int userid = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Values["UserId"].ToString());
        string username = gvDetails.DataKeys[e.RowIndex].Values["UserName"].ToString();
        con.Open();
        SqlCommand cmd = new SqlCommand("delete from EmpDetail where UserId=" + userid, con);
        int result = cmd.ExecuteNonQuery();
        con.Close();
        if (result == 1)
        {
            BindEmployeeDetails();
            lblresult.ForeColor = Color.Red;
            lblresult.Text = username + " details deleted successfully";
        }
    }
    protected void gvDetails_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName.Equals("AddNew"))
        {
            TextBox txtUsrname = (TextBox)gvDetails.FooterRow.FindControl("txtftrusrname");
            TextBox txtCity = (TextBox)gvDetails.FooterRow.FindControl("txtftrcity");
            TextBox txtDesgnation = (TextBox)gvDetails.FooterRow.FindControl("txtftrDesignation");
            con.Open();
            SqlCommand cmd =
            new SqlCommand(
            "insert into EmpDetail(UserName,City,Designation) values('" + txtUsrname.Text + "','" +
            txtCity.Text + "','" + txtDesgnation.Text + "')", con);
            int result = cmd.ExecuteNonQuery();
            con.Close();
            if (result == 1)
            {
                BindEmployeeDetails();
                lblresult.ForeColor = Color.Green;
                lblresult.Text = txtUsrname.Text + " Details inserted successfully";
            }
            else
            {
                lblresult.ForeColor = Color.Red;
                lblresult.Text = txtUsrname.Text + " Details not inserted";
            }
        }
    }

Now Run your application and enjoy the code you have done
  • Share This:  
  •  Facebook
  •  Twitter
  •  Google+
  •  Stumble
  •  Digg
Email ThisBlogThis!Share to TwitterShare to Facebook
Newer Post Older Post Home

0 comments:

Post a Comment

Facebook Updates

Guruji Point

Categories

comma seperated string in sql Comman table Expression in SQL Dynamic Grid Row IQueryable in c# Learn AngularJS row_number() and dense_rank() salary table structure. Scope_Identity() Use of indexes while loop in sql why do we need cursor why tuple used

About Us

This blog is about the Programming ,Tech News and some intresting facts related Contents. Here you can find fundamental and expert level topic of programming languages very practically related to C# , Asp.Net ,Sql-Server, JavaScript, Jquery, WebServices And also Web-Api, WCF ,WPF, Angular Js.

Contact Us

Email Us - gurujipoints@gmail.com
Contact- 8077657477

Reach Us

Copyright © Guruji Point - Code You Want To Write