ADO.NET transaction encapsulation

In the process of compiling database tool class, to avoid duplicate operation of transaction object assignment of each atomic operation, the method to expose to the outside is as follows

   public bool ExecuteTransition(Action TransitionAction, out string ExceptionStr)

The external incoming database operations are uniformly packaged with delegation, and the internal Transaction operations are carried out. First of all, we need to understand that the embodiment of database Transaction operation in ADO.NET coding is that DbConnection is the same, and DbCommand Transaction is the same.


First of all, we need to know the context of each database operation, whether it is in the TransitionAction delegation. For simplicity, when executing the TransitionAction, we need to open a Task, obtain the thread ID of the current thread as the unique identification of the transaction delegation, generate a DbTransaction and put it into a TransactionDic, execute the SQL statement in the SqlHelper execution class, and create Co During connection, obtain the current ThreadID and search in TransactionDic. If there is a corresponding Transition, it means that the SQL statement is executed in a transaction. Connection takes the database connection of the Transition directly and assigns a value to the Transition object of DbCommand


This solution is also feasible for executing database operations or other combined operations in method classes in TransitionAction, but further improvement is needed for nested transactions.

For example, if I encapsulate a framework workflow method MethodA, I will carry out my own things, but I need to combine things with business update method MethodB. The above scheme does not meet the requirements, so we need to improve it, and judge whether the current thing TransitionAction is a nested transaction, that is, TransitionAction B is actually packaged in TransitionAction a. In the process of adding DbTransaction of TransitionAction, we need to take the thread ID out of the Task, which is called rootthread ID here. At the same time, we need to maintain a concurrentdictionary < string, list < string > > transitionidmapdic, which is used to maintain the relationship between rootthread ID and thread ID of nested transactions. When creating a Task, we can judge whether the current thread ID is in TransactionDic, save it You need to merge the current TransitionAction into the Root thing


TransactionManage code

 public class TransactionManage
    {
        private static ConcurrentDictionary<string, LocalTransaction> TransactionDic = new ConcurrentDictionary<string, LocalTransaction>();
        private static ConcurrentDictionary<string, List<string>> TransitionIDMapDic = new ConcurrentDictionary<string, List<string>>();
        public static void AddTransition(string TransitionID,string RootThreadID,  DbTransaction Transition,Action TransitionAction)
        {
            LocalTransaction LT = new LocalTransaction();
            LT.RootThreadID = RootThreadID;
            LT.TransitionID = TransitionID;
            LT.Transition = Transition;
            //Add execution list Action
            LT.AddTransitionAction(TransitionAction);
            TransactionDic.TryAdd(TransitionID, LT);
            //Add transaction root thread ID Transactions related to nested transactions ID
            TransitionIDMapDic.TryAdd(RootThreadID, new List<string>() { TransitionID });

        }

        public static void ContactTransition(string TransitionID, string RootThreadID,Action TransitionAction)
        {
            LocalTransaction LT = TransactionDic[RootThreadID];
            if (!TransactionDic.ContainsKey(LT.RootThreadID))
            {
                LT.TransitionID = TransitionID;
                //Add execution list Action
                LT.AddTransitionAction(TransitionAction);
                TransactionDic.TryAdd(TransitionID, LT);
                //Add transaction root thread ID Transactions related to nested transactions ID
                List<string> TransitionIDS = TransitionIDMapDic[LT.RootThreadID];
                TransitionIDS.Add(TransitionID);
                TransitionIDMapDic[LT.RootThreadID] = TransitionIDS;
            }
            else
            {
                ContactTransition(TransitionID, LT.RootThreadID, TransitionAction);
            }
        }

        public static string GetRootID(string TransitionID)
        {
            LocalTransaction LT = TransactionDic[TransitionID];
            if (!TransactionDic.ContainsKey(LT.RootThreadID))
            {
                return LT.RootThreadID;
            }
            else
            {
                return GetRootID(LT.RootThreadID);
            }
        }

        public static LocalTransaction GetTransition(string TransitionID)
        {
            LocalTransaction LT = null;
            TransactionDic.TryGetValue(TransitionID, out LT);
            return LT;
        }
        public static bool ContainsTransition(string TransitionID)
        {
            return TransactionDic.ContainsKey(TransitionID);
        }
        public static void RemoveTransition(string TransitionID)
        {
            string RootID = GetRootID(TransitionID);
            List<string> TransitionIDList = null;
            TransitionIDMapDic.TryRemove(RootID, out TransitionIDList);
            foreach (string TransitionIDItem in TransitionIDList)
            {
                LocalTransaction LT = null;
                TransactionDic.TryRemove(TransitionIDItem, out LT);
            }
        }

Implementation method of external affairs

   public bool ExecuteTransition(Action TransitionAction, out string ExceptionStr)
        {
            bool IsSuccess = true;
            ExceptionStr = string.Empty;
            string RootThreadID = Thread.CurrentThread.ManagedThreadId.ToString();
            var TrabsitionTask = new Task<LocalTransactionResult>(() =>
            {
                string TransitionID = Thread.CurrentThread.ManagedThreadId.ToString();
                LocalTransactionResult Result = new LocalTransactionResult();
                if (!TransactionManage.ContainsTransition(RootThreadID))
                {
                    using (DbConnection connection = DBExecute.CreateConnection(ConnectionString))
                    {
                        connection.Open();
                        DbTransaction Transaction = connection.BeginTransaction();
                        TransactionManage.AddTransition(TransitionID, RootThreadID, Transaction, TransitionAction);
                        try
                        {
                            TransactionManage.GetTransition(TransitionID).Execute();
                            Transaction.Commit();
                        }
                        catch (System.Exception e)
                        {
                            Result.ExecuteStatus = false;
                            Result.ExceptionMessage = e.Message;
                            Transaction.Rollback();
                        }
                        finally
                        {
                            Transaction.Dispose();
                            connection.Close();
                            connection.Dispose();
                            Transaction = null;
                            TransactionManage.RemoveTransition(TransitionID);
                        }
                        return Result;
                    }
                }
                else
                {
                    //Currently, it is a nested transaction, not executed, and executed uniformly by the root transaction
                    TransactionManage.ContactTransition(TransitionID, RootThreadID, TransitionAction);
                    Result.ExecuteStatus = true;
                    Result.ExceptionMessage = string.Empty;
                    return Result;
                }
              
            });
            TrabsitionTask.Start();
            TrabsitionTask.Wait();
            IsSuccess = TrabsitionTask.Result.ExecuteStatus;
            ExceptionStr = TrabsitionTask.Result.ExceptionMessage;
            return IsSuccess;

        }

Complete module code address: https://gitee.com/grassprogramming/FastExecutorCore/tree/master/code/FastCore/FastCore/FastORM

Note: although it's convenient for me to use threads, there may be problems with multithreading in the actual use process. In the future, I will modify the binding of context objects to the execution class

Tags: C# Database SQL

Posted on Sun, 12 Jan 2020 07:52:28 -0500 by jwrightnisha