Using Three Tier Architecture Asp.Net C# with
Sql Server 2008 Connected Mode
·
DAL(Data
Access Layer)
·
BAL(Business
Access Layer)
·
PAL(Presentation
Access Layer)
//execute this
query in Sql Server 2008
use
test
create table CollegeDetails (RollNo int primary key,Name nvarchar(50),Gender nvarchar(50),Course nvarchar(50),EmailId nvarchar(50),Address nvarchar(max),CurrentCity nvarchar(50))
//create dal class in our website
(data access layer)….
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
//dal= data acess layer
public class dal
{
SqlConnection con = new SqlConnection();
SqlCommand cmd = new SqlCommand();
SqlDataReader dr;
public dal()
{
con.ConnectionString
= "Data Source=KUSH-PC\\KUSH;Initial Catalog=test;Integrated
Security=True";
}
public int
query_table(string s)
{
cmd.Connection
= con;
cmd.CommandText
= s;
con.Open();
int i = cmd.ExecuteNonQuery();
con.Close();
return i;
}
public SqlDataReader
fetchdata(string f)
{
cmd.Connection
= con;
cmd.CommandText
= f;
con.Open();
return
cmd.ExecuteReader();
// con.Close();
}
}
//create bll class in our website
(business logic layer)….
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
public class bll
{
public bll()
{
}
dal d = new dal();
SqlDataReader dr;
public int insert(int rollno, string
name, string gender, string
course, string emailid,string
addresss, string currentcity)
{
string s = "insert
into CollegeDetails values ('"+rollno + "','"
+ name + "','"+gender+"','" + course + "','" + emailid + "','"
+ addresss + "','" + currentcity +
"')";
int j= d.query_table(s);
return j;
}
public int
DeleteWithRollNo(int rollno)
{
string s = "delete
from CollegeDetails where rollno=('" + rollno + "')";
int j = d.query_table(s);
return j;
}
public SqlDataReader
SelectWithRollNo(int rollno)
{
string f = "Select *
from CollegeDetails where rollno=('" + rollno + "')";
dr=
d.fetchdata(f);
return dr;
}
public int UpdateData(int rollno, string
name, string gender, string
course, string emailid, string addresss, string
currentcity)
{
string s = "update
CollegeDetails set Name='" + name + "',Gender='"
+ gender + "',Course='" + course +
"',EmailId='" + emailid + "',Address='" + addresss + "',CurrentCity='" + currentcity + "' where rollno='"+rollno+"'";
int j = d.query_table(s);
return j;
}
public SqlDataReader
SelectAll()
{
string f = "Select *
from CollegeDetails";
dr =
d.fetchdata(f);
return dr;
}
}
****************************************************************************
//code for (presention layer)….
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Drawing;
using System.Data.SqlClient;
public partial class _Default :
System.Web.UI.Page
{
bll b= new bll();
SqlDataReader dr;
protected void Page_Load(object sender, EventArgs
e)
{
if (!IsPostBack)
{
GridViewBind();
}
}
protected void
btnSave_Click(object sender, EventArgs e)
{
try
{
int i = b.insert(Convert.ToInt32(txtrollno.Text),
txtname.Text, RbGender.SelectedItem.Text, ddlcourse.SelectedItem.Text,
txtemailid.Text, txtaddress.Text, ddlcityname.SelectedValue);
if (i > 0)
{
lblmessage.Visible
= true;
lblmessage.ForeColor
= Color.Green;
lblmessage.Text
= "Add sucessfully";
GridViewBind();
}
else
{
lblmessage.Visible
= true;
lblmessage.ForeColor
= Color.Red;
lblmessage.Text
= "Some Problem with your code...";
}
}
catch (Exception
ex)
{
lblmessage.Visible
= true;
lblmessage.ForeColor
= Color.Red;
lblmessage.Text
= ex.Message;
}
}
protected void
btnDelete_Click(object sender, EventArgs e)
{
int i=b.DeleteWithRollNo(Convert.ToInt32(txtrollno.Text));
if (i > 0)
{
lblmessage.Visible
= true;
lblmessage.ForeColor
= Color.Green;
lblmessage.Text
= "delete sucessfully";
GridViewBind();
}
else
{
lblmessage.Visible
= true;
lblmessage.ForeColor
= Color.Red;
lblmessage.Text
= "Some Problem with your code...";
}
}
protected void
btnsearch_Click(object sender, EventArgs e)
{
dr =
b.SelectWithRollNo(Convert.ToInt32(txtrollno.Text));
if (dr.Read())
{
txtname.Text
= "" + dr[1];
txtemailid.Text
= "" + dr["EmailId"];
txtaddress.Text
= dr["Address"].ToString();
ddlcourse.SelectedItem.Text
= dr["Course"].ToString();
RbGender.SelectedValue
= "" + dr["Gender"];
ddlcityname.SelectedValue
= dr["CurrentCity"].ToString();
dr.Close();
}
else
{
lblmessage.Visible
= true;
lblmessage.ForeColor
= Color.Red;
lblmessage.Text
= "There is no data with this rollno";
}
}
protected void
btnUpdate_Click(object sender, EventArgs e)
{
int i = b.UpdateData(Convert.ToInt32(txtrollno.Text),
txtname.Text, RbGender.SelectedItem.Text, ddlcourse.SelectedItem.Text,
txtemailid.Text, txtaddress.Text, ddlcityname.SelectedValue);
if (i > 0)
{
lblmessage.Visible
= true;
lblmessage.ForeColor
= Color.Green;
lblmessage.Text
= "Udate Successfully "+txtrollno.Text+"";
GridViewBind();
}
else
{
lblmessage.Visible
= true;
lblmessage.ForeColor
= Color.Red;
lblmessage.Text
= "Some Problem with your code...";
}
}
private void
GridViewBind()
{
dr =
b.SelectAll();
GridView1.DataSource
= dr;
GridView1.DataBind();
dr.Close();
}
}
//this source code for Default.aspx
%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!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>
<form id="form1" runat="server">
<table width="50%">
<tr><td colspan="2"><center><font color="red"><b><i>Using Three Tier Architecture Asp.Net with Sql
Server 2008</i></b></font></center></td></tr>
<tr><td>RollNo</td><td>
<asp:TextBox ID="txtrollno"
runat="server"></asp:TextBox>
</td></tr>
<tr><td>Name</td><td>
<asp:TextBox ID="txtname"
runat="server"></asp:TextBox>
</td></tr>
<tr><td>Gender</td><td>
<asp:RadioButtonList ID="RbGender"
runat="server"
RepeatDirection="Horizontal">
<asp:ListItem>Male</asp:ListItem>
<asp:ListItem>Female</asp:ListItem>
</asp:RadioButtonList>
</td></tr>
<tr><td>Course</td><td>
<asp:DropDownList ID="ddlcourse"
runat="server">
<asp:ListItem>--Select--</asp:ListItem>
<asp:ListItem>B. Tech</asp:ListItem>
<asp:ListItem>Mca</asp:ListItem>
<asp:ListItem>Mba</asp:ListItem>
</asp:DropDownList>
</td></tr>
<tr><td>EmailId</td><td>
<asp:TextBox ID="txtemailid"
runat="server"></asp:TextBox>
</td></tr>
<tr><td>Address</td><td>
<asp:TextBox ID="txtaddress"
runat="server"></asp:TextBox>
</td></tr>
<tr><td>Current City</td><td>
<asp:DropDownList ID="ddlcityname"
runat="server">
<asp:ListItem>--Select--</asp:ListItem>
<asp:ListItem>G B Nagar</asp:ListItem>
<asp:ListItem>New Delhi</asp:ListItem>
<asp:ListItem>Varanasi</asp:ListItem>
</asp:DropDownList>
</td></tr>
<tr><td colspan="2"><center>
<asp:Button ID="btnSave"
runat="server"
onclick="btnSave_Click"
Text="Save"
/>
<asp:Button ID="btnDelete"
runat="server"
onclick="btnDelete_Click"
Text="Delete" />
<asp:Button ID="btnsearch"
runat="server"
onclick="btnsearch_Click"
Text="Search" />
<asp:Button ID="btnUpdate"
runat="server"
onclick="btnUpdate_Click"
Text="Update" />
</center>
</td></tr>
<tr><td colspan="2">
<center><asp:Label ID="lblmessage"
runat="server"
Visible="False"></asp:Label></center>
</td></tr>
<tr><td colspan="2">
<asp:GridView ID="GridView1"
runat="server">
</asp:GridView>
</td></tr>
</table>
</form>
</body>
</html>
0 comments:
Post a Comment