Data Access Layer (Part I)

• Download source files - 9.19 Kb

Introduction

We often read or hear about n-tier applications, layers etc. The benefits of implementing such a logic in the code is not the subject of this article as most developers are aware of that. Since my beginning in .NET, I had in mind that maybe I could develop a Data Access Layer so I would not have to ever code that layer again and only code stored procedures and the layer just above the data access layer.

My Solution

The first part of my solution comes with this article. I developed in the beginning just the basic parts of the layer and functions that retrieve records and datasets, update tables, or delete records. My basic data access layer consists of three classes (AccessingData, Field, FieldsCollection).

Field Class

The Field class is used to represent a field from a table (and to later add it to a collection and pass it to a command). It simply has four properties: ColumnName, ColumnType, ColumnLength, and ColumnValue (each the desired type). Here is the source:

Public Class Field
    Private _columnName As String = ""
    Private _columnType As SqlDbType = SqlDbType.VarChar
    Private _columnLength As Integer = 0
    Private _columnValue As Object
    Public Property ColumnName() As String
        Get
            Return _columnName
        End Get
        Set(ByVal Value As String)
            _columnName = Value
        End Set
    End Property

    Public Property ColumnType() As SqlDbType
        Get
            Return _columnType
        End Get
        Set(ByVal Value As SqlDbType)
            _columnType = Value
        End Set
    End Property
    Public Property ColumnLength() As Integer
        Get
            Return _columnLength
        End Get
        Set(ByVal Value As Integer)
            _columnLength = Value
        End Set
    End Property

    Public Property ColumnValue() As Object
        Get
            Return _columnValue
        End Get
        Set(ByVal Value As Object)
            _columnValue = Value
        End Set
    End Property

    Public Sub New(ByVal colName As String, ByVal colType As SqlDbType, _
                        ByVal colValue As Object, ByVal colLength As Integer)
        Me.ColumnName = colName
        Me.ColumnType = colType
        Me.ColumnLength = colLength
        Me.ColumnValue = colValue
    End Sub
End Class

FieldsCollection Class

This class is simply used as a collection of Fields. Nothing so much special about it. Here is the code:

Public Class FieldsCollection
    Inherits System.Collections.CollectionBase

    Public Sub Add(ByVal objItemToAdd As Field)
        Me.List.Add(objItemToAdd)
    End Sub
    Public ReadOnly Property Item(ByVal iIndex As Integer) As Field
        Get
            Return Me.List(iIndex)
        End Get
    End Property
End Class

AccessingData Class

This class is the most important one. Here we have properties for the connection, the connection string (which we pass from the App.Config file), the SqlCommand, the transaction and the functions to retrieve datasets (whole table - Get_Dataset), to retrieve a specific record by ID (Get_record_byId), to simply delete a record (Delete_record) and to update records (Update_Record). We can set if we want a transaction or not from the above layer. If we do not want a transaction then this layer opens connections, does the job and then closes the connection. Here is the code:

Imports System.Data.SqlClient
Public Class AccessingData
    Private _conn As New SqlConnection
    Private _trans As SqlTransaction
    Private _connectionstring As String = _
      System.Configuration.ConfigurationSettings.AppSettings("SQLconn")
    Private _command As New SqlCommand
    Public Property SqlCommand() As SqlCommand
        Get
            Return _command
        End Get
        Set(ByVal Value As SqlCommand)
            _command = Value
        End Set
    End Property
    Public Property SqlConnection() As SqlConnection
        Get
            Return _conn
        End Get
        Set(ByVal Value As SqlConnection)
            _conn = Value
        End Set
    End Property
    Public Property Transaction() As SqlTransaction
        Get
            Return _trans
        End Get
        Set(ByVal Value As SqlTransaction)
            _trans = Value
        End Set
    End Property
    Public Property SqlConnectionString() As String
        Get
            Return _connectionstring
        End Get
        Set(ByVal Value As String)
            _connectionstring = Value
        End Set
    End Property
    Public Function Get_Dataset(ByVal storedprocedurename As String, _
                             ByVal datatable As String) As DataSet
        Dim sqlDataAdapter As New SqlDataAdapter
        Try
            sqlDataAdapter.SelectCommand = New SqlCommand
            If _conn.ConnectionString = "" Then
                _conn.ConnectionString = _connectionstring
            End If
            sqlDataAdapter.SelectCommand.Connection = _conn
            If Not _conn.State = ConnectionState.Open Then
                _conn.Open()
            End If
            sqlDataAdapter.SelectCommand.CommandText = storedprocedurename
            sqlDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
            If Not IsNothing(_trans) Then
                sqlDataAdapter.SelectCommand.Transaction = _trans
            End If

            Dim myDataSet As DataSet = New DataSet
            sqlDataAdapter.Fill(myDataSet, datatable)
            Return myDataSet
        Catch ex As Exception
            Throw ex
        Finally
            If IsNothing(_trans) Then
                _conn.Close()
            End If

        End Try
    End Function
    Public Function Get_record_byID(ByVal myField As Field, _
                                ByVal storedprocedurename As String, _
                                ByVal datatable As String) As DataSet
        Dim sqlDataAdapter As New SqlDataAdapter
        Try
            sqlDataAdapter.SelectCommand = New SqlCommand
            If _conn.ConnectionString = "" Then
                _conn.ConnectionString = _connectionstring
            End If

            sqlDataAdapter.SelectCommand.Connection = _conn
            If Not _conn.State = ConnectionState.Open Then
                _conn.Open()
            End If

            sqlDataAdapter.SelectCommand.CommandText = storedprocedurename
            sqlDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
            If Not IsNothing(_trans) Then
                sqlDataAdapter.SelectCommand.Transaction = _trans
            End If
            'Parameters
            Dim myparam As SqlParameter
            myparam = New SqlParameter("@" & myField.ColumnName, _
                                       myField.ColumnType, myField.ColumnLength)
            myparam.Value = myField.ColumnValue
            myparam.Direction = ParameterDirection.Input
            sqlDataAdapter.SelectCommand.Parameters.Add(myparam)

            'sqlDataAdapter.SelectCommand.ExecuteNonQuery()
            Dim myDataSet As DataSet = New DataSet
            sqlDataAdapter.Fill(myDataSet, datatable)
            Return myDataSet
        Catch ex As Exception
            Throw ex
        Finally
            If IsNothing(_trans) Then
                _conn.Close()
            End If
        End Try
    End Function
    Public Sub Delete_record(ByVal myField As Field, _
                                ByVal storedprocedure As String)

        Dim mycommand As New SqlCommand
        mycommand.CommandType = CommandType.StoredProcedure
        Try
            mycommand.Connection = _conn
            If _conn.ConnectionString = "" Then
                _conn.ConnectionString = _connectionstring
            End If
            If Not IsNothing(_trans) Then
                mycommand.Transaction = _trans
            End If
            mycommand.CommandText = storedprocedure

            If Not _conn.State = ConnectionState.Open Then
                _conn.Open()
            End If

            'Parameters
            Dim myparam As SqlParameter
            myparam = New SqlParameter("@" & myField.ColumnName, _
                                       myField.ColumnType, myField.ColumnLength)
            myparam.Value = myField.ColumnValue
            myparam.Direction = ParameterDirection.Input
            mycommand.Parameters.Add(myparam)

            mycommand.ExecuteNonQuery()
        Catch ex As Exception
            If IsNothing(_trans) Then
                _conn.Close()
            End If
        End Try
    End Sub
    Public Sub Update_Record(ByVal storedprocedure As String, _
                                ByVal myFieldsCollection As FieldsCollection)
        Try
            Dim mycommand As New SqlCommand
            mycommand.CommandType = CommandType.StoredProcedure
            mycommand.CommandText = storedprocedure
            mycommand.Connection = _conn
            If _conn.ConnectionString = "" Then
                _conn.ConnectionString = _connectionstring
            End If
            If Not IsNothing(_trans) Then
                mycommand.Transaction = _trans
            End If
            If Not _conn.State = ConnectionState.Open Then
                _conn.Open()
            End If
            'Parameters
            Dim myparam As SqlParameter
            For i As Integer = 0 To myFieldsCollection.Count - 1
                Dim obj As Field
                obj = myFieldsCollection.Item(i)

                myparam = New SqlParameter("@" & obj.ColumnName, _
                                           obj.ColumnType, obj.ColumnLength)
                myparam.Value = obj.ColumnValue

                myparam.Direction = ParameterDirection.Input
                mycommand.Parameters.Add(myparam)
            Next
            mycommand.ExecuteNonQuery()
        Catch ex As Exception
            Throw ex
        Finally
            If IsNothing(_trans) Then
                _conn.Close()
            End If
        End Try
    End Sub
End Class

To use all of the above, we simply need stored procedures to retrieve values , insert records etc. Let us use the Northwind database for the example. Here is a stored procedure for updating the Region table:

CREATE  PROCEDURE DBO.UPDATE_REGION
@REGIONDESCRIPTION VARCHAR (50),
@REGION_ID INT

AS
BEGIN
    UPDATE REGION
        SET REGIONDESCRIPTION = @REGIONDESCRIPTION 
    WHERE REGIONID = @REGION_ID
END
GO

Wrap it up!

To use all of the above, we reference the DLL in our project. Then, for example, to update the Region table, we use the following code:

Dim mx As New AccessingData
Try
       Dim myField1 As New Field("REGIONDESCRIPTION", _
                       SqlDbType.NChar, "REGION1", 50)
       Dim myCollection As New FieldsCollection
       myCollection.Add(myField1)
       myField1 = Nothing
       Dim myField2 As New Field("REGION_ID", SqlDbType.Int, 1, 4)
       myCollection.Add(myField2)
       myField2 = Nothing

       mx.Update_Record("UPDATE_REGION", myCollection)
Catch ex As Exception

End Try

We could easily use a transaction simply by adding this code before the "Update_record" line:

'With transaction
mx.SqlConnection.ConnectionString = mx.SqlConnectionString
mx.SqlConnection.Open()
mx.Transaction = mx.SqlConnection.BeginTransaction()
'But after that dont forget to close the connection:
mx.SqlConnection.Close()

Coming Next

In the next article I will go a little bit deeper and use custom attributes :-)