INSERT UPDATE DELETE USING STORED PROCEDURE
ALTER procedure RegIns(
@id int,
@username varchar(25),
@Password varchar(35),
@Count int
)
as
begin
if @count=1
begin
update reg set Password=@Password ,username=@username where id=@id
end
else if @count=2
begin
insert into reg values(@username,@Password)
end
else if @count=3
begin
delete reg where id=@id
end
end
storedprocedure.aspx:
storedprocedure.aspx.cs
STORED PROCEDURE
ALTER procedure RegIns(
@id int,
@username varchar(25),
@Password varchar(35),
@Count int
)
as
begin
if @count=1
begin
update reg set Password=@Password ,username=@username where id=@id
end
else if @count=2
begin
insert into reg values(@username,@Password)
end
else if @count=3
begin
delete reg where id=@id
end
end
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="storedprocedure.aspx.cs" Inherits="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></title>
<style type="text/css">
.style2
{
width:
200px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<table style="border: solid 1px black; padding: 20px; position: relative; top: 50px;"
align="center">
<tr>
<td>
ID
:
</td>
<td
class="style2">
<asp:TextBox ID="TextBox1" runat="server" Width="200px"></asp:TextBox>
</td>
</tr>
<tr>
<td>
UserName :
</td>
<td class="style2">
<asp:TextBox ID="TextBox2" runat="server"
Width="200px"></asp:TextBox>
</td>
</tr>
<tr>
<td>
Password
:
</td>
<td class="style2">
<asp:TextBox ID="TextBox3" runat="server" TextMode="Password" Width="200px"></asp:TextBox>
</td>
</tr>
<tr>
<td>
</td>
<td valign="middle" class="style2">
<table>
<tr>
<td align="right">
<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="insert" />
</td>
<td align="middle">
<asp:Button ID="Button2"
runat="server"
onclick="Button2_Click"
Text="update"
/>
</td>
<td align="right">
<asp:Button ID="Button3"
runat="server"
onclick="Button3_Click"
Text="delete"
/>
</table>
</td>
</tr>
<tr>
<td colspan="2" align="center">
</tr>
<asp:Label ID="error" runat="server"></asp:Label>
<asp:Label ID="info" runat="server"></asp:Label>
</table>
</br>
</br>
</br>
</br>
</br>
</br>
<table style="border: solid 1px black; padding: 20px; position: relative; top: 50px; align="center">
<asp:GridView ID="g1" align="center" runat="server" AutoGenerateColumns="False"
DataKeyNames="id" BackColor="#CCCCCC" BorderColor="#999999"
BorderStyle="Solid" BorderWidth="3px" CellPadding="4" CellSpacing="2"
ForeColor="Black">
<Columns>
<asp:BoundField DataField="id" HeaderText="ID" />
<asp:BoundField DataField="name" HeaderText="USERNAME" />
<asp:BoundField DataField="pwd" HeaderText="PASSWORD" />
</Columns>
<FooterStyle BackColor="#CCCCCC" />
<HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#CCCCCC" ForeColor="Black" HorizontalAlign="Left" />
<RowStyle BackColor="White" />
<SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#F1F1F1" />
<SortedAscendingHeaderStyle BackColor="#808080" />
<SortedDescendingCellStyle BackColor="#CAC9C9" />
<SortedDescendingHeaderStyle BackColor="#383838" />
</asp:GridView>
</table>
</div>
</form>
</body>
</html>
using
System;
using
System.Collections.Generic;
using
System.Linq;
using
System.Web;
using
System.Web.UI;
using
System.Web.UI.WebControls;
using
System.Data.SqlClient;
using
System.Data;
public partial class storedprocedure : System.Web.UI.Page
{ SqlConnection
con = new SqlConnection("Data Source=COM3\\SQLEXPRESS;Initial
Catalog=dotnettasks;Integrated Security=True");
protected void Page_Load(object
sender, EventArgs e)
{
if
(!Page.IsPostBack)
{
griddata();
}
}protected void Button1_Click(object
sender, EventArgs e)
{
con.Open();
try
{
int
Type = 1;
SqlCommand
cmd = new SqlCommand("procname", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@id", TextBox1.Text);
cmd.Parameters.AddWithValue("@name", TextBox2.Text);
cmd.Parameters.AddWithValue("@pwd", TextBox3.Text);
cmd.Parameters.AddWithValue("@type", Type);
cmd.ExecuteNonQuery();
info.Text="Executed";
con.Close();
griddata();
}
catch (Exception e1)
{
error.Text="Not
inserted"+e1;
}
}
protected void
Button2_Click(object sender, EventArgs e)
{
con.Open();
try
{
int
Type = 2;
SqlCommand
cmd1 = new SqlCommand("procname", con);
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.AddWithValue("@id", TextBox1.Text);
cmd1.Parameters.AddWithValue("@name", TextBox2.Text);
cmd1.Parameters.AddWithValue("@pwd", TextBox3.Text);
cmd1.Parameters.AddWithValue("@type", Type);
cmd1.ExecuteNonQuery();
info.Text = "updated";
con.Close();
griddata();
}
catch (Exception e1)
{
error.Text = "Not updated" + e1;
}
}
protected void Button3_Click(object
sender, EventArgs e)
{
con.Open();
try
{
int
Type = 3;
SqlCommand
cmd2 = new SqlCommand("procname", con);
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.Parameters.AddWithValue("@id", TextBox1.Text);
cmd2.Parameters.AddWithValue("@name", TextBox2.Text);
cmd2.Parameters.AddWithValue("@pwd", TextBox3.Text);
cmd2.Parameters.AddWithValue("@type", Type);
cmd2.ExecuteNonQuery();
info.Text = "deleted";
con.Close();
griddata();
}
catch (Exception e1)
{
error.Text = "Not
deleted" + e1;
}
}
public void griddata()
{
SqlCommand
comm = new SqlCommand("select * from st", con);
con.Open();
SqlDataAdapter
da = new SqlDataAdapter(comm);
DataTable
dt = new DataTable();
dt.Clear();
da.Fill(dt);
if (dt.Rows.Count > 0)
{
g1.DataSource = dt;
g1.DataBind();
}
}
}
Download Source Code:storeprocedure