When creating applications such as ASP.NET applications, often times the application is split into various conceptual or physical layers. There are typically three layers in such a scheme and they may generally be broken out as the presentation layer, the business logic layer, and the data access layer. Depending on your specific needs, standards, etc. sometimes the layers may be folded into a single layer. For example, you may choose to implement your business logic using stored procedures and co-locate the business logic layer with the data access layer in the database.
There are plusses and minuses (or trade-offs) associated with the architectural decisions for an application and I don't intend to propose any one being better than another. Instead, I would like to illustrate a simple method of creating a presentation layer and a data access layer using ObjectDataSource controls, Ref Cursors, and custom classes. There is no business logic layer in this simple example as it would merely be a proxy or wrapper of the data access layer. The data access layer returns collections of custom classes to the presentation layer using the .NET Framework 2.0 System.Collections.Generic.List generic type.
The sample uses a simple ASP.NET page:
In order to provide the data access layer with data from the database the following PL/SQL Package and Package Body should be created as the HR user in the database you plan to use:
create or replace package human_resources as
procedure get_departments (p_departments out sys_refcursor);
procedure get_department_employees(p_department_id in employees.department_id%type,
p_employees out sys_refcursor);
end;
/
create or replace package body human_resources as
procedure get_departments (p_departments out sys_refcursor) is
begin
open p_departments for
select department_id,
department_name
from departments
order by department_name;
end;
procedure get_department_employees(p_department_id in employees.department_id%type,
p_employees out sys_refcursor) is
begin
open p_employees for
select employee_id,
first_name,
last_name
from employees
where department_id = p_department_id
order by last_name,
first_name;
end;
end;
/
This sample uses a single ASP.NET web page (default.aspx) and three C# code files:
Department.cs -- the department class in the .NET code (get_departments procedure)
Employee.cs -- the employee class in the .NET code (get_department_employees procedure)
EmployeeDAL.cs -- the data access layer. This code accesses the database and returns the collection of either Department or Employee objects to the presentation layer.
The components that make up the Visual Studio Solution are as follows in the Solution Explorer:
As with the PL/SQL code in the database, the C# code is simple and omits many normal features such as error handling. In addition, the classes only implement "get" functionality for the class properties in order to keep the code as short and as simple as possible.
The Department class has DepartmentID and DepartmentName properties and is as follows:
public class Department { private int _department_id;
private string _department_name;
public int DepartmentID { get { return _department_id;
}
}
public string DepartmentName { get { return _department_name;
}
}
public Department(int DepartmentID, string DepartmentName) { _department_id = DepartmentID;
_department_name = DepartmentName;
}
}
The Employee class exposes EmployeeID, FirstName, and LastName properties:
public class Employee { private int _employee_id;
private string _first_name;
private string _last_name;
public int EmployeeID { get { return _employee_id;
}
}
public string FirstName { get { return _first_name;
}
}
public string LastName { get { return _last_name;
}
}
public Employee(int EmployeeID, string FirstName, string LastName) { _employee_id = EmployeeID;
_first_name = FirstName;
_last_name = LastName;
}
}
The EmployeesDAL class handles connecting to the database, executing the stored procedures, building the collection of objects, and returning those objects to the caller:
public class EmployeesDAL { private static string constr = "User Id=hr; Password=hr; Data Source=lt10gr2; Enlist=false";
public List<Department> GetDepartments()
{ OracleConnection con = new OracleConnection(constr);
con.Open();
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "human_resources.get_departments";
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter p_ref_cur = new OracleParameter();
p_ref_cur.OracleDbType = OracleDbType.RefCursor;
p_ref_cur.Direction = ParameterDirection.Output;
cmd.Parameters.Add(p_ref_cur);
OracleDataReader dr = cmd.ExecuteReader();
List<Department> Departments = new List<Department>();
while (dr.Read())
{ Department thisDept = new Department(dr.GetInt32(0), dr.GetString(1));
Departments.Add(thisDept);
}
dr.Dispose();
cmd.Dispose();
con.Dispose();
return Departments;
}
public List<Employee> GetDepartmentEmployees(int p_department_id) { OracleConnection con = new OracleConnection(constr);
con.Open();
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "human_resources.get_department_employees";
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter p_dept_id = new OracleParameter();
p_dept_id.OracleDbType = OracleDbType.Decimal;
p_dept_id.Value = p_department_id;
OracleParameter p_ref_cur = new OracleParameter();
p_ref_cur.OracleDbType = OracleDbType.RefCursor;
p_ref_cur.Direction = ParameterDirection.Output;
cmd.Parameters.Add(p_dept_id);
cmd.Parameters.Add(p_ref_cur);
OracleDataReader dr = cmd.ExecuteReader();
List<Employee> Employees = new List<Employee>();
while (dr.Read()) { Employee thisEmp = new Employee(dr.GetInt32(0), dr.GetString(1), dr.GetString(2));
Employees.Add(thisEmp);
}
p_dept_id.Dispose();
p_ref_cur.Dispose();
dr.Dispose();
cmd.Dispose();
con.Dispose();
return Employees;
}
}
The ASP.NET page consists of simple text to denote the Department data which is presented in a DropDownList, a GridView control to display the employees in the selected department, and two ObjectDataSource controls. This is the Default.aspx page at design time:
To configure the ObjectDataSource1 control select "Configure Data Source..." from the quick tasks configuration:
Choose a business object:
Click Next and then select the GetDeparments method for the SELECT tab and click Finish:
Next, configure the DropDownList1 control which displays the Department information. Ensure that the EnableAutoPostBack option is checked in the quick task configuration or that the AutoPostBack property is True in the Properties window. To configure the Data Source for the control, select "Choose Data Source..." from the quick task configuration and complete as follows:
The ObjectDataSource2 control should be configured next by clicking the "Configure Data Source..." option on the quick tasks configuration:
Again, choose a Business Object:
Followed by the GetDepartmentEmployees method for the SELECT tab:
The GetDepartmentEmployees method takes an input parameter that specifies the Department ID for which employees should be retrieved. Therefore, it is necessary to configure the parameter input value so that the Department ID selected by the user in the DropDownList1 control is passed to the GetDepartmentEmployees method:
Finally, associate the ObjectDataSource2 control with the GridView1 control by choosing "ObjectDataSource2" in the "Choose Data Source" drop-down list for the GridView quick tasks. Of course you may also choose to enable paging and formatting as you desire.
Now that both the DropDownList1 and the GridView1 web controls are "hooked up" to their respective ObjectDataSource controls, and the ObjectDataSource controls are, in turn, configured to expose the data returned by the EmployeesDAL class, it is time to run the sample page!
