Mega Code Archive
Define data layer in a separate class
<![CDATA[
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="ComponentTest" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<h2>Employees</h2>
<asp:Literal runat="server" ID="HtmlContent" />
</div>
</form>
</body>
</html>
File: Default.aspx.cs
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;
using System.Data.SqlClient;
using System.Web.Configuration;
public partial class ComponentTest : System.Web.UI.Page
{
private EmployeeDB db = new EmployeeDB();
protected void Page_Load(object sender, System.EventArgs e)
{
WriteEmployeesList();
int empID = db.InsertEmployee(
new EmployeeDetails(0, "Mr.", "Bellinaso", "Marco"));
HtmlContent.Text += "<br>Inserted 1 employee.<br>";
WriteEmployeesList();
db.DeleteEmployee(empID);
HtmlContent.Text += "<br>Deleted 1 employee.<br>";
WriteEmployeesList();
}
private void WriteEmployeesList()
{
StringBuilder htmlStr = new StringBuilder("");
int numEmployees = db.CountEmployees();
htmlStr.Append("<br>Total employees: <b>");
htmlStr.Append(numEmployees.ToString());
htmlStr.Append("</b><br><br>");
EmployeeDetails[] employees = db.GetEmployees();
foreach (EmployeeDetails emp in employees)
{
htmlStr.Append("<li>");
htmlStr.Append(emp.EmployeeID);
htmlStr.Append(" ");
htmlStr.Append(emp.TitleOfCourtesy);
htmlStr.Append(" <b>");
htmlStr.Append(emp.FirstName);
htmlStr.Append("</b>, ");
htmlStr.Append(emp.LastName);
htmlStr.Append("</li>");
}
htmlStr.Append("<br>");
HtmlContent.Text += htmlStr.ToString();
}
}
public class EmployeeDetails
{
private int employeeID;
private string firstName;
private string lastName;
private string titleOfCourtesy;
public int EmployeeID
{
get {return employeeID;}
set {employeeID = value;}
}
public string FirstName
{
get {return firstName;}
set {firstName = value;}
}
public string LastName
{
get {return lastName;}
set {lastName = value;}
}
public string TitleOfCourtesy
{
get {return titleOfCourtesy;}
set {titleOfCourtesy = value;}
}
public EmployeeDetails(int employeeID, string firstName, string lastName,
string titleOfCourtesy)
{
this.employeeID = employeeID;
this.firstName = firstName;
this.lastName = lastName;
this.titleOfCourtesy = titleOfCourtesy;
}
public EmployeeDetails(){}
}
public class EmployeeDB
{
private string connectionString;
public EmployeeDB()
{
connectionString = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
}
public EmployeeDB(string connectionString)
{
this.connectionString = connectionString;
}
public int InsertEmployee(EmployeeDetails emp)
{
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("InsertEmployee", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
cmd.Parameters["@FirstName"].Value = emp.FirstName;
cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
cmd.Parameters["@LastName"].Value = emp.LastName;
cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25));
cmd.Parameters["@TitleOfCourtesy"].Value = emp.TitleOfCourtesy;
cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
cmd.Parameters["@EmployeeID"].Direction = ParameterDirection.Output;
try
{
con.Open();
cmd.ExecuteNonQuery();
return (int)cmd.Parameters["@EmployeeID"].Value;
}
catch (SqlException err)
{
throw new ApplicationException("Data error.");
}
finally
{
con.Close();
}
}
public void UpdateEmployee(EmployeeDetails emp)
{
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("UpdateEmployee", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
cmd.Parameters["@FirstName"].Value = emp.FirstName;
cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
cmd.Parameters["@LastName"].Value = emp.LastName;
cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25));
cmd.Parameters["@TitleOfCourtesy"].Value = emp.TitleOfCourtesy;
cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
cmd.Parameters["@EmployeeID"].Value = emp.EmployeeID;
try
{
con.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException err)
{
throw new ApplicationException("Data error.");
}
finally
{
con.Close();
}
}
public void UpdateEmployee(int EmployeeID, string firstName, string lastName, string titleOfCourtesy)
{
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("UpdateEmployee", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
cmd.Parameters["@FirstName"].Value = firstName;
cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
cmd.Parameters["@LastName"].Value = lastName;
cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25));
cmd.Parameters["@TitleOfCourtesy"].Value = titleOfCourtesy;
cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
cmd.Parameters["@EmployeeID"].Value = EmployeeID;
try
{
con.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException err)
{
throw new ApplicationException("Data error.");
}
finally
{
con.Close();
}
}
public void DeleteEmployee(int employeeID)
{
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("DeleteEmployee", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
cmd.Parameters["@EmployeeID"].Value = employeeID;
try
{
con.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException err)
{
throw new ApplicationException("Data error.");
}
finally
{
con.Close();
}
}
public EmployeeDetails GetEmployee(int employeeID)
{
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("GetEmployee", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
cmd.Parameters["@EmployeeID"].Value = employeeID;
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
reader.Read();
EmployeeDetails emp = new EmployeeDetails(
(int)reader["EmployeeID"], (string)reader["FirstName"],
(string)reader["LastName"], (string)reader["TitleOfCourtesy"]);
reader.Close();
return emp;
}
catch (SqlException err)
{
throw new ApplicationException("Data error.");
}
finally
{
con.Close();
}
}
public EmployeeDetails[] GetEmployees()
{
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("GetAllEmployees", con);
cmd.CommandType = CommandType.StoredProcedure;
ArrayList employees = new ArrayList();
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
EmployeeDetails emp = new EmployeeDetails(
(int)reader["EmployeeID"], (string)reader["FirstName"],
(string)reader["LastName"], (string)reader["TitleOfCourtesy"]);
employees.Add(emp);
}
reader.Close();
return (EmployeeDetails[])employees.ToArray(typeof(EmployeeDetails));
}
catch (SqlException err)
{
throw new ApplicationException("Data error.");
}
finally
{
con.Close();
}
}
public int CountEmployees()
{
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("CountEmployees", con);
cmd.CommandType = CommandType.StoredProcedure;
try
{
con.Open();
return (int)cmd.ExecuteScalar();
}
catch (SqlException err)
{
throw new ApplicationException("Data error.");
}
finally
{
con.Close();
}
}
}
File: Web.config
<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
<connectionStrings>
<add name="Northwind" connectionString= "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI"/>
</connectionStrings>
<system.web>
<compilation debug="true"/>
<authentication mode="Windows"/>
</system.web>
</configuration>
]]>