In this article I will explain with an example, how to implement Three Tier Architecture using Stored Procedure in ASP.Net using C#.
 
 
Database
I have made use of the following table Customers with the schema as follows.
Implement Three Tier Architecture using Stored Procedure in ASP.Net
 
I have already inserted few records in the table.
Implement Three Tier Architecture using Stored Procedure in ASP.Net
 
Note: You can download the database table SQL by clicking the download link below.
         Download SQL file
 
 
Stored Procedures
Customers_GetName
This Stored Procedure returns the Name of single Customer.
CREATE PROCEDURE [Customers_GetName]
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT [Name]
    FROM Customers
    WHERE [CustomerId]=@CustomerId
END
 
Customers_GetCustomer
This Stored Procedure returns the details of single Customer.
CREATE PROCEDURE [Customers_GetCustomer]
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT [CustomerId]
           ,[Name]
           ,[Country]
           ,[BirthDate]
    FROM Customers
    WHERE [CustomerId]=@CustomerId
END
 
Customers_GetCustomers
This Stored Procedure returns the details of multiple Customers.
CREATE PROCEDURE [Customers_GetCustomers]     
AS
BEGIN
    SET NOCOUNT ON;
    SELECT [CustomerId]
           ,[Name]
           ,[Country]
           ,[BirthDate]
    FROM Customers
END
 
 
What is Three Tier Architecture?
Three Tier Architecture means, dividing a project into 3 layers i.e. Data Access Layer, Business Layer and the UI (Front End) Layer.
Implement Three Tier Architecture using Stored Procedure in ASP.Net
 
The benefit of the Three Tier Architecture is that these tiers are developed and maintained independently.
So it will not impact the others in case of any modification.
It improves the performance and scalability of the application.
 
 
Data Transfer Object
This layer is used to define the properties. It is used to transfer data between itself and the front end layer.
It does not contain any logic and it is only used to hold data.
In order to add Data Transfer Object project, select File from the menu and click Add, then New Project.
Implement Three Tier Architecture using Stored Procedure in ASP.Net
 
From the Add a new project dialog window, select Class Library and click Next.
Implement Three Tier Architecture using Stored Procedure in ASP.Net
 
Then, give a suitable Project Name i.e. DTO and click Create.
Implement Three Tier Architecture using Stored Procedure in ASP.Net
 
Once the project is created, we need to now create the Data Transfer Object (DTO) class for each Table. For example, to handle operations of Customers Table we are creating Customer class.
The class will contain properties with names similar to the Customers Table. This is necessary as it will be used to load the appropriate fields in appropriate properties i.e. CustomerId will be loaded in CustomerId property.
namespace DTO
{
    ///<summary>
    /// Customer Data Transfer Object.
    ///</summary>
    public class Customer
    {
        public int CustomerId { get; set; }
        public string Name { get; set; }
        public string Country { get; set; }
        public DateTime BirthDate { get; set; }
        public int Age { get; set; }
    }
}
 
 
Data Access Layer (DAL)
Data Access Layer (DAL) is used to retrieve data from data store (database) separate from Business Layer (BL) and Front End (UI) layer.
Thus, if you have to change data stores, you don't have to rewriting the whole code again.
In order to add DAL, select File from the menu and click Add, then New Project.
Implement Three Tier Architecture using Stored Procedure in ASP.Net
 
From the Add a new project dialog window, select Class Library and click Next.
Implement Three Tier Architecture using Stored Procedure in ASP.Net
 
Then, give a suitable Project Name i.e. Data_Layer and click Create.
Implement Three Tier Architecture using Stored Procedure in ASP.Net
 
In order to use the DTO classes in the DAL project, you need to add the reference of DTO project to the DAL project. For that right click on the References, then click on Add Reference.
Implement Three Tier Architecture using Stored Procedure in ASP.Net
 
Now select the Projects node and check the DTO CheckBox and click OK.
Implement Three Tier Architecture using Stored Procedure in ASP.Net
 
Now we will create a new class in the DAL project i.e. Helper class, which will be used to keep all the necessary methods for Database operations.
Helper Class
The Connection String to the database will be fetched and stored in the ConString property.
Note: For more details on how to read Connection String from Web.Config file, please refer my article Read (Get) Connection String from Web.Config file in ASP.Net using C# and VB.Net.
 
The Helper Class consists of following methods.
Private methods
HasColumn: This method is used to check whether a Column exists in the DataRecord.
GetSingleObject: This method is used to read the Column value from SqlDataReader and set into the respective property.
Note: The name of the property in DTO classes and the Database Table must be exact same.
 
Public methods
The following methods are used by the Business Layer to perform the Database operations. Each of these methods accepts the following parameters.
sql – The query or the stored procedure name.
parameters – Default value null. The parameters to be passed to the query or stored procedure.
isStoredProc – Default value false. Value true means stored procedure.
 
Execute: Performs ExecuteNonQuery operation and returns rows affected.
GetScalar: Performs ExecuteScalar operation and returns single scalar value.
GetSingle: Performs ExecuteReader operation and returns single row i.e. single object.
GetList: Performs ExecuteReader operation and returns multiple rows i.e. List of objects.
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
 
namespace Data_Layer
{
    public static class Helper
    {
        //Connection string to the Database.
        private static string ConString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
 
        ///<summary>
        /// Gets Single DTO object.
        ///</summary>
        ///<typeparam name="T">Dynamic object.</typeparam>
        ///<param name="sql">SQL Query.</param>
        ///<param name="parameters">Parameter collection.</param>
        ///<param name="isStoredProc">Boolean value. True if Stored Procedure.</param>
        ///<returns>DTO object.</returns>
        public static T GetSingle<T>(this string sql, Dictionary<string, object> parameters = null, bool isStoredProc = false)
        {
            PropertyInfo[] info = typeof(T).GetProperties();
            object t = Activator.CreateInstance(typeof(T));
            using (SqlConnection con = new SqlConnection(Helper.ConString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    cmd.CommandType = isStoredProc ? CommandType.StoredProcedure : CommandType.Text;
                    if (parameters != null)
                    {
                        foreach (var p in parameters)
                        {
                            cmd.Parameters.AddWithValue(p.Key, p.Value);
                        }
                    }
                    con.Open();
                    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {
                        if (sdr.Read())
                        {
                            t = GetSingleObject<T>(sdr);
                        }
                        sdr.Close();
                    }
                    con.Close();
                }
            }
 
            return (T)t;
        }
 
        ///<summary>
        /// Gets List of DTO object.
        ///</summary>
        ///<typeparam name="T">Dynamic object.</typeparam>
        ///<param name="sql">SQL Query.</param>
        ///<param name="parameters">Parameter collection.</param>
        ///<param name="isStoredProc">Boolean value. True if Stored Procedure.</param>
        ///<returns>List of DTO objects.</returns>
        public static List<T> GetList<T>(this string sql, Dictionary<string, object> parameters = null, bool isStoredProc = false)
        {
            PropertyInfo[] info = typeof(T).GetProperties();
            List<T> t = new List<T>();
            using (SqlConnection con = new SqlConnection(Helper.ConString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    cmd.CommandType = isStoredProc ? CommandType.StoredProcedure : CommandType.Text;
                    if (parameters != null)
                    {
                        foreach (var p in parameters)
                        {
                            cmd.Parameters.AddWithValue(p.Key, p.Value);
                        }
                    }
 
                    con.Open();
                    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            t.Add(GetSingleObject<T>(sdr));
                        }
                        sdr.Close();
                    }
                    con.Close();
                }
            }
 
            return t;
        }
 
        ///<summary>
        /// Gets Single Scalar value.
        ///</summary>
        ///<param name="sql">SQL Query.</param>
        ///<param name="parameters">Parameter collection.</param>
        ///<param name="isStoredProc">Boolean value. True if Stored Procedure.</param>
        ///<returns>Scalar object.</returns>
        public static object GetScalar(this string sql, Dictionary<string, object> parameters = null, bool isStoredProc = false)
        {
            object t;
            using (SqlConnection con = new SqlConnection(Helper.ConString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    cmd.CommandType = isStoredProc ? CommandType.StoredProcedure : CommandType.Text;
                    if (parameters != null)
                    {
                        foreach (var p in parameters)
                        {
                            cmd.Parameters.AddWithValue(p.Key, p.Value);
                        }
                    }
                    con.Open();
                    t = cmd.ExecuteScalar();
                    con.Close();
                }
            }
 
            return t;
        }
 
        ///<summary>
        /// Executes query.
        ///</summary>
        ///<param name="sql">SQL Query.</param>
        ///<param name="parameters">Parameter collection.</param>
        ///<param name="isStoredProc">Boolean value. True if Stored Procedure.</param>
        ///<returns>Rows affected.</returns>
        public static int Execute(this string sql, Dictionary<string, object> parameters = null, bool isStoredProc = false)
        {
            int rowsAffected = 0;
            using (SqlConnection con = new SqlConnection(Helper.ConString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    cmd.CommandType = isStoredProc ? CommandType.StoredProcedure : CommandType.Text;
                    if (parameters != null)
                    {
                        foreach (var p in parameters)
                        {
                            cmd.Parameters.AddWithValue(p.Key, p.Value);
                        }
                    }
                    con.Open();
                    rowsAffected = cmd.ExecuteNonQuery();
                    con.Close();
                }
            }
 
            return rowsAffected;
        }
 
        ///<summary>
        /// Reads data from DataReader.
        ///</summary>
        ///<typeparam name="T">Dynamic object.</typeparam>
        ///<param name="sdr">SqlDataReader object</param>
        ///<returns>DTO object.</returns>
        private static T GetSingleObject<T>(this SqlDataReader sdr)
        {
            PropertyInfo[] info = typeof(T).GetProperties();
            object t = Activator.CreateInstance(typeof(T));
 
            foreach (PropertyInfo field in info)
            {
                if (sdr.HasColumn(field.Name))
                {
                    field.SetValue(t, sdr[field.Name]);
                }
            }
 
            return (T)t;
        }
 
        ///<summary>
        /// Checks whether Column present in DataRecord.
        ///</summary>
        ///<param name="dr">DataRcord object.</param>
        ///<param name="columnName">Column Name.</param>
        ///<returns>Boolean value.</returns>
        public static bool HasColumn(this IDataRecord dr, string columnName)
        {
            for (int i = 0; i < dr.FieldCount; i++)
            {
                if (dr.GetName(i).Equals(columnName, StringComparison.InvariantCultureIgnoreCase))
                {
                    return true;
                }
            }
            return false;
        }
    }
}
 
CustomerDL Class
Once the Helper class is complete, we need to now create the Data Layer (DL) class for each Table. For example, to handle operations of Customers Table we are creating CustomerDL class.
This Class consists of following methods.
GetCustomerName
This method accepts the CustomerId as parameter and uses Customers_GetName Stored Procedure.
Inside this method, the parameter is passed as Dictionary object to the GetScalar method.
Finally, the GetScalar method returns the Customer Name as scalar value.
 
GetCustomer
This method accepts the CustomerId as parameter and uses Customers_GetCustomer Stored Procedure.
Inside this method, the parameter is passed as Dictionary object to the GetSingle method.
Finally, the GetSingle method returns single Customer.
 
GetCustomers
Inside this method, the Customers_GetCustomers Stored Procedure is used and the GetList method is called.
Finally, the GetList method returns list of Customers.
using DTO;
using System;
using System.Collections.Generic;
 
namespace Data_Layer
{
    public class CustomerDL
    {
        ///<summary>
        /// Gets Customer Name.
        ///</summary>
        ///<param name="customerId">CustomerId object.</param>
        ///<returns>Customer Name.</returns>
        public static string GetCustomerName(int customerId)
        {
            string sql = "Customers_GetName";
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("@CustomerId", customerId);
            return sql.GetScalar(parameters, true).ToString();
        }
 
        ///<summary>
        /// Gets Customer based on Id.
        ///</summary>
        ///<param name="customerId">CustomerId object.</param>
        ///<returns>Customer object.</returns>
        public static Customer GetCustomer(int customerId)
        {
            string sql = "Customers_GetCustomer";
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("@CustomerId", customerId);
 
            Customer customer = sql.GetSingle<Customer>(parameters, true);
            return customer;
        }
 
        ///<summary>
        /// Gets all Customers.
        ///</summary>
        ///<returns>List of Customers.</returns>
        public static List<Customer> GetCustomers()
        {
            string sql = "Customers_GetCustomers";
            List<Customer> customers = sql.GetList<Customer>(isStoredProc: true);
            return customers;
        }
    }
}
 
 
Business Layer (BL)
The Business Layer (BL) act as an intermediate between the Front End Layer and the Data Access Layer.
This layer handles the business logic, business rules as well as calculations.
In order to add BL project, select File from the menu and click Add, then New Project.
Implement Three Tier Architecture using Stored Procedure in ASP.Net
 
From the Add a new project dialog window, select Class Library and click Next.
Implement Three Tier Architecture using Stored Procedure in ASP.Net
 
Then, give a suitable Project Name i.e. Business_Layer and click Create.
Implement Three Tier Architecture using Stored Procedure in ASP.Net
 
In order to use the DTO classes and Data_Layer classes in the BL project, you need to add their reference to the BL project by right clicking on the References in the Solution Explorer, then clicking on Add Reference.
Implement Three Tier Architecture using Stored Procedure in ASP.Net
 
Now select the Projects node and check the Data_Layer and DTO CheckBoxes and click OK.
Implement Three Tier Architecture using Stored Procedure in ASP.Net
 
CustomerBL
The following class inherits the ICustomerBL interface.
Inside this class, the implementation of all the methods are written.
Methods:– 
GetCustomerName – This method returns name of the Customer based on the CustomerId value.
GetCustomer – This method returns details of Customer based on the CustomerId value.
GetCustomers – This method returns multiple Customers.
Inside GetCustomers method, a FOR EACH loop is executed over the list collection and Age is calculated based on the Current Year and the Birth Year.
using Data_Layer;
using DTO;
using System.Collections.Generic;
 
namespace Business_Layer
{
    public class CustomerBL : ICustomerBL
    {
        ///<summary>
        /// Gets Customer Name.
        ///</summary>
        ///<param name="customerId">CustomerId object.</param>
        ///<returns>Customer Name.</returns>
        public string GetCustomerName(int customerId)
        {
            return CustomerDL.GetCustomerName(customerId);
        }
 
        ///<summary>
        /// Gets Customer based on Id.
        ///</summary>
        ///<param name="customerId">CustomerId object.</param>
        ///<returns>Customer object.</returns>
        public Customer GetCustomer(int customerId)
        {
            return CustomerDL.GetCustomer(customerId);
        }
 
        ///<summary>
        /// Gets all Customers.
        ///</summary>
        ///<returns>List of Customers.</returns>
        public List<Customer> GetCustomers()
        {
            List<Customer> customers = CustomerDL.GetCustomers();
 
            //Loop and calculate Age from Birth Date.
            foreach (Customer customer in customers)
            {
                customer.Age = (DateTime.Today.Year - customer.BirthDate.Year);
            }
 
            return customers;
        }
    }
}
 
Now we will create a new interface in the BL project i.e. ICustomerBL class.
The interface implements following methods of the CustomerBL class.
GetCustomerName
GetCustomer
GetCustomers
using DTO;
using System.Collections.Generic;
 
namespace Business_Layer
{
    public interface ICustomerBL
    {
        ///<summary>
        /// Gets Customer Name.
        ///</summary>
        ///<param name="customerId">CustomerId object.</param>
        ///<returns>Customer Name.</returns>
        string GetCustomerName(int customerId);
 
        ///<summary>
        /// Gets Customer based on Id.
        ///</summary>
        ///<param name="customerId">CustomerId object.</param>
        ///<returns>Customer object.</returns>
        Customer GetCustomer(int customerId);
 
        ///<summary>
        /// Gets all Customers.
        ///</summary>
        ///<returns>List of Customers.</returns>
        List<Customer> GetCustomers();
    }
}
 
 
Front End Layer
Front End (UI) Layer will be a Website project.
In order to add FrontEnd Layer, select File from the menu and click Add, then New Project.
Implement Three Tier Architecture using Stored Procedure in ASP.Net
 
From the Add a new project dialog window, select ASP.Net Empty Web Site and click Next.
Implement Three Tier Architecture using Stored Procedure in ASP.Net
 
Then, give a suitable Project Name i.e. Frontend_Layer and click Create.
Implement Three Tier Architecture using Stored Procedure in ASP.Net
 
In order to use the DTO and BL classes in the UI project, you need to add the reference of DTO and BL projects to the UI project. For that right click on the References, then click on Add Reference.
Implement Three Tier Architecture using Stored Procedure in ASP.Net
 
Now select the Projects node and check the DTO and Business_Layer and click OK.
Implement Three Tier Architecture using Stored Procedure in ASP.Net
 
Now, you will see DLL reference has been added to the project inside the Bin folder.
Implement Three Tier Architecture using Stored Procedure in ASP.Net
 
Finally, add a Web Form to your project by right clicking on the project, then clicking on Add and select Add New Item.
Then, in the Add New Item dialog window select Web Form and give a suitable name and click on Add.
Implement Three Tier Architecture using Stored Procedure in ASP.Net
 
 
Namespaces
You will need to inherit the following namespaces.
using Business_Layer;
using DTO;
 
 
Code
Inside the Page_Load event handler of page, an object of CustomerBL is created and the GetCustomerName, GetCustomer and GetCustomers methods are called.
protected void Page_Load(object sender, EventArgs e)
{
    ICustomerBL customerBL = new CustomerBL();
 
    //Get single Customer Name.
    string name = customerBL.GetCustomerName(2);
 
    //Get single Customer data.
    Customer customer = customerBL.GetCustomer(2);
 
    //Get multiple Customers data.
    List<Customer> customers = customerBL.GetCustomers();
}
 
 
Screenshots
Returning Customer Name
Implement Three Tier Architecture using Stored Procedure in ASP.Net
 
Returning single Customer
Implement Three Tier Architecture using Stored Procedure in ASP.Net
 
Returning multiple Customers
Implement Three Tier Architecture using Stored Procedure in ASP.Net
 
 
Downloads
Download Code