StoredProcedure.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="StoredProcedure.aspx.cs" Inherits="Insert_update_delete_Stored_Pro.StoredProcedure" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" AllowPaging="true" ShowFooter="true"
PageSize="5" AutoGenerateColumns="false"
onpageindexchanging="GridView1_PageIndexChanging"
onrowcancelingedit="GridView1_RowCancelingEdit"
onrowcommand="GridView1_RowCommand" onrowdeleting="GridView1_RowDeleting"
onrowediting="GridView1_RowEditing" onrowupdating="GridView1_RowUpdating" HeaderStyle-BackColor="Red"
HeaderStyle-ForeColor="White" BackColor="#FFCC66">
<AlternatingRowStyle BackColor="#FFFFCC" />
<Columns>
<asp:TemplateField HeaderText="Empid">
<ItemTemplate>
<asp:Label ID="lblempid" runat="server" Text='<%#Eval("empid") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="lblAdd" runat="server"></asp:Label>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Name">
<ItemTemplate>
<asp:Label ID="lblname" runat="server" Text='<%#Eval("name") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtname" runat="server" Text='<%#Eval("name") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtAddname" runat="server"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Address">
<ItemTemplate>
<asp:Label ID="lbladdress" runat="server" Text='<%#Eval("address") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtaddress" runat="server" Text='<%#Eval("address") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtAddaddress" runat="server"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Designation">
<ItemTemplate>
<asp:Label ID="lblesignation" runat="server" Text='<%#Eval("designation") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtdesignation" runat="server" Text='<%#Eval("designation") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtAdddesignation" runat="server"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Create Time">
<ItemTemplate>
<asp:Label ID="lblcreatetime" runat="server" Text='<%#Eval("createtime") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Edit">
<ItemTemplate>
<asp:LinkButton ID="btnEdit" Text="Edit" runat="server" CommandName="Edit" />
<br />
<span onclick="return confirm('Are you sure you want to delete this record?')">
<asp:LinkButton ID="btnDelete" Text="Delete" runat="server" CommandName="Delete"/>
</span>
</ItemTemplate>
<EditItemTemplate>
<asp:LinkButton ID="btnUpdate" Text="Update" runat="server" CommandName="Update" />
<br />
<asp:LinkButton ID="btnCancel" Text="Cancel" runat="server" CommandName="Cancel" />
</EditItemTemplate>
<FooterTemplate>
<asp:Button ID="btnAddRecord" runat="server" Text="Add" CommandName="Add"></asp:Button>
</FooterTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
StoredProcedure.aspx.cs
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
namespace Insert_update_delete_Stored_Pro
{
public partial class StoredProcedure : System.Web.UI.Page
{
string strConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlCommand com;
SqlDataAdapter sqlda;
DataSet ds;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGrid();
}
}
protected void BindGrid()
{
SqlConnection con = new SqlConnection(strConnString);
com = new SqlCommand();
con.Open();
com.Connection = con;
com.CommandText = "employee_pro";
com.CommandType = CommandType.StoredProcedure;
com.Parameters.Add(new SqlParameter("@status", SqlDbType.VarChar, 50));
com.Parameters["@status"].Value = "Display";
sqlda = new SqlDataAdapter(com);
ds = new DataSet();
sqlda.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
con.Close();
}
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
BindGrid();
}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
BindGrid();
}
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals("Add"))
{
TextBox txtname = (TextBox)GridView1.FooterRow.FindControl("txtAddname");
TextBox txtaddress = (TextBox)GridView1.FooterRow.FindControl("txtAddaddress");
TextBox txtdesignation = (TextBox)GridView1.FooterRow.FindControl("txtAdddesignation");
string name, address, designation;
name = txtname.Text;
address = txtaddress.Text;
designation = txtdesignation.Text;
Addemployee(name, address, designation);
GridView1.EditIndex = -1;
BindGrid();
}
}
protected void Addemployee(string name, string address, string designation)
{
SqlConnection con = new SqlConnection(strConnString);
con.Open();
com = new SqlCommand();
com.CommandText = "employee_pro";
com.CommandType = CommandType.StoredProcedure;
com.Connection = con;
com.Parameters.Add(new SqlParameter("@status", SqlDbType.VarChar, 50));
com.Parameters.Add(new SqlParameter("@name", SqlDbType.VarChar, 50));
com.Parameters.Add(new SqlParameter("@address", SqlDbType.VarChar, 50));
com.Parameters.Add(new SqlParameter("@designation", SqlDbType.VarChar, 50));
com.Parameters["@status"].Value = "Add";
com.Parameters["@name"].Value = name;
com.Parameters["@address"].Value = address;
com.Parameters["@designation"].Value = designation;
sqlda = new SqlDataAdapter(com);
ds = new DataSet();
sqlda.Fill(ds);
con.Close();
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
Label empid = (Label)GridView1.Rows[e.RowIndex].FindControl("lblempid");
string eid = empid.Text;
Deleteemployee(eid);
GridView1.EditIndex = -1;
BindGrid();
}
protected void Deleteemployee(string empid)
{
SqlConnection con = new SqlConnection(strConnString);
com = new SqlCommand();
com.CommandText = "employee_pro";
com.CommandType = CommandType.StoredProcedure;
com.Connection = con;
com.Parameters.Add(new SqlParameter("@status", SqlDbType.VarChar, 50));
com.Parameters.Add(new SqlParameter("@empid", SqlDbType.Int));
com.Parameters["@status"].Value = "Delete";
com.Parameters["@empid"].Value = empid;
sqlda = new SqlDataAdapter(com);
ds = new DataSet();
sqlda.Fill(ds);
con.Close();
}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
BindGrid();
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
Label empid = (Label)GridView1.Rows[e.RowIndex].FindControl("lblempid");
TextBox name = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtname");
TextBox address = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtaddress");
TextBox designation = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtdesignation");
string eid = empid.Text;
string ename = name.Text;
string eaddress = address.Text;
string edesignation = designation.Text;
Updateemployee(eid, ename, eaddress, edesignation);
GridView1.EditIndex = -1;
BindGrid();
}
protected void Updateemployee(string empid, string name, string address, string designation)
{
SqlConnection con = new SqlConnection(strConnString);
com = new SqlCommand();
con.Open();
com.Connection = con;
com.CommandText = "employee_pro";
com.CommandType = CommandType.StoredProcedure;
com.Parameters.Add(new SqlParameter("@empid", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@status", SqlDbType.VarChar, 50));
com.Parameters.Add(new SqlParameter("@name", SqlDbType.VarChar, 50));
com.Parameters.Add(new SqlParameter("@address", SqlDbType.VarChar, 50));
com.Parameters.Add(new SqlParameter("@designation", SqlDbType.VarChar, 50));
com.Parameters["@empid"].Value = Convert.ToInt32(empid.ToString());
com.Parameters["@status"].Value = "Update";
com.Parameters["@name"].Value = name;
com.Parameters["@address"].Value = address;
com.Parameters["@designation"].Value = designation;
sqlda = new SqlDataAdapter(com);
ds = new DataSet();
sqlda.Fill(ds);
con.Close();
}
}
}