.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Code Snippets >> Visual Studio >> Post New Resource Bookmark and Share   

 Subscribe to Code Snippets

Insert Edit Update in Gridview using Single Stored Procedure

Posted By:satyapriyanayak       Posted Date: October 31, 2013    Points: 40    Category: Visual Studio    URL: http://www.dotnetspark.com  

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

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)=''
)
AS
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 lets 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">

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



     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More code samples in C#, ASP.Net, Vb.Net and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend