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:
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.
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:
ProtectedOverridesSub Construct(ByVal constructString AsString) ' 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.
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 AsString) As System.Data.DataRow Dim da As SqlDataAdapter Try Dim dt AsNew DataTable da = New SqlDataAdapter(sqlSELECT, _ConnectionString) da.Fill(dt) da.Dispose() If dt.Rows.Count = 0 ThenReturnNothingElseReturn dt.Rows(0) 'return only first row Catch ex As Exception Try da.Dispose() Catch e As Exception 'do nothing...if still error persists EndTry ThrowNew Exception(ex.Message & ". SQL Statement: " & sqlSELECT) EndTry EndFunction
Before proceeding further you may need to understand the issue of “ConnectingString.” I made it simple to provide the “ConnectionString” as follows:
Private_ConnectionString AsString PublicWriteOnlyProperty ConnectionString() AsString Set(ByVal Value AsString) _ConnectionString = Value EndSet EndProperty
The following template will help you retrieve more than one row in the form of a data table.
PublicFunction getDataTable(ByVal sqlSELECT AsString) As System.Data.DataTable Dim Conn As SqlConnection Dim da As SqlDataAdapter Try Conn = New SqlConnection(_ConnectionString) Dim dt AsNew 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 EndTry ThrowNew Exception(ex.Message & ". SQL Statement: " & sqlSELECT) EndTry EndFunction
You can also retrieve a “DataView” object as follows:
PublicFunction getDataView(ByVal sqlSELECT AsString) As System.Data.DataView ReturnNew DataView(getDataTable(sqlSELECT)) EndFunction
To retrieve a single value from the database based on the SELECT we provide, the following template will help you:
PublicFunction getRowValue(ByVal sqlSELECT AsString) AsString Dim Conn As SqlConnection
Dim cmd As SqlCommand Dim value AsString = "" 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() EndWith Return value Catch ex As Exception Try If cmd.Connection.State = ConnectionState.Open Then cmd.Connection.Close() cmd.Dispose() EndIf Catch e As Exception 'do nothing...if still error persists EndTry ThrowNew Exception(ex.Message & ". SQL Statement: " & sqlSELECT) EndTry EndFunction
To execute any SQL statement given by the user, the following will help you:
PublicOverloadsSub SQLExecute(ByVal strSQL AsString) 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() EndWith Catch ex As Exception Try If cmd.Connection.State = ConnectionState.Open Then cmd.Connection.Close() cmd.Dispose() EndIf Catch e As Exception 'do nothing...if still error persists EndTry ThrowNew Exception(ex.Message & ". SQL Statement: " & strSQL) EndTry EndSub
The following method shows how to develop a helper method to work with BLOB values.
PublicOverloadsSub SQLExecute(ByVal strSQL AsString, ByRef BLOBColNames() AsString, ByRef ByteArrayObjects() AsObject) Dim Conn As SqlConnection Dim cmd As SqlCommand Try Conn = New SqlConnection(_ConnectionString) 'validations If BLOBColNames IsNothingThen ThrowNew Exception("No file column names are provided") EndIf If ByteArrayObjects IsNothingThen ThrowNew Exception("No ByteArray(ByteArray objects) are provided to upload the files") EndIf If BLOBColNames.Length <> ByteArrayObjects.Length Then ThrowNew Exception("No. of col names doesn't match with no. of Files to be uploaded") EndIf cmd = New SqlCommand(strSQL, Conn) Dim i AsInteger Dim ColName AsString Dim FileByteArray() AsByte For i = 0 To BLOBColNames.Length - 1 If BLOBColNames(i) IsNothingOr ByteArrayObjects (i) IsNothingThen ThrowNew Exception("One of the Column Name or ByteArrayObject contains no information to upload..") EndIf 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() EndWith Catch ex As Exception Try If cmd.Connection.State = ConnectionState.Open Then cmd.Connection.Close() cmd.Dispose() EndIf Catch e As Exception 'do nothing...if still error persists EndTry ThrowNew Exception(ex.Message & ". SQL Statement: " & strSQL) EndTry EndSub
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
The following method gives you an idea of how to retrieve BLOB values from the database:
PublicSub getBLOBValue(ByVal sqlSELECT AsString, ByRef BinData() AsByte) Dim Conn As SqlConnection Dim cmd As SqlCommand Dim ByteArray() AsByte 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() EndWith Catch ex As Exception Try If cmd.Connection.State = ConnectionState.Open Then cmd.Connection.Close() cmd.Dispose() EndIf Catch e As Exception 'do nothing...if still error persists EndTry ThrowNew Exception(ex.Message & ". SQL Statement: " & sqlSELECT) EndTry EndSub
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.