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);
                }
            }
        }