首页 > 代码库 > DBHelper (支持事务与数据库变更） z
DBHelper (支持事务与数据库变更） z
using System;using System.Data;using System.Data.Common;using Project.BaseFramework;using System.Collections.Generic;using System.Configuration;namespace Project.BaseFramework.DataProvider{ public class DBHelper { #region Constuctor public DBHelper() { } private static string ConnectionString = ConfigurationManager.AppSettings["DBConnectionString"]; private static IDBClient DBClient = DBClientFactory.GetDBClient(ConfigurationManager.AppSettings["DBClient"]); [ThreadStatic] private static TransConnection TransConnectionObj = null; #endregion #region ExecuteNonQuery public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params DbParameter[] parameterValues) { int result = 0; bool mustCloseConn = true; DbCommand cmd = PrepareCmd(cmdType, cmdText, parameterValues, out mustCloseConn); OpenConn(cmd.Connection); result = cmd.ExecuteNonQuery(); if (mustCloseConn) CloseConn(cmd.Connection); ClearCmdParameters(cmd); cmd.Dispose(); return result; } #endregion ExecuteNonQuery #region ExecuteScalar public static object ExecuteScalar(CommandType cmdType, string cmdText, params DbParameter[] parameterValues) { object result = 0; bool mustCloseConn = true; DbCommand cmd = PrepareCmd(cmdType, cmdText, parameterValues, out mustCloseConn); OpenConn(cmd.Connection); result = cmd.ExecuteScalar(); if (mustCloseConn) CloseConn(cmd.Connection); ClearCmdParameters(cmd); cmd.Dispose(); return result; } #endregion ExecuteScalar #region ExecuteReader public static DbDataReader ExecuteReader(CommandType cmdType, string cmdText, params DbParameter[] parameterValues) { DbDataReader result = null; bool mustCloseConn = true; DbCommand cmd = PrepareCmd(cmdType, cmdText, parameterValues, out mustCloseConn); try { OpenConn(cmd.Connection); if (mustCloseConn) { result = cmd.ExecuteReader(CommandBehavior.CloseConnection); } else { result = cmd.ExecuteReader(); } ClearCmdParameters(cmd); return result; } catch (Exception ex) { if (mustCloseConn) CloseConn(cmd.Connection); ClearCmdParameters(cmd); cmd.Dispose(); throw ; } } #endregion ExecuteReader #region ExecuteDataset public static DataSet ExecuteDataSet(CommandType cmdType, string cmdText, params DbParameter[] parameterValues) { DataSet result = null; bool mustCloseConn = true; DbCommand cmd = PrepareCmd(cmdType, cmdText, parameterValues, out mustCloseConn); using (DbDataAdapter da = DBClient.GetDbDataAdappter()) { da.SelectCommand = cmd; result = new DataSet(); da.Fill(result); } if (mustCloseConn) CloseConn(cmd.Connection); ClearCmdParameters(cmd); cmd.Dispose(); return result; } #endregion ExecuteDataset #region ExecuteDataTable public static DataTable ExecuteDataTable(CommandType cmdType, string cmdText, params DbParameter[] parameterValues) { DataSet ds = ExecuteDataSet(cmdType,cmdText, parameterValues); if (ds != null && ds.Tables.Count > 0) return ds.Tables[0]; else return null; } #endregion #region Transaction public static void BeginTransaction() { if (TransConnectionObj == null) { DbConnection conn = DBClient.GetDbConnection(ConnectionString); OpenConn(conn); DbTransaction trans = conn.BeginTransaction(); TransConnectionObj = new TransConnection(); TransConnectionObj.DBTransaction = trans; } else { TransConnectionObj.Deeps += 1; } } public static void CommitTransaction() { if (TransConnectionObj == null) return; if (TransConnectionObj.Deeps > 0) { TransConnectionObj.Deeps -= 1; } else { TransConnectionObj.DBTransaction.Commit(); ReleaseTransaction(); } } public static void RollbackTransaction() { if (TransConnectionObj == null) return; if (TransConnectionObj.Deeps > 0) { TransConnectionObj.Deeps -= 1; } else { TransConnectionObj.DBTransaction.Rollback(); ReleaseTransaction(); } } private static void ReleaseTransaction() { if (TransConnectionObj == null) return; DbConnection conn = TransConnectionObj.DBTransaction.Connection; TransConnectionObj.DBTransaction.Dispose(); TransConnectionObj = null; CloseConn(conn); } #endregion #region Connection private static void OpenConn(DbConnection conn) { if (conn == null) conn = DBClient.GetDbConnection(ConnectionString); if (conn.State == ConnectionState.Closed) conn.Open(); } private static void CloseConn(DbConnection conn) { if (conn == null) return; if (conn.State == ConnectionState.Open) conn.Close(); conn.Dispose(); conn = null; } #endregion #region Create DbParameter public static DbParameter CreateInDbParameter(string paraName, DbType type, int size, object value) { return CreateDbParameter(paraName, type, size, value, ParameterDirection.Input); } public static DbParameter CreateInDbParameter(string paraName, DbType type, object value) { return CreateDbParameter(paraName, type, 0, value, ParameterDirection.Input); } public static DbParameter CreateOutDbParameter(string paraName, DbType type, int size) { return CreateDbParameter(paraName, type, size, null, ParameterDirection.Output); } public static DbParameter CreateOutDbParameter(string paraName, DbType type) { return CreateDbParameter(paraName, type, 0, null, ParameterDirection.Output); } public static DbParameter CreateReturnDbParameter(string paraName, DbType type, int size) { return CreateDbParameter(paraName, type, size, null, ParameterDirection.ReturnValue); } public static DbParameter CreateReturnDbParameter(string paraName, DbType type) { return CreateDbParameter(paraName, type, 0, null, ParameterDirection.ReturnValue); } public static DbParameter CreateDbParameter(string paraName, DbType type, int size, object value, ParameterDirection direction) { DbParameter para = DBClient.GetDbParameter(); para.ParameterName = paraName; if (size != 0) { para.Size = size; } para.DbType = type; if (value != null) { para.Value = http://www.mamicode.com/value;"command"); if (commandParameters != null) { foreach (DbParameter p in commandParameters) { if (p != null) { // 检查未分配值的输出参数,将其分配以DBNull.Value. if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) && (p.Value =http://www.mamicode.com/= null))"Project.BaseFramework"; public static IDBClient GetDBClient(string dbClientClassName) { if(string.IsNullOrEmpty(dbClientClassName)) dbClientClassName="SqlServerClient"; string className = string.Format("{0}.DataProvider.{1}", path, dbClientClassName); return (IDBClient)Assembly.Load(path).CreateInstance(className); } }}配置文件
<appSettings> <add key="DBConnectionString" value="http://www.mamicode.com/Data Source=.;Initial Catalog=ProjectData;Persist Security Info=True;User ID=sa;Password=kjkj,911;"/> <add key="DBClient" value="http://www.mamicode.com/SqlServerClient"/></appSettings>
单个事务操作示例
try{ DBHelper.BeginTransaction(); // add DBHelper.ExecuteNonQuery(CommandType.Text, "INSERT INTO TRole(ID,RoleName) VALUES(‘R1‘,‘MKT‘)"); //detele by pk DBHelper.ExecuteNonQuery(CommandType.Text, "DELETE FROM TRole WHERE ID=‘R1‘"); Console.WriteLine(string.Format("Success and Commited")); DBHelper.CommitTransaction();}catch (Exception ex){ Console.WriteLine(string.Format("Exception and rollback")); DBHelper.RollbackTransaction();}
用法是：只需要把相关联的代码放在BeginTransaction和CommitTransaction中间，如果发生异常调用RollbackTransaction即可。
实现事务的方法是：
首先，DBHelper维护一个TransConnection类型的字段，并添加ThreadStatic. ThreadStatic可以维护在线程级别上的唯一性。
[ThreadStatic]private static TransConnection TransConnectionObj = null;
其次，TransConnection的作用是保存事务，并记录嵌套事务的嵌套级别。
internal class TransConnection{ public TransConnection() { this.Deeps = 0; } public DbTransaction DBTransaction { get; set; } public int Deeps { get; set; }}
最后，当调用 BeginTransaction时创建TransConnection对象。之后的多个DbCommand命令都从这个事务上拿连接。因为 TransConnectionObj添加了ThreadStatic属性，所以它是线程唯一的，不会影响其它线程上的事务；所有方法执行完后，调用 CommitTransaction 就提交事务，并关闭连接；如果发生异常，则调用RollbackTransaction，就会回滚所有命令，并关闭连接。
嵌套事务示例
static void Main(string[] args){ try { DBHelper.BeginTransaction(); // add DBHelper.ExecuteNonQuery(CommandType.Text, "INSERT INTO TRole(ID,RoleName) VALUES(‘R1‘,‘MKT‘)"); Transaction2(); //detele by pk DBHelper.ExecuteNonQuery(CommandType.Text, "DELETE FROM TRole WHERE ID=‘R1‘"); Console.WriteLine(string.Format("Success and Commited")); DBHelper.CommitTransaction(); } catch (Exception ex) { Console.WriteLine(string.Format("Exception and rollback")); DBHelper.RollbackTransaction(); } Console.ReadLine();}private static void Transaction2() { try { DBHelper.BeginTransaction(); //update model DBHelper.ExecuteNonQuery(CommandType.Text, "UPDATE TRole SET RoleName=‘Marketer‘ WHERE ID=‘R1‘"); //throw new Exception(""); DbParameter param = DBHelper.CreateInDbParameter("@ID", DbType.String, "R1"); DbDataReader reader= DBHelper.ExecuteReader(CommandType.Text, "SELECT * FROM TRole WHERE ID=@ID",param); while (reader.Read()) { Console.WriteLine(reader["RoleName"]); } reader.Close(); DBHelper.CommitTransaction(); } catch(Exception ex) { Console.WriteLine(string.Format("Exception and rollback: {0}", ex.Message)); DBHelper.RollbackTransaction(); throw; }}
当为嵌套事务时，首次调用BeginTransaction，同样会创建新的TransConnection对象，深度默认为0，并保存在TransConnectionObj字段上；
第n(n>1)次调用时方法时，仅会累加嵌套的深度，不会开起新的事务。
public static void BeginTransaction(){ if (TransConnectionObj == null) { DbConnection conn = DBClient.GetDbConnection(ConnectionString); OpenConn(conn); DbTransaction trans = conn.BeginTransaction(); TransConnectionObj = new TransConnection(); TransConnectionObj.DBTransaction = trans; } else { TransConnectionObj.Deeps += 1; }}
当CommitTransaction提交事务时，如果深度Deeps>0，那么表示此次提交的事务是内层事务，计数器减1即可；
当调用CommitTransaction提交事务，如果深度为0时，表示为最外层事务，刚做实际上的提交事务工作；
public static void CommitTransaction(){ if (TransConnectionObj == null) return; if (TransConnectionObj.Deeps > 0) { TransConnectionObj.Deeps -= 1; } else { TransConnectionObj.DBTransaction.Commit(); ReleaseTransaction(); }}
当RollbackTransaction提交事务时，如果深度Deeps>0，那么表示此次提交的事务是内层事务，计数器减1即可;
当调用RollbackTransaction提交事务，如果深度为0时，表示为最外层事务，刚做实际上的回滚操作;
public static void RollbackTransaction(){ if (TransConnectionObj == null) return; if (TransConnectionObj.Deeps > 0) { TransConnectionObj.Deeps -= 1; } else { TransConnectionObj.DBTransaction.Rollback(); ReleaseTransaction(); }}
同一个底层库，应用到不同项目时，数据库可能会不同。如果我们比较下不同数据库操作类之间的不同点，我们会发现所有的方法都是一致的，就是某些类型不同，如下表所示:
基类 | SQL Server | MySql |
DbConnection | SqlConnection | MySqlConnection |
DbCommand | SqlCommand | MySqlCommand |
DbDataAdapter | SqlDataAdapter | MySqlDataAdapte |
DbParameter | SqlParameter | MySqlParameter |
所以，根据子类出现的地方，可以用父类替换的原则，将SqlHeper中关于特定数据库的类，换成基类，并将创建特定数据库对象实例的代码统一到IDBClinet中
主要类有
DBHelper: 使用基类访问数据库，并聚合IDBClient来创建特定数据库对象的实例。
IDBClient: 定义创建特定数据库实例的接口，并转成基类型；
SqlServerClient：定义创建SqlServer对象的实例，并转成基类型；
MySqlCient：定义创建MySql对象的实例，并转成基类型；
DBClientFactory:根据类名动态创建IDBClient的实现类；
最后如果想要更换数据库时，只需要修改如下代码，并在配置文件中修改下连接字符串和具体的DBClient的类名：
<appSettings> <add key="DBConnectionString" value=http://www.mamicode.com/"Data Source=.;Initial Catalog=ProjectData;Persist Security Info=True;User ID=sa;Password=kjkj,911;"/> <add key="DBClient" value=http://www.mamicode.com/"SqlServerClient"/></appSettings>
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。