Introduction
We often read or hear about n-tier applications, layers e.t.c. The benefits of implementing such a logic in the code is not a subject of this article, but most of 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 rocedures 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, datasets, update tables, delete records.
My basic Data Access Layer consists from 3 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 the command). It simply has 4 properties ColumnName,ColumnType, ColumnLength, ColumnValue (each the desired type)
This is the code.

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

Fields Collection Class
This class is simply used as a collection of fields. Nothing so much special abou that. 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

Accessing Data Class
This class is the most important one. Here we have properties for the connection, the connection string, the sqlcommand, the transaction and the functions to retrieve datasets (whole table - Get_Dataset), to retrieve specific record by id (Get_record_byId), the sub to delete record (Delete_record) and the sub to update records (Update_Record).
We can set if we want transaction or not from the above layer. If we do not want to 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

            '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 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 e.t.c.
Let us use the Northwind database. 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 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()

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