The Data Access Layer Object

If you’ve ever has to refactor a database or business objects that interact with strongly typed datasets, you’ll quickly understand why they’re evil. Pure evil. This extra meaty slab walks through creating a data access layer, and how to use it.

Steps to Creating a DAL Object in VB.Net

1. Start a new project or add to an existing solution. In order to see the solution itself in the Solution Explorer, you may need to set it to visible somewhere under Tools, Options.

2. Create the new project of type “Class Library”. Name it “DataLayer”.

3. Add a setting to DataLayer by double clicking “My Project” and navigating to the Settings tab. Name it cnn, fill in a value, and scope it to user.

4. Add references to the project (by right clicking the project, then Add Reference…) for System.Data and System.Configuration.

5. Rename Class1.vb to DAL.vb.

6. Place this code in it:

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Text

Public NotInheritable Class DAL
    Implements IDisposable
    Private sqlCn As New SqlConnection()
    Private _LastException As SqlException
    Public ReadOnly Property LastException() As SqlException
        Get
            Return _LastException
        End Get
    End Property
    Public Sub New(ByVal cnnString As String)
        MyBase.New()
        Me.OpenConnection(cnnString)
    End Sub
    Public Overrides Function ToString() As String
        If Me.sqlCn Is Nothing Then
            Return ""
        Else
            Return Me.sqlCn.ToString
        End If
    End Function
    Private Sub OpenConnection(ByVal cnnString As String)
        If sqlCn.State <> ConnectionState.Open Then
            Try
                If Me.sqlCn Is Nothing Then
                    Me.sqlCn = New SqlConnection
                End If
                sqlCn.ConnectionString = cnnString
                sqlCn.Open()
            Catch sqlEx As SqlException
                Me._LastException = sqlEx
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub
    Private Sub CloseConnection()
        If sqlCn.State = ConnectionState.Open Then
            sqlCn.Close()
        End If
        Me.Dispose()
    End Sub
    Public Function GetDataTable(ByVal SQL As String) As DataTable
        Dim inv As New DataTable()
        Try
            Using cmd As New SqlCommand(SQL, Me.sqlCn)
                Dim dr As SqlDataReader = cmd.ExecuteReader()
                inv.Load(dr)
                dr.Close()
            End Using
            Me.CloseConnection()
            Return inv
        Catch sqlEx As SqlException
            Return Nothing
        Catch ex As Exception
            Return Nothing
        End Try
    End Function
    Public Function RunNonQuery(ByVal SQL As String) As Object
        Try
            If sqlCn.State = ConnectionState.Closed Then
                sqlCn.Open()
            End If
            Using cmd As New SqlCommand(SQL, Me.sqlCn)
                cmd.CommandTimeout = 0
                cmd.ExecuteNonQuery()
            End Using
        Catch sqlEx As SqlException
            Return sqlEx
        Catch ex As Exception
            Return ex
        End Try
        Me.CloseConnection()
        Return Nothing
    End Function
    Public Function ExecuteProc(ByVal SP As String, ByVal params As Array) As Object
        Dim SQL As New StringBuilder
        SQL.Append("EXEC dbo.")
        SQL.Append(SP)
        SQL.Append(" ")
        For index As Integer = 0 To params.Length - 1
            SQL.Append(params(index))
            SQL.Append(", ")
        Next
        SQL.Remove(SQL.Length - 2, 2)
        Try
            If sqlCn.State = ConnectionState.Closed Then
                sqlCn.Open()
            End If
            Using cmd As New SqlCommand(SQL.ToString, Me.sqlCn)
                cmd.CommandTimeout = 0
                cmd.ExecuteNonQuery()
            End Using
        Catch sqlEx As SqlException
            Return sqlEx
        Catch ex As Exception
            Return ex.ToString
        End Try
        Me.CloseConnection()
        Return Nothing
    End Function
    Public Function PerformTransaction(ByVal OrderedCmdList As Generic.SortedList(Of Integer, String)) As Boolean
        Dim trans = sqlCn.BeginTransaction()
        Try
            For i As Integer = 0 To OrderedCmdList.Count - 1
                Using cmd As New SqlCommand(OrderedCmdList(i), Me.sqlCn)
                    cmd.Transaction = trans
                    cmd.CommandTimeout = 0
                    cmd.ExecuteNonQuery()
                End Using
            Next
            trans.Commit()
            Return True
        Catch sqlEx As SqlException
            Me._LastException = sqlEx
            trans.Rollback()
            Return False
        Finally
            Me.CloseConnection()
        End Try
    End Function
    Public Sub PerformBulkCommands(ByVal OrderedCmdList As Generic.SortedList(Of Integer, String), ByVal IgnoreErrors As Boolean)
        For i As Integer = 0 To OrderedCmdList.Count - 1
            Try
                Using cmd As New SqlCommand(OrderedCmdList(i), Me.sqlCn)
                    cmd.CommandTimeout = 0
                    cmd.ExecuteNonQuery()
                End Using
            Catch sqlEx As SqlException
                Me._LastException = sqlEx
                If IgnoreErrors = False Then
                    Exit For
                End If
            End Try
        Next
        Me.CloseConnection()
    End Sub
    Public Sub Dispose() Implements IDisposable.Dispose
        Me.sqlCn = Nothing
        GC.Collect()
    End Sub
End Class

Using the DAL Object

1. Remove references to System.Data in the project that is to be changed.

2. Add a reference to DataLayer (right click the project, Add Reference…, Projects tab)

3. Remove the “Imports System.Data”, if you have one.

4. Add: “Imports DataLayer”.

5. If you want to consume a data table:

Private Sub ShowRows()
    Dim MyDAL As New DAL(cnn)
    Dim strSQL As String = "SELECT * FROM dbo.EmployeeMaster"
    Dim dt As DataTable = MyDAL.GetDataTable(strSQL)
    If retval IsNot Nothing Then
        For curRow As Integer = 0 To dt.Rows.Count - 1
            'do something
        Next
    Else
        MsgBox("You did something bad, and now I'm mad at you.", _
            MsgBoxStyle.Exclamation + MsgBoxStyle.OkOnly, "Error")
    End If
End Sub

6. If you want to run a stored procedure that does not return a dataset:

Private Sub RunProc()
    Dim retval As Object = Nothing
    Dim MyDAL As New DAL(cnn)
    Dim strSQL As String = "EXEC dbo.SomeProCedure"

    retval = MyDAL.RunNonQuery(strSQL)
    If retval IsNot Nothing Then
        MsgBox(retval.ToString, MsgBoxStyle.Exclamation + MsgBoxStyle.OkOnly, "Error")
    End If
End Sub

7. If your proc has params, you do this:

Private Sub RunProc()
    Dim retval As Object = Nothing
    Dim MyDAL As New DAL(cnn)
    Dim SP As String = "SomeProcedure"
    Dim params(2) As String
    params(0) = Shift.ToString
    params(1) = "'" & ProdDate & "'"
    retval = MyDAL.ExecuteProc(SP, params)
    If retval IsNot Nothing Then
        MsgBox(retval.ToString, MsgBoxStyle.Exclamation + MsgBoxStyle.OkOnly, "Error")
    End If
End Sub

Last Thoughts

The DataLayer DLL can now be used anywhere.

This paves the way to building business objects that hydrate themselves. Instead of binding form widgets to datasets, you can new up an object and bind the widget to the
object. This is good because it gets data access out of the UI.

note: edited 11 Nov 2008 – added more error handling.