Insert, Update, Delete in Gridview using Single Stored Procedure

Ankitsrist
Posted by in ASP.NET category on for Beginner level | Points: 250 | Views : 103855 red flag

In this article lets see how to Insert, Update, Delete in Gridview using Single Stored Procedure


 Download source code for Insert, Update, Delete in Gridview using Single Stored Procedure

Introduction


In this article lets see how to Insert, Update, Delete in Gridview using a Single Stored Procedure
first create the data table like below



stored procedure

create PROCEDURE [dbo].[sample_dml_proc]
(
@id as int=0,
@name as varchar(50)='',
@age as int='',
@city as varchar(50)='',
@status as varchar(50)=''
)
AS
BEGIN
SET NOCOUNT ON;
if(@status='display')
begin
SELECT * from sample_dml
end
else if(@status='insert')
begin
insert into sample_dml(name, age, city) values(@name, @age, @city)
end
else if(@status='delete')
begin
delete from sample_dml where id=@id
end
else if(@status='update')
begin
update sample_dml set name=@name, age=@age, city=@city where id=@id
end
END

default.aspx

<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server" />
<div>
<table>
<tr>
<td style="width: 100px">
name</td>
<td style="width: 100px">
<asp:TextBox ID="txtname" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 100px">
age</td>
<td style="width: 100px">
<asp:TextBox ID="txtage" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 100px">
city</td>
<td style="width: 100px">
<asp:TextBox ID="txtcity" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 100px">
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="submit" /></td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td colspan="2">
<asp:GridView ID="GridView1" runat="server" DataKeyNames ="id" AutoGenerateDeleteButton="True" AutoGenerateSelectButton="True" OnRowDeleting="GridView1_RowDeleting" OnSelectedIndexChanged="GridView1_SelectedIndexChanged">
</asp:GridView>
</td>
</tr>
</table>
</div>
</form>
</body>

default.aspx.cs

using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
Class1 db = new Class1();
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
fillgrid();
}
}
public void fillgrid()
{
db.mycon();
SqlCommand cmd = new SqlCommand("sample_dml_proc", db.con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@status", SqlDbType.VarChar, 50).Value = "display";
SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adp.Fill(ds,"table1");
GridView1.DataSource = ds;
GridView1.DataBind();
}
protected void Button1_Click(object sender, EventArgs e)
{
if (Button1.Text == "submit")
{
db.mycon();
SqlCommand cmd = new SqlCommand("sample_dml_proc", db.con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@status", SqlDbType.VarChar).Value = "insert";
cmd.Parameters.AddWithValue("@name", SqlDbType.VarChar).Value = txtname.Text;
cmd.Parameters.AddWithValue("@age", SqlDbType.Int).Value = txtage.Text;
cmd.Parameters.AddWithValue("@city", SqlDbType.VarChar).Value = txtcity.Text;
SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adp.Fill(ds);
fillgrid();
}
if (Button1.Text == "update")
{
db.mycon();

SqlCommand cmd = new SqlCommand("sample_dml_proc", db.con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@status", SqlDbType.VarChar).Value = "update";
cmd.Parameters.AddWithValue("@id", GridView1.SelectedValue.ToString ());
cmd.Parameters.AddWithValue("@name", SqlDbType.VarChar).Value = txtname.Text;
cmd.Parameters.AddWithValue("@age", SqlDbType.Int).Value = txtage.Text;
cmd.Parameters.AddWithValue("@city", SqlDbType.VarChar).Value = txtcity.Text;
cmd.ExecuteNonQuery();
SqlDataAdapter adp1 = new SqlDataAdapter(cmd);
DataSet ds1 = new DataSet();
adp1.Fill(ds1);
fillgrid();

}

}

protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
db.mycon();
int id = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values["id"].ToString());
SqlCommand cmd = new SqlCommand("sample_dml_proc", db.con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@status", SqlDbType.VarChar).Value = "delete";
cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
cmd.ExecuteNonQuery();
fillgrid();

}
protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
{
db.mycon();
SqlCommand cmd = new SqlCommand("select * from sample_dml where id='"+GridView1.SelectedValue.ToString ()+"'", db .con);
SqlDataReader rd = cmd.ExecuteReader();
rd.Read();
if (rd.HasRows)
{
txtname.Text = rd["name"].ToString();
txtage.Text = rd["age"].ToString();
txtcity.Text = rd["city"].ToString();
Button1.Text = "update";
}
else
{
txtname.Text = "";
txtage.Text = "";
txtcity.Text = "";

}
}
}

Conclusion


In this you can save lots of time instead of writing individual stored procedure for each operations,
please find the attached sample description

Regards
Ankit Saxena

Page copy protected against web site content infringement by Copyscape

About the Author

Ankitsrist
Full Name: ankit saxena
Member Level: Starter
Member Status: Member
Member Since: 11/16/2012 11:33:51 PM
Country: India

http://www.dotnetfunda.com
hello, i have done engineering from I.T. branch, recently got placed in endroit technologies mumbai as a trainee engineer its my first company ...i was java developer but during job my interest towards asp.net has emerged :)

Login to vote for this post.

Comments or Responses

Posted by: Jayakumars on: 4/9/2013 | Points: 25
hi

ankrist Good one
Posted by: Ankitsrist on: 4/9/2013 | Points: 25
thanks jaya
Posted by: Pravesh Singh on: 6/1/2013 | Points: 25
This is awesome!! really helpful for me. Thanks for sharing with us. Following links also helped me to complete my task.

http://www.mindstick.com/Articles/f50bdd96-7941-495c-aa53-169d9711a096/?Select%20Insert%20Update%20and%20Delete%20using%20Stored%20Procedure%20in%20ASP%20NET%20MVC4

http://www.c-sharpcorner.com/UploadFile/krishnasarala/select-insert-update-and-delete-with-Asp-Net-mvc/
Posted by: Ankitsrist on: 6/2/2013 | Points: 25
thanks you so much pravesh..
Posted by: Suwarna on: 8/7/2013 | Points: 25
Nice Article
Posted by: Ankitsrist on: 8/11/2013 | Points: 25
thanks suwarna

Login to post response

Comment using Facebook(Author doesn't get notification)