Vishwamohan

Welcome to Vishwa's blog - Technology, Spirituality and More...

Developing 3 Tier Application in .NET - Part 1

This is an example for creating a 3-tier web application. You can add additional tier to the base design but a traditional 3-tier app mainly refers the following:

1. Database Access Layer (DAL)
2. Business Logic Layer (BLL)
3. User Interface Layer (UIL)

I will take a very simple example of a customer table which contains only three columns: Customer ID (auto generated), Customer Name (max 50 characters) and Customer address (max 100 characters).

So before I write any DAL code in Visual Studio.NET 2005, I should have a database containing the customer table, and four stored procedures for CRUD (Create, Read, Update and Delete) operations. You can use inline SQL statements for the same operation but I prefer to use Stored procedures.

Table Name: tbl_Customer
Stored Procedures:

1. Usp_GetCustomer - Gets one customer record
2. Usp_GetCustomers – Retrieves all existing customers
3. Usp_InsertCustomer – Adds a new customer
4. Usp_UpdateCustomer – Updates a customer record
5. Usp_DeleteCustomer – Deletes a customer record. 

 
 
CREATE TABLE [dbo].[tbl_Customer](
 
 [Cust_ID] [int] IDENTITY(1,1) NOT NULL,
 
 [Cust_Name] [varchar](50) NOT NULL,
 
 [Cust_DOB] [datetime] NOT NULL,
 
 [Cust_Address] [varchar](100) NOT NULL,
 
 [Date_Created] [datetime] NOT NULL CONSTRAINT [DF_tbl_Customer_Date_Created]  DEFAULT (getdate()),
 
 [Date_Modified] [datetime] NOT NULL CONSTRAINT [DF_tbl_Customer_Date_Modified]  DEFAULT (getdate())
 
) ON [PRIMARY]
 
 
 
GO
 
 
 
ALTER TABLE [tbl_Customer]
 
ADD CONSTRAINT [PK_tbl_Customer] PRIMARY KEY
 
CLUSTERED ([Cust_ID])ON [PRIMARY]
 
GO
 
 
 
 
 
 
 
CREATE PROCEDURE [dbo].[Usp_GetCustomer]
 
(
 
@CustID Int
 
)
 
AS
 
 
 
/**************************************
 
* PROCEDURE:     Usp_GetCustomer
 
* PURPOSE:       Get a Customer Record
 
* AUTHOR:  Vishwa Mohan
 
* Date Created   10/15/2006 
 
* NOTES:   
 
********************************
 
* MODIFICATION LOG
 
* DATE     AUTHOR     DESCRIPTION
 
*----------------------------------
 
* 
 
********************************/
 
 SELECT Cust_ID, Cust_Name, Cust_DOB,Cust_Address, Date_Created,Date_Modified  
 
 FROM tbl_Customer(NOLOCK) WHERE Cust_ID=@CustID
 
 
 
GO
 
 
 
CREATE PROCEDURE [dbo].[Usp_GetCustomers]
 
AS
 
 
 
/***********************************
 
* PROCEDURE:     Usp_GetCustomers
 
* PURPOSE:       Get All Customer Record
 
* AUTHOR:  Vishwa Mohan
 
* Date Created   10/15/2006 
 
* NOTES:   
 
**************************************
 
* MODIFICATION LOG
 
* DATE     AUTHOR     DESCRIPTION
 
*---------------------------------------
 
* 
 
***************************************/
 
 SELECT Cust_ID, Cust_Name, Cust_DOB,Cust_Address,Date_Created,Date_Modified 
 
 FROM tbl_Customer(NOLOCK)
 
 
 
GO
 
 
 
 
 
CREATE PROCEDURE [dbo].[Usp_InsertCustomer]
 
(
 
@CustName VarChar(50),
 
@CustDOB DateTime,
 
@CustAddress VarChar(100)
 
)
 
AS
 
 
 
/*********************************
 
* PROCEDURE:     Usp_InsertCustomer
 
* PURPOSE:       Inserts a Customer Record
 
* AUTHOR:  Vishwa Mohan
 
* Date Created   10/15/2006 
 
* NOTES:   
 
************************************
 
* MODIFICATION LOG
 
* DATE     AUTHOR     DESCRIPTION
 
*-----------------------------------
 
* 
 
*****************************************/
 
 INSERT tbl_Customer(Cust_Name,Cust_DOB,Cust_Address, Date_Created,Date_Modified )
 
 VALUES (@CustName,@CustDOB, @CustAddress,GetDate(),GetDate())
 
 
 
 If @@RowCount>0
 
  RETURN SCOPE_IDENTITY()
 
 Else
 
  RETURN -1
 
 
 
 
 
GO
 
 
 
 
 
CREATE PROCEDURE [dbo].[Usp_UpdateCustomer]
 
(
 
@CustID Int,
 
@CustName VarChar(50),
 
@CustDOB DateTime,
 
@CustAddress VarChar(100) 
 
)
 
AS
 
 
 
/***************************************
 
* PROCEDURE:     Usp_UpdateCustomer
 
* PURPOSE:       Updates a Customer Record
 
* AUTHOR:  Vishwa Mohan
 
* Date Created   10/15/2006 
 
* NOTES:   
 
*******************************************
 
* MODIFICATION LOG
 
* DATE     AUTHOR     DESCRIPTION
 
*--------------------------------------------
 
* 
 
********************************/
 
 UPDATE tbl_Customer 
 
 SET Cust_Name = @CustName,
 
  Cust_DOB = @CustDOB,
 
  Cust_Address = @CustAddress,
 
  Date_Created = GetDate(),
 
  Date_Modified = GetDate()  
 
 WHERE Cust_ID = @CustID
 
 
 
 If @@RowCount>0
 
  RETURN @CustID
 
 Else
 
  RETURN -1
 
 
 
GO
 
 
 
 
 
CREATE PROCEDURE [dbo].[Usp_DeleteCustomer]
 
(
 
@CustID Int
 
)
 
AS
 
 
 
/**********************************
 
* PROCEDURE:     Usp_DeleteCustomer
 
* PURPOSE:       Deletes a Customer Record
 
* AUTHOR:  Vishwa Mohan
 
* Date Created   10/15/2006 
 
* NOTES:   
 
*****************************************
 
* MODIFICATION LOG
 
* DATE     AUTHOR     DESCRIPTION
 
*-------------------------------------
 
* 
 
*****************************************/
 
 DELETE tbl_Customer  
 
 WHERE Cust_ID = @CustID
 
 
 
 If @@RowCount>0
 
  RETURN @CustID
 
 Else
 
  RETURN -1
 
 
 
GO
 

  

Now, my next step is to create a test project in Visual Studio 2005 to design Data Access Layer, Business Logic La and UIL. First, let's create a blank ASP.NET Web Site Project (Language VB.NET).
My example project namespace is Vishwa.Example.WebSite1

This project will contain following subfolders and hierarchy.

 

App_Code (BLL and DAL)

Business (BLL) - Customer.vb class - It defines customer's attributes,properties and methods
Data (DAL) - DataAccess.vb - This class communicates with BLL for data manipulation

App_Data (Database Scripts)
Customer.sql - Above SQL script including drop statements and comments- kept for clarity

Web Page (UIL)
Customer.aspx - Contains a simple Grid view, Link Button and Object Data Source control to demonstrate Read, Add, Update and Delete operations.

Config File
Web.Config - Contains the database connection string

I hope till now everything looks simple and easy. In Part 2, I will demonstrate DAL layer.

Comments (2) -

  • Franklin Munoz

    7/2/2010 11:25:05 PM | Reply

    Awesome article. There may be a lot of good info here, although I want inform you one point - I am using Mac OS X with the newest beta of Firefox, the look and feel of the website is sort of quirky for me. I can understand the post, but the navigation does not operate so great for me.

  • Software Review

    7/8/2010 12:14:11 PM | Reply

    Thanks for the kind words, Liz! I hope you'll share some of your appreciation ideas here, too!

Loading