學習后端語言的時候，都會涉及到數(shù)據(jù)庫的相關操作，不同語言在操作數(shù)據(jù)庫方面有不同的驅(qū)動程序庆揩，比如java的JDBC,C#的ADO.NET副签。當進行數(shù)據(jù)的新增，更新以及刪除的時候脸甘，經(jīng)常需要開啟數(shù)據(jù)庫事務。比如ADO.NET是這樣使用：
SqlConnection con = new Sqlconnection("數(shù)據(jù)庫連接語句");
con.Open();
var trans = con.BeginTransaction();
try
{
SqlCommand com = new SqlCommand(trans);
//處理插入或更新邏輯
trans.Commit();
}catch(ex){
trans.Rollback();//如果前面有異常則事務回滾
}
finally
{
con.Close();
}
很多教程都將事務寫在數(shù)據(jù)訪問層(dao層)，但是更多時候我們需要的是業(yè)務邏輯層(service層)級別的事務控制啄骇。比如我們有一個學生表，一個班級表瘟斜。學生表存有對應的班級字段缸夹，學生與班級表都有對應的dao和service操作類。每個dao只操作相關的數(shù)據(jù)螺句，不能即操作學生的數(shù)據(jù)虽惭，又操作班級的數(shù)據(jù)。現(xiàn)在我們要刪除一個班級蛇尚，并且將該班級的學生一并刪除芽唇。不管是先刪除班級還是先刪除學生(不存在外鍵約束)，反正就是要一起刪除取劫。因為每個dao只操作單一的對象匆笤，這時候dao中進行刪除操作的時候開啟事務是達不到我們目的研侣。班級刪除失敗，學生的刪除操作是不會回滾的疚膊，反之也一樣义辕。
刪除班級的同時一并刪除學生，某一個失敗寓盗，另一個刪除操作回滾灌砖。這屬于一個業(yè)務層的原子操作。在班級的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();
}
}
}
}
打開連接后可以顯式調(diào)用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("創(chuàng)建數(shù)據(jù)庫連接,Id: " + key.CconId);
return true;
}
throw new Exception("打開數(shù)據(jù)庫連接失敗");
}
public static void BeginTransaction()
{
var id = GetId();
if (!_cache.ContainsKey(id))
throw new Exception("內(nèi)部錯誤撩幽，鏈接已丟失");
_cache[id].BeginTransaction();
}
public static void Commit()
{
try
{
var id = GetId();
if(!_cache.ContainsKey(id))
throw new Exception("內(nèi)部錯誤，鏈接已丟失");
_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)部錯誤箩艺，鏈接已丟失");
_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)部錯誤窜醉，鏈接已丟失");
_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)部錯誤: 連接已丟失.");
return _cache[id].SqlCommand;
}
private static string GetId()
{
var id = _threadLocal.Value;
if (string.IsNullOrEmpty(id))
{
throw new Exception("內(nèi)部錯誤: 連接已丟失.");
}
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;
}
}
}
通過靜態(tài)屬性_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
{
//業(yè)務邏輯調(diào)用
ConnManager.Commit();
ConnManager.ReleaseConn();
}
catch (Exception e)
{
ConnManager.Rollback();
ConnManager.ReleaseConn();
}
使用過spring或者spring.net的應該都知道將事務控制轉(zhuǎn)到業(yè)務層事多簡單琅催，比如spring.net
[Transaction]
public void DeleteData(string name)
{
UserDao.Delete(name);
AccountDao.Delete(name);
}
只需要在service方法加上Transaction attribute。原理就是AOP編程虫给。
