Description:-
In this
example we explain that How perform Insert,update,Delete in Gridview Using
Stored Procedure.By using stored procedure the execution of the SQL Query make
fast and also reduce the code of the aspx.cs page. So we can say that stored procedure in the sql server is very important and also very
helpful.before we discuss anything we will understand the what is
procedure.
What is Procedure:-
Procedure
is a Set of SQL Statement and that in stored in SqlServer.stored procedure is a
precompiled code and that is Execute only once and reduce the network traffic.procedure
can be Defined only once and we can use it in multiple time.
Types of Procedure:-
1.
System Defined Stored Procedure:-
This procedure is already
defined in sqlserver and this are physically stored in Hidden sqlserver. The prefix
of this procedure is SP.
2.
UserDefined Storeprocedure:-
This procedure are created by user and it
can store in whole Database.
3.
Extended Procedure:-
Extended procedure provide various
program to maintain the Resource or activity.
4.
CLR Defined StoredProcedure:-
CLR stored
procedure are special type of procedure that are based on the CLR (Common
Language Runtime) available in .net framework.
Implement Remember Me functionality using CheckBox ASP.Net set WaterMark Text in PDF using itextsharp in C#
How to set Default Button in MVC Web Form Application
How to Bind XML File data to Treeview in asp.net
JQuery datepicker calender with Dropdown month and year in asp.net.
ALTER PROCEDURE dbo.procsel
AS
begin
select * from
hk_proc
end
ALTER PROCEDURE dbo.procins
(
@name varchar(50),
@stream varchar(50),
@gender varchar(50),
@hobby varchar(100),
@photo varchar(200)
)
AS
begin
insert into hk_proc
values(@name,@stream,@gender,@hobby,@photo)
end
Procedure for Update Statement:-
ALTER PROCEDURE dbo.procupd
(
@id int,
@name varchar(50),
@stream varchar(50),
@gender varchar(50),
@hobby varchar(100),
@photo varchar(200)
)
AS
begin
update hk_proc set
name=@name,stream=@stream,gender=@gender,hobby=@hobby,photo=@photo
where id=@id
end
Procedure for Delete Statement:-
ALTER PROCEDURE dbo.procdel
(
@id int
)
AS
begin
delete from hk_proc
where id=@id
end
storeprocgrid.aspx:-
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="storprocgrid.aspx.cs" Inherits="storprocgrid" %>
<!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 id="Head1" runat="server">
<script language="javascript">
function
changeImg(valueTemp) {
var a
= document.getElementById("<%=
img.ClientID %>");
a.src = valueTemp.value;
}
function
changeIm() {
var a
= document.getElementById("<%=
img.ClientID %>");
$(document).ready(function()
{
$("<%=
img.ClientID %>").animate({
left: '250px',
opacity: '0.5',
height: '150px',
width: '150px'
});
});
}
</script>
<title></title>
</head>
<body onmousemove="changeIm()">
<form id="form1" runat="server">
<div>
<asp:Panel ID="p" runat="server" BorderColor="#FFFF99" BorderStyle="Solid"
GroupingText="Registration" ScrollBars="Auto"
>
<center>
<label for="password">Name</label> <asp:TextBox ID="txtcnm" runat="server" CssClass="txtfield"> </asp:TextBox>
<asp:RequiredFieldValidator ID="rfvFirstName"
runat="server"
ControlToValidate="txtcnm"
ErrorMessage="company
Name can't be left blank"
Display="Dynamic"> </asp:RequiredFieldValidator>
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<label for="email">Stream</label>
<asp:DropDownList ID="DropDownList1" runat="server"
AutoPostBack="True" CssClass="txtfield"
>
</asp:DropDownList>
<br />
<table class="txtfield" >
<tr>
<td rowspan="2"><b>Gender</b></td>
<td> <asp:RadioButton ID="RadioButton1" runat="server" AutoPostBack="True"
GroupName="abc" Text="male" />
<br />
<asp:RadioButton ID="RadioButton2" runat="server" GroupName="abc"
AutoPostBack="True" Text="female" />
</td>
</tr>
</table>
<br />
<table class="txtfield" >
<tr>
<td rowspan="2"><b>Hobby</b></td>
<td>
<asp:CheckBox ID="CheckBox1" runat="server" AutoPostBack="true" Text="Cricket" />
<br />
<asp:CheckBox ID="CheckBox2" runat="server" AutoPostBack="true" Text="Football" />
<br />
<asp:CheckBox ID="CheckBox3" runat="server" AutoPostBack="true" Text="Tennis" />
<br />
<asp:CheckBox ID="CheckBox4" runat="server" AutoPostBack="true" Text="Other" />
</td>
</tr>
</table>
</ContentTemplate>
</asp:UpdatePanel>
<label for="email">choose
photo</label>
<asp:FileUpload ID="FileUpload1" runat="server" onchange="changeImg(this)" CssClass="btn"/>
<asp:Image ID="img" runat="server" style="position:absolute; left:650px;" Width="100" Height="50" />
<br /> <asp:Button id="Button1"
Text="Register"
CommandName="register"
CommandArgument="register"
OnCommand="Button1_Click1" CssClass="btn"
runat="server"/>
<asp:Button id="Button3"
Text="Update"
CommandName="update"
CommandArgument="update"
OnCommand="Button1_Click1" CssClass="btn" Visible="false"
runat="server"/>
<asp:GridView ID="GridView1" runat="server"
Font-Size="Large"
ForeColor="#333333" ShowFooter="True" GridLines="None"
AutoGenerateColumns="False" DataKeyNames="id"
onrowdeleting="GridView1_RowDeleting" onrowediting="GridView1_RowEditing"
>
<FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
<PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:TemplateField HeaderText="ID" Visible="false">
<ItemTemplate>
<asp:Label ID="Label10" runat="server" Text='<%#
Eval("id") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%#
Eval("id") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="name">
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%#
Eval("name") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="TextBox2" runat="server" Text='<%#
Eval("name") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="stream">
<ItemTemplate>
<asp:Label ID="Label6" runat="server" Text='<%#
Eval("stream") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Gender">
<ItemTemplate>
<asp:Label ID="Label8" runat="server" Text='<%#
Eval("gender") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Hobby">
<ItemTemplate>
<asp:Label ID="Label9" runat="server" Text='<%#
Eval("hobby") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Image">
<ItemTemplate>
<asp:Image ID="Image1" runat="server" ImageUrl='<%#
Eval("photo") %>' Width="80" Height="80" />
<asp:Label ID="i1" runat="server" Visible="false" Text='<%#
Eval("photo") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:LinkButton ID="LinkButton2" runat="server" CommandName="Delete" CausesValidation="False">Delete</asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:LinkButton ID="LinkButton1" runat="server" CommandName="Edit" CausesValidation="False">Edit</asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</center>
</asp:Panel>
</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;
using
System.IO;
public partial class storprocgrid :
System.Web.UI.Page
{
DataTable
dt;
DataTable
ds;
string no
= null;
string
connStr = @"Data Source=SQLDB;Initial
Catalog=Demo;User ID=Demoh;Password=Demo1@";
protected void
Page_Load(object sender, EventArgs e)
{
if
(!Page.IsPostBack)
{
grid();
DropDownList1.Items.Add("BCA");
DropDownList1.Items.Add("MCA");
DropDownList1.Items.Add("BBA");
DropDownList1.Items.Add("MBA");
}
}
public string
chk()
{
string
ch=null;
if
(CheckBox1.Checked)
ch += CheckBox1.Text+",";
if
(CheckBox2.Checked)
ch += CheckBox2.Text + ",";
if
(CheckBox3.Checked)
ch += CheckBox3.Text + ",";
if
(CheckBox4.Checked)
ch += CheckBox4.Text + ",";
if
(ch == null)
ch = "Other,";
return
ch.TrimEnd(ch[ch.Length - 1]);
}
public string
rad()
{
string ch
= null;
if
(RadioButton1.Checked)
ch = RadioButton1.Text;
else
ch = RadioButton2.Text;
return
ch;
}
public void
clear()
{
txtcnm.Text = "";
RadioButton1.Checked = false;
RadioButton2.Checked = false;
CheckBox1.Checked = false;
CheckBox2.Checked = false;
CheckBox3.Checked = false;
CheckBox4.Checked = false;
}
public void
grid()
{
SqlConnection
conn = new SqlConnection(connStr);
SqlDataAdapter
dAd = new SqlDataAdapter("dbo.procsel",
conn);
dAd.SelectCommand.CommandType = CommandType.StoredProcedure;
DataSet
dSet = new DataSet();
try
{
dAd.Fill(dSet);
GridView1.DataSource = dSet;
GridView1.DataBind();
}
catch
{
throw;
}
finally
{
dSet.Dispose();
dAd.Dispose();
conn.Close();
conn.Dispose();
}
}
protected void
Button1_Click1(object sender, CommandEventArgs e)
{
if
(e.CommandName == "register")
{
if
(FileUpload1.HasFile)
{
string
fileExtension = Path.GetExtension(FileUpload1.FileName.ToString());
fileExtension.ToLower();
if
(fileExtension != ".gif"
&& fileExtension != ".jpg"
&& fileExtension != ".jpeg"
&& fileExtension != ".png")
{
ClientScript.RegisterClientScriptBlock(this.GetType(),
"ke", "<script>alert('only
jpeg,jpg,png,gif photo is allowed');</script>");
return;
}
string
serverPath = Server.MapPath(@"~/" +
FileUpload1.FileName);
FileUpload1.SaveAs(serverPath);
}
else
{
ClientScript.RegisterClientScriptBlock(this.GetType(),
"ke", "<script>alert('plz
browse the image');</script>");
FileUpload1.Focus();
return;
}
SqlConnection
conn = new SqlConnection(connStr);
conn.Open();
SqlCommand
dCmd = new SqlCommand("dbo.procins",
conn);
dCmd.CommandType = CommandType.StoredProcedure;
try
{
string
gender,hobby;
gender=rad();
hobby=chk();
dCmd.Parameters.AddWithValue("@name",txtcnm.Text);
dCmd.Parameters.AddWithValue("@stream",DropDownList1.SelectedValue);
dCmd.Parameters.AddWithValue("@gender",
gender);
dCmd.Parameters.AddWithValue("@hobby",
hobby);
dCmd.Parameters.AddWithValue("@photo",FileUpload1.FileName);
int j=
dCmd.ExecuteNonQuery();
if (j
> 0)
{
ClientScript.RegisterClientScriptBlock(this.GetType(),
"key", "<script>
alert('record added successfully');</script>");
grid();
clear();
}
}
catch
{
throw;
}
finally
{
dCmd.Dispose();
conn.Close();
conn.Dispose();
}
}
if
(e.CommandName == "update")
{
if
(FileUpload1.HasFile)
{
string
fileExtension = Path.GetExtension(FileUpload1.FileName.ToString());
fileExtension.ToLower();
if
(fileExtension != ".gif"
&& fileExtension != ".jpg"
&& fileExtension != ".jpeg"
&& fileExtension != ".png")
{
ClientScript.RegisterClientScriptBlock(this.GetType(),
"ke", "<script>alert('only
jpeg,jpg,png,gif photo is allowed');</script>");
return;
}
string
serverPath = Server.MapPath(@"~/" +
FileUpload1.FileName);
FileUpload1.SaveAs(serverPath);
}
SqlConnection
conn = new SqlConnection(connStr);
conn.Open();
SqlCommand
dCmd = new SqlCommand("dbo.procupd",
conn);
dCmd.CommandType = CommandType.StoredProcedure;
try
{
string
gender=rad();
string
hobby=chk();
string
fl=null;
if
(FileUpload1.HasFile)
fl = FileUpload1.FileName;
else
fl = Session["fl"].ToString();
dCmd.Parameters.AddWithValue("@id",Session["id"].ToString());
dCmd.Parameters.AddWithValue("@name",txtcnm.Text);
dCmd.Parameters.AddWithValue("@stream",DropDownList1.SelectedValue);
dCmd.Parameters.AddWithValue("@gender",gender);
dCmd.Parameters.AddWithValue("@hobby",
hobby);
dCmd.Parameters.AddWithValue("@photo",fl);
int j=
dCmd.ExecuteNonQuery();
if (j
> 0)
{
ClientScript.RegisterClientScriptBlock(this.GetType(),
"key", "<script>
alert('record updated successfully');</script>");
grid();
clear();
Session["id"] =
null;
Session["fl"] =
null;
}
}
catch
{
throw;
}
finally
{
dCmd.Dispose();
conn.Close();
conn.Dispose();
}
}
}
protected void
GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
clear();
Label l
= (Label)GridView1.Rows[e.NewEditIndex].FindControl("Label10");
Label l1
= (Label)GridView1.Rows[e.NewEditIndex].FindControl("Label1");
Label l6
= (Label)GridView1.Rows[e.NewEditIndex].FindControl("Label6");
Label l7
= (Label)GridView1.Rows[e.NewEditIndex].FindControl("Label7");
Label l8
= (Label)GridView1.Rows[e.NewEditIndex].FindControl("Label8");
Label l9
= (Label)GridView1.Rows[e.NewEditIndex].FindControl("Label9");
Label
l10 = (Label)GridView1.Rows[e.NewEditIndex].FindControl("i1");
Session["id"] =
l.Text;
Session["fl"] =
l10.Text;
txtcnm.Text = l1.Text;
DropDownList1.SelectedValue = l6.Text;
if
(l8.Text.Equals("male"))
{
RadioButton1.Checked = true;
}
else
{
RadioButton2.Checked = true;
}
string s
= l9.Text;
string[]
words = s.Split(',');
foreach (string
word in words)
{
switch
(word.ToString())
{
case ("Cricket"):
CheckBox1.Checked = true;
break;
case ("Football"):
CheckBox2.Checked = true;
break;
case ("Tennis"):
CheckBox3.Checked = true;
break;
case ("Other"):
CheckBox4.Checked = true;
break;
}
}
Button3.Visible = true;
Button1.Visible = false;
}
protected void
GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
string
id=GridView1.DataKeys[e.RowIndex].Value.ToString();
SqlConnection
conn = new SqlConnection(connStr);
conn.Open();
SqlCommand
dCmd = new SqlCommand("dbo.procdel",
conn);
dCmd.CommandType = CommandType.StoredProcedure;
try
{
dCmd.Parameters.AddWithValue("@id",id);
int j=
dCmd.ExecuteNonQuery();
if (j
> 0)
{
ClientScript.RegisterClientScriptBlock(this.GetType(),
"key", "<script>
alert('deleted successfully');</script>");
grid();
}
}
catch
{
throw;
}
finally
{
dCmd.Dispose();
conn.Close();
conn.Dispose();
}
}
}
hello, my name is ketan.(email - ketan_padhiyar@ymai.com)
ReplyDeletei need store procedure with multiple query. and all query call in asp.net page using like if @mode = 'delete'.
i try bt not success.. plz give me a sample code of it with three tier arch.
http://aspsolutionkirit.blogspot.in/2013/03/three-tier-insertupdatedelete-in.html
ReplyDeletei hope that is useful for you.