In this article lets see how to 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