3-tire Architecture in Linq And store Procedure using asp.net with C#
ADD FILE IN PROJECT
INSERT DATA IN GRIDVIEW
GENERATE REPORT
ASPX FILE
<%@ Page Language="C#"
AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" EnableEventValidation="false" %>
<%@ Register Assembly="CrystalDecisions.Web,
Version=10.5.3700.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"
Namespace="CrystalDecisions.Web" TagPrefix="CR"
%>
<!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></title>
</head>
<body bgcolor="#3399CC">
<form id="form1" runat="server">
<div>
<asp:Label ID="Label19" runat="server" ForeColor="White" Text="Label"></asp:Label>
<table border="1" bordercolor="white">
<tr>
<td>
<h1 class="stylee">REGISTER
FORM</h1>
</td>
</tr>
<tr>
<td>
<asp:Label ID="Label1" ForeColor="White"
runat="server"
Text="employee
name"></asp:Label>
</td>
<td>
<asp:TextBox ID="TextBox1"
runat="server"></asp:TextBox>
</td><td>
<asp:RequiredFieldValidator
ID="RequiredFieldValidator1"
runat="server"
ErrorMessage="please
enter company name" ForeColor="Blue"
ControlToValidate="TextBox1">
</asp:RequiredFieldValidator></td>
</tr>
<tr>
<td>
<asp:Label ID="Label2" ForeColor="White"
runat="server"
Text="employee
address"></asp:Label>
</td>
<td>
<asp:TextBox ID="TextBox2"
runat="server"
TextMode="MultiLine"></asp:TextBox>
</td>
<td>
<asp:RequiredFieldValidator
ID="RequiredFieldValidator2"
runat="server"
ErrorMessage="please
enter company address" ForeColor="Blue"
ControlToValidate="TextBox2"
>
</asp:RequiredFieldValidator></td>
</tr>
<tr>
<td>
<asp:Label ID="Label4" ForeColor="White"
runat="server"
Text="employee
state"></asp:Label>
</td>
<td>
<asp:DropDownList ID="DropDownList1"
runat="server"
style="height: 22px"
onselectedindexchanged="DropDownList1_SelectedIndexChanged"
AutoPostBack="True"
DataTextField="statename"
DataValueField="statename"
>
</asp:DropDownList>
<td>
</tr>
<tr>
<td>
<asp:Label ID="Label5" ForeColor="White"
runat="server"
Text="employee
city"></asp:Label>
</td>
<td>
<asp:DropDownList ID="DropDownList2"
runat="server"
AutoPostBack="True"
DataTextField="city"
DataValueField="city"
>
</asp:DropDownList>
</td>
<td>
</tr>
<tr>
<td>
<asp:Label ID="Label6" ForeColor="White"
runat="server"
Text="employee
email"></asp:Label>
</td>
<td>
<asp:TextBox ID="TextBox6"
runat="server"></asp:TextBox>
</td>
<td>
<asp:RegularExpressionValidator
ID="RegularExpressionValidator1"
runat="server"
ForeColor="Blue"
ErrorMessage="invalid
email" ValidationExpression="\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"
ControlToValidate="TextBox6">
</asp:RegularExpressionValidator>
</td>
</tr>
<tr>
<td>
<asp:Label ID="Label7" ForeColor="White"
runat="server"
Text="employee
salary"></asp:Label>
</td>
<td>
<asp:TextBox ID="TextBox7"
runat="server"
></asp:TextBox>
</td>
<td>
</td>
</tr>
<tr>
<td>
<asp:Label ID="Label14"
ForeColor="White"
runat="server"
Text="employee
photos"></asp:Label>
</td>
<td>
<asp:FileUpload ID="FileUpload1" runat="server" />
</td>
<td>
<asp:RequiredFieldValidator
ID="RequiredFieldValidator8"
runat="server"
ErrorMessage="enter
logo" ForeColor="Blue" ControlToValidate="FileUpload1"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td>
<asp:Label ID="Label3" runat="server"
ForeColor="White"
Text="Gender"></asp:Label>
</td>
<td>
<%--<asp:RadioButtonList ForeColor="White"
ID="RadioButtonList1" runat="server">
<asp:ListItem Text="male"
Value="0"></asp:ListItem>
<asp:ListItem Text="female"
Value="1"></asp:ListItem>
</asp:RadioButtonList>--%>
<asp:RadioButton ID="RadioButton1" ForeColor="White" Text="male" runat="server" GroupName="x" />
<asp:RadioButton ID="RadioButton2"
ForeColor="White"
Text="female"
runat="server"
GroupName="x"
/>
</td>
<td>
<%--<asp:RequiredFieldValidator
ID="RequiredFieldValidator3"
runat="server" ErrorMessage="please check Gender"
ControlToValidate="RadioButtonList1"></asp:RequiredFieldValidator>--%>
</td>
</tr>
<tr>
<td>
<asp:Label ID="Label8" runat="server" ForeColor="White" Text="Hobbys"></asp:Label>
</td>
<td>
<asp:CheckBoxList ForeColor="White"
ID="CheckBoxList1"
runat="server">
<asp:ListItem Text="Reading
book"></asp:ListItem>
<asp:ListItem Text="coock"></asp:ListItem>
<asp:ListItem Text="watch
movie"></asp:ListItem>
</asp:CheckBoxList>
</td>
<%--<td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator4"
runat="server" ErrorMessage="please checked hobbys"
ControlToValidate="CheckBoxList1"></asp:RequiredFieldValidator>
</td>--%>
</tr>
<tr>
<td>
</td>
<td>
<asp:Button ID="Button1"
runat="server"
Text="insert"
onclick="Button1_Click"
/>
<asp:Button ID="Button2"
runat="server"
Text="update"
CausesValidation="False"
onclick="Button2_Click"
Visible="false"
/>
<asp:Button ID="Button3"
runat="server"
Text="Cleare"
CausesValidation="False"
onclick="Button3_Click"
/>
</td>
</tr>
</table>
<asp:Button ID="Button4" runat="server" CausesValidation="false" Text="create crystalreport"
onclick="Button4_Click" />
<asp:GridView ID="GridView1" ForeColor="White" runat="server"
AutoGenerateColumns="False" DataKeyNames="emp_id"
onrowediting="GridView1_RowEditing" onrowdeleting="GridView1_RowDeleting"
>
<Columns>
<asp:TemplateField HeaderText="id" Visible="false">
<ItemTemplate>
<asp:Label ID="Label9" runat="server" Text='<%#Eval("emp_id")
%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="emp_nm">
<ItemTemplate>
<asp:Label ID="Label10" runat="server" Text='<%#Eval("emp_nm")
%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="emp_add">
<ItemTemplate>
<asp:Label ID="Label11" runat="server" Text='<%#Eval("emp_add")
%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="state">
<ItemTemplate>
<asp:Label ID="Label12" runat="server" Text='<%#Eval("state")
%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="city">
<ItemTemplate>
<asp:Label ID="Label13" runat="server" Text='<%#Eval("city")
%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="emp_email">
<ItemTemplate>
<asp:Label ID="Label14" runat="server" Text='<%#Eval("emp_email")
%>' ></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="emp_salary">
<ItemTemplate>
<asp:Label ID="Label15" runat="server" Text='<%#Eval("emp_salary")
%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="emp_nm">
<ItemTemplate>
<asp:Image ID="Image1" runat="server" Width="60px"
Height="60px"
ImageUrl='<%#Eval("emp_photo")
%>' />
<asp:Label ID="Label18" runat="server" Text='<%#Eval("emp_photo")
%>' Visible="false"></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="gender">
<ItemTemplate>
<asp:Label ID="Label16" runat="server" Text='<%#Eval("gender")
%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="hobby">
<ItemTemplate>
<asp:Label ID="Label17" runat="server" Text='<%#Eval("hobby")
%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="edit">
<ItemTemplate>
<asp:LinkButton ID="LinkButton1" runat="server" CommandName="edit" CausesValidation="False">edit</asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="delete">
<ItemTemplate>
<asp:LinkButton ID="LinkButton2" CommandName="delete" CausesValidation="false" runat="server">delete</asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Report">
<ItemTemplate>
<asp:ImageButton ID="ImageButton1" OnClick="select" CausesValidation="false" ImageUrl="~/image/face.jpeg" Height="30px"
Width="30px"
runat="server"
/>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server"
AutoDataBind="true"
/>
</div>
</form>
</body>
</html>
ASPX.CS FILE
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.IO;
using
System.Collections;
using
CrystalDecisions.CrystalReports.Engine;
using
CrystalDecisions.Shared;
public partial class _Default : System.Web.UI.Page
{
DataClassesDataContext
dc = new DataClassesDataContext();
protected void Page_Load(object
sender, EventArgs e)
{
if
(!IsPostBack)
{
Bind_ddlstate();
}
grid();
}
public void Bind_ddlstate()
{
var emps = from myemp
in dc.pi_statedemos
select
new
{
myemp.statename
};
DropDownList1.DataSource = emps;
DropDownList1.DataBind();
DropDownList1.Items.Insert(0, new ListItem("--select--"));
}
public void Bind_ddlCity()
{
int i =
DropDownList1.SelectedIndex;
DropDownList2.Items.Clear();
var
emps = from myemp in
dc.pi_citydemos
where
myemp.id == i
select
new
{
myemp.city
};
DropDownList2.DataSource = emps;
DropDownList2.DataBind();
}
public void grid()
{
emp_bal
empb = new emp_bal();
DataTable
dt = empb.select();
GridView1.DataSource = dt;
GridView1.DataBind();
}
protected void Button1_Click(object
sender, EventArgs e)
{
FileUpload1.SaveAs(Server.MapPath("~/image/") + FileUpload1.FileName);
string
s = "~/image/" +
FileUpload1.FileName;
string
g = null;
string
s1 = string.Empty;
if
(RadioButton1.Checked)
{
g = RadioButton1.Text;
}
else
{
g = RadioButton2.Text;
}
foreach
(ListItem item in
this.CheckBoxList1.Items)
{
if
(item.Selected)
{
s1 += item + ",";
}
}
emp_bal
empb = new emp_bal();
empb.insert(TextBox1.Text,
TextBox2.Text, DropDownList1.SelectedValue, DropDownList2.SelectedValue,
TextBox6.Text, int.Parse(TextBox7.Text), s, g,
s1);
grid();
clearedata();
}
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs
e)
{
Bind_ddlCity();
}
protected void GridView1_RowEditing(object
sender, GridViewEditEventArgs e)
{
Label
l9 = (Label)GridView1.Rows[e.NewEditIndex].FindControl("Label9");
Session["id"]
= l9.Text;
Label
l10 = (Label)GridView1.Rows[e.NewEditIndex].FindControl("Label10");
TextBox1.Text = l10.Text;
Label
l11 = (Label)GridView1.Rows[e.NewEditIndex].FindControl("Label11");
TextBox2.Text = l11.Text;
Label
l12 = (Label)GridView1.Rows[e.NewEditIndex].FindControl("Label12");
DropDownList1.SelectedValue = l12.Text;
Bind_ddlCity();
//Label l13 =
(Label)GridView1.Rows[e.NewEditIndex].FindControl("Label13");
Label
l14 = (Label)GridView1.Rows[e.NewEditIndex].FindControl("Label14");
TextBox6.Text = l14.Text;
Label
l15 = (Label)GridView1.Rows[e.NewEditIndex].FindControl("Label15");
TextBox7.Text = l15.Text;
Label
l18 = (Label)GridView1.Rows[e.NewEditIndex].FindControl("Label18");
Session["photos"]
= l18.Text;
Label
l16 = (Label)GridView1.Rows[e.NewEditIndex].FindControl("Label16");
if
(l16.Text.Equals("male"))
{
RadioButton1.Checked = true;
}
else
{
RadioButton2.Checked = true;
}
Label
l17 = (Label)GridView1.Rows[e.NewEditIndex].FindControl("Label17");
foreach
(ListItem li in
CheckBoxList1.Items)
{
if
(l17.Text.Contains(li.Text))
{
li.Selected = true;
}
}
grid();
Button2.Visible = true;
Button1.Visible = false;
Button3.Visible = false;
}
protected void Button2_Click(object
sender, EventArgs e)
{
emp_bal
empb = new emp_bal();
string
ph = null;
if
(FileUpload1.HasFile)
{
FileUpload1.SaveAs(Server.MapPath("~/image" + FileUpload1.FileName));
ph = "~/image"
+ FileUpload1.FileName;
}
else
{
ph = Session["photos"].ToString();
}
string
g = null;
if
(RadioButton1.Checked)
{
g = RadioButton1.Text;
}
else
{
g = RadioButton2.Text;
}
string
s1 = string.Empty;
foreach
(ListItem item in
this.CheckBoxList1.Items)
{
if
(item.Selected)
{
s1 += item + ",";
}
}
if
(empb.update(int.Parse(Session["id"].ToString()), TextBox1.Text,
TextBox2.Text, DropDownList1.SelectedValue, DropDownList2.SelectedValue,
TextBox6.Text, int.Parse(TextBox7.Text), ph, g,
s1) > 0)
{
Label19.Text = "good";
}
grid();
clearedata();
Button1.Visible = true;
Button2.Visible = false;
Button3.Visible = true;
}
protected void GridView1_RowDeleting(object
sender, GridViewDeleteEventArgs e)
{
emp_bal
empb = new emp_bal();
Label
l9 = (Label)GridView1.Rows[e.RowIndex].FindControl("Label9");
string
s = l9.Text;
if
(empb.delete(int.Parse(s)) > 0)
{
Label19.Text = "good one";
}
grid();
}
protected void select(object
sender, ImageClickEventArgs e)
{
ImageButton
imgs = sender as ImageButton;
GridViewRow
r = (GridViewRow)imgs.NamingContainer;
ReportDocument
rd = new ReportDocument();
rd.Load(Server.MapPath("emp_report.rpt"));
empdata
emp = new empdata();
Label
l10 = (Label)r.FindControl("Label10");
Label
l11 = (Label)r.FindControl("Label11");
Label
l12 = (Label)r.FindControl("Label12");
Label
l13 = (Label)r.FindControl("Label13");
Label
l14 = (Label)r.FindControl("Label14");
Label
l15 = (Label)r.FindControl("Label15");
Label
l18 = (Label)r.FindControl("Label18");
Label
l16 = (Label)r.FindControl("Label16");
Label
l17 = (Label)r.FindControl("Label17");
DataRow
dr = emp.emp_data.NewRow();
dr["emp_nm"]
= l10.Text;
dr["emp_add"]
= l11.Text;
dr["state"]
= l12.Text;
dr["city"]
= l13.Text;
dr["emp_email"]
= l14.Text;
dr["emp_salary"]
= l15.Text;
string
ie = l18.Text;
string
s = Server.MapPath(ie);
FileStream
fs = new FileStream(s,
FileMode.Open, FileAccess.Read);
Byte[]
imge = new byte[fs.Length];
fs.Read(imge, 0, System.Convert.ToInt32(fs.Length));
dr["emp_photo"]
= imge;
dr["gender"]
= l16.Text;
dr["hobby"]
= l17.Text;
emp.emp_data.Rows.Add(dr);
ReportDocument
report = new ReportDocument();
report.Load(Server.MapPath("emp_report.rpt"));
report.SetDataSource(emp);
CrystalReportViewer1.ReportSource =
report;
CrystalReportViewer1.RefreshReport();
}
public void clearedata()
{
TextBox1.Text = "";
TextBox2.Text = "";
TextBox6.Text = "";
TextBox7.Text = "";
DropDownList1.SelectedIndex = -1;
Bind_ddlCity();
RadioButton1.Checked = false;
RadioButton2.Checked = false;
CheckBoxList1.SelectedIndex = -1;
}
protected void Button3_Click(object
sender, EventArgs e)
{
clearedata();
}
protected void Button4_Click(object
sender, EventArgs e)
{
string
con = @"Data Source=SQLDB;Initial
Catalog=Demo;User ID=Demoh;Password=Demo1@";
SqlConnection
conn = new SqlConnection(con);
ReportDocument
rd = new ReportDocument();
rd.Load(Server.MapPath("emp_report.rpt"));
empdata
emp = new empdata();
conn.Open();
string
q = "select * from pi_emp";
SqlDataAdapter
adp = new SqlDataAdapter(q,
con);
DataTable
dt = new DataTable();
adp.Fill(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow
dr = emp.emp_data.NewRow();
dr["emp_nm"]
= dt.Rows[i]["emp_nm"].ToString();
dr["emp_add"]
= dt.Rows[i]["emp_add"].ToString();
dr["state"]
= dt.Rows[i]["state"].ToString();
dr["city"]
= dt.Rows[i]["city"].ToString();
dr["emp_email"]
= dt.Rows[i]["emp_email"].ToString();
dr["emp_salary"]
= dt.Rows[i]["emp_salary"].ToString();
string
ie = dt.Rows[i]["emp_photo"].ToString();
string
s = Server.MapPath(ie);
FileStream
fs = new FileStream(s,
FileMode.Open, FileAccess.Read);
Byte[]
imge = new byte[fs.Length];
fs.Read(imge, 0, System.Convert.ToInt32(fs.Length));
dr["emp_photo"]
= imge;
dr["gender"]
= dt.Rows[i]["gender"].ToString();
dr["hobby"]
= dt.Rows[i]["hobby"].ToString();
emp.emp_data.Rows.Add(dr);
}
rd.SetDataSource(emp);
CrystalReportViewer1.ReportSource = rd;
CrystalReportViewer1.RefreshReport();
}
}
BAL CLASS FILE
using System;
using
System.Collections.Generic;
using
System.Linq;
using
System.Web;
using
System.Data;
using
System.Data.SqlClient;
using System.IO;
using
System.Collections;
/// <summary>
/// Summary description for emp_bal
/// </summary>
public class emp_bal
{
public emp_bal()
{
//
// TODO: Add constructor logic
here
//
}
public void insert(string
emp_nm, string emp_add, string state, string city, string emp_email, int
emp_salary, string emp_photo, string gender, string
hobby)
{
emp_dal
empd = new emp_dal();
try
{
empd.insert(emp_nm, emp_add,
state, city, emp_email, emp_salary, emp_photo, gender, hobby);
}
catch
{
throw;
}
finally
{
empd = null;
}
}
public int update(int
emp_id, string emp_nm, string
emp_add, string state, string
city, string emp_email, int emp_salary, string emp_photo, string gender, string
hobby)
{
emp_dal
empd = new emp_dal();
try
{
return
empd.update(emp_id, emp_nm, emp_add, state, city, emp_email, emp_salary,
emp_photo, gender, hobby);
}
catch
{
throw;
}
finally
{
empd = null;
}
}
public int delete(int
emp_id)
{
emp_dal
empd = new emp_dal();
try
{
return
empd.delete(emp_id);
}
catch
{
throw;
}
finally
{
empd = null;
}
}
public DataTable select()
{
emp_dal
empd = new emp_dal();
try
{
return
empd.select();
}
catch
{
throw;
}
finally
{
empd = null;
}
}
}
DAL CLASS FILE
using System;
using
System.Collections.Generic;
using
System.Linq;
using
System.Web;
using
System.Data;
using
System.Data.SqlClient;
using System.IO;
using
System.Collections;
/// <summary>
/// Summary description for emp_dal
/// </summary>
public class emp_dal
{
public emp_dal()
{
//
// TODO: Add constructor logic
here
//
}
DataClassesDataContext
dc = new DataClassesDataContext();
public void insert(string
emp_nm, string emp_add, string state, string city, string emp_email, int
emp_salary, string emp_photo, string gender, string
hobby)
{
dc.pi_insert(emp_nm, emp_add, state,
city, emp_email, emp_salary, emp_photo, gender, hobby);
}
public int update(int
emp_id, string emp_nm, string
emp_add, string state, string
city, string emp_email, int emp_salary, string emp_photo, string gender, string
hobby)
{
return
dc.pi_update(emp_id, emp_nm, emp_add, state, city, emp_email, emp_salary,
emp_photo, gender, hobby);
}
public int delete(int
emp_id)
{
return
dc.pi_delete(emp_id);
}
public DataTable select()
{
var i =
dc.pi_selectdata();
DataTable
dt = new DataTable();
dt.Columns.Add("emp_id");
dt.Columns.Add("emp_nm");
dt.Columns.Add("emp_add");
dt.Columns.Add("state");
dt.Columns.Add("city");
dt.Columns.Add("emp_email");
dt.Columns.Add("emp_salary");
dt.Columns.Add("emp_photo");
dt.Columns.Add("gender");
dt.Columns.Add("hobby");
foreach(var r in i)
{
DataRow
data = dt.NewRow();
data["emp_id"]
= r.emp_id;
data["emp_nm"]
= r.emp_nm;
data["emp_add"]
= r.emp_add;
data["state"]
= r.state;
data["city"]
= r.city;
data["emp_email"]
= r.emp_email;
data["emp_salary"]
= r.emp_salary.ToString();
data["emp_photo"]
= r.emp_photo;
data["gender"]
= r.gender;
data["hobby"]
= r.hobby;
dt.Rows.Add(data);
}
return
dt;
}
}
REPORT VIEW
0 comments :