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

This article is not an introductory article to COM+ or ADO.NET.  If you are very new to COM+, I suggest you to go through a previous article of mine that serves as more of an introduction here. 

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

This article is the second of two parts. You can find the first part here.

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 that the comment written in the code which looks like an “ApplicationName” attribute 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 the 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 didn’t really make 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 performance.  You can customize them according to your requirements.

Customization of the 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 “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 the “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 (multiple rows)

The following template will help you to 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 SELECT that 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

Executing SQL statements 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.