ADO Net Database ASP.Net Tutorial

  1. Home
  2. ASP.Net Tutorial
  3. ADO Net Database
  4. Define data layer in a separate class
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs"  Inherits="ComponentTest" %>



    Untitled Page


    
    

    

Employees


      
    

    


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 += "
Inserted 1 employee.
";
    WriteEmployeesList();
    db.DeleteEmployee(empID);
    HtmlContent.Text += "
Deleted 1 employee.
";
    WriteEmployeesList();
  }
  private void WriteEmployeesList()
  {
    StringBuilder htmlStr = new StringBuilder("");
    int numEmployees = db.CountEmployees();
    htmlStr.Append("
Total employees: ");
    htmlStr.Append(numEmployees.ToString());
    htmlStr.Append("

");
    EmployeeDetails[] employees = db.GetEmployees();
    foreach (EmployeeDetails emp in employees)
    {
      htmlStr.Append("
  • ");
          htmlStr.Append(emp.EmployeeID);
          htmlStr.Append(" ");
          htmlStr.Append(emp.TitleOfCourtesy);
          htmlStr.Append(" ");
          htmlStr.Append(emp.FirstName);
          htmlStr.Append("    , ");
          htmlStr.Append(emp.LastName);
          htmlStr.Append("
    • ");
        }
        htmlStr.Append("
    ");
        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