Thursday, March 14, 2013
Labels:
Asp.net
,
Gridview
A stored procedure is a group of sql statements that has been created and stored in the database. Stored procedure will accept input parameters so that a single procedure can be used over the network by several clients using different input data. Stored procedure will reduce network traffic and increase the performance. If we modify stored procedure all the clients will get the updated stored procedure.
(
@nm nvarchar(50)=null,
@gen nvarchar(50)=null,
@quly bit=null,
@post nvarchar(50)=null,
@resume nvarchar(50)=null,
@id int = null
)
AS
If @id IS NULL
Begin
insert into aby_JobApplication(nm,gen,quly,post,resume) values(@nm, @gen, @quly, @post, @resume)
End
Else
if @nm IS NOT NULL
begin
update aby_JobApplication set nm=@nm, gen=@gen, quly=@quly, post=@post, resume=@resume where id=@id
End
else
Begin
delete from aby_JobApplication where id=@id
End
Insert, Update, Delete operation in Database using Stored Procedure for controles like Radio Button, FileUplod , CheckBox and DropDownList in Gridview in asp.net with c#- Example
Posted by
Abhay Makadiya
at
3/14/2013
Introduction about Stored Procedure:-
A stored procedure is a group of sql statements that has been created and stored in the database. Stored procedure will accept input parameters so that a single procedure can be used over the network by several clients using different input data. Stored procedure will reduce network traffic and increase the performance. If we modify stored procedure all the clients will get the updated stored procedure.
My Procedure:-
CREATE PROCEDURE [dbo].[AbyProc](
@nm nvarchar(50)=null,
@gen nvarchar(50)=null,
@quly bit=null,
@post nvarchar(50)=null,
@resume nvarchar(50)=null,
@id int = null
)
AS
If @id IS NULL
Begin
insert into aby_JobApplication(nm,gen,quly,post,resume) values(@nm, @gen, @quly, @post, @resume)
End
Else
if @nm IS NOT NULL
begin
update aby_JobApplication set nm=@nm, gen=@gen, quly=@quly, post=@post, resume=@resume where id=@id
End
else
Begin
delete from aby_JobApplication where id=@id
End
Source Code :-(in ASP.NET)
<%@ page language="C#" autoeventwireup="true" codefile="Proc.aspx.cs" inherits="Proc" %>
<!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">
.style1
{
width: 100%;
}
.style2
{
width: 129px;
}
.style3
{
width: 129px;
height: 26px;
}
.style4
{
height: 26px;
}
.style5
{
width: 129px;
height: 56px;
}
.style6
{
height: 56px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<table class="style1">
<tr>
<td class="style3">
Name
</td>
<td class="style4">
<asp:textbox id="txtnm" runat="server" maxlength="40"></asp:textbox>
<asp:requiredfieldvalidator id="RequiredFieldValidator1" runat="server" controltovalidate="txtnm"
errormessage="RequiredFieldValidator" validationgroup="vjobapp"></asp:requiredfieldvalidator>
</td>
</tr>
<tr>
<td class="style5">
gender
</td>
<td class="style6">
<asp:radiobuttonlist id="rblgen" runat="server" height="39px" width="131px">
<asp:ListItem Selected="True" Value="0">MALE</asp:ListItem>
<asp:ListItem Value="1">FEMALE</asp:ListItem>
</asp:radiobuttonlist>
</td>
</tr>
<tr>
<td class="style2">
Qualification
</td>
<td>
<asp:checkboxlist id="cbquly" runat="server">
<asp:ListItem>Deploma</asp:ListItem>
<asp:ListItem>Becholar</asp:ListItem>
<asp:ListItem>Post Gradute</asp:ListItem>
</asp:checkboxlist>
</td>
</tr>
<tr>
<td class="style2">
Appied For Post
</td>
<td>
<asp:dropdownlist id="ddlpost" runat="server">
<asp:ListItem Value="0"><SELECT POST></asp:ListItem>
<asp:ListItem>Project Manager</asp:ListItem>
<asp:ListItem>Tester</asp:ListItem>
<asp:ListItem>Developer</asp:ListItem>
<asp:ListItem>HR</asp:ListItem>
<asp:ListItem>System Support</asp:ListItem>
</asp:dropdownlist>
<asp:requiredfieldvalidator id="RequiredFieldValidator3" runat="server" controltovalidate="ddlpost"
errormessage="RequiredFieldValidator" initialvalue="0" validationgroup="vjobapp"></asp:requiredfieldvalidator>
</td>
</tr>
<tr>
<td class="style2">
Image
</td>
<td>
<asp:fileupload id="FileUplodResume" runat="server" />
<asp:label id="lblpath" runat="server" visible="False"></asp:label>
<asp:requiredfieldvalidator id="RequiredFieldValidator4" runat="server" controltovalidate="FileUplodResume"
errormessage="RequiredFieldValidator" validationgroup="vjobapp"></asp:requiredfieldvalidator>
</td>
</tr>
<tr>
<td class="style2">
</td>
<td>
<asp:button id="btnadd" runat="server" onclick="btnAdd_Click" text="Add" validationgroup="vjobapp" />
<asp:button id="btnCancle" runat="server" text="Cancle" onclick="btnCancle_Click" />
<asp:hiddenfield id="hid" runat="server" />
</td>
</tr>
<tr>
<td class="style2">
</td>
<td>
<asp:gridview id="GridView1" runat="server" autogeneratecolumns="False" cellpadding="4"
forecolor="#333333" gridlines="None">
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<Columns>
<asp:TemplateField Visible="false">
<ItemTemplate>
<asp:Label ID="lid" runat="server" Text='<%#Eval("id")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Name">
<ItemTemplate>
<asp:Label ID="lnm" runat="server" Text='<%#Eval("nm")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Gender">
<ItemTemplate>
<asp:Label ID="lgen" runat="server" Text='<%# Eval("gen").ToString()=="0" ? "MALE":"FEMAlE" %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Qualification">
<ItemTemplate>
<asp:Label ID="lquly" runat="server" Text='<%# Eval("quly")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Post">
<ItemTemplate>
<asp:Label ID="lpost" runat="server" Text='<%#Eval("post")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Name">
<ItemTemplate>
<asp:Image ID="lresume" runat="server" Height="200px" Width="200px" ImageUrl='<%#Eval("resume") %>'/>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:LinkButton ID="lbedit" runat="server" OnClick="editDetail"
CommandArgument='<% #Eval("id")%>'>Edit</asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:LinkButton ID="lbdelete" runat="server" OnClick="deleteDetail" CommandArgument='<% #Eval("id")%>'>Delete</asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<EditRowStyle BackColor="#999999" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:gridview>
</td>
</tr>
</table>
</form>
</body>
</html>
Code Behind (in C#):-
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
public partial class Proc : System.Web.UI.Page
{
static string str = @"Data Source=SQLDB;Persist Security Info=True;User ID=Demod;Password=Demo1@";
public static SqlConnection con = new SqlConnection(str);
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
gridbind();
}
}
private void gridbind()
{
GridView1.DataSource = Data_of_Grid("select * from aby_JobApplication");
GridView1.DataBind();
}
public DataTable Data_of_Grid(string q)
{
DataTable dt = new DataTable();
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd = new SqlCommand(q, con);
SqlDataAdapter sd = new SqlDataAdapter();
sd.SelectCommand = cmd;
sd.Fill(dt);
}
catch (Exception err)
{
//TODO
}
finally
{
con.Close();
}
return dt;
}
protected void btnAdd_Click(object sender, EventArgs e)
{
string cbl = "";
foreach (ListItem li in cbquly.Items)
{
if (li.Selected == true)
{
cbl += li.Text + ",";
}
}
string ig = FileUplodResume.FileName.ToString();
string url = "photo/" + ig;
FileUplodResume.SaveAs(Server.MapPath("~//") + url);
SqlCommand command = new SqlCommand("[AbyProc]", con);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@nm", txtnm.Text);
command.Parameters.AddWithValue("@gen", rblgen.SelectedValue);
command.Parameters.AddWithValue("@quly", cbl);
command.Parameters.AddWithValue("@post", ddlpost.SelectedItem.Text);
command.Parameters.AddWithValue("@resume", url);
if (btnadd.Text == "Add")
{
//command.Parameters.AddWithValue("@id", null);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
command.ExecuteNonQuery();
con.Close();
}
else
{
command.Parameters.AddWithValue("@id", Convert.ToInt32(hid.Value));
if (con.State == ConnectionState.Closed)
{
con.Open();
}
command.ExecuteNonQuery();
con.Close();
btnadd.Text = "Add";
lblpath.Visible = false;
}
clr();
gridbind();
}
protected void editDetail(object sender, EventArgs e)
{
LinkButton lb = (LinkButton)sender;
GridViewRow gr = (GridViewRow)lb.NamingContainer;
hid.Value = lb.CommandArgument;
Label lnm1 = (Label)gr.FindControl("lnm");
txtnm.Text = lnm1.Text;
Label lgen1 = (Label)gr.FindControl("lgen");
rblgen.SelectedValue = lgen1.Text == "MALE" ? "0" : "1";
Label lquly1 = (Label)gr.FindControl("lquly");
foreach (ListItem li in cbquly.Items)
{
if (lquly1.Text.Contains(li.Text))
{
li.Selected = true;
}
}
//cbquly.Checked = lquly1.Text == "POST GRADUATED" ? true : false;
Label lpost1 = (Label)gr.FindControl("lpost");
ddlpost.SelectedValue = lpost1.Text;
Image im = (Image)gr.FindControl("lresume");
lblpath.Text = im.ImageUrl;
lblpath.Visible = true;
gridbind();
btnadd.Text = "Update";
}
protected void deleteDetail(object sender, EventArgs e)
{
LinkButton lb = (LinkButton)sender;
SqlCommand command = new SqlCommand("[AbyProc]", con);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@id", Convert.ToInt32(lb.CommandArgument));
if (con.State == ConnectionState.Closed)
{
con.Open();
}
command.ExecuteNonQuery();
con.Close();
gridbind();
clr();
btnadd.Text = "Add";
}
protected void btnCancle_Click(object sender, EventArgs e)
{
btnadd.Text = "Add";
clr();
}
private void clr()
{
lblpath.Visible = false;
txtnm.Text = "";
rblgen.SelectedValue = "0";
foreach (ListItem li in cbquly.Items)
{
li.Selected = false;
}
ddlpost.SelectedValue = "0";
}
}
Subscribe to:
Post Comments
(
Atom
)
Nice post Brother...keep it up......
ReplyDeletethanks Bro.........!!!!
Deletehelpful post aby...carry on
ReplyDelete