A Wrapper Class for the SQL Server Based Data Access Helper

---

There are two downloadable zip files available for this article. You can find them here and here.

---

This is an extension to my previous articles; it is not an introductory article to COM+ or ADO.NET.  I suggest you to refer my previous articles before continuing with this one. If you are very new to COM+, I suggest you go through one of my previous articles here.

I developed this wrapper class for extensibility. Currently, it works only with SQL Server. But you can add few more COM+ classes and link this wrapper very easily, due to its extensibility.

The entire source code for this article is available in the form of downloadable zip files. The solution was developed using Microsoft Visual Studio 2003 Enterprise Architect with Microsoft SQL Server 2005 Developer Edition on Microsoft Windows Server 2003 Enterprise Edition.  Even though I believe that the source code available with this contribution can work with Microsoft Visual Studio.NET 2002, I didn’t really test it in any other environment. I request that you post in the discussion area if you have any problems with execution.

Designing the wrapper for extensibility

Let us consider the following code fragment:

PublicEnum DBConnType

    MSSQL

EndEnum

PublicClass DBLib

    Friend ObjLib As Object 'can be used flexibly in any of the sub-classes

    Private arTransCommands As ArrayList

    Private _ConnectionString As String

    Private _ConnectionType As DBConnType

    Public Sub New(ByVal ConnectionType As DBConnType, ByVal ConnectionString As String)

        Try

            _ConnectionString = ConnectionString

            _ConnectionType = ConnectionType

            If Len(Trim(_ConnectionString & "")) = 0 Then Throw New Exception("Connection String not configured")

        Catch ex As Exception

            Throw New Exception(ex.Message & ". Invalid Database Configuration -->Error Logged")

        End Try

    End Sub

Currently, I developed the above only for the SQL Server type of database connection. When you require support for more than one database, you will add a few more database types to the enumeration. 

The variable “arTransCommands” is mainly used to add all the commands which need to be executed as a single transaction.

The following is the code needed to work with the respective database related object:

FriendFunction getDBHelperObject() As Object 'can be used flexibly in any of the sub-classes

        Select Case _ConnectionType

            Case DBConnType.MSSQL

                Dim dbhelper As New CoreMSSQLDataAccessHelper.CDataAccess

                dbhelper.ConnectionString = _ConnectionString

                Return dbhelper

            Case Else

                Throw New Exception("This library doesn't support the database '" & _ConnectionType.ToString & "'")

        End Select

    End Function

You need to add a few more cases to the above method when you work with multiple databases.

Getting a single row or single value from the data access helper

The following is the wrapper method used to access the data access helper method. It is used mainly to retrieve a single row:

PublicFunction getDataRow(ByVal sqlSELECT As String) As System.Data.DataRow

        Try

            ObjLib = getDBHelperObject()

           Return ObjLib.getDataRow(sqlSELECT)

ElseReturn dt.Rows(0)

        Catch ex As Exception

            Throw ex

        Finally

            Try

                System.EnterpriseServices.ServicedComponent.DisposeObject(ObjLib)

            Catch ex As Exception

            End Try

        End Try

    End Function

The following is the wrapper method used to retrieve a single value:

    Public Function getRowValue(ByVal sqlSELECT As String) As String

        Try

            ObjLib = getDBHelperObject()

            Return ObjLib.getRowValue(sqlSELECT)

        Catch ex As Exception

            Throw ex

        Finally

            Try

                System.EnterpriseServices.ServicedComponent.DisposeObject(ObjLib)

            Catch ex As Exception

            End Try

        End Try

    End Function

You can observe that I am creating an object of the data helper type in “getDBHelperObject.” I also need to dispose of the same when not in use. If you don’t dispose of it, it will not be released and it will never return back to the pool.  To make the pool always reusable, you need to dispose of the COM+ object immediately after finishing your work.

You can also increase the size of the pool by modifying the attribute value given in previous articles.

Getting multiple rows using wrapper methods from the data access helper

The following is the wrapper method used to retrieve more than one row:

PublicFunction getDataTable(ByVal sqlSELECT As String) As System.Data.DataTable

        Try

            ObjLib = getDBHelperObject()

            Return ObjLib.getDataTable(sqlSELECT)

        Catch ex As Exception

            Throw ex

        Finally

            Try

                System.EnterpriseServices.ServicedComponent.DisposeObject(ObjLib)

            Catch ex As Exception

            End Try

        End Try

    End Function

The above returns only a data table. If you need a return in the form of a dataset, you need to modify the same as follows:

PublicFunction getDataSet(ByVal sqlSELECT As String, Optional ByVal DataTableName As String = Nothing) As System.Data.DataSet

        Try

            ObjLib = getDBHelperObject()

            Return ObjLib.getDataSet(sqlSELECT)

        Catch ex As Exception

            Throw ex

        Finally

            Try

                System.EnterpriseServices.ServicedComponent.DisposeObject(ObjLib)

            Catch ex As Exception

            End Try

        End Try

    End Function

   

If you need a return in the form of a data view, you need to modify the same as follows:

PublicFunction getDataView(ByVal sqlSELECT As String) As System.Data.DataView

        Try

            ObjLib = getDBHelperObject()

            Return ObjLib.getDataView(sqlSELECT)

    Catch ex As Exception

            Throw ex

        Finally

            Try

                System.EnterpriseServices.ServicedComponent.DisposeObject(ObjLib)

            Catch ex As Exception

            End Try

        End Try

    End Function   

Adding wrappers for transactions

The following are the wrapper methods used to work with transaction-related commands:

PublicSub TransactionBegin()

        arTransCommands = New ArrayList

    End Sub

    Public Sub TransactionAddCommand(ByVal SQLStatement As String)

        arTransCommands.Add(SQLStatement)

    End Sub

    Public Sub TransactionRollback()

        arTransCommands.Clear()

    End Sub

    Public Sub TransactionCommit()

        Try

            ObjLib = getDBHelperObject()

            Dim b As Boolean = ObjLib.TransactionExecute(arTransCommands)

            If Not b Then Throw New Exception("Transaction could not be performed -->Error logged.")

        Catch ex As Exception

            Throw ex

        Finally

            Try

                System.EnterpriseServices.ServicedComponent.DisposeObject(ObjLib)

            Catch ex As Exception

            End Try

        End Try

    End Sub

To work with the above methods, you need to first start the transaction by simply calling the method “TransactionBegin.” You can add as many commands as possible by using “TransactionAddCommand.”  After adding all the transaction commands, you simply execute all of them at the same time by issuing the “TransactionCommit” command.

Please note that I didn’t add any stored procedures to work with transactions.

A Wrapper method to test whether a row exists

The following wrapper method is mainly used to test whether a row exists or not:

PublicFunction isRowExists(ByVal sqlSELECT As String) As Boolean

        Try

            Dim dr As DataRow

            dr = getDataRow(sqlSELECT)

            If dr Is Nothing Then

                Return False

            Else

                Return True

            End If

        Catch ex As Exception

            Return False

        End Try

    End Function

You can modify/overload the above to make it more flexible as follows:

PublicFunction isRowExists(ByVal TableName As String, ByVal ColumnName As String, ByVal ValueToSearch As String, Optional ByVal PatternSearch As Boolean = False) As Boolean

        Try

            Dim sql As String

            If PatternSearch Then

                sql = "SELECT * FROM " & TableName & " WHERE " & ColumnName & " like '%" & ValueToSearch & "%'"

            Else

                sql = "SELECT * FROM " & TableName & " WHERE " & ColumnName & " = '" & ValueToSearch & "'"

            End If

            Dim dr As DataRow

            dr = getDataRow(sql)

            If dr Is Nothing Then

                Return False

            Else

                Return True

            End If

        Catch ex As Exception

            Return False

        End Try

    End Function

Adding a wrapper to execute SQL statements

The following is the wrapper method used to execute SQL statements:

PublicOverloads Sub SQLExecute(ByVal strSQL As String)

        Try

            ObjLib = getDBHelperObject()

            ObjLib.SQLExecute(strSQL)

        Catch ex As Exception

            Throw ex

        Finally

            Try

                System.EnterpriseServices.ServicedComponent.DisposeObject(ObjLib)

            Catch ex As Exception

            End Try

        End Try

    End Sub

    

To deal with BLOB values, you can overload the wrapper method above as follows:

PublicOverloads Sub SQLExecute(ByVal strSQL As String, ByRef BLOBColNames() As String, ByRef ByteArrayObjects() As Object)

        Try

            ObjLib = getDBHelperObject()

            ObjLib.SQLExecute(strSQL, BLOBColNames, ByteArrayObjects) 'contains reference parameters to return back BLOBS

        Catch ex As Exception

            Throw ex

        Finally

            Try

                System.EnterpriseServices.ServicedComponent.DisposeObject(ObjLib)

            Catch ex As Exception

            End Try

        End Try

    End Sub

Adding miscellaneous wrapper methods

The following is the wrapper method used to get the next value in a sequence:

    Public Function getNextSequence(ByVal ParamName As String, Optional ByVal TableName As String = "SysParams") As String

        Try

            ObjLib = getDBHelperObject()

            Return ObjLib.getNextSequence(ParamName, TableName)

        Catch ex As Exception

            Throw ex

        Finally

            Try

                System.EnterpriseServices.ServicedComponent.DisposeObject(ObjLib)

            Catch ex As Exception

            End Try

        End Try

    End Function

The following is the wrapper method used to retrieve a BLOB value from the database:

    Public Sub getBLOBValue(ByVal sqlSELECT As String, ByRef BinData() As Byte)

        Try

            ObjLib = getDBHelperObject()

            ObjLib.getBLOBValue(sqlSELECT, BinData) 'it contains reference parameter, so nothing to return back

        Catch ex As Exception

            Throw ex

        Finally

            Try

                System.EnterpriseServices.ServicedComponent.DisposeObject(ObjLib)

            Catch ex As Exception

            End Try

        End Try

    End Sub

Adding SQL Server stored procedure parameter support to the wrapper

As the parameters of a stored procedure would be a bit different for every database, I started developing a different class especially for SQL Server by extending the same from the above wrapper class.

The class definition would look something like the following:

PublicClass MSSQLDBLib

    Inherits DBLib

To work with data types, I include the following enumeration:

PublicEnum MSSQLDataType

    SQLString

    SQLChar

    SQLInteger

    SQLBit

    SQLDateTime

    SQLDecimal

    SQLMoney

    SQLImage

    SQLFloat

EndEnum

I also included the following members to work with stored procedures:

    Private dtParameterList As New DataTable

    Private arParameterList As New ArrayList

The source code for MSSQLDBLib is included as part of the download.  You can download it and use it for yourself. 

The entire series was created only for understanding the development of data helpers with object orientation. Before trying to use this data helper in your applications, I request that you go through the Microsoft Data Access Blocks, which include several features.

Any feedback, suggestions, bugs, errors, improvements etc., are highly appreciated at jag_chat@yahoo.com.

blog comments powered by Disqus