将数据库事务从dao层提升到service层

原文地址

学习后端语言的时候，都会涉及到数据库的相关操作，不同语言在操作数据库方面有不同的驱动程序，比如java的JDBC,C#的ADO.NET。当进行数据的新增，更新以及删除的时候，经常需要开启数据库事务。比如ADO.NET是这样使用：

SqlConnection con = new Sqlconnection("数据库连接语句");
con.Open();
var trans = con.BeginTransaction();
try
{
     SqlCommand com = new SqlCommand(trans);
     //处理插入或更新逻辑
     trans.Commit();
}catch(ex){
     trans.Rollback();//如果前面有异常则事务回滚
}
finally
{
     con.Close();
}

很多教程都将事务写在数据访问层(dao层)，但是更多时候我们需要的是业务逻辑层(service层)级别的事务控制。比如我们有一个学生表，一个班级表。学生表存有对应的班级字段，学生与班级表都有对应的dao和service操作类。每个dao只操作相关的数据，不能即操作学生的数据，又操作班级的数据。现在我们要删除一个班级，并且将该班级的学生一并删除。不管是先删除班级还是先删除学生(不存在外键约束)，反正就是要一起删除。因为每个dao只操作单一的对象，这时候dao中进行删除操作的时候开启事务是达不到我们目的。班级删除失败，学生的删除操作是不会回滚的，反之也一样。
删除班级的同时一并删除学生，某一个失败，另一个删除操作回滚。这属于一个业务层的原子操作。在班级的service操作类中可以引入班级和学生的dao进行操作，两个dao的操作放到同一事务中进行操作。

连接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; }
       }
    }
}

生成一个guid，后面标识每个连接实例的唯一性。

连接类

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

        }
    }
}

打开连接后可以显式调用BeginTransaction来决定使用事务

连接管理类

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("创建数据库连接,Id: " + key.CconId);
                return true;
            }
            throw new Exception("打开数据库连接失败");
        }
        public static void BeginTransaction()
        {
            var id = GetId();
            if (!_cache.ContainsKey(id))
                throw new Exception("内部错误，链接已丢失");
            _cache[id].BeginTransaction();
        }

        public static void Commit()
        {
            try
            {
                var id = GetId();
                if(!_cache.ContainsKey(id))
                    throw new Exception("内部错误，链接已丢失");
                _cache[id].Commit();
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static void Rollback()
        {
            try
            {
                var id = GetId();
                if (!_cache.ContainsKey(id))
                    throw new Exception("内部错误，链接已丢失");
                _cache[id].Rollback();
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static void ReleaseConn()
        {
            try
            {
                var id = GetId();
                if (!_cache.ContainsKey(id))
                    throw new Exception("内部错误，链接已丢失");
                _cache[id].Close();
                Remove(id);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static SqlCommand GetSqlCommand()
        {

            var id = GetId();
            if (!_cache.ContainsKey(id))
                throw new Exception("内部错误: 连接已丢失.");
            return _cache[id].SqlCommand;
        }

        private static string GetId()
        {
            var id = _threadLocal.Value;
            if (string.IsNullOrEmpty(id))
            {
                throw new Exception("内部错误: 连接已丢失.");
            }
            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;
        }
    }
}

通过静态属性_cache保存每个连接的Id,_threadLocal保存当前线程中的连接Id，不管一个service中涉及多少个dao操作，都是处于同一线程中，通过_threadLocal就可以取出同一个连接对象进行操作。

使用

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

        }
    }

虽然将事务提取到了service层，但是每次都要写这样的代码

            ConnManager.CreateConn();
            ConnManager.BeginTransaction();
            try
            {
                //业务逻辑调用
                ConnManager.Commit();
                ConnManager.ReleaseConn();
            }
            catch (Exception e)
            {
                ConnManager.Rollback();
                ConnManager.ReleaseConn();
            }

使用过spring或者spring.net的应该都知道将事务控制转到业务层事多简单，比如spring.net

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

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