Developing Your Own SQL Server Based Data Access Helper using COM+ and VB.NET

This article is not an introductory piece on COM+ or ADO.NET. If you are very new to COM+, I suggest you go through my previous article at http://www.aspfree.com/c/a/VB.NET/Building-a-Robust-and-Highly-Scalable-
Distributed-Architecture-using-VB-NET/

The entire source code for this article is available in the form of a downloadable zip.  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.

Setting up the COM+ attributes

For any COM+ applications, you need to set up/configure few of the attributes which would affect your application, together with performance and identity. In our case, the following statements will be necessary to configure the COM+ application:

<Assembly: ApplicationName("CoreMSSQLDataAccessHelper")>

<Assembly: ApplicationActivation(ActivationOption.Server)>

<Assembly: AssemblyKeyFile("../../CoreMSSQLDataAccessHelper.snk")>

<Assembly: ApplicationAccessControl()>

The first statement is mainly necessary to provide the name of the application.  You can also understand the comment written in the code which looks like an “ApplicationName” attribute; it specifies the name of the COM+ application that will hold assembly components.

The second statement is mainly necessary to make our COM+ application a server based application. The ApplicationActivation.ActivationOption attribute specifies where assembly components are loaded on activation. It could be either Library or Server. The option “Library” makes our application run in the consumer/creator’s process. The option “Server” causes our application to be executed in a separate system process (totally different from the consumer/creator’s process).

The third statement is mainly necessary to make our COM+ application strongly signed. The key file (or Assembly key file) specifies the name of the strong key that will be used to sign the assembly. The sink file must be generated using “sn.exe” as follows:

sn -k CoreMSSQLDataAccessHelper.snk

Every COM+ application is equipped with its own security features by default. To work with default security features, the fourth statement is necessary. There exist several options for security. It may be necessary to deal with a few of them based on your application requirements.

Customizing the COM+ class a bit

It may be necessary to customize the definition of the COM+ class a bit according to our requirements.  Let us consider the following definition, which I modified for our application:

<ConstructionEnabled([Default]:="Connection String is not implemented from this point."), _
 Transaction(TransactionOption.Supported), _
 JustInTimeActivation(True), Serializable(), _
 ObjectPooling(MinPoolSize:=5, MaxPoolSize:=25, CreationTimeout:=20000), _
 ComponentAccessControl(), SecurityRole("Administrator"), SecurityRole("User", SetEveryoneAccess:=True)> _
PublicClass CDataAccess
    Inherits ServicedComponent

By nature, a COM+ would be quite different from any normal OOP-based class.  The first attribute in the above definition is the “ConstructionEnabled” attribute.  This is generally used to provide database-related “connection string” information or other configuration options.  At this moment, I'm not really making use of “connectionstring” from this attribute.  That is why I wrote a small comment there. 

If you really wanted to implement the connection string through configuration properties, you may need to modify the code as follows:

ProtectedOverrides Sub Construct(ByVal constructString As String)
        ' Construct method will be called after the New method
        m_ConnectionString = constructString
EndSub

The class level variable m_ConnectionString would hold the connection string you specified in the configuration properties of the COM+ application.

The second attribute is the “Transaction” attribute.  A COM+ application may or may not need to support any transactions.  But, as we are developing a data access helper, it may be necessary to have a COM+ application with transaction support.

Further, I defined “JustInTimeActivation,” “Serializable” and “ObjectPooling.”  Those are essential for the performance. You can customize those according to your requirements.

Customization of roles of security is possible through the “SecurityRole” attribute.  And last but not least, every COM+ class must inherit from the class “ServicedComponent” available in the “System.EnterpriseServices” namespace.

Getting a row of data from the database using a single method

The following code gives you an idea of how to simply return a “datarow” based on the SQL SELECT statement provided as a parameter to it.

PublicFunction getDataRow(ByVal sqlSELECT As String) As
System.Data.DataRow
        Dim da As SqlDataAdapter
        Try
            Dim dt As New DataTable
            da = New SqlDataAdapter(sqlSELECT, _ConnectionString)
            da.Fill(dt)
            da.Dispose()
            If dt.Rows.Count = 0 Then Return Nothing Else Return
dt.Rows(0) 'return only first row
        Catch ex As Exception
            Try
                da.Dispose()
            Catch e As Exception
                'do nothing...if still error persists
            End Try
            Throw New Exception(ex.Message & ". SQL Statement: "
& sqlSELECT)
        End Try
    End Function

Before proceeding further you may need to understand the issue of “ConnectingString.”  I made it simple to provide the “ConnectionString” as follows:

Private_ConnectionString As String
PublicWriteOnly Property ConnectionString() As String
        Set(ByVal Value As String)
            _ConnectionString = Value
        End Set
    End Property

Retrieving more than one row

The following template will help you retrieve more than one row in the form of a data table.

PublicFunction getDataTable(ByVal sqlSELECT As String) As
System.Data.DataTable
        Dim Conn As SqlConnection
        Dim da As SqlDataAdapter
        Try
            Conn = New SqlConnection(_ConnectionString)
            Dim dt As New DataTable

            da = New SqlDataAdapter(sqlSELECT, Conn)
            da.Fill(dt)
            da.Dispose()
            Return dt

        Catch ex As Exception
            Try
                da.Dispose()
            Catch e As Exception
                'do nothing...if still error persists
            End Try
            Throw New Exception(ex.Message & ". SQL Statement: "
& sqlSELECT)
        End Try
    End Function

You can also retrieve a “DataView” object as follows:

PublicFunction getDataView(ByVal sqlSELECT As String) As System.Data.DataView
        Return New DataView(getDataTable(sqlSELECT))
    End Function

Adding a few more helper methods

To retrieve a single value from the database based on the SELECT we provide, the following template will help you:

PublicFunction getRowValue(ByVal sqlSELECT As String) As String
        Dim Conn As SqlConnection

        Dim cmd As SqlCommand
        Dim value As String = ""
        Try
            Conn = New SqlConnection(_ConnectionString)

            cmd = New SqlCommand(sqlSELECT, Conn)
            With cmd
                .Connection.Open()
                value = .ExecuteScalar() & "" 'concatenating an
empty string..to eliminate null or nothing
                .Connection.Close()
                .Dispose()
            End With
            Return value
        Catch ex As Exception
            Try
                If cmd.Connection.State = ConnectionState.Open
Then
                    cmd.Connection.Close()
                    cmd.Dispose()
                End If
            Catch e As Exception
                'do nothing...if still error persists
            End Try
            Throw New Exception(ex.Message & ". SQL Statement: "
& sqlSELECT)
        End Try
    End Function

To execute any SQL statement given by the user, the following will help you:

PublicOverloads Sub SQLExecute(ByVal strSQL As String)
        Dim Conn As SqlConnection
        Dim cmd As SqlCommand
        Try
            Conn = New SqlConnection(_ConnectionString)
            cmd = New SqlCommand(strSQL, Conn)
            With cmd
                .Connection.Open()
                .ExecuteNonQuery()
                .Connection.Close()
                .Dispose()
            End With
        Catch ex As Exception
            Try
                If cmd.Connection.State = ConnectionState.Open
Then
                    cmd.Connection.Close()
                    cmd.Dispose()
                End If
            Catch e As Exception
                'do nothing...if still error persists
            End Try
            Throw New Exception(ex.Message & ". SQL Statement: "
& strSQL)
        End Try
    End Sub

Execute the SQL statement along with BLOB values

The following method shows how to develop a helper method to work with BLOB values.

    Public Overloads Sub SQLExecute(ByVal strSQL As String, ByRef
BLOBColNames() As String, ByRef ByteArrayObjects() As Object)
        Dim Conn As SqlConnection
        Dim cmd As SqlCommand
        Try
            Conn = New SqlConnection(_ConnectionString)
            'validations
            If BLOBColNames Is Nothing Then
                Throw New Exception("No file column names are
provided")
            End If
            If ByteArrayObjects Is Nothing Then
                Throw New Exception("No ByteArray(ByteArray
objects) are provided to upload the files")
            End If
            If BLOBColNames.Length <> ByteArrayObjects.Length
Then
                Throw New Exception("No. of col names doesn't
match with no. of Files to be uploaded")
            End If
            cmd = New SqlCommand(strSQL, Conn)
            Dim i As Integer
            Dim ColName As String
            Dim FileByteArray() As Byte
            For i = 0 To BLOBColNames.Length - 1
                If BLOBColNames(i) Is Nothing Or ByteArrayObjects
(i) Is Nothing Then
                    Throw New Exception("One of the Column Name
or ByteArrayObject contains no information to upload..")
                End If
                FileByteArray = ByteArrayObjects(i)
                ColName = "@" & Trim(BLOBColNames(i))
                cmd.Parameters.Add(ColName, System.Data.SqlDbType.Image, FileByteArray.Length).Value = FileByteArray
            Next
            With cmd
                .Connection.Open()
                .ExecuteNonQuery()
                .Connection.Close()
                .Dispose()
            End With
        Catch ex As Exception
            Try
                If cmd.Connection.State = ConnectionState.Open
Then
                    cmd.Connection.Close()
                    cmd.Dispose()
                End If
            Catch e As Exception
                'do nothing...if still error persists
            End Try
            Throw New Exception(ex.Message & ". SQL Statement: "
& strSQL)
        End Try
    End Sub

The following is a simple way to execute the above in DAL:

        If Not (m_Customer.arPhoto Is Nothing) Then
            If m_Customer.arPhoto.Length > 0 Then
                Dim ImageColumns() As String = New String()
{"Photo"}
                Dim ImageData() As Byte = m_Customer.arPhoto
                Dim ByteArrayObjects() As Object = New Object()
{ImageData}
                db.SQLExecute("update tCustomers set Photo=@Photo
where ICNO='" & m_Customer.sICNO & "'", ImageColumns,
ByteArrayObjects)
            End If
        End If

Retrieving a BLOB value from the database

The following method gives you an idea of how to retrieve BLOB values from the database:

    Public Sub getBLOBValue(ByVal sqlSELECT As String, ByRef
BinData() As Byte)
        Dim Conn As SqlConnection
        Dim cmd As SqlCommand
        Dim ByteArray() As Byte
        Try
            Conn = New SqlConnection(_ConnectionString)
            cmd = New SqlCommand(sqlSELECT, Conn)
            Dim dr As SqlDataReader
            With cmd
                .Connection.Open()
                dr = .ExecuteReader()
                dr.Read()
                ByteArray = dr.GetValue(0)
                BinData = ByteArray
                dr.Close()
                .Connection.Close()
                .Dispose()
            End With
        Catch ex As Exception
            Try
                If cmd.Connection.State = ConnectionState.Open
Then
                    cmd.Connection.Close()
                    cmd.Dispose()
                End If
            Catch e As Exception
                'do nothing...if still error persists
            End Try
            Throw New Exception(ex.Message & ". SQL Statement: "
& sqlSELECT)
        End Try
    End Sub

Please note that I used the “ByRef” in the above method to send back the BLOB data.

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