Developing 3-Tier Application in .NET 2.0 – 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.