----------------------------------Create Table----------------------------
CREATE TABLE [Employee]
(
[Empid] [int] IDENTITY(1,1)Primary Key NOT NULL,
[EmpName] [varchar](50) NULL,
[Age] [varchar](50) NULL
)
--------------------Create Store Procedure-----------------------------
CREATE PROCEDURE CrudOperations
@Empid int = 0,
@EmpName varchar(50)=null,
@Age int=0,
@status varchar(50)
AS
BEGIN
SET NOCOUNT ON;
--- Insert New
Records
IF @status='INSERT'
BEGIN
INSERT INTO Employee(EmpName,Age) VALUES(@EmpName,@Age)
END
--- Select
Records in Table
IF @status='SELECT'
BEGIN
SELECT Empid,EmpName,Age FROM Employee
END
--- Update
Records in Table
IF @status='UPDATE'
BEGIN
UPDATE Employee SET EmpName=EmpName,Age=@Age WHERE Empid=@Empid
END
--- Delete
Records from Table
IF @status='DELETE'
BEGIN
DELETE FROM Employee where Empid=@Empid
END
SET NOCOUNT OFF
END
Once we finish stored procedure creation in database
now open your aspx page and write the code like as shown belowAfter completion of aspx page
<%@ Page
Language="C#"
AutoEventWireup="true"
CodeFile="Insert_Update_Delete_GridView.aspx.cs"
Inherits="Insert_Update_Delete_GridView"
%>
<!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>GridView Crud (Select Insert Edit Update Delete)
Operations using Single Stored Procedure in ASP.Net</title>
<style type="text/css">
.GridviewDiv {font-size:
100%; font-family:
'Lucida Grande', 'Lucida
Sans Unicode', Verdana, Arial, Helevetica, sans-serif; color: #303933;}
.headerstyle
{
color:#FFFFFF;border-right-color:#abb079;border-bottom-color:#abb079;background-color: Green;padding:0.5em 0.5em 0.5em 0.5em;text-align:center;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div class="GridviewDiv">
<asp:GridView runat="server" ID="gvDetails" ShowFooter="true" AllowPaging="true"
PageSize="10"
AutoGenerateColumns="false"
DataKeyNames="Empid,EmpName"
OnPageIndexChanging="gvDetails_PageIndexChanging"
OnRowCancelingEdit="gvDetails_RowCancelingEdit"
OnRowEditing="gvDetails_RowEditing" OnRowUpdating="gvDetails_RowUpdating"
OnRowDeleting="gvDetails_RowDeleting"
OnRowCommand ="gvDetails_RowCommand"
>
<HeaderStyle CssClass="headerstyle" />
<Columns>
<asp:BoundField DataField="Empid" HeaderText="Employee Id" ReadOnly="true" />
<asp:TemplateField HeaderText="Employee Name">
<ItemTemplate>
<asp:Label ID="lblEmpname" runat="server" Text='<%# Eval("EmpName")%>'/>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtEmpname" runat="server" Text='<%# Eval("EmpName")%>'/>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtEmpname" runat="server" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText = "Age">
<ItemTemplate>
<asp:Label ID="lblEmpAge" runat="server" Text='<%# Eval("Age")%>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtEmpAge" runat="server" Text='<%# Eval("Age")%>'/>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtEmpAge" runat="server" />
<asp:Button ID="btnAdd" CommandName="AddNew" runat="server" Text="Add" />
</FooterTemplate>
</asp:TemplateField>
<asp:CommandField ShowEditButton="True" ShowDeleteButton="true" />
</Columns>
</asp:GridView>
<asp:Label ID="lblresult" runat="server"></asp:Label>
</div>
</form>
</body>
</html>
add following namespaces
in codebehind
using System;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Drawing;
using System;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Drawing;
public partial class Insert_Update_Delete_GridView
: System.Web.UI.Page
{
protected void
Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGridview();
}
}
protected void
BindGridview()
{
DataSet ds = new DataSet();
using (SqlConnection
con = new SqlConnection("Data Source=LENOVO-PC;Initial
Catalog=nmcnews;Integrated Security=True"))
{
con.Open();
SqlCommand cmd = new
SqlCommand("crudoperations",
con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@status",
"SELECT");
SqlDataAdapter da = new
SqlDataAdapter(cmd);
da.Fill(ds);
con.Close();
if (ds.Tables[0].Rows.Count > 0)
{
gvDetails.DataSource = ds;
gvDetails.DataBind();
}
else
{
ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
gvDetails.DataSource = ds;
gvDetails.DataBind();
int columncount =
gvDetails.Rows[0].Cells.Count;
gvDetails.Rows[0].Cells.Clear();
gvDetails.Rows[0].Cells.Add(new TableCell());
gvDetails.Rows[0].Cells[0].ColumnSpan = columncount;
gvDetails.Rows[0].Cells[0].Text = "No
Records Found";
}
}
}
protected void
crudoperations(string status, string Empname, string
Age, int Empid)
{
using (SqlConnection
con = new SqlConnection("Data Source=LENOVO-PC;Initial Catalog=nmcnews;Integrated
Security=True"))
{
con.Open();
SqlCommand cmd = new
SqlCommand("crudoperations",
con);
cmd.CommandType = CommandType.StoredProcedure;
if (status == "INSERT")
{
cmd.Parameters.AddWithValue("@status",
status);
cmd.Parameters.AddWithValue("@Empname",
Empname);
cmd.Parameters.AddWithValue("@Age",
Age);
}
else if (status == "UPDATE")
{
cmd.Parameters.AddWithValue("@status",
status);
cmd.Parameters.AddWithValue("@Empname",
Empname);
cmd.Parameters.AddWithValue("@Age",
Age);
cmd.Parameters.AddWithValue("@Empid",
Empid);
}
else if (status == "DELETE")
{
cmd.Parameters.AddWithValue("@status",
status);
cmd.Parameters.AddWithValue("@Empid",
Empid);
}
cmd.ExecuteNonQuery();
lblresult.ForeColor = Color.Green;
lblresult.Text = Empname + " details
" + status.ToLower() + "d
successfully";
gvDetails.EditIndex = -1;
BindGridview();
}
}
protected void
gvDetails_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals("AddNew"))
{
TextBox txtEmpname = (TextBox)gvDetails.FooterRow.FindControl("txtEmpName");
TextBox txtEmpAge = (TextBox)gvDetails.FooterRow.FindControl("txtEmpAge");
crudoperations("INSERT",
txtEmpname.Text, txtEmpAge.Text, 0);
}
}
protected void
gvDetails_RowEditing(object sender, GridViewEditEventArgs e)
{
gvDetails.EditIndex = e.NewEditIndex;
BindGridview();
}
protected void
gvDetails_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int Empid = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Values["Empid"].ToString());
string Empname = gvDetails.DataKeys[e.RowIndex].Values["EmpName"].ToString();
crudoperations("DELETE",
Empname, "", Empid);
}
protected void
gvDetails_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
gvDetails.EditIndex = -1;
BindGridview();
}
protected void
gvDetails_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
gvDetails.PageIndex = e.NewPageIndex;
BindGridview();
}
protected void
gvDetails_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
int Empid = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Values["Empid"].ToString());
TextBox txtname = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtEmpname");
TextBox txtAge = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtEmpAge");
crudoperations("UPDATE",
txtname.Text, txtAge.Text, Empid);
BindGridview();
}
}
No comments:
Post a Comment