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 SubAnd 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.