Transaction Scope on multiple object saves

Transaction Scope on multiple object saves

Old forum URL: forums.lhotka.net/forums/t/10293.aspx


superservo15 posted on Thursday, April 21, 2011

Hi,

I'm not sure if this has been asked a million times or not, but I can't seem to find anything yet so I thought I would post here :) We are building a vb.net app using CSLA 3.8.4, SQL Server 2005 and an MVC pattern.

On many of our pages, we are performing multiple saves or updates to different obejcts that are not children of parents or anything RI related. I need to wrap all of my saves and updates inside of a transaction... However I haven't been able to figure out if I can leverage the existing Transaction code in the classes since not all of my obejcts are related. 

Is anyone here familiar with how I can do this? Just old fashioned Transaction Scope coding and then I might need to alter the data access to manage the connection strings with the transaction or..?

Any help or guidance would be appreciated. I keep going in circles as I'm not sure the best way to do this.

Thanks in advance :)

thaehn replied on Thursday, April 21, 2011

Have you tried creating one parent business object for the whole page and make all the others children? 

RockfordLhotka replied on Thursday, April 21, 2011

Typically you'd use a unit of work pattern (and object) to do this.

There's some info in the FAQ: http://www.lhotka.net/cslanet/faq/CslaObjectFaq.ashx

And this is discussed in the Using CSLA 4 ebook series in some depth.

superservo15 replied on Tuesday, April 26, 2011

Hi,

Don't have access to the books or video, but I am wondering, with UoW, are you suggesting making like a custom class that would do all my saves inside and the transaction should be started there? I'm not entirely sure what I am supposed to do with this (relative newbie to csla 3)

The problem I'm currently working on is I have a new Plan object being created. I need to take the new plan ID and create relationships between the plan and the provisions the plan is related to. Then for each of these plan / provision relationship IDs, I need to make more relationships with features that relate to the plan/provisions...

I don't really want to make page level objects for each page, but I'm thinking that is what this UoW basically is... I was hoping to make minimal changes to the generated files... however I'm having problems with each data access object creating a new connection string... does CSLA not share connection strings for the transactions? I've implemented TransactionScope several times before but this was quite confuses me...

Thanks for any more input you might possibly provide :)

RockfordLhotka replied on Tuesday, April 26, 2011

A UoW is typically a read-only (for object retrieval) or command object (for object updates) that simply manages server-side interactions with multiple normal root objects.

This is extremely common in Silverilght, because server calls are async. So it is normal to create a read-only UoW to retrieve all the objects necessary for a given screen (often the editable business object, and one or more NVL or other list objects).

It is far less common to update multiple objects at the same time, but that's done using a command object.

A UoW is almost never very complex. They typically have a property for each root object they retrieve/update. And they have a DP_Fetch method that invokes the normal root object factories (on the server) to set the properties. In a command UoW there's a DP_Execute that calls the normal sync Save methods on each root object - all within a transaction (usually through the [Transactional] attribute on the DP_Execute method).

superservo15 replied on Wednesday, April 27, 2011

Hi, thanks for the reply... sooo what you;re saying is that I should be using a common object to do multiple saves on different objects and that UoW is not what I need?

My problem is that CSLA is creating a new connection string for each data access method so if I try to start a parent transaction around all of my inserts and updates it fails on the second one because my transaction needs to use the same connection... I've been trying to see if there is another way to do this with CSLA without butchering the generated classes... is there or is CSLA not really the right framework to be using for what I need?

Thanks again, I'm finding there is a lot to learn and I'm just trying to sift through it all :)

RockfordLhotka replied on Wednesday, April 27, 2011

To be a little blunt, I'm not going to copy-paste the sections of the Using CSLA 4: Data Access ebook that answers your question completely :)  Although I give the framework away, I do have to fund all this work somehow.

Fortunately, all that information is lurking somewhere in the archives of this forum, or in the Samples download - you'll need to dig it out manually to avoid spending the money on the consolidated documentation in the ebook(s).

If you need to save multiple root objects within the context of a single transaction, you need a UoW command object.

Additionally, if you are using TransactionScope transactions, you should be using the connection or context manager classes from Csla.Data. This is because the .NET TransactionScope object will use the DTC if you attempt to open more than one database connection within a single transaction, and most environments don't have the DTC configured (and even if you do - the perf hit is something to avoid).

The connection and context manager types in Csla.Data help keep the database connection open so you automatically reuse it across all the objects within a given data portal call.

There's a specific coding pattern you should use when building data access code for CSLA objects, where each object uses a connection/context manager to get at the database connection. If you follow that pattern, then the UoW command object can wrap all the Save calls for the multiple root objects, and they'll run within the same transaction, and reuse the same connection.

For example, in the command object (pseudocode):

[Transactional(TransactionScope)]
private void DataPortal_Execute()
{
  using (var ctx = ConnectionManager<SqlConnection>.GetManager("mydatabase"))
  {
    Customer = Customer.Save();
    Product = Product.Save();
  }
}

That assumes the UoW command object has Customer and Product properties - each containing an editable root object you want to save.

superservo15 replied on Wednesday, April 27, 2011

LOL Rocky :) I do own several incarnations of your books, but someone at the office has borrowed it and I have not gotten it back yet and haven't even had a chance to get through all of the latest one before I got trust upon this new project :) But I completely understand, sorry for being such a brat!

Thanks for the reply, this is much more for me to chomp on and better than most of the other threads have have found so far... Now to get it to work :)

RockfordLhotka replied on Wednesday, April 27, 2011

fwiw, only the Using CSLA 4 ebook series and the Core 3.8 video series provide any real coverage of the UoW technique.

superservo15 replied on Thursday, May 05, 2011

Hey, I know I marked as answer but I have another question (and I'm sure it will seem dumb)...

I am making a Unit of Work class that does a

using (SqlConnection conn ConnectionManager<SqlConnection>.GetManager("Db").Connection) 
{ 
    connectionTransaction
= conn.BeginTransaction(); 
 
    objectRef
= objectRef.Save(); 
 
   
//other logic here 
 
    objectRef
= objectRef.Save(); 
    connectionTransaction
.Commit(); 
} 

But my objectRef.Save() logic that goes to the Data Access layer either creates a new connection int he Update/Insert or does it's own

Using 

 

 

ctx = ContextManager(Of CUMISCCR.Dal.CCRDataContext).GetManager(CUMISCCR.Dal.Database.CCR)

Which will only use the connection until the using is done or the connection is closed. Any advice on how the pattern should actually work? We got the CSLA 3.8.4 templates from a co-worker, but I'm not sure where he got them from and your samples all seem to use context manager.

Also I was using conmnection manager as we are using stored procs and not LINQ to SQl.

Thanks again :)

RockfordLhotka replied on Thursday, May 05, 2011

The problem is that your using block is using the connection, not the connectionmanager. So the connectionmanager exsits for the duration of a single line of code, not the whole block.

I'm surprised this works - I think CSLA 4 would have closed the connection instantly...

Also, you can't do the transaction like that, because the command objects used in each of the Save methods (actually the DataPortal_XYZ methods) must attach that transaction.

You really need to use the TransactionManager class to make the transaction object available throughout the code.

The Using CSLA 4: Data Access ebook covers these objects in pretty good detail.

superservo15 replied on Thursday, May 05, 2011

Thanks rocky :)

Actually the first blurb i posted was an example I was working off of and yeah, doesn't work...

I have put in a request with my company to purchse the ebooks :) Until then, I have one more question...

I made a UoW and am using the connectionManager (properly this time I hope)... When I call obj.Save, I am wondering, what should my  obj DataPortal_XYZ's look like. Currently one of mine looks like

  <Transactional(TransactionalTypes.TransactionScope)> _
        Protected Overrides Sub DataPortal_Insert()
            Dim cancel As Boolean = False
            OnInserting(cancel)
            If (cancel) Then
                Return
            End If
   
            Using connection As New SqlConnection(ADOHelper.ConnectionString)
                connection.Open()
                Using command As New SqlCommand("[dbo].[CSLA_Client_Insert]", connection)
                    command.CommandType = CommandType.StoredProcedure
                    //paramters go here    
                    command.ExecuteNonQuery()
   
                    Using (BypassPropertyChecks)
                        ConcurrencyCheck = DirectCast(command.Parameters("@p_ConcurrencyCheck").Value, System.Byte())
                        _clientID =  DirectCast(command.Parameters("@p_ClientID").Value,System.Int32)
                    End Using
                End Using
               
   
                FieldManager.UpdateChildren(Me, connection)
            End Using
   
            OnInserted()
        End Sub

So I'm wondering, should I make all of my data access methods accept an optional paramter of connection, and if it exists use it, otherwise continue to use the new connection? The fact that we have a new connection in every method is what's killing me. Also every example I have found so far all use the "using someManager" code so once the using is done, the transaction is done too, which is not what i want

Thanks again and don't worry, books will be purchased :) Especially since I can't seem to find what i need (yet) :) Thanks for the hard work and help.

superservo15 replied on Thursday, May 05, 2011

Actually I think I finally found what I need in Chapter 16 of your Expert 2008 Business Objects book... I didn't realize that multiple nexted usings have a counter and don't close the ConnectionManager object if there are nested usings... I think I just need to switch all my DataPortal_XYZs to use

 Using ctx = ConnectionManager(Of SqlConnection).GetManager("MyDb")
' insert object's data here using ctx.Connection
End Using

And have my UoW start the transaction from within it's own using.
Thanks for all the help and great framework :)

Santosh Wavare replied on Tuesday, April 23, 2013

'Usage
Public Function Transfer(ByRef transactionAmount As Integer, ByRef sourceAccount As Integer, _
                         ByRef destinationAccount As Integer) As Boolean

  Dim result As Boolean = False

  ' Create the database object, using the default database service. The
  ' default database service is determined through configuration.
  Dim db As Database = DatabaseFactory.CreateDatabase()

  ' Two operations: one to credit an account and one to debit another account.
  Dim sql As String = "CreditAccount"
  Dim creditCommand As DbCommand = db.GetStoredProcCommand(sql)

  db.AddInParameter(creditCommand, "AccountID", DbType.Int32, sourceAccount)
  db.AddInParameter(creditCommand, "Amount", DbType.Int32, transactionAmount)

  sql = "DebitAccount"
  Dim debitCommand As DbCommand = db.GetStoredProcCommand(sql)
  db.AddInParameter(debitCommand, "AccountID", DbType.Int32, destinationAccount)
  db.AddInParameter(debitCommand, "Amount", DbType.Int32, transactionAmount)

  Using conn As DbConnection = db.CreateConnection()

    conn.Open()
    Dim trans As DbTransaction = conn.BeginTransaction()

    Try
      ' Credit the first account.
      db.ExecuteNonQuery(creditCommand, trans)
      ' Debit the second account.
      db.ExecuteNonQuery(debitCommand, trans)
      ' Commit the transaction.
      trans.Commit()
      result = True
    Catch
      ' Roll back the transaction.
      trans.Rollback()
    End Try
    conn.Close()

    Return result

  End Using

End Function

Copyright (c) Marimer LLC