Database Code:-
create table Country
(
Cid int primary key identity(1,1),
Cname varchar(50)
)
create table Employee
(
Empid int primary key identity(1,1),
Name varchar(50)
Gender int,
Country int,
Age int,
Address varchar(50)
)
ALTER proc [dbo].[usp_country_get]
as
begin
select * from Country
end
ALTER proc [dbo].[usp_employee_delete]
@Empid int
as
begin
delete from Employee where Empid=@Empid
end
ALTER proc [dbo].[usp_employee_get]
as
begin
select Employee.*,Country.* from Employee inner join Country on Employee.Country= country.cid
end
ALTER proc [dbo].[usp_employee_ins_upd]
@Empid int,
@Name varchar(50),
@Gender int,
@Country int,
@Age int,
@Address varchar(50)
as
begin
if(@Empid=0)
begin
insert into Employee(Name,Gender,Country,Age,Address) values(@Name,@Gender,@Country,@Age,@Address)
end
else
begin
update Employee set Name=@Name,Gender=@Gender,Country=@Country,Age=@Age,Address=@Address where Empid=@Empid
end
end
Registration .Aspx page Code:-
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Registration.aspx.cs" Inherits="Row_delete_test_23.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>Gender:</td>
<td><asp:RadioButtonList ID="rblgender" runat="server" RepeatColumns="3">
<asp:ListItem Value="1" Text="Male"></asp:ListItem>
<asp:ListItem Value="2" Text="Female"></asp:ListItem>
<asp:ListItem Value="3" Text="Other"></asp:ListItem>
</asp:RadioButtonList></td>
</tr>
<tr>
<td>Country:</td>
<td><asp:DropDownList ID="ddlcountry" runat="server" AutoPostBack="true"></asp:DropDownList></td>
</tr>
<tr>
<td>Age:</td>
<td><asp:TextBox ID="Textage" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>Address:</td>
<td><asp:TextBox ID="Textaddress" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td></td>
<td><asp:Button ID="btnsave" runat="server" Text="Save" OnClick="btnsave_Click" /></td>
</tr>
</table>
<table border="1" style="background-color:pink">
<tr>
<td></td>
<td><asp:GridView ID="grd" runat="server" ShowFooter="true" AutoGenerateColumns="false" DataKeyNames="Empid" OnRowDeleting="grd_RowDeleting" OnRowEditing="grd_RowEditing" OnRowCancelingEdit="grd_RowCancelingEdit" OnRowUpdating="grd_RowUpdating" OnRowCommand="grd_RowCommand">
<Columns>
<asp:TemplateField HeaderText="Emp Name">
<ItemTemplate>
<%#Eval("Name") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="textnameedit" runat="server" Text='<%#Eval("Name") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="textfootername" runat="server</asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Gender">
<ItemTemplate>
<%#Eval("Gender").ToString()=="1" ? "Male" :Eval("Gender").ToString()=="2" ? "Female" : "Other" %>
</ItemTemplate>
<EditItemTemplate>
<asp:RadioButtonList ID="rblgenderedit" runat="server" RepeatColumns="3">
<asp:ListItem Value="1" Text="Male">Male</asp:ListItem>
<asp:ListItem Value="2" Text="Female">Female</asp:ListItem>
<asp:ListItem Value="3" Text="Other">Other</asp:ListItem>
</asp:RadioButtonList>
</EditItemTemplate>
<FooterTemplate>
<asp:RadioButtonList ID="rblgenderfooter" runat="server" RepeatColumns="3">
<asp:ListItem Value="1" Text="Male">Male</asp:ListItem>
<asp:ListItem Value="2" Text="Female">Female</asp:ListItem>
<asp:ListItem Value="3" Text="Other">Other</asp:ListItem>
</asp:RadioButtonList>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Country">
<ItemTemplate>
<%#Eval("Cname") %>
</ItemTemplate>
<EditItemTemplate>
<asp:DropDownList ID="ddlcountryedit" runat="server">
<asp:ListItem Text="--Select--" Value="0">--Select--</asp:ListItem>
<asp:ListItem Text="India" Value="1">India</asp:ListItem>
<asp:ListItem Text="India" Value="2">USA</asp:ListItem>
<asp:ListItem Text="India" Value="3">Japan</asp:ListItem>
<asp:ListItem Text="India" Value="4">Canada</asp:ListItem>
<asp:ListItem Text="India" Value="5">Dubai</asp:ListItem>
</asp:DropDownList>
</EditItemTemplate>
<FooterTemplate>
<asp:DropDownList ID="ddlcountryfooter" runat="server">
<asp:ListItem Text="--Select--" Value="0">--Select--</asp:ListItem>
<asp:ListItem Text="India" Value="1">India</asp:ListItem>
<asp:ListItem Text="India" Value="2">USA</asp:ListItem>
<asp:ListItem Text="India" Value="3">Japan</asp:ListItem>
<asp:ListItem Text="India" Value="4">Canada</asp:ListItem>
<asp:ListItem Text="India" Value="5">Dubai</asp:ListItem>
</asp:DropDownList>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Age">
<ItemTemplate>
<%#Eval("Age") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="textageedit" runat="server" Text='<%#Eval("Age") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="textfooterage" runat="server"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Address">
<ItemTemplate>
<%#Eval("Address") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="textaddressedit" runat="server" Text='<%#Eval("Address") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="textfooteraddress" runat="server"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField>
<FooterTemplate>
<asp:Button ID="btnsavefooter" runat="server" Text="Inser" CommandName="INS" />
</FooterTemplate>
</asp:TemplateField>
<asp:CommandField HeaderText="Delete" ShowDeleteButton="true" />
<asp:CommandField HeaderText="Edit" ShowEditButton="true" />
<asp:TemplateField HeaderText="Select">
<ItemTemplate>
<asp:CheckBox ID="chkdelete" runat="server" />
</ItemTemplate>
<FooterTemplate>
<asp:Button id="btndeletefooter" runat="server" Text="All_Delete" CommandName="DEL" />
</FooterTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView></td>
</tr>
</table>
</div>
</form>
</body>
</html>
C# 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 Row_delete_test_23
{
public partial class Registration : System.Web.UI.Page
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Fill_country();
Fill_data();
}
}
public void Fill_country()
{
con.Open();
SqlCommand cmd = new SqlCommand("usp_country_get", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
if (ds.Tables[0].Rows.Count > 0)
{
ddlcountry.DataValueField = "Cid";
ddlcountry.DataTextField = "Cname";
ddlcountry.DataSource = ds;
ddlcountry.DataBind();
ddlcountry.Items.Insert(0, new ListItem("--select--","0"));
}
}
public void Fill_data()
{
con.Open();
SqlCommand cmd = new SqlCommand("usp_employee_get", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
if (ds.Tables[0].Rows.Count > 0)
{
grd.DataSource = ds;
grd.DataBind();
}
else
{
DataTable dt = new DataTable();
DataRow row;
dt.Columns.Add(new DataColumn("Empid", typeof(int)));
dt.Columns.Add(new DataColumn("Name", typeof(string)));
dt.Columns.Add(new DataColumn("Gender", typeof(int)));
dt.Columns.Add(new DataColumn("Cname", typeof(string)));
dt.Columns.Add(new DataColumn("Age", typeof(int)));
dt.Columns.Add(new DataColumn("Address", typeof(string)));
row = dt.NewRow();
dt.Rows.Add(row);
grd.DataSource = dt; //Minimum one row show in grid ,because after all record show inset text.
grd.DataBind();
grd.Rows[0].Visible = false;
}
}
protected void btnsave_Click(object sender, EventArgs e)
{
con.Open();
SqlCommand cmd = new SqlCommand("usp_employee_ins_upd", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Empid",0);
cmd.Parameters.AddWithValue("@Name", textname.Text);
cmd.Parameters.AddWithValue("@Gender", rblgender.SelectedValue);
cmd.Parameters.AddWithValue("@Country", ddlcountry.SelectedValue);
cmd.Parameters.AddWithValue("@Age", Textage.Text);
cmd.Parameters.AddWithValue("@Address", Textaddress.Text);
cmd.ExecuteNonQuery();
con.Close();
Fill_data();
}
protected void grd_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
string id = grd.DataKeys[e.RowIndex].Value.ToString();
con.Open();
SqlCommand cmd = new SqlCommand("usp_employee_delete", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Empid",id);
cmd.ExecuteNonQuery();
con.Close();
Fill_data();
}
protected void grd_RowEditing(object sender, GridViewEditEventArgs e)
{
grd.EditIndex = e.NewEditIndex; //row editing
this.Fill_data();
}
protected void grd_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
grd.EditIndex = -1; // row cancleing
Fill_data();
}
protected void grd_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
TextBox TBN = grd.Rows[e.RowIndex].FindControl("textnameedit") as TextBox;
RadioButtonList RBL = (RadioButtonList)grd.Rows[e.RowIndex].FindControl("rblgenderedit");
string DDL = (grd.Rows[e.RowIndex].FindControl("ddlcountryedit") as DropDownList).SelectedItem.Value;
Response.Write(DDL);
TextBox TBAG = grd.Rows[e.RowIndex].FindControl("textageedit") as TextBox;
TextBox TBA = grd.Rows[e.RowIndex].FindControl("textaddressedit") as TextBox;
string IDD = grd.DataKeys[e.RowIndex].Value.ToString();
con.Open();
SqlCommand cmd = new SqlCommand("usp_employee_ins_upd", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Empid",IDD);
cmd.Parameters.AddWithValue("@Name", TBN.Text);
cmd.Parameters.AddWithValue("@Gender", RBL.SelectedValue.ToString());
cmd.Parameters.AddWithValue("@Country", DDL);
cmd.Parameters.AddWithValue("@Age", TBAG.Text);
cmd.Parameters.AddWithValue("@Address", TBA.Text);
cmd.ExecuteNonQuery();
con.Close();
grd.EditIndex = -1;
Fill_data();
}
protected void grd_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "INS")
{
TextBox TBNF = (TextBox)grd.FooterRow.FindControl("textfootername");
RadioButtonList RBLF = (RadioButtonList)grd.FooterRow.FindControl("rblgenderfooter");
string DDLF = (grd.FooterRow.FindControl("ddlcountryfooter") as DropDownList).SelectedItem.Value;
Response.Write(DDLF);
TextBox TBAF = (TextBox)grd.FooterRow.FindControl("textfooterage");
TextBox TBDF = (TextBox)grd.FooterRow.FindControl("textfooteraddress");
con.Open();
SqlCommand cmd = new SqlCommand("usp_employee_ins_upd", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Empid", 0);
cmd.Parameters.AddWithValue("@Name", TBNF.Text);
cmd.Parameters.AddWithValue("@Gender", RBLF.SelectedValue);
cmd.Parameters.AddWithValue("@Country", DDLF);
cmd.Parameters.AddWithValue("@Age", TBAF.Text);
cmd.Parameters.AddWithValue("@Address", TBDF.Text);
cmd.ExecuteNonQuery();
con.Close();
Fill_data();
}
else if (e.CommandName == "DEL")
{
con.Open();
foreach (GridViewRow gvr in grd.Rows)
{
CheckBox CHK = (CheckBox)gvr.FindControl("chkdelete");
if (CHK.Checked == true)
{
string IDDD = grd.DataKeys[gvr.RowIndex].Value.ToString();
SqlCommand cmd = new SqlCommand("usp_employee_delete", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Empid", IDDD);
cmd.ExecuteNonQuery();
}
}
con.Close();
Fill_data();
}
}
}
}
Web.Config:-
<connectionStrings>
<add name="DBCS" connectionString="data source=Shiva;integrated security=true;initial catalog=Row_delete_test_23"/>
3 comments:
give the database code
plzzz sir give the database scripts
Ok dear
Post a Comment