How N-Tier Architecture Application are made, using Stored Procedure in Database Layer

Hi All,
In this article i will be explaining how n-tier Architecture Applications are made. There are 3 Layers  typical in any application. They are

1. Presentation Layer: Presentation layer is basically the front end forms which is used by the end users
2. Business Logic Layer: This layer consists of business logic like transactional operations
3. Database Layer :
   Consists of data and data related programming like triggers, stored procedures & functions for complex sql transactions

Making these three layers independent makes you the deal with these layers independently like any changes required in stored procedure logic can be done without disturbing other layers. Further application can be developed for other platform like from web to mobile application, without disturbing database layer and business logic layers


To demonstrate i will be making small application using ASP.NET with C# & SQL server where add the State names in the database using a web form. I have made a database with name myDB in SQL server with a table “States” shown below.



I have made a sql stored procedure for inserting the record in the database to make database layer bulky.
CREATE PROCEDURE DBO.USP_INSERT_INTO_States
(
@STATEID CHAR(5)
,@STATE VARCHAR(25)
)
AS BEGIN
INSERT INTO States(
[STATEID]
,[STATE]
)
VALUES
(
@STATEID
,@STATE
)
END

Now we will create a business layer, here business will be adding new state, this transaction in the database will be done using the stored procedure, so in next step we will be making class called StateBL for handling the State (table) related transaction and we will add business method AddNewState() for adding new state. This business layers normally made as a web service, in java EJB, .NET remoting in .NET for global access.  For now we will be making ASP.NET website and we will add business class to this website i.e. from website menu-> Add new item. This class will be our Business layer in our application, coding is given below

public class StateBL
{
    //declare the objects for database operation
    SqlCommand cmd;
    SqlConnection con;

   //represents the exception occurred during the sql transaction
    public Exception exception=null;
  
    // Initialize the connnection and command object in constructor
    public StateBL()
    {
       con=new SqlConnection("Data Source=RAJU-PC\\SQLEXPRESS;Initial Catalog=myDB;Integrated Security=True;");    
       cmd=new SqlCommand();
       cmd.CommandType=CommandType.StoredProcedure;
    }

    // Used for adding State
    public Boolean AddNewState(string StateID, String State)
    {
        try
        {
            cmd.CommandText="DBO.USP_INSERT_INTO_STATES";
            cmd.Parameters.AddWithValue("@STATEID",StateID);
            cmd.Parameters.AddWithValue("@STATEID",State);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
            return true; //represents successful transaction
        }
        catch(Exception ex)
        {
            exception=ex;
            return false; //represents sql operation failed
        }
    }
}

Now we will create a web form i.e. our presentation layer to add record into the table through business layer



Coding for save button click is given below

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class _Default : System.Web.UI.Page
{
    protected void btnSave_Click(object sender, EventArgs e)
    {
        StateBL objState = new StateBL();
        if (objState.AddNewState(txtStateID.Text, txtStateName.Text)) //check the return value of the AddNewState
        {
            //display the message on success
            lbMsg.Text = "State added successfully...";
        }
        else
        {
            //display the error message from exception object in the StateBL class
            lbMsg.Text = "Unable to add state, error:" + objState.exception.Message;
        }
    }
}

Here is the output of the program, first output is showing the error message due to connection string problem and second shows the successful addition of the record in the table.




Conclusion:
We have made three layers of the application
Database Layer: with data tables, stored procedure
Business Layers: having business class with business method for adding new state
Presentation Layer: we have a form for data entry

If i make business layer as web service and consider in future i need to develop the same application for mobile platform then what i need to develop, only the presentation for mobile platform. isn’t it?


I hope you find this article helpful, if you liked it please do not forget to add your feedback below

download source code of above example

Thanks & Regards
l¯¯l)¯¯)
l__l\__\aju
.      l¯¯l
(¯¯(/_  /etla