I had the same problem when trying to do transactional updates in separate objects.
The way I got round it was to add an SQL Connection property to the criteria class that is passed into the DataPortal_Execute and to pass the live connection that way. Although I am not sure if this is correct, it is working well for transactions where multiple updates occur in different objects.
Let me know if you need more help/code.
Bonio
Correct - System.Transactions does not inspect the connection string when determining whether or not or promote the transaction to the DTC. If you open a second connection within a transaction, it's going to get promoted - even if the connection is to the same database as your first connection. Passing the connection object among objects is a common way to avoid this behavior.
Also, if you're using SQL 2000, please note that it doesn't fully support the new System.Transactions namespace, so even if you only open a single connection using System.Transactions and are using SQL 2000, it will be promoted to the DTC. There is a workaround for this, though - see http://www.lhotka.net/Article.aspx?area=4&id=39c79955-ddc9-42c7-a657-d5c2ed49975e for more details.
Unfortunately it turns out that System.Transactions isn't quite as smart as would be nice, and it doesn't realize that you are reusing the same connection parameters on subsequent connections.
Toward this end, in version 2.1 I'm adding ApplicationContext.LocalContext - which works just like ClientContext and GlobalContext today, but is entirely ignored by the data portal. You can use it on the app server to store a connection or transaction object - making it available to all your code without having to pass parameters.
The only catch: you need to clean up after yourself or you'll be in trouble - resource leaks and the like.
You can actually do this today using ClientContext. ClientContext isn't returned from the server to the client, so you can use it on the app server exactly like you'll be able to use LocalContext.
Or you can be explicit (which might be better anyway) and pass the connection/transaction object as a parameter to each data method like I did in the book.
Sorry for digging up an old thread, but I am experiencing this problem (in my application - not the projecttracker) and am struggling with a resolution...
I am running csla2.0 (don't have the resources right now to move forward), and experience this problem when trying to update child rows .
I am trying to pass the connection down the hierarchy, but I have a strange(?) behaviour.
I'll put it in 'projecttracker' terms...
I have included a "ByVal cn As SqlConnection" through the necessary class functions.
From my "projectresources" type class..
Friend
Sub Update(ByVal cn As SqlConnection, ByVal cardMoves_selection As CardMoves_Selection) Me.RaiseListChangedEvents = False ' update (thus deleting) any deleted child objects For Each obj As CardMoves_Selection_State In DeletedListobj.DeleteSelf(cn, cardMoves_selection)
Next ' now that they are deleted, remove them from memory tooDeletedList.Clear()
' add/update any current child objects For Each obj As CardMoves_Selection_State In Me If obj.IsNew Thenobj.Insert(cn, cardMoves_selection)
Elseobj.Update(cn, cardMoves_selection)
End If Next Me.RaiseListChangedEvents = True End Sub my "projectresource" type .Update looks like thisFriend
Sub Update(ByRef cn As SqlConnection, ByVal cardmoves_selection As CardMoves_Selection) ' if we're not dirty then don't update the database If Not Me.IsDirty Then Exit Sub Using cn 'As New SqlConnection(Database.crm2k6Connection) If cn.State <> ConnectionState.Open Then cn.Open()CardMoves_Selection_Assignment.UpdateStateAssignment( _
cn, cardmoves_selection.SALESREP, mSTATE, mSELECTED)
MarkOld()
End Using End Suband my "assignments" type subs look like this: -
Public
Sub UpdateStateAssignment(ByRef cn As SqlConnection, _ ByVal salesrep As String, _ ByVal state As String, _ ByVal selected As Boolean) If cn.State <> ConnectionState.Open Then cn.Open() Using cm As SqlCommand = cn.CreateCommand()cm.CommandText =
"C2K6_CardMoves_Selection_UpdateState"DoStateAddUpdate(cm, salesrep, state, selected)
End Using End Sub Private Sub DoStateAddUpdate(ByVal cm As SqlCommand, _ ByVal salesrep As String, _ ByVal state As String, _ ByVal selected As Boolean)cm.CommandType = CommandType.StoredProcedure
cm.Parameters.AddWithValue(
"@SALESREP", salesrep)cm.Parameters.AddWithValue(
"@USERNAME", Csla.ApplicationContext.User.Identity.Name)cm.Parameters.AddWithValue(
"@STATE", state)cm.Parameters.AddWithValue(
"@SELECTED", selected)cm.ExecuteNonQuery()
End Sub
What appears to be happening is that my connection object persists through the process, but for some reason comes back empty...
' add/update any current child objects
For Each obj As CardMoves_Selection_State In Me If obj.IsNew Thenobj.Insert(cn, cardMoves_selection)
Elseobj.Update(cn, cardMoves_selection)
'stepping through the code, the connection object persists until it returns back from .UPDATE
End If Next Me.RaiseListChangedEvents = True
Anyone have any ideas why?
Or alternatively, if someone has an exmaple of how they manage to pass the connection object about successfully, it would be much appreciated.
Thanks,
G.
First thing I see - you're wrapping the child updates in a 'using' clause. That's bad. :-) The connection will be closed and disposed during each child update. That means each child update will have to create/open a new connection, which is why you're seeing the DTC behavior. You want to wrap the 'using' clause around the root object (and ONLY the root object).
//Root object
using (SqlConnection conn = new SqlConnection(blah, blah)
{
//Update root
ChildCollection1.Update(conn);
ChildCollection2.Update(conn);
}
This will eliminate the need to check if the connection is open in the child update statements. It will also only result in one transaction in SQL Server.
gajit:
i had actually given up on the single transaction methodolgy based on ajj's previous post.
However, I'm not sure of the impact of these actions.I also removed the transaction_scope prefixes on my parent object's methods - et voila, it works.... I'm hoping someone might share their wisdom..
But thanks Mark, depending on the answer I get with regards to the impact, I may very well need to go back to the single connection and this will prove very useful.
Copyright (c) Marimer LLC