Appear in Online Interview to test your skills and be ready for job!
Twitter Twitter LinkedIn YouTube Google
Be Interview ready ! | Search
Make friends on DotNetFunda.com and expand your network!
Winners

Win Prizes

Announcements
Like us on Facebook
Sponsor
Top Articles Author
Fri, 25-Apr-2014 Authors
All Time Authors
Sourav.Kayal
39750
Sheonarayan
34800
Niladri.Biswas
33350

Latest members | More ...


(Statistics delayed by 5 minutes)
Ads

Using UI, BLL and DAL in ASP.NET 3-Tier

Stewsterl
Posted by under ASP.NET category on for Intermediate level | Points: 250 | Views : 28120 red flag
If you found plagiarised (copied) or inappropriate content,
please let us know the original source along with your correct email id (to communicate) for further action.
Rating: 5 out of 5
1 vote(s)


Quick and dirty 3-Tier Architecture in ASP.NET with C#/VB.NET.
Using UI BLL and DAL

Introduction

This is a short article showing a very basic yet effective way to create a Data Access Layer, Business Logic Layer, and using them in a project. I have used VB.NET for code in this example.


Objective

This article will help you understand and get started in creating your own BLL and DAL.
Not to start a battle between VB and C# programmers, but my examples are in VB because I think C# developers are more capable of converting the code. ;-) Doesn't matter much anymore because of the Common Language Infrastructure. But that's another discussion altogether.


Using the code

First let's create the DAL. This version can be reused in any ASP.NET project. As most examples I've read and seen, developers do not cleanly separate the data access details from the presentation layer and their business rules (BLL).Some include properties in their DAL as noted from the article on this site. 

Here is the DAL. Notice that it stands alone and is cleanly separate from anything else on the page? We didn't mix any BLL or UI in here at all. You can use this in any project using SQL server. This DAL will handle just about any type of call to the database (ExecuteReader,ExecuteScalar,ExecuteDataSet, and ExecuteNonQuery's). Notice that Public Sub New(ByVal connectionstring As String) allows us to only need one DAL for as many SQL databases we are using. The connection string name is passed and the connection string is located in the web.config.
<connectionStrings>
    <add name="CustomerSeriveConnectionString" connectionString="Data Source="";Initial Catalog="";Integrated Security=True" providerName="System.Data.SqlClient" />
  </connectionStrings>
DAL
Imports Microsoft.VisualBasic
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Namespace DBAccess.DAL

    Public Class SQLDBAccess
        Private cmd As IDbCommand = New SqlCommand()
        Private strConnectionString As String = ""
        Private handleErrors As Boolean = False
        Private strLastError As String = ""

        Public Sub New(ByVal connectionstring As String)
            Dim objConnectionStringSettings As ConnectionStringSettings = ConfigurationManager.ConnectionStrings(connectionstring)
            strConnectionString = objConnectionStringSettings.ConnectionString
            Dim cnn As New SqlConnection()
            cnn.ConnectionString = strConnectionString
            cmd.Connection = cnn
            cmd.CommandType = CommandType.StoredProcedure
        End Sub

        Public Function ExecuteReader() As IDataReader
            Dim reader As IDataReader = Nothing
            Try
                Me.Open()
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            Catch ex As Exception
                If handleErrors Then
                    strLastError = ex.Message
                Else
                    Throw
                End If
            End Try
            Return reader
        End Function

        Public Function ExecuteReader(ByVal commandtext As String) As IDataReader
            Dim reader As IDataReader = Nothing
            Try
                cmd.CommandText = commandtext
                reader = Me.ExecuteReader()
            Catch ex As Exception
                If (handleErrors) Then
                    strLastError = ex.Message
                Else
                    Throw
                End If
            End Try
            Return reader
        End Function

        Public Function ExecuteScalar() As Object
            Dim obj As Object = Nothing
            Try
                Me.Open()
                obj = cmd.ExecuteScalar()
                Me.Close()
            Catch ex As Exception
                If handleErrors Then
                    strLastError = ex.Message
                Else
                    Throw
                End If
            End Try
            Return obj
        End Function
        Public Function ExecuteScalar(ByVal commandtext As String) As Object
            Dim obj As Object = Nothing
            Try
                cmd.CommandText = commandtext
                obj = Me.ExecuteScalar()
            Catch ex As Exception
                If (handleErrors) Then
                    strLastError = ex.Message
                Else
                    Throw
                End If
            End Try
            Return obj
        End Function

        Public Function ExecuteNonQuery() As Integer
            Dim i As Integer = -1
            Try
                Me.Open()
                i = cmd.ExecuteNonQuery()
                Me.Close()
            Catch ex As Exception
                If handleErrors Then
                    strLastError = ex.Message
                Else
                    Throw
                End If
            End Try
            Return i
        End Function

        Public Function ExecuteNonQuery(ByVal commandtext As String) As Integer
            Dim i As Integer = -1
            Try
                cmd.CommandText = commandtext
                i = Me.ExecuteNonQuery()
            Catch ex As Exception
                If handleErrors Then
                    strLastError = ex.Message
                Else
                    Throw
                End If
            End Try
            Return i
        End Function
        Public Function ExecuteDataSet() As DataSet
            Dim da As SqlDataAdapter = Nothing
            Dim ds As DataSet = Nothing
            Try
                da = New SqlDataAdapter()
                da.SelectCommand = CType(cmd, SqlCommand)
                ds = New DataSet()
                da.Fill(ds)
            Catch ex As Exception
                If (handleErrors) Then
                    strLastError = ex.Message
                Else
                    Throw
                End If
            End Try
            Return ds
        End Function
        Public Function ExecuteDataSet(ByVal commandtext As String) As DataSet
            Dim ds As DataSet = Nothing
            Try
                cmd.CommandText = commandtext
                ds = Me.ExecuteDataSet()
            Catch ex As Exception
                If handleErrors Then
                    strLastError = ex.Message
                Else
                    Throw
                End If
            End Try
            Return ds
        End Function

        Public Property CommandText() As String
            Get
                Return cmd.CommandText
            End Get
            Set(ByVal value As String)
                cmd.CommandText = value
                cmd.Parameters.Clear()
            End Set
        End Property

        Public ReadOnly Property Parameters() As IDataParameterCollection
            Get
                Return cmd.Parameters
            End Get
        End Property

        Public Sub AddParameter(ByVal paramname As String, ByVal paramvalue As Object)
            Dim param As SqlParameter = New SqlParameter(paramname, paramvalue)
            cmd.Parameters.Add(param)
        End Sub

        Public Sub AddParameter(ByVal param As IDataParameter)
            cmd.Parameters.Add(param)
        End Sub

        Public Property ConnectionString() As String
            Get
                Return strConnectionString
            End Get
            Set(ByVal value As String)
                strConnectionString = value
            End Set
        End Property

        Private Sub Open()
            cmd.Connection.Open()
        End Sub

        Private Sub Close()
            cmd.Connection.Close()
        End Sub

        Public Property HandleExceptions() As Boolean
            Get
                Return handleErrors
            End Get
            Set(ByVal value As Boolean)
                handleErrors = value
            End Set
        End Property

        Public ReadOnly Property LastError() As String
            Get
                Return strLastError
            End Get
        End Property

        Public Sub Dispose()
            cmd.Dispose()
        End Sub

    End Class
End Namespace

Now that we have our DAL created. Let's create a very simple BLL. Noticed that we included (Imports DBAccess.DAL) in DAL. Here we get and set our customer object. In our Public Shared Function UpsertCustomer(ByVal p As Customers) As String we pass in our customer object.

We assign our stored procedure parameters from our object here 
db.Parameters.Add(New SqlParameter("@Customer_Id", p.Customer_Id)) 
Then we execute the ExecuteNonQuery from our DAL with this 
db.ExecuteNonQuery("UpsertCustomer")
UpsertCustomer being the name of the stored procedure.
Imports Microsoft.VisualBasic
Imports DBAccess.DAL
Imports System.Data.SqlClient
Imports System.Data

Public Class Customers

    Private _iCustomer_Id As Integer
    Private _sFirst_Name As String
    Private _sLast_Name As String
    Private _sAddress1 As String
    Private _sAddress2 As String
    Private _sAddress3 As String
    Private _sCity As String
    Private _sState As String
    Private _sZip As String
    Private _sCountry As String
    Private _sPhone As String
    Private _sWork_Phone As String
    Private _sOther_Phone As String
    Private _sEmail As String
   
    Public Property Customer_Id As Integer
        Get
            Return _iCustomer_Id
        End Get
        Set(value As Integer)
            _iCustomer_Id = value
        End Set
    End Property

    Public Property First_Name() As String
        Get
            Return _sFirst_Name
        End Get
        Set(ByVal value As String)
            _sFirst_Name = value
        End Set
    End Property
    Public Property Last_Name() As String
        Get
            Return _sLast_Name
        End Get
        Set(ByVal value As String)
            _sLast_Name = value
        End Set
    End Property
    Public Property Address1() As String
        Get
            Return _sAddress1
        End Get
        Set(ByVal value As String)
            _sAddress1 = value
        End Set
    End Property

    Public Property Address2() As String
        Get
            Return _sAddress2
        End Get
        Set(ByVal value As String)
            _sAddress2 = value
        End Set
    End Property

    Public Property Address3() As String
        Get
            Return _sAddress3
        End Get
        Set(ByVal value As String)
            _sAddress3 = value
        End Set
    End Property
    Public Property City() As String
        Get
            Return _sCity
        End Get
        Set(ByVal value As String)
            _sCity = value
        End Set
    End Property
    Public Property State() As String
        Get
            Return _sState
        End Get
        Set(ByVal value As String)
            _sState = value
        End Set
    End Property
    Public Property Zip() As String
        Get
            Return _sZip
        End Get
        Set(ByVal value As String)
            _sZip = value
        End Set
    End Property
    Public Property Country() As String
        Get
            Return _sCountry
        End Get
        Set(ByVal value As String)
            _sCountry = value
        End Set
    End Property
    Public Property Work_Phone() As String
        Get
            Return _sWork_Phone
        End Get
        Set(ByVal value As String)
            _sWork_Phone = value
        End Set
    End Property
    Public Property Other_Phone() As String
        Get
            Return _sOther_Phone
        End Get
        Set(ByVal value As String)
            _sOther_Phone = value
        End Set
    End Property
    Public Property Phone() As String
        Get
            Return _sPhone
        End Get
        Set(ByVal value As String)
            _sPhone = value
        End Set
    End Property
    Public Property Email() As String
        Get
            Return _sEmail
        End Get
        Set(ByVal value As String)
            _sEmail = value
        End Set
    End Property
  

    Public Shared Function UpsertCustomer(ByVal p As Customers) As String

        Dim db As SQLDBAccess = New SQLDBAccess("CustomerSeriveConnectionString")

        Dim objParam As SqlParameter = New SqlParameter("@iMySearchID", 0)
        objParam.Direction = ParameterDirection.Output

        db.Parameters.Add(New SqlParameter("@Customer_Id", p.Customer_Id))
        db.Parameters.Add(New SqlParameter("@First_Name", p.First_Name))
        db.Parameters.Add(New SqlParameter("@Last_Name", p.Last_Name))
        db.Parameters.Add(New SqlParameter("@Address1", p.Address1))
        db.Parameters.Add(New SqlParameter("@Address2", p.Address2))
        db.Parameters.Add(New SqlParameter("@Address3", p.Address3))
        db.Parameters.Add(New SqlParameter("@City", p.City))
        db.Parameters.Add(New SqlParameter("@State", p.State))
        db.Parameters.Add(New SqlParameter("@Zip", p.Zip))
        db.Parameters.Add(New SqlParameter("@Phone", p.Phone))
        db.Parameters.Add(New SqlParameter("@Work_Phone", p.Work_Phone))
        db.Parameters.Add(New SqlParameter("@Other_Phone", p.Other_Phone))
        db.Parameters.Add(New SqlParameter("@Email", p.Email))
        db.Parameters.Add(New SqlParameter("@Country", p.Country))
       
        db.Parameters.Add(objParam)
        Dim retval As Integer = db.ExecuteNonQuery("UpsertCustomer")
        If retval = 1 Then
            Return objParam.Value.ToString
        Else
            Return -1
        End If

    End Function

   
    Public Shared Function GetCustomerByID(ByVal Customer_Id As String) As Customers

        Dim db As SQLDBAccess = New SQLDBAccess("CustomerSeriveConnectionString")

        db.Parameters.Add(New SqlParameter("@Customer_Id", Customer_Id))

        Dim dr As SqlDataReader = CType(db.ExecuteReader("GetCustomerByID"), SqlDataReader)

        If dr.HasRows Then
            Dim objCustomer As Customers = New Customers

            While dr.Read

                objCustomer.First_Name = dr.Item(dr.GetOrdinal("First_Name")).ToString
                objCustomer.Last_Name = dr.Item(dr.GetOrdinal("Last_Name")).ToString
                objCustomer.Address1 = dr.Item(dr.GetOrdinal("Address1")).ToString
                objCustomer.Address2 = dr.Item(dr.GetOrdinal("Address2")).ToString
                objCustomer.Address3 = dr.Item(dr.GetOrdinal("Address3")).ToString
                objCustomer.City = dr.Item(dr.GetOrdinal("City")).ToString
                objCustomer.State = dr.Item(dr.GetOrdinal("State")).ToString
                objCustomer.Zip = dr.Item(dr.GetOrdinal("Zip"))
                objCustomer.Phone = dr.Item(dr.GetOrdinal("Phone")).ToString
                objCustomer.Work_Phone = dr.Item(dr.GetOrdinal("Work_Phone")).ToString
                objCustomer.Other_Phone = dr.Item(dr.GetOrdinal("Other_Phone")).ToString
                objCustomer.Email = dr.Item(dr.GetOrdinal("Email")).ToString
                objCustomer.Country = dr.Item(dr.GetOrdinal("Country")).ToString
               
            End While

            dr.Close()

            Return objCustomer

        Else

            dr.Close()

            Return New Customers

        End If
    End Function

    Public Shared Function ConvertDBNull(Of T)(ByVal obj As Object) As T
        If (obj Is System.DBNull.Value) Then
            obj = Nothing
        End If
        Return CType(obj, T)
    End Function

End Class
Now in our UI (ASPX pages) we need to instantiate our object and call the update method in our BLL
notice the custid variable? this is the scope identity returned from the insert stored procedure.
Notice from our BLL 
Dim objParam As SqlParameter = New SqlParameter("@iMySearchID", 0)         
objParam.Direction = ParameterDirection.Output Dim retval As Integer = db.ExecuteNonQuery("UpsertCustomer")
Protected Sub btnSave_Click(sender As Object, e As System.EventArgs) Handles btnSave.Click
        Dim custid As String = Nothing
        
            Dim c As Customers = New Customers
            c.Address1 = txtAddress1.Text
            c.Address2 = txtAddress2.Text
            c.Address3 = txtAddress3.Text
            c.City = txtCity.Text
            c.Email = txtEmail.Text
            c.First_Name = txtFirstName.Text
            c.Last_Name = txtLastName.Text
            c.Other_Phone = txtOtherPhone.Text
            c.Phone = txtPhone.Text
            c.Work_Phone = txtWorkPhone.Text
            c.Zip = txtZip.Text
            c.State = ddlState.SelectedValue
            c.Customer_Id = "0"
            c.Country = txtCountry.Text
            c.Title = ddlTitle.SelectedValue
            custid = Customers.UpsertCustomer(c)

End Sub
And obviously to get the data in the UI we would do this
Private Sub loadCustomerInfo()

        Dim c As Customers = Customers.GetCustomerByID(txtCustomerId.Text)
        txtAddress1.Text = c.Address1
        txtAddress2.Text = c.Address2
        txtAddress3.Text = c.Address3
        txtCity.Text = c.City
        txtEmail.Text = c.Email
        txtFirstName.Text = c.First_Name
        txtLastName.Text = c.Last_Name
        txtOtherPhone.Text = PhoneNumber.FormatPhone(c.Other_Phone)
        txtPhone.Text = PhoneNumber.FormatPhone(c.Phone)
        txtWorkPhone.Text = PhoneNumber.FormatPhone(c.Work_Phone)
        txtZip.Text = c.Zip
        ddlState.SelectedValue = c.State
        txtCountry.Text = c.Country
              
    End Sub

Conclusion

I hope this helps and gives you ideas on how to implement a 3-Tier solution to your projects. Not only is it easy, it makes your code cleaner, scalable and easy to troubleshoot.

Page copy protected against web site content infringement by Copyscape
Found interesting? Add this to:




About the Author

Stewsterl

Full Name: Larry Stewart
Member Level: Starter
Member Status: Member
Member Since: 11/6/2012 11:18:00 AM
Country: United States

http://www.dotnetfunda.com

Login to vote for this post.

Comments or Responses


Login to post response.

Comments / Responses
Select text & click toolbar to format. Formatting appears in viewmode only. HTML Tags are not allowed.
Bold Italic Underline Paragraph Title Code  Link 
 Wait ... Processing ..... please wait.

Comment using Facebook