In this article we provide to how to insert, edit, update and cancel the records in a GridView using stored procedure.and we are use a Template Field inside a grid view controls. and this concept work on_row_command.
Step1. Data base Code- Single stored procedure for insert update and delete in sql server
create database Grdindeleteupdate
use Grdindeleteupdate
create table Registration
(
Empid int primary key identity(1,1),
Name varchar(50),
Address varchar(50),
Salary float(50),
Age int,
Mobile varchar(11)
)
select * from Registration
How to create Edit and Update one t procedure-
create proc usp_Registration
@Empid int,
@Name varchar(50),
@Address varchar(50),
@Salary float(50),
@Age int,
@Mobile varchar(11)
as
begin
if(@Empid=0)
begin
insert into Registration(Name,Address,Salary,Age,Mobile)
values(@Name,@Address,@Salary,@Age,@Mobile)
end
else
begin
update Registration set Name=@Name,Address=@Address,Salary=@Salary,Age=@Age,Mobile=@Mobile
where Empid=@Empid
end
end
How to create Get procedure-
create proc usp_Registration_get
as
begin
select* from Registration
end
How to create Delete procedure-
create proc usp_Registration_delete
@Empid int
as
begin
delete from Registration where Empid=@Empid
end
Step.2- Aspx page code.
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Registration.aspx.cs" Inherits="Grdinsert_and_delete.Registration" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<tr>
<td>Name</td>
<td><asp:TextBox ID="TextName" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>Address</td>
<td><asp:TextBox ID="TextAddress" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>Salary</td>
<td><asp:TextBox ID="TextSalary" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>Age</td>
<td><asp:TextBox ID="TextAge" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>Mobile</td>
<td><asp:TextBox ID="TextMobile" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td></td>
<td><asp:Button ID="btnSave" Text="Save" runat="server" OnClick="btnSave_Click" /></td>
</tr>
<tr>
<td></td>
<td><asp:GridView ID="grd" runat="server" AutoGenerateColumns="false" DataKeyNames="Empid" OnRowEditing="grd_RowEditing" OnRowCancelingEdit="grd_RowCancelingEdit" OnRowUpdating="grd_RowUpdating" OnRowDeleting="grd_RowDeleting">
<Columns>
<asp:TemplateField HeaderText="Employee Name">
<ItemTemplate>
<%#Eval("Name") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="TextNameedit" runat="server" Text='<%#Eval("Name") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Employee Address">
<ItemTemplate>
<%#Eval("Address") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="TextAddressedit" runat="server" Text='<%#Eval("Address") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Employee Salary">
<ItemTemplate>
<%#Eval("Salary") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="TextSalaryedit" runat="server" Text='<%#Eval("Salary") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Employee Age">
<ItemTemplate>
<%#Eval("Age") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="TextAgeedit" runat="server" Text='<%#Eval("Age") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Employee Mobile">
<ItemTemplate>
<%#Eval("Mobile") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="TextMobileedit" runat="server" Text='<%#Eval("Mobile") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:LinkButton ID="lnkbtnedit" runat="server" Text="Edit" CommandName="Edit"></asp:LinkButton>
<asp:LinkButton ID="lnkbtndelete" runat="server" Text="Delete" CommandName="Delete"></asp:LinkButton>
</ItemTemplate>
<EditItemTemplate>
<asp:LinkButton ID="lnkbtncancel" runat="server" Text="Cancel" CommandName="Cancel"></asp:LinkButton>
<asp:LinkButton ID="lnkbtnUpdate" runat="server" Text="Update" CommandName="Update"></asp:LinkButton>
</EditItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView></td>
</tr>
</table>
</div>
</form>
</body>
</html>
Step3. .CS page code.
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;
using System.Configuration;
namespace Grdinsert_and_delete
{
public partial class Registration : System.Web.UI.Page
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBA"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
fill_open();
}
}
public void fill_open()
{
con.Open();
SqlCommand cmd = new SqlCommand("usp_Registration_get", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
if (ds.Tables[0].Rows.Count > 0)
{
grd.DataSource = ds;
grd.DataBind();
}
else
{
grd.DataSource = null;
grd.DataBind();
}
con.Close();
}
protected void btnSave_Click(object sender, EventArgs e)
{
con.Open();
SqlCommand cmd = new SqlCommand("usp_Registration", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Empid", 0);
cmd.Parameters.AddWithValue("@Name", TextName.Text);
cmd.Parameters.AddWithValue("Address", TextAddress.Text);
cmd.Parameters.AddWithValue("@Salary", TextSalary.Text);
cmd.Parameters.AddWithValue("@Age", TextAge.Text);
cmd.Parameters.AddWithValue("@Mobile",TextMobile.Text);
cmd.ExecuteNonQuery();
con.Close();
fill_open();
}
protected void grd_RowEditing(object sender, GridViewEditEventArgs e)
{
grd.EditIndex = e.NewEditIndex;
fill_open();
}
protected void grd_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
grd.EditIndex = -1;
fill_open();
}
protected void grd_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
TextBox TBN = grd.Rows[e.RowIndex].FindControl("TextNameedit") as TextBox;
TextBox TBD = grd.Rows[e.RowIndex].FindControl("TextAddressedit") as TextBox;
TextBox TBS = grd.Rows[e.RowIndex].FindControl("TextSalaryedit") as TextBox;
TextBox TBA = grd.Rows[e.RowIndex].FindControl("TextAgeedit") as TextBox;
TextBox TBM = grd.Rows[e.RowIndex].FindControl("TextMobileedit") as TextBox;
string EMPID = grd.DataKeys[e.RowIndex].Value.ToString();
con.Open();
SqlCommand cmd = new SqlCommand("usp_Registration", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Empid", EMPID);
cmd.Parameters.AddWithValue("@Name", TBN.Text);
cmd.Parameters.AddWithValue("@Address", TBD.Text);
cmd.Parameters.AddWithValue("@Salary", TBS.Text);
cmd.Parameters.AddWithValue("@Age", TBA.Text);
cmd.Parameters.AddWithValue("@Mobile", TBM.Text);
cmd.ExecuteNonQuery();
grd.EditIndex = -1;
con.Close();
}
protected void grd_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
string EMPID = grd.DataKeys[e.RowIndex].Value.ToString();
con.Open();
SqlCommand cmd = new SqlCommand("usp_Registration_delete", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Empid", EMPID);
cmd.ExecuteNonQuery();
con.Close();
fill_open();
}
}
}
Step5. Web.config code.
<?xml version="1.0"?>
<!--
For more information on how to configure your ASP.NET application, please visit
http://go.microsoft.com/fwlink/?LinkId=169433
-->
<configuration>
<system.web>
<compilation debug="true" targetFramework="4.5" />
<httpRuntime targetFramework="4.5" />
</system.web>
<connectionStrings>
<add name="DBA" connectionString="data source =Shiva;integrated security=true;initial catalog=Grdindeleteupdate "/>
</connectionStrings>
No comments:
Post a Comment