Business Layer and Transactions in the Data Layer

[Guest]

I have a BO that must call 2 functions each function residing in 2 different
DO.
BO->DO1.Update1
BO->DO2.Update2

Let's say that each DO call point to the same SQL Server.
Let's say that each BO is on my Appserver and the 2 DOs on the SQL Server.

I want to make a transaction so I am assuming that the BO is handling it.
How do I create the transaction and the connections on the BO and DO's
What about .NET Remoting in all this.

If I create a SQLConnection in the BO and pass the SQLTransaction to the DO
and try to enlist the connection(opened in the DO), the EnlistConnection
takes only a transaction object but SqlTransaction cannot be converted to a
Transactions.Transaction type ....

BO.startTransaction
DO1.Update1(tx as sqlTransaction) 'creates its own connection? receives it
from BO?
DO2.Update2(tx as sqlTransaction)
BO.Commit or Rollback

Unfortunately, there is not much information regarding the way to handle
transactions between tiers, all the examples I find are transactions
happenning in the same object.

Thanks for your help.

 

[Cowboy \(Gregory A. Beamer\)]

I do not know how your DataObjects are fashioned, but if you accept a
transaction, you can wrap the entire "transaction" on multiple objects in
the same transaction. If you are wrapping muliple objects in a single
transaction, you should create the transaction outside of the data objects
as it gives you more control.

As for the Remoting (or web service, etc.) question, you should create a
service interface that accepts the BO, basically acting as a facade between
business and data. You then create both data objects and the transaction on
that side of the boundary. Do not start remoting all sorts of objects, esp.
connection and transaction objects across the service boundar. it will kill
you.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think outside of the box!
*************************************************

 

[Guest]

I understand all this. Thanks. The thing is that I don't know how to code
all this....
Which transaction (sqltransaction or simply transactions.transaction) should
I create inside the BO to pass to the DOs ?

Any good reference sites or even better books on the subject of transactions?

Thanks all.

 

[Kevin Yu [MSFT]]

Hi,

You might need to make many changes or add some overloads to methods to
your DO to make it support transactions. You can get a SqlTransaction
object from SqlConnection.BeginTransaction method and apply to serveral
SqlCommands. The transaction can be passed through BO to DO, to include all
the database operations for serveral BOs in one transaction.

I only have samples for how to complete a transaction.

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqltransactio
n.aspx

You can check it and add transaction support to DO. And call DO from BO
with transaction.

HTH.

Kevin Yu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[ferguslogic]

I have this exact same issue and am looking for a workable solution

Have you had any luck resolving this

 

[Guest]

What I did is the following and I guess this is not the best solution but
it's ok in my case. I start a Transaction from the BO and pass it to the DO.
The Transaction passed to the DO contains the opened connection. I assign
the tx.connection to my command, the tx itself to the command and execute it.
I return true or false to my BO and the BO decide to commit or rollback.


Hope it helps

From my BO
------------------
Dim tx As SqlClient.SqlTransaction = Nothing
Using con As New SqlClient.SqlConnection(SQLHelper.GetConnectionString)
Try
con.Open()
tx = con.BeginTransaction(Data.IsolationLevel.ReadUncommitted)
Catch ex As Exception
'catch error
Return False
End Try

Dim uDO As New UserDO()

Try
If Not _uDO.UpdateUser(tx,userId) Then
txFailedCount += 1
End If
Catch ex As Exception
'catch error
Return False
End Try

If txFailedCount = 0 Then
tx.Commit()
Else
tx.Rollback()
End If

From my DO
---------------
Public Function UpdateUser(ByVal tx As SqlTransaction, ByVal userId As
Integer) As Boolean

Dim aCmd As SqlCommand
Dim dr As SqlDataReader = Nothing

Try
aCmd = SQLHelper.createSqlCommand("UpdateUser")
aCmd.Connection = tx.Connection
aCmd.Transaction = tx
dr = aCmd.ExecuteReader(CommandBehavior.SingleResult)
dr.Close()

If Not aCmd Is Nothing AndAlso CType(aCmd.Parameters.Item(0).Value,
Integer) = -1 Then
Return False
End If

Return True
Catch ex As Exception
'error
Return False
End Try
End Function