Transactions in subsonic, SPT helper object

Hello everyone,

I have gone through the forum in search for more info about transactions. I understand there are are two ways to do it:

• SharedDbConnectionScope inside TransactionScope, which seems to work for MS SQL 2005 though (on MS SQL 2000 you end up with distributed transactions, not sure about any support in MySQL, sqlLite etc)
• use simple pass-through, which requires you to do things manually (getting the insert/update commands, taking care of the object status, taking care of objects deleted from the collection)

Are there any plans to implement transactions into the basic classes / db providers similarly to how SharedDbConnectionScope make it use a single transaction? Maybe I am missing something but it seems to me it would not be too difficult and I would volunteer to do it, too :)

To simplify working with transactions in our project I created a simple helper class that collects sql commands from record objects and collections, runs them and updates the records as clean if the transaction succeeds. It won't load primary keys for you and it won't take care of deleted records. But it works for me and maybe it will make sense for others.

To use it, you replace this:

                    col1.SaveAll(userName);
                    record1.Save(userName);
                    col2.SaveAll(userName);
                    col3.SaveAll(userName);
                    col4.SaveAll(userName);

with this:

                SubsonicTransaction tran = new SubsonicTransaction(userName);
                tran.AddCollection(col1);
                tran.AddRecord(record1);
                tran.AddCollection(col2);
                tran.AddCollection(col3);
                tran.AddCollection(col4);
                tran.Process();

Here is the source code:

       public class SubsonicTransaction
        {
            QueryCommandCollection collection = new QueryCommandCollection();
            List<object> objectsInvolved = new List<object>();
            string _userName = string.Empty;
            public SubsonicTransaction(string userName)
            {
                _userName = userName;
            }
            public SubsonicTransaction()
            {}
            public void AddRecord(object record)
            {
                QueryCommand cmd = (QueryCommand)record.GetType().InvokeMember("GetSaveCommand", System.Reflection.BindingFlags.InvokeMethod, null, record, new object[ { _userName });
                if (cmd != null)
                {
                    collection.Add(cmd);
                    objectsInvolved.Add(record);
                }
            }
            public void AddCollection(System.Collections.IList col)
            {
                foreach (object record in col)
                {
                    AddRecord(record);
                }
            }
            public void AddRecord() { }
            public void Process()
            {
                DataService.ExecuteTransaction(collection);
                foreach (object record in objectsInvolved)
                {
                    record.GetType().InvokeMember("MarkOld", System.Reflection.BindingFlags.InvokeMethod, null, record, null);
                    record.GetType().InvokeMember("MarkClean", System.Reflection.BindingFlags.InvokeMethod, null, record, null);
                }
            }
        }

Very good post and very helpful if I may add. I've been looking for some time for something like this. The only thing is that it does not handle queries but that was easily fixed. Thanks again and I quote below the changes (in VB) just in case someone else need this.

    Public Class SubsonicTransaction

        Dim collection As New QueryCommandCollection()
        Dim objectsInvolved As New List(Of Object)
        Dim _userName As String = String.Empty

        Public Sub New(ByVal userName As String)
            _userName = userName
        End Sub

        Public Sub New()
        End Sub

        Public Sub AddRecord(ByVal record As Object)
            Dim cmd As QueryCommand
            If TypeOf record Is Query Then
                cmd = DirectCast(record, Query).BuildCommand
            Else
                cmd = CType(record.GetType().InvokeMember("GetSaveCommand", System.Reflection.BindingFlags.InvokeMethod, Nothing, record, New Object() {_userName}), QueryCommand)
            End If
            If (cmd IsNot Nothing) Then
                collection.Add(cmd)
                objectsInvolved.Add(record)
            End If
        End Sub

        Public Sub AddCollection(ByVal col As System.Collections.IList)
            For Each record As Object In col
                AddRecord(record)
            Next record
        End Sub

        Public Sub AddRecord()
        End Sub

        Public Sub Process()
            DataService.ExecuteTransaction(collection)
            For Each record As Object In objectsInvolved
                If Not TypeOf record Is Query Then
                    record.GetType().InvokeMember("MarkOld", System.Reflection.BindingFlags.InvokeMethod, Nothing, record, Nothing)
                    record.GetType().InvokeMember("MarkClean", System.Reflection.BindingFlags.InvokeMethod, Nothing, record, Nothing)
                End If
            Next record
        End Sub

    End Class

I submitted a patch awhile ago that adds lightweight database transaction support to SubSonic.  I believe it would do what you want here.  It functions more or less the same as the recommended method for transactions, but it doesn't use System.Transactions, so you don't have to worry about compatibility with database platforms other than SQL Server, or having the DTC service running.  I am hopeful that it will make its way into the project because I think a lot of us need a way to do simple database transactions with SubSonic.  You can find the patch here:

http://www.codeplex.com/subsonic/SourceControl/PatchList.aspx

The patch I am talking about is #1392.

Why have they not worked this patch into the main line of development?  This is really needed, as the only way to do transcactions right now (for sql server 2000) is by using the DTC, and I cannot use that method. 

Please be mindful..."they" is really just two people, working on this outside of their day jobs. You are likely using this at your day job (as am I). Most of the other committers on the project haven't made a commit in the past year.

if I have some Update and Delete!