Insert Edit Update in Gridview Using Single Stored Procedure

Satyapriya Nayak
12y
231.6k
0
1

Article

Introduction

In this article we will learn how to insert, edit, update and cancel the records in a GridView using a single stored procedure. Here we use a Template Field inside a grid view control. Inside the Template Field we place an Item Template for displaying records from the database at runtime. Other templates used here are EditItemTemplate and Footer Template. Here, paging facilities are also provided.

Table Structure

Stored Procedure

CREATE PROCEDURE [dbo].[employee_pro]

(

@empid as int=0,

@name as varchar(50)='',

@address as varchar(50)='',

@designation as varchar(50)='',

@status as varchar(50)=''

)

BEGIN

SET NOCOUNT ON;

if(@status = 'Display')

begin

Select * from employee

end

else if(@status = 'Add')

begin

Insert into employee(name,address,designation,createtime) values (@name,@address,@designation,getdate())

end

else if(@status = 'Update')

begin

Update employee set name=@name, address=@address,designation=@designation where empid=@empid

end

else if(@status = 'Delete')

begin

Delete from employee where empid=@empid

end

END

Now let's move to the coding part.

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

<title>Untitled Page</title>

</head>

<body>

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

<asp:TemplateField HeaderText="Empid">

<asp:Label ID="lblempid" runat="server" Text='<%#Eval("empid") %>'></asp:Label>

</ItemTemplate>

<asp:Label ID="lblAdd" runat="server"></asp:Label>

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Name">

<asp:Label ID="lblname" runat="server" Text='<%#Eval("name") %>'></asp:Label>

</ItemTemplate>

<asp:TextBox ID="txtname" runat="server" Text='<%#Eval("name") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBox ID="txtAddname" runat="server"></asp:TextBox>

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Address">

<asp:Label ID="lbladdress" runat="server" Text='<%#Eval("address") %>'></asp:Label>

</ItemTemplate>

<asp:TextBox ID="txtaddress" runat="server" Text='<%#Eval("address") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBox ID="txtAddaddress" runat="server"></asp:TextBox>

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Designation">

<asp:Label ID="lblesignation" runat="server" Text='<%#Eval("designation") %>'></asp:Label>

</ItemTemplate>

<asp:TextBox ID="txtdesignation" runat="server" Text='<%#Eval("designation") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBox ID="txtAdddesignation" runat="server"></asp:TextBox>

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Create Time">

<asp:Label ID="lblcreatetime" runat="server" Text='<%#Eval("createtime") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Edit">

<asp:LinkButton ID="btnEdit" Text="Edit" runat="server" CommandName="Edit" />

<br />

<asp:LinkButton ID="btnDelete" Text="Delete" runat="server" CommandName="Delete"/>

</span>

</ItemTemplate>

<asp:LinkButton ID="btnUpdate" Text="Update" runat="server" CommandName="Update" />

<br />

<asp:LinkButton ID="btnCancel" Text="Cancel" runat="server" CommandName="Cancel" />

</EditItemTemplate>

<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();

}

Output

Conclusion

So here we learned how to insert, edit, update and cancel the records in a grid view using a single stored procedure.

Up Next

Ebook Download

View all

Diving Into ASP.NET WebAPI

Read by 6.2k people

Download Now!

Learn

View all