Developing 3-Tier Application in .NET 2.0 – Part 2

In this section, I will show you how to connect Data Access Layer(DAL) to previously designed database customer table in Part -1. But in order to access the database we will require connection string, so let's define the connection string called “LocalSqlServer” in web.config file.

NOTE: Considering it is a quick and dirty example, please keep in mind that DAL depends on customer object in this given example, so you will have to visit the Part 3 where Business Object is created, copy that code first. You will have to comment Data Access Layer calls in Customer class Temporarily, once your DAL methods is ready, you will have to uncomment them to make it work.

Web.Config

<connectionStrings> 

<remove name="LocalSqlServer" /> 

<add name="LocalSqlServer" connectionString="Data Source=Vishwa;Database=Example;User ID=eUser;Password=ePassword;" providerName="System.Data.SqlClient" /> 

</connectionStrings>

Data Access Layer Code:

DataAccess.vb: This class will be consumed by Customer class of Business Logic Layer (BLL) and allow to Get, Insert, Update and Delete customer record. Please note that there is a reference of BLL Namespace for Customer class in this class, which will described in BLL section (Part 3).

Option Explicit On

Option Strict On

Imports System.Data

Imports System.Data.SqlClient

Imports Vishwa.Example.Business

Namespace Vishwa.Example.Data

    Public Class DataAccess

        ' Author : Vishwa Mohan

        ' Date : 10/15/2006

        ' Class : Data Accesss Manager

       ‘ Design Pattern: Singleton

        ' Purpose: An Example to demonstrate Customer Data Access Management

        Private Shared instance As New DataAccess

#Region "--Customer Functions--- "

        Public Shared Function GetCustomers(ByVal custID As Integer) As Customer

            Dim custRecord As New Customer

            Using connection As New SqlConnection(GetConnectionString())

                Using command As New SqlCommand("dbo.Usp_GetCustomer", connection)

                    command.CommandType = CommandType.StoredProcedure

                    command.Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custID

                    command.Connection.Open()

                   Using reader As SqlDataReader = command.ExecuteReader(CommandBehavior.CloseConnection)

                        Dim tempCustomer As New Customer

                        custRecord.CustID = CInt(reader.Item("Cust_ID"))

                        custRecord.CustName = reader.Item("Cust_Name").ToString

                        custRecord.CustAddress = reader.Item("Cust_Address").ToString

                        custRecord.CustDOB = CDate(reader.Item("Cust_DOB"))

                        custRecord.DateCreated = CDate(reader.Item("Date_Created"))

                        custRecord.DateModified = CDate(reader.Item("Date_Modified"))

                    End Using

                End Using

            End Using

            Return custRecord

        End Function

        Public Shared Function GetAllCustomers() As Generic.List(Of Customer)

            Dim list As New Generic.List(Of Customer)()

            Using connection As New SqlConnection(GetConnectionString())

                Using command As New SqlCommand("dbo.Usp_GetCustomers", connection)

                    command.CommandType = CommandType.StoredProcedure

                    command.Connection.Open()

                    Using reader As SqlDataReader = command.ExecuteReader(CommandBehavior.CloseConnection)

                        Do While (reader.Read())

                            Dim tempCustomer As New Customer

                            tempCustomer.CustID = CInt(reader.Item("Cust_ID"))

                            tempCustomer.CustName = reader.Item("Cust_Name").ToString

                            tempCustomer.CustAddress = reader.Item("Cust_Address").ToString

                            tempCustomer.CustDOB = CDate(reader.Item("Cust_DOB"))

                            tempCustomer.DateCreated = CDate(reader.Item("Date_Created"))

                            tempCustomer.DateModified = CDate(reader.Item("Date_Modified"))

                            list.Add(tempCustomer)

                        Loop

                    End Using

                End Using

            End Using

            Return list

        End Function

        Public Shared Function InsertCustomer(ByVal custInfo As Customer) As Integer

            Dim returnValue As Integer = -1

            Using connection As New SqlConnection(GetConnectionString())

                Using command As New SqlCommand("dbo.Usp_InsertCustomer", connection)

                    With command

                        .CommandType = CommandType.StoredProcedure

                        .Parameters.Add(New SqlParameter("@ReturnID", SqlDbType.Int)).Direction = ParameterDirection.ReturnValue

                        .Parameters.Add(New SqlParameter("@CustName", SqlDbType.VarChar, 50)).Value = custInfo.CustName

                        .Parameters.Add(New SqlParameter("@CustDOB", SqlDbType.DateTime)).Value = custInfo.CustDOB

                        .Parameters.Add(New SqlParameter("@CustAddress", SqlDbType.VarChar, 100)).Value = custInfo.CustAddress

                        .Connection.Open()

                        .ExecuteNonQuery()

                        returnValue = CInt(command.Parameters("@ReturnID").Value)

                    End With

                End Using

            End Using

            Return returnValue

        End Function

        Public Shared Function UpdateCustomer(ByVal custInfo As Customer) As Integer

            Dim returnValue As Integer = -1

            Using connection As New SqlConnection(GetConnectionString())

                Using command As New SqlCommand("dbo.Usp_UpdateCustomer", connection)

                    With command

                        .CommandType = CommandType.StoredProcedure

                        .Parameters.Add(New SqlParameter("@ReturnID", SqlDbType.Int)).Direction = ParameterDirection.ReturnValue

                        .Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custInfo.CustID

                        .Parameters.Add(New SqlParameter("@CustName", SqlDbType.VarChar, 50)).Value = custInfo.CustName

                        .Parameters.Add(New SqlParameter("@CustDOB", SqlDbType.DateTime)).Value = custInfo.CustDOB

                        .Parameters.Add(New SqlParameter("@CustAddress", SqlDbType.VarChar, 100)).Value = custInfo.CustAddress

                        .Connection.Open()

                        .ExecuteNonQuery()

                        returnValue = CInt(command.Parameters("@ReturnID").Value)

                    End With

                End Using

            End Using

            Return returnValue

        End Function

        Public Shared Function DeleteCustomer(ByVal custInfo As Customer) As Integer

            Dim returnValue As Integer = -1

            Using connection As New SqlConnection(GetConnectionString())

                Using command As New SqlCommand("dbo.Usp_DeleteCustomer", connection)

                    With command

                        .CommandText = "Usp_DeleteCustomer"

                        .CommandType = CommandType.StoredProcedure

                        .Parameters.Add(New SqlParameter("@ReturnID", SqlDbType.Int)).Direction = ParameterDirection.ReturnValue

                        .Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custInfo.CustID

                        .Connection.Open()

                        .ExecuteNonQuery()

                        returnValue = CInt(command.Parameters("@ReturnID").Value)

                    End With

                End Using

            End Using

            Return returnValue

        End Function

#End Region

#Region "Common Connection Functions"

        Private Shared Function GetConnectionString() As String

            Try

                Return ConfigurationManager.ConnectionStrings.Item("LocalSqlServer").ConnectionString

            Catch exc As Exception

                Return ""

            End Try

        End Function

#End Region

#Region "Private Constructor"

        Private Sub New()

        End Sub

#End Region

    End Class

End Namespace

Now you can move on to Business Logic Layer in Part 3

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.