Introduction- We provide to Edit delete update with check box and fileupload . and country bind dynamically in grdview.
create database self20
use self20
create table Country
(
Cid int primary key identity(1,1),
CName varchar(50),
)
insert into Country values ('India'),('Pakistan'),('Nepal'),('USA'),('Canada')
select * from Country
create proc usp_Country
as
begin
select * from Country
end
create table Employee
(
Empid int primary key identity(1,1),
FirstName varchar(50),
LastName varchar(50),
Age int,
Gender int,
Country int,
Hobbies varchar(100),
File_Upload varchar(100)
)
create proc usp_self20_insert
@FirstName varchar(50),
@LastName varchar(50),
@Age int,
@Gender int,
@Country int,
@Hobbies varchar(100),
@File_Upload varchar(100)
as
begin
insert into Employee(FirstName,LastName,Age,Gender,Country,Hobbies,File_Upload)
values(@FirstName,@LastName,@Age,@Gender,@Country,@Hobbies,@File_Upload)
end
alter proc usp_employee_get
as
begin
select Employee.*,Country.* from Employee inner join Country on Employee.Country=Country.Cid
end
create proc usp_employee_update
@Empid int,
@FirstName varchar(50),
@LastName varchar(50),
@Age int,
@Gender int,
@Country int,
@Hobbies varchar(100),
@File_Upload varchar(100)
as
begin
update Employee set FirstName=@FirstName,LastName=@LastName,Age=@Age,Gender=@Gender,Country=@Country,Hobbies=@Hobbies
where Empid = @Empid
end
create proc usp_employee_delete
@Empid int
as
begin
delete from Employee where Empid = @Empid
end
create proc usp_employee_edit
@Empid int
as
begin
select * from Employee where Empid = @Empid
end
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;
using System.IO;
namespace self12asp
{
public partial class Registration : System.Web.UI.Page
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["CSDB"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
fill_grd();
fill_country();
}
}
public void fill_grd()
{
SqlCommand cmd = new SqlCommand("usp_employee_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();
}
}
public void fill_country()
{
con.Open();
SqlCommand cmd = new SqlCommand("usp_Country", 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"));
}
}
protected void btnsave_Click(object sender, EventArgs e)
{
string HOB = "";
for (int i = 0; i < cblhobbies.Items.Count; i++)
{
if (cblhobbies.Items[i].Selected == true)
{
HOB += cblhobbies.Items[i].Text + ",";
}
}
HOB = HOB.TrimEnd(',');
string FN = "";
if (btnsave.Text == "Submit")
{
FN = Path.GetFileName(fufile.PostedFile.FileName);
fufile.SaveAs(Server.MapPath("FileUpload" + "\\" + FN));
con.Open();
SqlCommand cmd = new SqlCommand("usp_self20_insert", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@FirstName", Textfirstname.Text);
cmd.Parameters.AddWithValue("@LastName", Textlastname.Text);
cmd.Parameters.AddWithValue("@Age", Textage.Text);
cmd.Parameters.AddWithValue("@Gender", rblgender.SelectedValue);
cmd.Parameters.AddWithValue("@Country", ddlcountry.SelectedValue);
cmd.Parameters.AddWithValue("@Hobbies", HOB);
cmd.Parameters.AddWithValue("@File_Upload", FN);
cmd.ExecuteNonQuery();
con.Close();
fill_grd();
}
else if (btnsave.Text == "Update")
{
con.Open();
SqlCommand cmd = new SqlCommand("usp_employee_update", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Empid", ViewState["EID"]);
cmd.Parameters.AddWithValue("@FirstName", Textfirstname.Text);
cmd.Parameters.AddWithValue("@LastName", Textlastname.Text);
cmd.Parameters.AddWithValue("@Age", Textage.Text);
cmd.Parameters.AddWithValue("@Gender", rblgender.SelectedValue);
cmd.Parameters.AddWithValue("@Country", ddlcountry.SelectedValue);
cmd.Parameters.AddWithValue("@Hobbies", HOB);
cmd.Parameters.AddWithValue("@File_Upload", FN);
cmd.ExecuteNonQuery();
con.Close();
fill_grd();
}
}
protected void grd_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "Dhoom3")
{
con.Open();
SqlCommand cmd = new SqlCommand("usp_employee_delete", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Empid", e.CommandArgument);
cmd.ExecuteNonQuery();
con.Close();
fill_grd();
}
else if (e.CommandName == "Krish3")
{
con.Open();
SqlCommand cmd = new SqlCommand("usp_employee_edit", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Empid", e.CommandArgument);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
if (ds.Tables[0].Rows.Count > 0)
{
Textfirstname.Text = ds.Tables[0].Rows[0]["FirstName"].ToString();
Textlastname.Text = ds.Tables[0].Rows[0]["LastName"].ToString();
Textage.Text = ds.Tables[0].Rows[0]["Age"].ToString();
rblgender.SelectedValue = ds.Tables[0].Rows[0]["Gender"].ToString();
ddlcountry.SelectedValue = ds.Tables[0].Rows[0]["Country"].ToString();
string[] arr = ds.Tables[0].Rows[0]["Hobbies"].ToString().Split(',');
cblhobbies.ClearSelection();
for (int i = 0; i < cblhobbies.Items.Count; i++)
{
for (int j = 0; j < arr.Length; j++)
{
if (cblhobbies.Items[i].Text == arr[j])
{
cblhobbies.Items[i].Selected = true;
break;
}
}
}
btnsave.Text = "Update";
ViewState["EID"] = e.CommandArgument;
}
con.Close();
}
}
}
}
Step1. Data base code in table and stored procedure etc. and dynamically country bind database code.
use self20
create table Country
(
Cid int primary key identity(1,1),
CName varchar(50),
)
insert into Country values ('India'),('Pakistan'),('Nepal'),('USA'),('Canada')
select * from Country
create proc usp_Country
as
begin
select * from Country
end
create table Employee
(
Empid int primary key identity(1,1),
FirstName varchar(50),
LastName varchar(50),
Age int,
Gender int,
Country int,
Hobbies varchar(100),
File_Upload varchar(100)
)
create proc usp_self20_insert
@FirstName varchar(50),
@LastName varchar(50),
@Age int,
@Gender int,
@Country int,
@Hobbies varchar(100),
@File_Upload varchar(100)
as
begin
insert into Employee(FirstName,LastName,Age,Gender,Country,Hobbies,File_Upload)
values(@FirstName,@LastName,@Age,@Gender,@Country,@Hobbies,@File_Upload)
end
alter proc usp_employee_get
as
begin
select Employee.*,Country.* from Employee inner join Country on Employee.Country=Country.Cid
end
create proc usp_employee_update
@Empid int,
@FirstName varchar(50),
@LastName varchar(50),
@Age int,
@Gender int,
@Country int,
@Hobbies varchar(100),
@File_Upload varchar(100)
as
begin
update Employee set FirstName=@FirstName,LastName=@LastName,Age=@Age,Gender=@Gender,Country=@Country,Hobbies=@Hobbies
where Empid = @Empid
end
create proc usp_employee_delete
@Empid int
as
begin
delete from Employee where Empid = @Empid
end
create proc usp_employee_edit
@Empid int
as
begin
select * from Employee where Empid = @Empid
end
Step2. .Aspx code in provide to name age salary and gender and dynamically country bind and file upload and checkbox code
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Registration.aspx.cs" Inherits="self12asp.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>First Name</td>
<td><asp:TextBox ID ="Textfirstname" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>Last Name</td>
<td><asp:TextBox ID ="Textlastname" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>Age</td>
<td><asp:TextBox ID ="Textage" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>Gender</td>
<td><asp:RadioButtonList ID="rblgender" runat="server" RepeatColumns="3">
<asp:ListItem Text="Male" Selected="True" Value="1"></asp:ListItem>
<asp:ListItem Text="Female" Selected="True" Value="2"></asp:ListItem>
<asp:ListItem Text="Other" Selected="True" Value="3"></asp:ListItem>
</asp:RadioButtonList></td>
</tr>
<tr>
<td>Country</td>
<td><asp:DropDownList ID="ddlcountry" runat="server"></asp:DropDownList></td>
</tr>
<tr>
<td>Hobbies</td>
<td><asp:CheckBoxList ID="cblhobbies" runat="server" RepeatColumns="3">
<asp:ListItem Text="Music" Value="1"></asp:ListItem>
<asp:ListItem Text="Cricket" Value="2" ></asp:ListItem>
<asp:ListItem Text="Travlling" Value="3" ></asp:ListItem>
<asp:ListItem Text="Books" Value="4"></asp:ListItem>
<asp:ListItem Text="Yoga" Value="5"></asp:ListItem>
<asp:ListItem Text="Football" Value="6" ></asp:ListItem>
</asp:CheckBoxList></td>
</tr>
<tr>
<td>File Upload</td>
<td><asp:FileUpload ID="fufile" runat="server" /></td>
</tr>
<tr>
<td></td>
<td><asp:Button ID="btnsave" Text="Submit" runat="server" OnClick="btnsave_Click" /></td>
</tr>
<tr>
<td></td>
<td><asp:GridView ID="grd" AutoGenerateColumns="false" runat="server" OnRowCommand="grd_RowCommand">
<Columns>
<asp:TemplateField HeaderText="First Name">
<ItemTemplate>
<%#Eval("FirstName") %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Last Name">
<ItemTemplate>
<%#Eval("LastName") %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Age">
<ItemTemplate>
<%#Eval("Age") %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Gender">
<ItemTemplate>
<%#Eval("Gender").ToString()=="1" ? "Male": Eval("Gender").ToString()=="2" ? "Female" :"Other" %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Country">
<ItemTemplate>
<%#Eval("CName") %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Hobbies">
<ItemTemplate>
<%#Eval("Hobbies") %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="File Upload">
<ItemTemplate>
<asp:Image ID="img" runat="server" ImageUrl='<%#Eval("File_Upload","~/FileUpload/{0}") %>' Width="60px" Height="70px" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Hobbies">
<ItemTemplate>
<asp:LinkButton ID="lnkbtndelete" runat="server" CommandName="Dhoom3" Text="Delete" CommandArgument='<%#Eval("Empid") %>'></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Hobbies">
<ItemTemplate>
<asp:LinkButton ID="lnkbtnedit" runat="server" CommandName="Krish3" Text="Edit" CommandArgument='<%#Eval("Empid") %>'></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView></td>
</tr>
</table>
</div>
</form>
</body>
</html>
Step3. .CS page code-
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;
using System.IO;
namespace self12asp
{
public partial class Registration : System.Web.UI.Page
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["CSDB"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
fill_grd();
fill_country();
}
}
public void fill_grd()
{
SqlCommand cmd = new SqlCommand("usp_employee_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();
}
}
public void fill_country()
{
con.Open();
SqlCommand cmd = new SqlCommand("usp_Country", 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"));
}
}
protected void btnsave_Click(object sender, EventArgs e)
{
string HOB = "";
for (int i = 0; i < cblhobbies.Items.Count; i++)
{
if (cblhobbies.Items[i].Selected == true)
{
HOB += cblhobbies.Items[i].Text + ",";
}
}
HOB = HOB.TrimEnd(',');
string FN = "";
if (btnsave.Text == "Submit")
{
FN = Path.GetFileName(fufile.PostedFile.FileName);
fufile.SaveAs(Server.MapPath("FileUpload" + "\\" + FN));
con.Open();
SqlCommand cmd = new SqlCommand("usp_self20_insert", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@FirstName", Textfirstname.Text);
cmd.Parameters.AddWithValue("@LastName", Textlastname.Text);
cmd.Parameters.AddWithValue("@Age", Textage.Text);
cmd.Parameters.AddWithValue("@Gender", rblgender.SelectedValue);
cmd.Parameters.AddWithValue("@Country", ddlcountry.SelectedValue);
cmd.Parameters.AddWithValue("@Hobbies", HOB);
cmd.Parameters.AddWithValue("@File_Upload", FN);
cmd.ExecuteNonQuery();
con.Close();
fill_grd();
}
else if (btnsave.Text == "Update")
{
con.Open();
SqlCommand cmd = new SqlCommand("usp_employee_update", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Empid", ViewState["EID"]);
cmd.Parameters.AddWithValue("@FirstName", Textfirstname.Text);
cmd.Parameters.AddWithValue("@LastName", Textlastname.Text);
cmd.Parameters.AddWithValue("@Age", Textage.Text);
cmd.Parameters.AddWithValue("@Gender", rblgender.SelectedValue);
cmd.Parameters.AddWithValue("@Country", ddlcountry.SelectedValue);
cmd.Parameters.AddWithValue("@Hobbies", HOB);
cmd.Parameters.AddWithValue("@File_Upload", FN);
cmd.ExecuteNonQuery();
con.Close();
fill_grd();
}
}
protected void grd_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "Dhoom3")
{
con.Open();
SqlCommand cmd = new SqlCommand("usp_employee_delete", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Empid", e.CommandArgument);
cmd.ExecuteNonQuery();
con.Close();
fill_grd();
}
else if (e.CommandName == "Krish3")
{
con.Open();
SqlCommand cmd = new SqlCommand("usp_employee_edit", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Empid", e.CommandArgument);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
if (ds.Tables[0].Rows.Count > 0)
{
Textfirstname.Text = ds.Tables[0].Rows[0]["FirstName"].ToString();
Textlastname.Text = ds.Tables[0].Rows[0]["LastName"].ToString();
Textage.Text = ds.Tables[0].Rows[0]["Age"].ToString();
rblgender.SelectedValue = ds.Tables[0].Rows[0]["Gender"].ToString();
ddlcountry.SelectedValue = ds.Tables[0].Rows[0]["Country"].ToString();
string[] arr = ds.Tables[0].Rows[0]["Hobbies"].ToString().Split(',');
cblhobbies.ClearSelection();
for (int i = 0; i < cblhobbies.Items.Count; i++)
{
for (int j = 0; j < arr.Length; j++)
{
if (cblhobbies.Items[i].Text == arr[j])
{
cblhobbies.Items[i].Selected = true;
break;
}
}
}
btnsave.Text = "Update";
ViewState["EID"] = e.CommandArgument;
}
con.Close();
}
}
}
}
Step4. Web .config file 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="CSDB" connectionString="data source=Shiva;integrated security=true ;initial catalog=self20"/>
</connectionStrings>
</configuration>
No comments:
Post a Comment