关于dapper的事务处理
dapper：一个轻量级的ORM框架
dapper事务处理的注意点：1、使用同一个Connection 2、使用同一个Transaction
为了方便调用，自己编写了一个DapperHelper.cs
代码如下：
using Dapper; using Model.DB; using MySql.Data.MySqlClient; using System; using System.Collections.Generic; using System.Data; using System.Data.Common; namespace Tools.DB { public class DapperHelper { private static DapperHelper dapperHelper; private static readonly object lockObj = new object(); public static string connStr = string.Empty; private DapperHelper() { //数据库连接字符串 connStr = Conn.CreateInstance().ConnStr; } public static DapperHelper CreateDapperHelper() { if (dapperHelper == null) { lock (lockObj) { if (dapperHelper == null) { dapperHelper = new DapperHelper(); } } } return dapperHelper; } /// <summary> /// 增删改 /// </summary> /// <typeparam name="T">数据类型 content可以是一个对象也可以是一个集合</typeparam> /// <param name="sql"></param> /// <param name="obj"></param> /// <returns></returns> public int ExecuteNonQuery<T>(string sql, T obj) { int result = 0; try { using (var conn = new MySqlConnection(connStr)) { result = conn.Execute(sql, obj); } } catch { } return result; } /// <summary> /// 增删改 /// </summary> /// <typeparam name="T">数据类型 content可以是一个对象也可以是一个集合</typeparam> /// <param name="sql"></param> /// <param name="obj"></param> /// <returns></returns> public int ExecuteNonQuery<T>(string sql, T obj, IDbTransaction tran, IDbConnection conn) { int result = 0; try { result = conn.Execute(sql, obj, tran); } catch { //回滚事务并销毁连接对象 Rollback(tran,conn); DisposeConn(conn); return -1; } return result; } /// <summary> /// 返回首行首列 /// </summary> /// <param name="sql"></param> /// <param name="obj"></param> /// <returns></returns> public object ExecuteScalar(string sql, object obj = null) { object result; using (var conn = new MySqlConnection(connStr)) { result = conn.ExecuteScalar(sql, obj); } return result; } /// <summary> /// 查询单条记录 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="sql"></param> /// <param name="obj">new{...}</param> /// <returns></returns> public T ExecuteQuery<T>(string sql, object obj = null) { T result; using (var conn = new MySqlConnection(connStr)) { result = conn.QueryFirstOrDefault<T>(sql, obj); } return result; } /// <summary> /// 使用事务查询单条记录 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="sql"></param> /// <param name="obj">new{...}</param> /// <returns></returns> public T ExecuteQuery<T>(string sql, IDbTransaction tran, IDbConnection conn, object obj = null) { T result; result = conn.QueryFirstOrDefault<T>(sql, obj, tran); return result; } /// <summary> /// 查询多条记录 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="sql"></param> /// <param name="obj">new{...}</param> /// <returns></returns> public IEnumerable<T> ExecuteQuerys<T>(string sql, object obj = null) { IEnumerable<T> result; using (var conn = new MySqlConnection(connStr)) { result = conn.Query<T>(sql, obj); } return result; } /// <summary> /// 使用事务查询多条记录 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="sql"></param> /// <param name="obj">new{...}</param> /// <returns></returns> public IEnumerable<T> ExecuteQuerys<T>(string sql, IDbTransaction tran, IDbConnection conn, object obj = null) { IEnumerable<T> result; result = conn.Query<T>(sql, obj,tran); return result; } /// <summary> /// 获得conn对象 /// </summary> /// <returns></returns> public IDbConnection GetConn() { var conn = new MySqlConnection(connStr); return conn; } /// <summary> /// 打开conn /// </summary> /// <param name="conn"></param> public void OpenConn(IDbConnection conn) { conn.Open(); } /// <summary> /// 销毁conn /// </summary> /// <param name="conn"></param> public void DisposeConn(IDbConnection conn) { conn.Dispose(); conn.Close(); } /// <summary> /// 开启事务 /// </summary> /// <param name="conn"></param> /// <returns></returns> public IDbTransaction BeginTransaction(IDbConnection conn) { IDbTransaction tran = conn.BeginTransaction(); return tran; } /// <summary> /// 提交事务 /// </summary> /// <param name="tran"></param> /// <param name="conn"></param> public void Commit(IDbTransaction tran, IDbConnection conn) { tran.Commit(); } /// <summary> /// 回滚事务 /// </summary> /// <param name="tran"></param> /// <param name="conn"></param> public void Rollback(IDbTransaction tran, IDbConnection conn) { tran.Rollback(); } } }
针对此类的事务编写如下：
private int SetConAct(List<ConAct> conActs) { //创建数据库连接对象 IDbConnection conn = dapper.GetConn(); using (conn) { //打开连接 dapper.OpenConn(conn); //开启事务 IDbTransaction tran = dapper.BeginTransaction(conn); //事务中需要执行的代码 IEnumerable<DBController> cons = GetCons(conn, tran); foreach (var conAct in conActs) { string conId; if (cons == null || cons.Where(c => c.name == conAct.ConName).Count() < 1) { conId = Guid.NewGuid().ToString(); string sql1 = "insert into controller (id, name, create_tm ) values (@id, @name, now()) "; int rs1 = dapper.ExecuteNonQuery(sql1, new { id = conId, name = conAct.ConName }, tran, conn); } else { conId = cons.Where(c => c.name == conAct.ConName).FirstOrDefault().id; } if (conAct.ActNames != null) { IEnumerable<DBAction> acts = GetAct(conn, tran, conId); foreach (var actName in conAct.ActNames) { string actId; if (acts == null || acts.Where(c => c.name == actName).Count() < 1) { actId = Guid.NewGuid().ToString(); string sql2 = @"insert into action (id, name, controller_id, create_tm) values (@id, @name, @controller_id, now())"; int rs2 = dapper.ExecuteNonQuery(sql2, new { id = actId, name = actName, controller_id = conId }, tran, conn); } } } } //提交事务 dapper.Commit(tran, conn); } return 0; }
