Developing 3 tier web sites in ASP.NET 2.0

Introduction

Many months back I wrote a series of articles that explained in plain words N-tier application development. The series can still be found at:

• N-Tier Applications and .NET
• N-Tier Applications and .NET: A Simple Example
• N-Tier Applications and .NET: Achieving Isolation between DAL and BOL
• N-Tier Applications and .NET: Achieving Isolation between UI and BOL

Then came .NET 2.0. Many things changed especially for ASP.NET. So I decided to write a sequel to the earlier series specifically for ASP.NET 2.0. This article will explain how a typical 3-tier architecture can be used in ASP.NET 2.0 with the help of Generics based collections and Data Source Controls. Note that the article aims at showing a simple 3 tier architecture and not at teaching you generics, data bound controls and data source controls.

What are Generic collections?

.NET 1.x provided many collection classes such as ArrayList, NameValueCollection, HashTable, Queue and Stack. All of them were designed to store objects. Have a look at the following code:

Dim arr As New ArrayList
Dim c As New Customer
...
arr.Add(c)

The code creates a new instance of ArrayList and a class called Customer. The Customer instance is then added to the ArrayList. Now the problem is ArrayList stores all the items as of type Object and not of a specific type. That means while retrieving the any element back you need to write:

c = CType(arr(0), Customer)

That means you must type case the element to the actual class type. This also means some performance penalty. If you are thinking it to be reasonable then have a look at the next example.

Dim arr As New ArrayList
Dim c As New Customer
arr.Add(c)
arr.Add("Hello World")
arr.Add(100)

Here the code adds an instance of Customer class, a string and an integer to the ArrayList. You will agree that errors can easily crop up while retrieving the values back.

Because of these reasons Microsoft added a special Generics based collection classes. These classes are available in System.Collections.Generic namespace. Some of the classes from this namespace are List, SortedList, Queue and Stack. You can of course create your own generic collection classes. The same code can be written as shown below:

Dim arr As New List(Of Customer)
Dim c As New Customer
arr.Add(c)
...
c = arr(0)

As you can see the code declares a new instance of List but it is not just List it is List of Customers. That means you can store only Customer instances in the List. Later on when you retrieve the values back you need not perform any typecasting. any attempt to store anything other than Customer objects will result in compile time error. Thus generic collections are better from performance as well as usage point of view.

Architecture of our sample 3 tier application

A three tiered application need not be necessarily distributed. Than means various layers (Data Access Layer, Business Logic Layer and User Interface Layer) can be within a single web site. You can of course separate out individual layers and host them on a dedicated server if you so wish. In such cases you may need to use Remoting or Web Services.

The components in a three tired system are generally implemented in a class library. However in ASP.NET 2.0 there is one more alternative. The special folder App_Code is intended to store the classes used by your web site. You can neatly organize the classes inside the App_Code folder by creating subdirectories.

In ASP.NET 1.x you would have used either DataSet or ArrayList kind of collection to pass data from one layer to the other. In ASP.NET 2.0 you can use DataSet in the same way as in 1.x. However, instead of using ArrayList you can go for generic List class.

In ASP.NET 1.x the user interface layer consists of web forms. You need to assemble various server controls on the form and manually data bind them with the underlying data source. Tasks such as inserting, updating and deleting data were all manual i.e. you need to write code to accomplish them. In ASP.NET 2.0 new data bound controls such as GridView, DetailsView and FormView along with Data Source Controls come handy. They allow you to design the UI with minimal coding.

To summarize the architecture of our sample application will be like this:

• A class called SqlHelper will act as a Data Access Layer and will reside in a subfolder called DAL inside the App_Code folder. This class will take the data in and out of Customers table of Northwind database
• A class called Customer will act as a Business Logic Layer and will be placed in a subfolder called BOL inside the App_Code folder
• A UP will consist of a web form. We will use Object Data Source and DetailsView control for adding, updating and deleting data

Developing the sample application

Let's begin our development. First of all create a new web site using VS.NET 2005. Open the solution explorer, right click on the web site and choose Add ASP.NET Folder option. Select App_Code folder so as to create the App_Code folder. Add two subfolders called DAL and BOL to the App_Code folder. Add a class called SqlHelper in the DAL folder. Similarly add a class called Customer in the BOL folder. Your folder structure should resemble as shown below:

Open the web.config file and add a connection string as shown below:

<connectionStrings>
<add name="connectionstring" connectionString="data source
=.\sqlexpress;initial catalog=northwind;integrated security
=true" providerName="System.Data.SqlClient"/>
</connectionStrings>

The <connectionStrings> section is used to store database connection strings. In our example the connection string points to Northwind database from a SQL Server Express database.

Creating Data Access Layer

The SqlHelper class represents our Data Access Layer. Open SqlHelper.vb and key in following code in it:

Public Class SqlHelper
Private Shared connectionstring As String

Shared Sub New()
connectionstring = ConfigurationManager.ConnectionStrings
("connectionstring").ConnectionString
End Sub

Public Shared Function ExecuteNonQuery(ByVal sql As String, 
ByVal params() As SqlParameter) As Integer
Dim cnn As New SqlConnection(connectionstring)
Dim cmd As New SqlCommand(sql, cnn)
For i As Integer = 0 To params.Length - 1
cmd.Parameters.Add(params(i))
Next
cnn.Open()
Dim retval As Integer = cmd.ExecuteNonQuery()
cnn.Close()
Return retval
End Function

Public Shared Function ExecuteDataSet(ByVal sql As String) 
As DataSet
Dim ds As New DataSet
Dim da As New SqlDataAdapter(sql, connectionstring)
da.Fill(ds)
Return ds
End Function

Public Shared Function ExecuteDataSet(ByVal sql As String, 
ByVal params() As SqlParameter) As DataSet
Dim ds As New DataSet
Dim da As New SqlDataAdapter(sql, connectionstring)
For i As Integer = 0 To params.Length - 1
da.SelectCommand.Parameters.Add(params(i))
Next
da.Fill(ds)
Return ds
End Function
End Class

The SqlHelper class consists of several shared methods. In the shared constructor the class reads the connection string that we stored in the web.config file previously. In order to read the connection string the code uses ConfigurationManager class.

The ExecuteNonQuery() method is designed for action queries such as INSERT, UPDATE and DELETE. The overloads of ExecuteDataSet() method is intended to return a DataSet filled with the required records.

Creating Business Logic Layer

The Customer class represents our Business Logic Layer. Open Customer class and add the following code to it.

Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections.Generic

Public Class Customer
Private strID As String
Private strCompany As String
Private strContact As String
Private strCountry As String

Public Property CustomerID() As String
Get
Return strID
End Get
Set(ByVal value As String)
strID = value
End Set
End Property

Public Property CompanyName() As String
Get
Return strCompany
End Get
Set(ByVal value As String)
strCompany = value
End Set
End Property

Public Property ContactName() As String
Get
Return strContact
End Get
Set(ByVal value As String)
strContact = value
End Set
End Property

Public Property Country() As String
Get
Return strCountry
End Get
Set(ByVal value As String)
strCountry = value
End Set
End Property

Public Shared Function Insert(ByVal c As Customer) As Integer
Dim sql As String
sql = "insert into customers(customerid,companyname,
contactname,country) values(@custid,@company,@contact,@country)"
Dim params(3) As SqlParameter
params(0) = New SqlParameter("@custid", c.CustomerID)
params(1) = New SqlParameter("@company", c.CompanyName)
params(2) = New SqlParameter("@contact", c.ContactName)
params(3) = New SqlParameter("@country", c.Country)
Return SqlHelper.ExecuteNonQuery(sql, params)
End Function

Public Shared Function Update(ByVal c As Customer) 
As Integer
Dim sql As String
sql = "update customers set companyname=@company,contactname
=@contact,country=@country where customerid=@custid"
Dim params(3) As SqlParameter
params(0) = New SqlParameter("@company", c.CompanyName)
params(1) = New SqlParameter("@contact", c.ContactName)
params(2) = New SqlParameter("@country", c.Country)
params(3) = New SqlParameter("@custid", c.CustomerID)
Return SqlHelper.ExecuteNonQuery(sql, params)
End Function

Public Shared Function Delete(ByVal c As Customer) 
As Integer
Dim sql As String
sql = "delete from customers where customerid=@custid"
Dim params(0) As SqlParameter
params(0) = New SqlParameter("@custid", c.CustomerID)
Return SqlHelper.ExecuteNonQuery(sql, params)
End Function

Public Shared Function SelectAll() As List(Of Customer)
Dim ds As DataSet = SqlHelper.ExecuteDataSet
("select customerid,companyname,contactname,
country from customers")
Dim arr As New List(Of Customer)
For Each row As DataRow In ds.Tables(0).Rows
Dim c As New Customer
c.CustomerID = row("customerid")
c.CompanyName = row("companyname")
c.ContactName = row("contactname")
c.Country = row("country")
arr.Add(c)
Next
Return arr
End Function

Public Shared Function SelectSingle(ByVal custid As String) 
As Customer
Dim params(0) As SqlParameter
params(0) = New SqlParameter("@custid", custid)
Dim ds As DataSet = SqlHelper.ExecuteDataSet
("select customerid,companyname,contactname,country from 
customers where customerid=@custid", params)
Dim row As DataRow = ds.Tables(0).Rows(0)
Dim c As New Customer
c.CustomerID = row("customerid")
c.CompanyName = row("companyname")
c.ContactName = row("contactname")
c.Country = row("country")
Return c
End Function
End Class

The Customer class consists of four public properties and five public shared methods. The method names are self-explanatory. The methods essentially manipulate data from Customers table of Northwind database.

Note the code marked in bold. We have imported System.Collections.Generic namespace because we want to return records as collection of Customer objects. Have a look carefully at SelectAll() method. The method returns a List of Customers. Inside we have declared a variable (arr) of type List. The SelectAll() method calls ExecuteDataSet() method of the SqlHelper class and retrieves a DataSet filled with required records. Then the method iterates through all the records from the DataSet and with each iterations add a Customer object to the List.

Creating the User Interface

The UI consists of a single web form. Open the default web form in the VS.NET IDE and design it as shown below:

The DropDownList shows a list of all CustomerIDs from the database. Once you select a perticular customer ID its details are displayed in the DetailsView below. The DetailsView is bound with ObjectDataSource1 where as the DropDownList is bound with ObjectDataSource2.

Open the smart tags of ObjectDataSource1 and choose "Configure Data Source...". Doing so will start a wizard as shown below:

Select Customer class from the list and click Next. On the next Wizard step choose methods for selecting, updating, inserting and deleting data. In our case the methods are SelectSingle(), Update(), Insert() and Delete() respectively.

Once you specify the methods click on Next. The next step will ask you the source of the custid parameter of SelectSingle() method. In our case the source control is DropDownList1.

Click on Finish to close the Wizard.

Similarly configure the ObjectDataSource2 to select all the Customers i.e. specify Select method as SelectAll().

We just finished configuring the object data source controls. Running the wizard sets the following important properties of the object data source controls.

Now open the smart tag of DetailsView and set its data source to ObjectDataSource1. Also, enable inserting, editing and deleting.

Similarly open smart tag of DropDownList1 and set its data source to ObjectDtaSource2. Specify DataTextField and DataValueField properties as CustomerID.

Finally enable AutoPostBack for the DropDownList.

We want that whenever an item is added or deleted the DropDownList should immediately reflect the changes. In order to achieve this you need to handle ItemInserted and ItemDeleted events of DetailsView. These event handlers are shown below

Protected Sub DetailsView1_ItemInserted
(ByVal sender As Object, ByVal e As DetailsViewInsertedEventArgs) 
Handles DetailsView1.ItemInserted
DropDownList1.DataBind()
End Sub

Protected Sub DetailsView1_ItemDeleted
(ByVal sender As Object, ByVal e As DetailsViewDeletedEventArgs)
Handles DetailsView1.ItemDeleted
DropDownList1.DataBind()
End Sub

The event handlers simply call the DataBind() method of the DropDownList which causes it to repopulate itself.

That's it. Run the web form and test editing and deleting functionality for different customers. The following figure shows the web form in action.