原文地址

學(xué)習(xí)后端語(yǔ)言的時(shí)候，都會(huì)涉及到數(shù)據(jù)庫(kù)的相關(guān)操作，不同語(yǔ)言在操作數(shù)據(jù)庫(kù)方面有不同的驅(qū)動(dòng)程序，比如java的JDBC,C#的ADO.NET。當(dāng)進(jìn)行數(shù)據(jù)的新增，更新以及刪除的時(shí)候，經(jīng)常需要開(kāi)啟數(shù)據(jù)庫(kù)事務(wù)。比如ADO.NET是這樣使用：

SqlConnection con = new Sqlconnection("數(shù)據(jù)庫(kù)連接語(yǔ)句");
con.Open();
var trans = con.BeginTransaction();
try
{
     SqlCommand com = new SqlCommand(trans);
     //處理插入或更新邏輯
     trans.Commit();
}catch(ex){
     trans.Rollback();//如果前面有異常則事務(wù)回滾
}
finally
{
     con.Close();
}

很多教程都將事務(wù)寫(xiě)在數(shù)據(jù)訪問(wèn)層(dao層)，但是更多時(shí)候我們需要的是業(yè)務(wù)邏輯層(service層)級(jí)別的事務(wù)控制。比如我們有一個(gè)學(xué)生表，一個(gè)班級(jí)表。學(xué)生表存有對(duì)應(yīng)的班級(jí)字段，學(xué)生與班級(jí)表都有對(duì)應(yīng)的dao和service操作類。每個(gè)dao只操作相關(guān)的數(shù)據(jù)，不能即操作學(xué)生的數(shù)據(jù)，又操作班級(jí)的數(shù)據(jù)。現(xiàn)在我們要?jiǎng)h除一個(gè)班級(jí)，并且將該班級(jí)的學(xué)生一并刪除。不管是先刪除班級(jí)還是先刪除學(xué)生(不存在外鍵約束)，反正就是要一起刪除。因?yàn)槊總€(gè)dao只操作單一的對(duì)象，這時(shí)候dao中進(jìn)行刪除操作的時(shí)候開(kāi)啟事務(wù)是達(dá)不到我們目的。班級(jí)刪除失敗，學(xué)生的刪除操作是不會(huì)回滾的，反之也一樣。
刪除班級(jí)的同時(shí)一并刪除學(xué)生，某一個(gè)失敗，另一個(gè)刪除操作回滾。這屬于一個(gè)業(yè)務(wù)層的原子操作。在班級(jí)的service操作類中可以引入班級(jí)和學(xué)生的dao進(jìn)行操作，兩個(gè)dao的操作放到同一事務(wù)中進(jìn)行操作。

連接Id類

namespace RuoXieTranscation
{
   public class ConnId
   {
       private string _cconId = Guid.NewGuid().ToString().Replace("-", "");
       private DateTime _createTime=DateTime.Now;

       public ConnId()
       {

       }

       public string CconId
       {
           get { return _cconId; }
       }

       public DateTime CreateTime
       {
            get { return _createTime; }
       }
    }
}

生成一個(gè)guid，后面標(biāo)識(shí)每個(gè)連接實(shí)例的唯一性。

連接類

namespace RuoXieTranscation
{
    public class DbConnection
    {
        private string _sConnStr = "";
        private ConnId _connId = null;
        private SqlConnection _sqlConnection = null;
        private SqlCommand _sqlCommand = null;

        public ConnId ConnId
        {
            get { return _connId; }
        }

        public SqlCommand SqlCommand
        {
            get { return _sqlCommand; }
        }

        public DbConnection(string connStr)
        {
            _sConnStr = connStr;
        }

        public ConnId ConnOpen()
        {
            try
            {
                this._sqlConnection = new SqlConnection(_sConnStr);
                this._sqlCommand = new SqlCommand();
                _sqlCommand.Connection = this._sqlConnection;
                this._connId = new ConnId();
                _sqlConnection.Open();
            }
            catch (Exception e)
            {
                if (this._sqlConnection.State != System.Data.ConnectionState.Closed)
                {
                    this._sqlConnection.Close();
                    this._sqlConnection.Dispose();
                }
                this._sqlConnection = null;
            }
            return this._connId;
        }

        public void BeginTransaction()
        {
            try
            {
                _sqlCommand.Transaction =
                    _sqlConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted,
                        this._connId.CconId);
            }
            catch (Exception e)
            {
                if (this._sqlConnection.State != System.Data.ConnectionState.Closed)
                {
                    this._sqlConnection.Close();
                    this._sqlConnection.Dispose();
                }
                this._sqlConnection = null;
            }

        }

        public void Commit()
        {
            try
            {
                this._sqlCommand.Transaction.Commit();
            }
            catch (Exception e)
            {
                this._sqlCommand.Transaction.Rollback();
            }
        }

        public void Rollback()
        {
            try
            {
                this._sqlCommand.Transaction.Rollback();
            }
            catch (Exception e)
            {
                this._sqlCommand.Transaction.Rollback();
            }
        }

        public void Close()
        {

            if (this._sqlCommand != null)
            {
                this._sqlCommand.Dispose();
            }
            if (this._sqlConnection.State != System.Data.ConnectionState.Closed)
            {
                this._sqlConnection.Close();
                this._sqlConnection.Dispose();
            }

        }
    }
}

打開(kāi)連接后可以顯式調(diào)用BeginTransaction來(lái)決定使用事務(wù)

連接管理類

namespace RuoXieTranscation
{
    public class ConnManager
    {
        private static ConcurrentDictionary<string, DbConnection> _cache =
            new ConcurrentDictionary<string, DbConnection>();
        private static ThreadLocal<string> _threadLocal;
        private static readonly string _connStr = @"Password=977865769;Persist Security Info=True;User ID=sa;Initial Catalog=RuoXie;Data Source=5ENALIZN94GYJZZ\SQLEXPRESS";

        static ConnManager()
        {
            _threadLocal=new ThreadLocal<string>();
        }

        public static bool CreateConn()
        {
            DbConnection dbconn = new DbConnection(_connStr);
            ConnId key = dbconn.ConnOpen();
            if (!_cache.ContainsKey(key.CconId))
            {
                _cache.TryAdd(key.CconId, dbconn);
                _threadLocal.Value = key.CconId;
                Console.WriteLine("創(chuàng)建數(shù)據(jù)庫(kù)連接,Id: " + key.CconId);
                return true;
            }
            throw new Exception("打開(kāi)數(shù)據(jù)庫(kù)連接失敗");
        }
        public static void BeginTransaction()
        {
            var id = GetId();
            if (!_cache.ContainsKey(id))
                throw new Exception("內(nèi)部錯(cuò)誤，鏈接已丟失");
            _cache[id].BeginTransaction();
        }

        public static void Commit()
        {
            try
            {
                var id = GetId();
                if(!_cache.ContainsKey(id))
                    throw new Exception("內(nèi)部錯(cuò)誤，鏈接已丟失");
                _cache[id].Commit();
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static void Rollback()
        {
            try
            {
                var id = GetId();
                if (!_cache.ContainsKey(id))
                    throw new Exception("內(nèi)部錯(cuò)誤，鏈接已丟失");
                _cache[id].Rollback();
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static void ReleaseConn()
        {
            try
            {
                var id = GetId();
                if (!_cache.ContainsKey(id))
                    throw new Exception("內(nèi)部錯(cuò)誤，鏈接已丟失");
                _cache[id].Close();
                Remove(id);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static SqlCommand GetSqlCommand()
        {

            var id = GetId();
            if (!_cache.ContainsKey(id))
                throw new Exception("內(nèi)部錯(cuò)誤: 連接已丟失.");
            return _cache[id].SqlCommand;
        }

        private static string GetId()
        {
            var id = _threadLocal.Value;
            if (string.IsNullOrEmpty(id))
            {
                throw new Exception("內(nèi)部錯(cuò)誤: 連接已丟失.");
            }
            return id;
        }

        private static bool Remove(string id)
        {
            if (!_cache.ContainsKey(id)) return false;

            DbConnection dbConnection;

            int index = 0;
            bool result = false;
            while (!(result = _cache.TryRemove(id, out dbConnection)))
            {
                index++;
                Thread.Sleep(20);
                if (index > 3) break;
            }
            return result;
        }
    }
}

通過(guò)靜態(tài)屬性_cache保存每個(gè)連接的Id,_threadLocal保存當(dāng)前線程中的連接Id，不管一個(gè)service中涉及多少個(gè)dao操作，都是處于同一線程中，通過(guò)_threadLocal就可以取出同一個(gè)連接對(duì)象進(jìn)行操作。

使用

public class SQLHelper
    {
        public static int ExecuteNonQuery(string sql, SqlParameter[] parameters = null)
        {
            var command = ConnManager.GetSqlCommand();
            command.CommandText = sql;
            command.CommandType = System.Data.CommandType.Text;
            if (parameters != null)
            {
                command.Parameters.Clear();
                command.Parameters.AddRange(parameters);
            }
            return command.ExecuteNonQuery();
        }

        public static object ExecuteScalar(string sql, SqlParameter[] parameters = null)
        {
            var command = ConnManager.GetSqlCommand();
            command.CommandText = sql;
            command.CommandType = System.Data.CommandType.Text;
            if (parameters != null)
            {
                command.Parameters.Clear();
                command.Parameters.AddRange(parameters);
            }
            return command.ExecuteScalar();
        }
    }

public class StudentDao
    {
        public bool Add(string name, string no)
        {
            string sql = string.Format("insert into T_Student(Name12,No) values(@name,@no)");
            var nameParameter = new SqlParameter("@name", SqlDbType.NVarChar);
            var noParameter = new SqlParameter("@no", SqlDbType.NVarChar);
            nameParameter.Value = name;
            noParameter.Value = no;
            SqlParameter[] paras = new SqlParameter[]{
                nameParameter,noParameter
            };
            return SQLHelper.ExecuteNonQuery(sql, paras) > 0;
        }
    }

public class StudentBll
   {
       private StudentDao mDao;

       public StudentBll()
       {
           mDao=new StudentDao();
       }

       public bool AddStudent(string name, string no)
       {
           return mDao.Add(name, no);
       }
    }

class Program
    {
        static void Main(string[] args)
        {
            test();
            test2();
            test3();
            Console.ReadLine();
        }

        static void test()
        {
            ConnManager.CreateConn();
            ConnManager.BeginTransaction();
            try
            {
                var classService = new ClassBll();
                classService.AddClass("7班");
                ConnManager.Commit();
                ConnManager.ReleaseConn();
            }
            catch (Exception e)
            {
                ConnManager.Rollback();
                ConnManager.ReleaseConn();
            }
        }
        static void test2()
        {
            ConnManager.CreateConn();
            ConnManager.BeginTransaction();
            try
            {
                var classService = new ClassBll();
                var studentService=new StudentBll();
                classService.AddClass("8班");
                studentService.AddStudent("李四","001");
                ConnManager.Commit();
                ConnManager.ReleaseConn();
            }
            catch (Exception e)
            {
                ConnManager.Rollback();
                ConnManager.ReleaseConn();
            }

        }
        static void test3()
        {
            ConnManager.CreateConn();
            //ConnManager.BeginTransaction();
            try
            {
                var classService = new ClassBll();
                var studentService = new StudentBll();
                classService.AddClass("8班");
                studentService.AddStudent("李四", "001");
                //ConnManager.Commit();
                ConnManager.ReleaseConn();
            }
            catch (Exception e)
            {
                //ConnManager.Rollback();
                ConnManager.ReleaseConn();
            }

        }
    }

雖然將事務(wù)提取到了service層，但是每次都要寫(xiě)這樣的代碼

            ConnManager.CreateConn();
            ConnManager.BeginTransaction();
            try
            {
                //業(yè)務(wù)邏輯調(diào)用
                ConnManager.Commit();
                ConnManager.ReleaseConn();
            }
            catch (Exception e)
            {
                ConnManager.Rollback();
                ConnManager.ReleaseConn();
            }

使用過(guò)spring或者spring.net的應(yīng)該都知道將事務(wù)控制轉(zhuǎn)到業(yè)務(wù)層事多簡(jiǎn)單，比如spring.net

        [Transaction]
        public void DeleteData(string name)
        {
            UserDao.Delete(name);
            AccountDao.Delete(name);
        }

只需要在service方法加上Transaction attribute。原理就是AOP編程。