Resources » Code Snippets » ASP.NET GridView

How to use stored procedure for insert, update and delete using grid view control?


Posted Date: 23-Dec-2012  Last Updated:   Category: ASP.NET GridView    
Author: Member Level: Diamond    Points: 75


In this article I am going to explain you in detail all grid view operation like Insert, update, delete and select using stored procedures using ASP.net.



Description

Most frequently we are use gridview control to display data from the database but some time we are struggle to manipulate data operation through gridview using stored procedure. For this reason I am write here one simple article to explain all operation using stored procedure.

Initially I have create one table in SQL SERVER using below query

--create table
create table emp(eno int,empname varchar(50),sal bigint)

Then I have created four separate stored procedures to manipulate data in that table.

--insert procedure
create procedure insert_emp
@eno int,
@empname varchar(50),
@sal varchar(50)
AS
Begin
insert into emp(eno,empname,sal) values(@eno,@empname,@sal)
END
--update procedure
create procedure update_emp
@eno int,
@empname varchar(50),
@sal varchar(50)
AS
Begin
update emp set empname=@empname,sal=@sal where eno=@eno
END
--delete procedure
create procedure delete_emp
@eno int
AS
Begin
delete from emp where eno=@eno
END
--stored procedure for select
create procedure select_emp
AS
Begin
select * from emp
END


Select Record details using Stored Procedure

First step in the page load event I load that table data into the grid view using select_emp procedure like below

void LoadGrid()
{
sqlcmd = new SqlCommand("select_emp", sqlcon);
sqlcmd.CommandType = CommandType.StoredProcedure;
try
{
sqlcon.Open();
da = new SqlDataAdapter(sqlcmd);
dt.Clear();
da.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();

if (dt.Rows.Count == 0)
{
Panel1.Visible = true;
}
else
{
Panel1.Visible = false;
}
}
catch (Exception ex)
{

}
finally
{
sqlcon.Close();
}
}


How to insert new record using stored procedure in grid view?

Here I have insert new record details using grid view footer template. If user wants to add new record then he/ she use footer row to insert new data like below screen.

StoredProcedureInsertupdate

Here I am using insert_emp stored procedure to insert new record in the table.

Code for Insert


protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "Insert")
{
GridViewRow row = GridView1.FooterRow;
TextBox txteno = (TextBox)row.FindControl("txteno");
TextBox txtempname = (TextBox)row.FindControl("txtempname");
TextBox txtsal = (TextBox)row.FindControl("txtsal");
Insert(txteno.Text, txtempname.Text, txtsal.Text);
lblmsg.Text = "Record inserted successfully....";
//Load common method
LoadGrid();
}
}
void Insert(string eno, string empname, string sal)
{
try
{
sqlcon.Open();
sqlcmd = new SqlCommand("insert_emp", sqlcon);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.Add("@eno", SqlDbType.Int).Value = eno;
sqlcmd.Parameters.Add("@empname", SqlDbType.VarChar).Value = empname;
sqlcmd.Parameters.Add("@sal", SqlDbType.Int).Value = sal;
sqlcmd.ExecuteNonQuery();
}
catch (Exception ex)
{
}
finally
{
sqlcon.Close();
}
LoadGrid();
}


How to update data using stored procedure in grid view?

Here I am going to explain how to update using stored procedure. I am call here previously created update_emp stored procedure to update existing record in the table.

StoredProcedureInsertupdate

Code for update


protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
try
{

GridViewRow row = GridView1.Rows[e.RowIndex];
string eno;
eno = GridView1.DataKeys[e.RowIndex].Value.ToString();
TextBox empname = (TextBox)row.FindControl("txtEempname");
TextBox sal = (TextBox)row.FindControl("txtEsal");
sqlcon.Open();
//update_emp is stored procedure name
sqlcmd = new SqlCommand("update_emp", sqlcon);
sqlcmd.CommandType = CommandType.StoredProcedure;
//Assign each stored procedure parameter value to update
sqlcmd.Parameters.Add("@eno", SqlDbType.Int).Value = Convert.ToInt32(eno);
sqlcmd.Parameters.Add("@empname", SqlDbType.VarChar).Value = empname.Text;
sqlcmd.Parameters.Add("@sal", SqlDbType.BigInt).Value = Convert.ToInt32(sal.Text);
sqlcmd.ExecuteNonQuery();
sqlcon.Close();
GridView1.EditIndex = -1;
LoadGrid();
}
catch (Exception ex)
{
lblmsg.Text = ex.ToString();
}
}


How to delete data using stored procedure in grid view?

Here I am going to explain how to update using stored procedure. I am call here previously created update_emp stored procedure to update existing record in the table.

StoredProcedureInsertupdate

Code for delete


protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
try
{

GridViewRow row = GridView1.Rows[e.RowIndex];
string eno;
eno = GridView1.DataKeys[e.RowIndex].Value.ToString();
TextBox empname = (TextBox)row.FindControl("txtEempname");
TextBox sal = (TextBox)row.FindControl("txtEsal");
sqlcon.Open();
//update_emp is stored procedure name
sqlcmd = new SqlCommand("update_emp", sqlcon);
sqlcmd.CommandType = CommandType.StoredProcedure;
//Assign each stored procedure parameter value to update
sqlcmd.Parameters.Add("@eno", SqlDbType.Int).Value = Convert.ToInt32(eno);
sqlcmd.Parameters.Add("@empname", SqlDbType.VarChar).Value = empname.Text;
sqlcmd.Parameters.Add("@sal", SqlDbType.BigInt).Value = Convert.ToInt32(sal.Text);
sqlcmd.ExecuteNonQuery();
sqlcon.Close();
GridView1.EditIndex = -1;
LoadGrid();
}
catch (Exception ex)
{
lblmsg.Text = ex.ToString();
}
}


Full Source Code


Client Side


<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!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>Insert, Update, Delete and Select using stored procedure</title>
</head>
<body>
<form id="form1" runat="server">
<h3>
GridView Operations like Insert, Delete, Update and Select records using Stored Procedure</h3>
<asp:Label ID="lblmsg" runat="server"></asp:Label><br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" ShowFooter="true"
OnRowCommand="GridView1_RowCommand" OnRowCancelingEdit="GridView1_RowCancelingEdit"
OnRowDeleting="GridView1_RowDeleting" OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating"
DataKeyNames="eno">
<Columns>
<asp:TemplateField HeaderText="Employee No.">
<ItemTemplate>
<%#Eval("eno") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtEeno" runat="server" Enabled="false" Text=' <%#Eval("eno") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txteno" runat="server"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Name">
<ItemTemplate>
<%#Eval("empname") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtEempname" runat="server" Text=' <%#Eval("empname") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtempname" runat="server"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Salary">
<ItemTemplate>
<%#Eval("sal") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtEsal" runat="server" Text=' <%#Eval("sal") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtsal" runat="server"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Edit / Delete">
<ItemTemplate>

<asp:ImageButton ID="ImageButton1" runat="server" ImageUrl="~/img/edit.png" Width="25"
Height="25" CommandName="Edit" />
<asp:ImageButton ID="ImageButton2" runat="server" ImageUrl="~/img/delete.png" CommandName="Delete"
OnClientClick="return confirm('Are you sure want to delete record?')" />
</ItemTemplate>
<EditItemTemplate>
<asp:LinkButton ID="LinkButton1" runat="server" CommandName="Update">Update</asp:LinkButton>
<asp:LinkButton ID="LinkButton2" runat="server" CommandName="Cancel">Cancel</asp:LinkButton>
</EditItemTemplate>
<FooterTemplate>
<asp:ImageButton ID="ImageButton2" runat="server" ImageUrl="~/img/add.png" CommandName="Insert" />
</FooterTemplate>
<FooterStyle HorizontalAlign="Center" />
</asp:TemplateField>
</Columns>
</asp:GridView>
<br />
<asp:Panel ID="Panel1" runat="server">
Enter Eno
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br />
<br />
Enter Empname
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br />
<br />
Enter Salary
<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox><br />
<br />
<asp:Button ID="Button1" runat="server" Text="Add Record" OnClick="Button1_Click" />
</asp:Panel>
</form>
</body>
</html>


Code Behind


using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public partial class _Default : System.Web.UI.Page
{
//change connection string as per your details
SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ToString());
SqlCommand sqlcmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();

protected void Page_Load(object sender, EventArgs e)
{
lblmsg.Text = "";
if (!Page.IsPostBack)
{
GridView1.ShowFooter = true;
//Load grid data using common method
LoadGrid();
}
}
void LoadGrid()
{
sqlcmd = new SqlCommand("select_emp", sqlcon);
sqlcmd.CommandType = CommandType.StoredProcedure;
try
{
sqlcon.Open();
da = new SqlDataAdapter(sqlcmd);
dt.Clear();
da.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();

if (dt.Rows.Count == 0)
{
Panel1.Visible = true;
}
else
{
Panel1.Visible = false;
}
}
catch (Exception ex)
{

}
finally
{
sqlcon.Close();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
Insert(TextBox1.Text, TextBox2.Text, TextBox3.Text);
}
void Insert(string eno, string empname, string sal)
{
try
{
sqlcon.Open();
sqlcmd = new SqlCommand("insert_emp", sqlcon);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.Add("@eno", SqlDbType.Int).Value = eno;
sqlcmd.Parameters.Add("@empname", SqlDbType.VarChar).Value = empname;
sqlcmd.Parameters.Add("@sal", SqlDbType.Int).Value = sal;
sqlcmd.ExecuteNonQuery();
}
catch (Exception ex)
{
}
finally
{
sqlcon.Close();
}
LoadGrid();
}
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "Insert")
{
GridViewRow row = GridView1.FooterRow;
TextBox txteno = (TextBox)row.FindControl("txteno");
TextBox txtempname = (TextBox)row.FindControl("txtempname");
TextBox txtsal = (TextBox)row.FindControl("txtsal");
Insert(txteno.Text, txtempname.Text, txtsal.Text);
lblmsg.Text = "Record inserted successfully....";
//Load common method
LoadGrid();
}
}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
LoadGrid();
}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
LoadGrid();
}

protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
try
{
string eno;
eno = GridView1.DataKeys[e.RowIndex].Value.ToString();
sqlcmd = new SqlCommand("delete_emp", sqlcon);
sqlcon.Open();
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.Add("@eno", SqlDbType.Int).Value = Convert.ToInt32(eno);
sqlcmd.ExecuteNonQuery();
sqlcon.Close();
LoadGrid();
}
catch (Exception ex)
{
lblmsg.Text = ex.ToString();
}
}

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
try
{

GridViewRow row = GridView1.Rows[e.RowIndex];
string eno;
eno = GridView1.DataKeys[e.RowIndex].Value.ToString();
TextBox empname = (TextBox)row.FindControl("txtEempname");
TextBox sal = (TextBox)row.FindControl("txtEsal");
sqlcon.Open();
//update_emp is stored procedure name
sqlcmd = new SqlCommand("update_emp", sqlcon);
sqlcmd.CommandType = CommandType.StoredProcedure;
//Assign each stored procedure parameter value to update
sqlcmd.Parameters.Add("@eno", SqlDbType.Int).Value = Convert.ToInt32(eno);
sqlcmd.Parameters.Add("@empname", SqlDbType.VarChar).Value = empname.Text;
sqlcmd.Parameters.Add("@sal", SqlDbType.BigInt).Value = Convert.ToInt32(sal.Text);
sqlcmd.ExecuteNonQuery();
sqlcon.Close();
GridView1.EditIndex = -1;
LoadGrid();
}
catch (Exception ex)
{
lblmsg.Text = ex.ToString();
}
}

}


Source Code

Here I have attached full source code for the same download it and test it.

Client Side : ASP.NET
Code Behind : C#

Conclusion

I hope this article is help you to know about all gridview operations using stored procedure.


Did you like this resource? Share it with your friends and show your love!

Responses to "How to use stored procedure for insert, update and delete using grid view control?"
Guest Author: Vishal     17 Jan 2013
Hi
i have implement this code but problem is when i use the txtempname1 it accept null value so please explane me what is the problem
TextBox txtempname1 = row.Cells[1].FindControl("txtempname") as TextBox;



Guest Author: Indrajeet B     17 Jul 2013
Thanks! It worked and helped me a lot!


Feedbacks      

Post Comment:




  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)



    Type the numbers and letters shown on the left.


    Submit Article     Return to Article Index

    Subscribe to Subscribers
    Active Members
    TodayLast 7 Daysmore...

    Awards & Gifts
    Talk to Webmaster Tony John

    Online Members

    Hassan sittar
    More...
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India