Insert_Update_delete_Using_Grid_View_Using_One_Procedure_Connection_in__Web.Config

.................................DataBase........................................

create database ranjeet_db
...................................................

drop table employee
---------------------------------------------------
create table employee
(
id int primary key,
FirstName varchar(50),
MName varchar(50),
LastName varchar(50)

)

------------------------------------------------------
select * from employee


------------------------------------------------------
 -----------Procedure---------------------------------
------------------------------------------------------

 create Procedure EmpEntry
(
  --variable  declareations
 @Action Varchar (10),    --to perform operation according to string ed to this varible such as Insert,update,delete,select     
 @id int=null,    --id to perform specific task
 @Fname Varchar (50)=null,   -- for FirstName
 @MName Varchar (50)=null,   -- for MName
 @Lname Varchar (50)=null    -- for LastName
)
as
Begin
  SET NOCOUNT ON;

If @Action='Insert'   --used to insert records
Begin
   Insert Into employee (FirstName,MName,LastName)values(@Fname,@MName,@Lname)
End


else if @Action='Select'   --used to Select records
Begin
    select *from employee
end


else if @Action='Update'  --used to update records
Begin
   update employee set FirstName=@Fname,MName=@MName,LastName=@Lname where id=@id
 End


 Else If @Action='delete'  --used to delete records
 Begin
   delete from employee where id=@id
 end





 End

------------------------------------------------------------------------------------------------

....................................................Default.aspx.....................................................

<%@ Page Title="" Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">


    <h4>This Application is Created by Ranjeet Kumar</h4>
     <table>
    <tr>
    <td>First Name</td>
<td>
   <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>   
  
        </td>   
    </tr>
   
     <tr>
    <td>Middle Name</td>

<td>
   <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>        
 
          </td>
         </tr>

      <tr>  
    <td>Last Name</td>
<td>
   <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>        
 
          </td>   
    </tr>
    <tr>
    <td>
        <asp:Button ID="Button1" runat="server" Text="Save" onclick="empsave" />
    </td>
    </tr>
    </table>
    <asp:label ID="Label1" runat="server" text="Label"></asp:label>
   
   
    <asp:HiddenField ID="HiddenField1" runat="server" />
   
   
    <asp:GridView ID="GridView1" runat="server"  DataKeyNames="id" OnRowEditing="edit"
        OnRowCancelingEdit="canceledit"
        OnRowDeleting="delete"
        OnRowUpdating="update"
       
         CellPadding="4" ForeColor="#333333"
        GridLines="None" OnSelectedIndexChanged="GridView1_SelectedIndexChanged" DataSourceID="SqlDataSource1">
        <AlternatingRowStyle BackColor="White" />
        <Columns>
            <asp:CommandField ShowEditButton="True" />
            <asp:CommandField ShowDeleteButton="True" />
        </Columns>
        <EditRowStyle BackColor="#2461BF" />
        <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
        <RowStyle BackColor="#EFF3FB" />
        <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
        <SortedAscendingCellStyle BackColor="#F5F7FB" />
        <SortedAscendingHeaderStyle BackColor="#6D95E1" />
        <SortedDescendingCellStyle BackColor="#E9EBEF" />
        <SortedDescendingHeaderStyle BackColor="#4870BE" />
    </asp:GridView>
   
   
 </asp:Content>

.............................................................Web.config For DB Connection...............................

<?xml version="1.0"?>
<!--
  For more information on how to configure your ASP.NET application, please visit
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->
<configuration>
  <system.web>
    <compilation debug="true" targetFramework="4.5"/>
    <httpRuntime targetFramework="4.5"/>
  </system.web>

  <connectionStrings>
    <add name="RK_CON" connectionString ="Data Source=RANJEETKUMAR-PC\SQLEXPRESS;Initial Catalog=ranjeet_db;User Id=sa;Password=ranjeet"
         providerName ="System.Data.SqlClient"/>

  </connectionStrings>
 
 
 
</configuration>

..................................................(c#)Default.aspx.cs............................................................

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data.SqlClient;//import it sql connection
using System.Configuration;// import it web.config connection
using System.Data;//import it for CommandType


public partial class _Default : System.Web.UI.Page
{

    string query;
    SqlConnection con;
    SqlCommand com;

    public void connection()
    {

      string  constr = ConfigurationManager.ConnectionStrings["RK_CON"].ToString();

      
        con = new SqlConnection(constr);
        con.Open();
    }



    protected void Page_Load(object sender, EventArgs e)
    {

        if (IsPostBack == false)
        {
           gedata();


        }
        Label1.Visible = false;
    }
    protected void empsave(object sender, EventArgs e)
    {
        connection();
        HiddenField1.Value = "Insert";
        query = "EmpEntry";
        com = new SqlCommand(query, con);
        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.AddWithValue("@Action", HiddenField1.Value).ToString();
        com.Parameters.AddWithValue("@FName", TextBox1.Text).ToString();
        com.Parameters.AddWithValue("@MName", TextBox2.Text).ToString();
        com.Parameters.AddWithValue("@LName", TextBox3.Text).ToString();

        int result = com.ExecuteNonQuery();
     
        con.Close();
      
        if (result >= 1)
        {
            Label1.Text = "Records Are Added";

        }

    }

    public void gedata()
    {
        connection();
        HiddenField1.Value = "view";
        query = "EmpEntry";
        com = new SqlCommand(query, con);
        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.AddWithValue("@Action", HiddenField1.Value).ToString();
        SqlDataAdapter da = new SqlDataAdapter(com);
        DataSet ds = new DataSet();
        da.Fill(ds);
       
      //GridView1.DataSource = ds;

   
  
   // GridView1.DataBind();
        con.Close();

    }




    protected void edit(object sender, GridViewEditEventArgs e)
    {

        GridView1.EditIndex = e.NewEditIndex;
        gedata();
    }
    protected void canceledit(object sender, GridViewCancelEditEventArgs e)
    {
        GridView1.EditIndex = -1;
        gedata();
    }
    protected void update(object sender, GridViewUpdateEventArgs e)
    {
        connection();

        int id = int.Parse(GridView1.DataKeys[e.RowIndex].Value.ToString());//here taking gridview id
      
        HiddenField1.Value = "update";
        query = "EmpEntry";
        com = new SqlCommand(query, con);
        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.AddWithValue("@Action", HiddenField1.Value).ToString();
        com.Parameters.AddWithValue("@FName", ((TextBox)GridView1.Rows[e.RowIndex].Cells[3].Controls[0]).Text.ToString());
        com.Parameters.AddWithValue("@MName", ((TextBox)GridView1.Rows[e.RowIndex].Cells[4].Controls[0]).Text.ToString());
        com.Parameters.AddWithValue("@LName", ((TextBox)GridView1.Rows[e.RowIndex].Cells[5].Controls[0]).Text.ToString());
        com.Parameters.AddWithValue("@id", SqlDbType.Int).Value = id;
        com.ExecuteNonQuery();
        con.Close();
        GridView1.EditIndex = -1;
        gedata();
    }
    protected void delete(object sender, GridViewDeleteEventArgs e)
    {
        connection();
      
        int id = int.Parse(GridView1.DataKeys[e.RowIndex].Value.ToString());//here taking gridview id
      
        HiddenField1.Value = "Delete";
        query = "EmpEntry";
        com = new SqlCommand(query, con);
        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.AddWithValue("@Action", HiddenField1.Value).ToString();
        com.Parameters.AddWithValue("id", SqlDbType.Int).Value = id;
        com.ExecuteNonQuery();
        con.Close();
        gedata();
    }
    protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
    {

    }
}