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.
The sql script for the database objects is given below.
--------------------------------------------------------------
This namespace is much more versatile than old CDO dependent System.Web.Mail. You can do a lot more in this new namespace. More features were presented in Microsoft Seminar. For detail information you can visit http://www.systemnetmail.com
I have created an Email Component using .NET 2.0, which can be used in your Web based or Windows based application to send email. If you are just sending regular message without any attachment or authentication, then you do not need this component. You can directly use System.Net.Mail namespace and add 2 lines of code.But if you are going to use attachments and perform user authentication dynamically then you can utilize this component.
Example for Basic and Advance use is given below. I created this component before I attended the Microsoft Seminar, so I have not yet implement additional cool features for email Content such as LinkedResource, ContentLink and AlternateView etc. Not sure if everyone needs it.
Basic Email - Just 2 lines of Code (VB.NET)
You can create four text boxes on a web page for From, To, Subject and Body and then replace the hard coded values with corresponding text box text. The following code assumes that you are using the local SMTP service of the machine on which this code is running.
--------------------------------------
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.