Asp.net GridView Crud Operations (Insert Select Edit Update Delete) using Single Stored Procedure.
I.Introduction :
Here I will explain how to implement gridview crud operations insert, select, edit, update and delete operations with single stored procedure in asp.net using C# with example.
II.Description:
Now create one new stored procedure "spCRUD" in your sql server database to perform insert, select, update and delete operations with single procedure for that follow below script.
create proc spCRUD
@Action varchar(50),
@Eid int =null,
@EName varchar(50)=null,
@Dept varchar(50)=null,
@Salary int=null
as
begin
if @Action='Select'
begin
select * from tblemployee;
end
if @Action='Insert'
begin
insert into tblemployee values(@Eid,@EName,@Dept,@Salary);
end
if @Action='Update'
begin
update tblemployee set Eid=@Eid, Ename=@EName,Dept=@Dept,Salary=@Salary
where Eid=@Eid;
end
if @Action='Delete'
begin
delete from tblemployee where Eid=@Eid;
end
end
@Action varchar(50),
@Eid int =null,
@EName varchar(50)=null,
@Dept varchar(50)=null,
@Salary int=null
as
begin
if @Action='Select'
begin
select * from tblemployee;
end
if @Action='Insert'
begin
insert into tblemployee values(@Eid,@EName,@Dept,@Salary);
end
if @Action='Update'
begin
update tblemployee set Eid=@Eid, Ename=@EName,Dept=@Dept,Salary=@Salary
where Eid=@Eid;
end
if @Action='Delete'
begin
delete from tblemployee where Eid=@Eid;
end
end
II.In case if you have any doubts to create procedure check below article
Once we finish stored procedure creation in database now open your aspx page and write the code like as shown below
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>CRUD Operation in ASP.Net</title>
<style type="text/css">
.auto-style1 {
text-align: center;
}
</style>
</head>
<body style="text-align: center">
<form id="form1" runat="server">
<div style="text-align: center">
<table style="align-content: center; margin-left: 350px;">
<tr>
<td class="auto-style1">
<asp:Label ID="Label4" runat="server" Text="Enter EmployeeId:"></asp:Label>
</td>
<td class="auto-style1">
<asp:TextBox ID="txtid" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td class="auto-style1">
<asp:Label ID="Label1" runat="server" Text="Enter Name:"></asp:Label>
</td>
<td class="auto-style1">
<asp:TextBox ID="txtname" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td class="auto-style1">
<asp:Label ID="Label2" runat="server" Text="Enter Department :"></asp:Label>
</td>
<td class="auto-style1">
<asp:TextBox ID="txtdept" runat="server" Style="text-align: justify"></asp:TextBox>
</td>
</tr>
<tr>
<td class="auto-style1">
<asp:Label ID="Label3" runat="server" Text="Enter Salary "></asp:Label>
</td>
<td class="auto-style1">
<asp:TextBox ID="txtsalary" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td colspan="2" style="text-align: right">
<asp:Label ID="lblerror" runat="server" Visible="true" ForeColor="Green"></asp:Label>
</td>
</tr>
<tr>
<td class="auto-style1">
<asp:Button ID="btnSave" runat="server" Text="Submit" OnClick="btnSave_Click1" />
<asp:Button ID="btnUpdate" runat="server" Text="Update " OnClick="btnUpdate_Click" Visible="false" />
</td>
<td class="auto-style1">
<asp:Button ID="btnRest" runat="server" Text="Reset" OnClick="btnRest_Click" />
</td>
</tr>
</table>
</div>
<br />
<br />
<div style="text-align: center; margin-left: 100px;">
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White"
BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="3" Style="margin-left: 278px">
<Columns>
<asp:TemplateField HeaderText="Eid">
<ItemTemplate>
<asp:Label ID="lblid" runat="server" Text='<%#Eval("Eid") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Name">
<ItemTemplate>
<asp:Label ID="lblname" runat="server" Text='<%#Eval("EName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Department">
<ItemTemplate>
<asp:Label ID="lbldept" runat="server" Text='<%#Eval("Dept") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Salary">
<ItemTemplate>
<asp:Label ID="lblsalary" runat="server" Text='<%#Eval("Salary") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Action">
<ItemTemplate>
<asp:Button ID="btnEdit" runat="server" Text="Edit" OnClick="btnEdit_Click" />
<asp:Button ID="btnDelete" runat="server" Text="Delete" OnClick="btnDelete_Click" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="White" ForeColor="#000066" />
<HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
<RowStyle ForeColor="#000066" />
<SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#F1F1F1" />
<SortedAscendingHeaderStyle BackColor="#007DBB" />
<SortedDescendingCellStyle BackColor="#CAC9C9" />
<SortedDescendingHeaderStyle BackColor="#00547E" />
</asp:GridView>
</div>
</form>
</body>
</html>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>CRUD Operation in ASP.Net</title>
<style type="text/css">
.auto-style1 {
text-align: center;
}
</style>
</head>
<body style="text-align: center">
<form id="form1" runat="server">
<div style="text-align: center">
<table style="align-content: center; margin-left: 350px;">
<tr>
<td class="auto-style1">
<asp:Label ID="Label4" runat="server" Text="Enter EmployeeId:"></asp:Label>
</td>
<td class="auto-style1">
<asp:TextBox ID="txtid" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td class="auto-style1">
<asp:Label ID="Label1" runat="server" Text="Enter Name:"></asp:Label>
</td>
<td class="auto-style1">
<asp:TextBox ID="txtname" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td class="auto-style1">
<asp:Label ID="Label2" runat="server" Text="Enter Department :"></asp:Label>
</td>
<td class="auto-style1">
<asp:TextBox ID="txtdept" runat="server" Style="text-align: justify"></asp:TextBox>
</td>
</tr>
<tr>
<td class="auto-style1">
<asp:Label ID="Label3" runat="server" Text="Enter Salary "></asp:Label>
</td>
<td class="auto-style1">
<asp:TextBox ID="txtsalary" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td colspan="2" style="text-align: right">
<asp:Label ID="lblerror" runat="server" Visible="true" ForeColor="Green"></asp:Label>
</td>
</tr>
<tr>
<td class="auto-style1">
<asp:Button ID="btnSave" runat="server" Text="Submit" OnClick="btnSave_Click1" />
<asp:Button ID="btnUpdate" runat="server" Text="Update " OnClick="btnUpdate_Click" Visible="false" />
</td>
<td class="auto-style1">
<asp:Button ID="btnRest" runat="server" Text="Reset" OnClick="btnRest_Click" />
</td>
</tr>
</table>
</div>
<br />
<br />
<div style="text-align: center; margin-left: 100px;">
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White"
BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="3" Style="margin-left: 278px">
<Columns>
<asp:TemplateField HeaderText="Eid">
<ItemTemplate>
<asp:Label ID="lblid" runat="server" Text='<%#Eval("Eid") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Name">
<ItemTemplate>
<asp:Label ID="lblname" runat="server" Text='<%#Eval("EName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Department">
<ItemTemplate>
<asp:Label ID="lbldept" runat="server" Text='<%#Eval("Dept") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Salary">
<ItemTemplate>
<asp:Label ID="lblsalary" runat="server" Text='<%#Eval("Salary") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Action">
<ItemTemplate>
<asp:Button ID="btnEdit" runat="server" Text="Edit" OnClick="btnEdit_Click" />
<asp:Button ID="btnDelete" runat="server" Text="Delete" OnClick="btnDelete_Click" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="White" ForeColor="#000066" />
<HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
<RowStyle ForeColor="#000066" />
<SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#F1F1F1" />
<SortedAscendingHeaderStyle BackColor="#007DBB" />
<SortedDescendingCellStyle BackColor="#CAC9C9" />
<SortedDescendingHeaderStyle BackColor="#00547E" />
</asp:GridView>
</div>
</form>
</body>
</html>
After completion of aspx page add following namespaces in codebehind
web.config:
<connectionStrings>
<add name="CRUD" connectionString="Data Source=ZAFFER- BAIG\SQLEXPRESS;Initial Catalog=Employee;
Integrated Security=True"
providerName="System.Data.SqlClient"/>
</connectionStrings>
C# code
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.WebControls;
After completion of adding namespaces you need to write the code like as shown below
string Con = ConfigurationManager.ConnectionStrings["Info"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGrid();
}
}
private void ClearControls()
{
txtid.Text = "";
txtname.Text = "";
txtdept.Text = "";
txtsalary.Text = "";
lblerror.Visible = false;
}
private void BindGrid()
{
using (SqlConnection con = new SqlConnection(Con))
{
SqlDataAdapter dr = new SqlDataAdapter("spCRUD", con);
dr.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
dr.SelectCommand.Parameters.AddWithValue("@Action", "Select");
DataTable dt = new DataTable();
dr.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
if (txtid.Text == "" || txtname.Text == "" || txtdept.Text == "" || txtsalary.Text == "")
{
lblerror.Visible = true;
lblerror.Text = "Enter the required field ";
}
else
{
using (SqlConnection con = new SqlConnection(Con))
{
con.Open();
SqlCommand cmd = new SqlCommand("spCRUD", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Action", "Insert");
cmd.Parameters.AddWithValue("@Eid", txtid.Text);
cmd.Parameters.AddWithValue("@EName", txtname.Text);
cmd.Parameters.AddWithValue("@Dept", txtdept.Text);
cmd.Parameters.AddWithValue("@Salary", txtsalary.Text);
cmd.ExecuteNonQuery();
con.Close();
}
BindGrid();
ClearControls();
lblerror.Visible = true;
lblerror.Text = "Successfully inserted !!";
}
}
protected void btnEdit_Click(object sender, EventArgs e)
{
Button btn = sender as Button;
GridViewRow grd = btn.NamingContainer as GridViewRow;
txtid.Text = (grd.FindControl("lblid") as Label).Text;
txtname.Text = (grd.FindControl("lblname") as Label).Text;
txtdept.Text = (grd.FindControl("lbldept") as Label).Text;
txtsalary.Text = (grd.FindControl("lblsalary") as Label).Text;
btnUpdate.Visible = true;
btnRest.Visible = true;
btnSave.Visible = false;
txtid.Enabled = true;
}
protected void btnDelete_Click(object sender, EventArgs e)
{
Button btn = sender as Button;
GridViewRow grd = btn.NamingContainer as GridViewRow;
lblerror.Text = (grd.FindControl("lblid") as Label).Text;
using (SqlConnection con = new SqlConnection(Con))
{
con.Open();
SqlCommand cmd = new SqlCommand("spCRUD", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Action", "Delete");
cmd.Parameters.AddWithValue("@Eid", lblerror.Text);
cmd.ExecuteNonQuery();
BindGrid();
lblerror.Visible = true;
lblerror.Text = "deleted sucessfully ";
}
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
if (txtname.Text == "" || txtdept.Text == "" || txtsalary.Text == "")
{
lblerror.Visible = true;
lblerror.Text = "Enter the required field ";
}
else
{
using (SqlConnection con = new SqlConnection(Con))
{
con.Open();
SqlCommand cmd = new SqlCommand("spCRUD", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Action", "Update");
cmd.Parameters.AddWithValue("@Eid", txtid.Text);
cmd.Parameters.AddWithValue("@EName", txtname.Text);
cmd.Parameters.AddWithValue("@Dept", txtdept.Text);
cmd.Parameters.AddWithValue("@Salary", txtsalary.Text);
cmd.ExecuteNonQuery();
con.Close();
}
BindGrid();
ClearControls();
lblerror.Visible = true;
lblerror.Text = " Updated Successfully inserted !!";
}
btnUpdate.Visible = false;
btnSave.Visible = true;
}
protected void btnRest_Click(object sender, EventArgs e)
{
ClearControls();
}
Demo:
Hope you all understand the Asp.net GridView CRUD Operation using Stored Procodure ..
Do have any query regarding my code you can comment below !!!
No comments:
Post a Comment