首页 > 代码库 > 十三、EnterpriseFrameWork框架核心类库之数据库操作（多数据库事务处理）
十三、EnterpriseFrameWork框架核心类库之数据库操作（多数据库事务处理）
本章介绍框架中封装的数据库操作的一些功能，在实现的过程中费了不少心思，针对不同数据库的操作（SQLServer、Oracle、DB2）这方面还是比较简单的，用工厂模式就能很好解决，反而是在多数据库同时操作方面走了不少弯路；现在从以下几个方面进行说明：
一、不同数据库操作
此处用到了工厂模式来实现不同数据库操作，看下图
AbstractDatabase是一个抽象类，定义了所有对数据库的操作抽象方法，包括执行一个SQL语句、执行存储过程、事务操作等
[Serializable] public abstract class AbstractDatabase { #region 属性 /// <summary> /// 数据库事务 /// </summary> protected DbTransaction transaction = null; //数据库事务 protected string _connString; /// <summary> /// 返回数据库连接字符串 /// </summary> public string ConnectionString { get { return _connString; } } protected bool isInTransaction = false; //是否在事务中 /// <summary> /// 返回是否处于事务中 /// </summary> protected bool IsInTransaction { get { return this.isInTransaction; } } public int WorkId { get; set; } public string DbKey { get; set; } public DatabaseType DbType { get; set; } public AbstractDatabase newOleDb(string dbkey) { return this; } public AbstractDatabase defaultOleDb() { return this; } public abstract void TestDbConnection(); #endregion /// <summary> /// 启动一个事务 /// </summary> public abstract void BeginTransaction(); /// <summary> /// 提交一个事务 /// </summary> public abstract void CommitTransaction(); /// <summary> /// 回滚一个事务 /// </summary> public abstract void RollbackTransaction(); public abstract DbCommand GetDbCommand(); #region 执行插入一条记录 适用于有 自动生成标识的列 public abstract int InsertRecord(IDbCommand cmd); public abstract int InsertRecord(string commandtext); #endregion #region 返回一个DataTable public abstract DataTable GetDataTable(IDbCommand cmd); public abstract DataTable GetDataTable(string commandtext); public abstract DataTable GetDataTable(string storeProcedureName, params object[] parameters); #endregion #region 返回一个DataReader public abstract IDataReader GetDataReader(IDbCommand cmd); public abstract IDataReader GetDataReader(string commandtext); #endregion #region 执行一个语句，返回执行情况 public abstract int DoCommand(IDbCommand cmd); public abstract int DoCommand(string commandtext); public abstract int DoCommand(string storeProcedureName, params object[] parameters); #endregion #region 执行一个命令返回一个数据结果 public abstract object GetDataResult(IDbCommand cmd); public abstract object GetDataResult(string commandtext); public abstract object GetDataResult(string storeProcedureName, params object[] parameters); #endregion public abstract DataSet GetDataSet(string storeProcedureName, params object[] parameters); }
SqlServerDb类继承AbstractDatabase抽象类并实现
public class SqlServerDb : AbstractDatabase { /// <summary> /// 数据库连接 /// </summary> protected DbConnection connection = null; //数据库连接 /// <summary> /// 数据库对象执行命令 /// </summary> protected DbCommand command = null; /// <summary> /// 企业库数据库访问对象 /// </summary> protected Database database = null; public SqlServerDb() : base() { database = ZhyContainer.CreateDataBase(); _connString = database.ConnectionString; } public SqlServerDb(string key) : base() { database = ZhyContainer.CreateDataBase(key); _connString = database.ConnectionString; } public override DbCommand GetDbCommand() { SqlCommand cmd = new SqlCommand(); connection = database.CreateConnection(); connection.Open(); cmd.Connection = (SqlConnection)connection; return cmd; } public override void TestDbConnection() { database.CreateConnection().Open(); } public override void BeginTransaction() { try { if (isInTransaction == false) { connection = database.CreateConnection(); connection.Open(); transaction = connection.BeginTransaction(); isInTransaction = true; } else { throw new Exception("事务正在进行，一个对象不能同时开启多个事务！"); } } catch (Exception e) { connection.Close(); isInTransaction = false; throw new Exception("事务启动失败，请再试一次！\n" + e.Message); } } public override void CommitTransaction() { if (transaction != null) { transaction.Commit(); isInTransaction = false; connection.Close(); }else throw new Exception("无可用事务！"); } public override void RollbackTransaction() { if (transaction != null) { transaction.Rollback(); isInTransaction = false; connection.Close(); }else throw new Exception("无可用事务！"); } public override int InsertRecord(string commandtext) { //string strsql = "SELECT Test_SQL.nextval FROM dual";SELECT @@IDENTITY if (isInTransaction) { command = database.GetSqlStringCommand(commandtext); command.Connection = connection; command.Transaction = transaction; command.CommandType = CommandType.Text; command.CommandText = command.CommandText + ";SELECT @@IDENTITY"; return Convert.ToInt32(database.ExecuteScalar(command, transaction)); } else { command = database.GetSqlStringCommand(commandtext); command.CommandText = command.CommandText + ";SELECT @@IDENTITY"; return Convert.ToInt32(database.ExecuteScalar(command)); } //command.CommandText = "SELECT @@IDENTITY"; //return Convert.ToInt32(database.ExecuteScalar(command)); } public override int InsertRecord(System.Data.IDbCommand cmd) { command = (System.Data.Common.DbCommand)cmd; command.CommandText = command.CommandText + ";SELECT @@IDENTITY"; object ret; if (isInTransaction) { command.Connection = connection; command.Transaction = transaction; ret = database.ExecuteScalar(command, transaction); return Convert.ToInt32(ret == DBNull.Value ? 1 : ret); } ret = database.ExecuteScalar(command); return Convert.ToInt32(ret == DBNull.Value ? 1 : ret); //command.CommandText = "SELECT @@IDENTITY"; //return Convert.ToInt32(database.ExecuteScalar(command));//? } public override DataTable GetDataTable(string commandtext) { DataSet ds = null; if (isInTransaction) { command = new SqlCommand(commandtext); command.Connection = connection; command.Transaction = transaction; command.CommandType = CommandType.Text; ds = database.ExecuteDataSet(command,transaction); } else { ds = database.ExecuteDataSet(CommandType.Text, commandtext); } if (ds != null && ds.Tables.Count > 0) { return ds.Tables[0]; } throw new Exception("没有数据"); } public override DataTable GetDataTable(System.Data.IDbCommand cmd) { command = (System.Data.Common.DbCommand)cmd; DataSet ds = null; if (isInTransaction) { command.Connection = connection; command.Transaction = transaction; ds = database.ExecuteDataSet(command, transaction); } else ds = database.ExecuteDataSet(command); if (ds != null && ds.Tables.Count > 0) { return ds.Tables[0]; } throw new Exception("没有数据"); } public override DataTable GetDataTable(string storeProcedureName, params object[] parameters) { DataSet ds = null; //List<object> param = new List<object>(); //foreach (IDbDataParameter val in parameters) //{ // param.Add(val.Value); //} if (isInTransaction) { command = database.GetStoredProcCommand(storeProcedureName, parameters); command.Connection = connection; command.Transaction = transaction; command.CommandType = CommandType.Text; ds = database.ExecuteDataSet(command, transaction); } else { ds = database.ExecuteDataSet(storeProcedureName, parameters); } if (ds != null && ds.Tables.Count > 0) { return ds.Tables[0]; } throw new Exception("没有数据"); } public override global::System.Data.IDataReader GetDataReader(string commandtext) { if (isInTransaction) { command = database.GetSqlStringCommand(commandtext); command.Connection = connection; command.Transaction = transaction; command.CommandType = CommandType.Text; return database.ExecuteReader(command,transaction); } else { return database.ExecuteReader(CommandType.Text, commandtext); } } public override System.Data.IDataReader GetDataReader(System.Data.IDbCommand cmd) { command = (System.Data.Common.DbCommand)cmd; if (isInTransaction) { command.Connection = connection; command.Transaction = transaction; return database.ExecuteReader(command, transaction); } return database.ExecuteReader(command); } public override int DoCommand(string commandtext) { if (isInTransaction) { command = database.GetSqlStringCommand(commandtext); command.Connection = connection; command.Transaction = transaction; command.CommandType = CommandType.Text; return database.ExecuteNonQuery(command,transaction); } else { return database.ExecuteNonQuery(CommandType.Text, commandtext); } } public override int DoCommand(System.Data.IDbCommand cmd) { command = (System.Data.Common.DbCommand)cmd; if (isInTransaction) { command.Connection = connection; command.Transaction = transaction; return database.ExecuteNonQuery(command,transaction); } return database.ExecuteNonQuery(command); } public override int DoCommand(string storeProcedureName, params object[] parameters) { if (isInTransaction) { command = database.GetStoredProcCommand(storeProcedureName, parameters); command.Connection = connection; command.Transaction = transaction; command.CommandType = CommandType.Text; return database.ExecuteNonQuery(command, transaction); } else { return database.ExecuteNonQuery(storeProcedureName, parameters); } } public override object GetDataResult(string commandtext) { if (isInTransaction) { command = database.GetSqlStringCommand(commandtext); command.Connection = connection; command.Transaction = transaction; command.CommandType = CommandType.Text; return database.ExecuteScalar(command, transaction); } else { return database.ExecuteScalar(CommandType.Text, commandtext); } } public override object GetDataResult(System.Data.IDbCommand cmd) { command = (System.Data.Common.DbCommand)cmd; if (isInTransaction) { command.Connection = connection; command.Transaction = transaction; return database.ExecuteScalar(command, transaction); } return database.ExecuteScalar(command); } public override object GetDataResult(string storeProcedureName, params object[] parameters) { if (isInTransaction) { command = database.GetStoredProcCommand(storeProcedureName, parameters); command.Connection = connection; command.Transaction = transaction; command.CommandType = CommandType.Text; return database.ExecuteScalar(command, transaction); } else { return database.ExecuteScalar(storeProcedureName, parameters); } } public override DataSet GetDataSet(string storeProcedureName, params object[] parameters) { DataSet ds = null; List<object> param = new List<object>(); foreach (IDbDataParameter val in parameters) { param.Add(val.Value); } if (isInTransaction) { command = database.GetStoredProcCommand(storeProcedureName, param.ToArray()); command.Connection = connection; command.Transaction = transaction; command.CommandType = CommandType.Text; ds = database.ExecuteDataSet(command, transaction); } else { ds = database.ExecuteDataSet(storeProcedureName, param.ToArray()); } if (ds != null && ds.Tables.Count > 0) { return ds; } throw new Exception("没有数据"); } }
OracleDb类同上，创建数据库操作对象实例FactoryDatabase类的实现
public static AbstractDatabase GetDatabase(string dbkey) { if (string.IsNullOrEmpty(dbkey)) throw new Exception("没有数据库Key！"); AbstractDatabase _oleDb = null; string dbtype = System.Configuration.ConfigurationManager.AppSettings["DbType"].ToString();//获取默认数据库连接 switch (dbtype) { case "SqlServer": _oleDb = new SqlServerDb(dbkey); _oleDb.DbType = DatabaseType.SqlServer2005; break; case "Oracle": _oleDb = new OracleDb(dbkey); _oleDb.DbType = DatabaseType.Oracle; break; } return _oleDb; }
上面代码需要读取系统配置文件Web.Config或App.Config的配置的数据库类型
二、多数据库同时操作
接下来详细讲解一下多数据库同时操作，什么时候我们系统会涉及到多数据库，比如：跟第三方系统对接需要提取数据，而对方并没有提供程序方面的接口而是把数据库结构让你直接访问；还有业务数据比较多，需要按业务进行分库才能满足性能上的需求；还有可能需要把某个子系统产品化，需要独立设计数据库；在项目中难免会遇到以上情况，所以框架必须支持对多数据库同时操作；
为什么说我在此处走了不少弯路，就是为了找到在编码的时候，怎样使用起来最简单、最直观的方法；一般的实现方法都是定义多个数据库操作对象，oleDb1、oleDb2。。。这中方法在使用的针对数据库1就必须用oleDb1，针对数据库2就必须用oleDb2，编码人员必须记住每个oleDb是操作的哪个数据库，一不小心就有可能出错；我就是想一个oleDb对象搞定多个数据库，在这个地方想了很久，直到有一天在SQL Server Management Studio中写sql语句发现它对多数据库的这种处理方式值得借鉴，看下图；
SQL Server Management Studio工具在一个页面操作多个数据库，只要先用USE命令指定数据库名，那么下面sql语句都是在指定的数据库中执行，直到碰到下一个USE；
下面来看框架中是怎么实现的：
先用OpenDBKeys指定哪两个数据库别名SQL2005和SQL20052，执行方法Text（），oleDb默认是操作SQL2005数据库，执行SQL语句“select * from basemenu”，接着通过“UseDb("SQL20052")”切换oleDb为可以操作SQL20052数据库了；Test（）方法执行完后oleDb又恢复到默认数据库；
不知道大家还有更好的方法来实现多数据库同时操作，可以一起讨论下；
三、数据库事务处理
关于数据库事务在上章讲EntLib中就有提到过，利用框架的AOP功能来实现；
如上图，使用起来很简单，在方法前面加上自定义标签AOP并绑定AopTransaction对象，这样InitFields方法中数据库操作都是在一个事务中进行的；我们在看一下AopTransaction类实现的一些细节；
BeginTransaction（）开启事务
CommitTransaction（）、RollbackTransaction（）提交事务或异常回滚事务
我们看画红线的部分是关于多数据库事务处理的代码，先循环BeginTransaction，但是在CommitTransaction或RollbackTransaction的时候必须先对oledb就行反序；意思就是先开始事务的oledb必须最后提交事务；
四、数据分页处理
数据分页功能在Web项目中经常用到，分页一般分为前端分页和后端分页，前端分页就是把数据全部从数据库取出，在界面控件中进行分页显示；而后端分页是每次只从数据库库取指定条数数据；这里当然是讲得后端分页，前端分页JqueryEasyUI控件就可以帮我们搞定；
PageInfo分页对象
/// <summary> /// 分页信息 /// </summary> public class PageInfo { private int _pageSize = 20; private int _pageNo = 1; private int _totalRecord = 0; private string _keyName; private int _columnLength = 1; /// <summary> /// 页面大小 /// </summary> public int pageSize { get { return _pageSize; } set { _pageSize = value; } } /// <summary> /// 要取的页面，默认为0页 /// </summary> public int pageNo { get { return _pageNo; } set { _pageNo = value; } } /// <summary> /// 总页数 /// </summary> public int totalPage { get { return totalRecord % pageSize == 0 ? totalRecord / pageSize : totalRecord / pageSize + 1; } } /// <summary> /// 总记录数 /// </summary> public int totalRecord { get { return _totalRecord; } set { _totalRecord = value; } } public int startNum { get { return (pageNo - 1) * pageSize + 1; } } public int endNum { get { return startNum + pageSize - 1; } } public string KeyName { get { return _keyName; } set { _keyName = value; } } /// <summary> /// 列的长度 /// </summary> public int ColumnLength { get { return _columnLength; } set { _columnLength = value; } } public PageInfo(int _pagesize, int _currpagenum) { _pageSize = _pagesize; _pageNo = _currpagenum; } public PageInfo(int _pagesize, int _currpagenum, int _columnlength) { _pageSize = _pagesize; _pageNo = _currpagenum; _columnLength = _columnlength; } }
SqlPage对sql语句进行分页处理后生成新的sql语句
/// <summary> /// SQL语句进行分页包装 /// </summary> public class SqlPage { /// <summary> /// 格式化SQL语句 /// </summary> /// <param name="strsql"></param> /// <param name="pageInfo"></param> /// <param name="oleDb"></param> /// <returns></returns> public static string FormatSql(string strsql, PageInfo pageInfo, AbstractDatabase oleDb) { switch (oleDb.DbType) { case DatabaseType.IbmDb2: return Db2FormatSql(strsql, pageInfo, oleDb); case DatabaseType.MsAccess: return MsAccessFormatSql(strsql, pageInfo, oleDb); case DatabaseType.MySQL: return MySQLFormatSql(strsql, pageInfo, oleDb); case DatabaseType.Oracle: return OracleFormatSql(strsql, pageInfo, oleDb); case DatabaseType.SqlServer2000: return Sql2000FormatSql(strsql, pageInfo, oleDb); case DatabaseType.SqlServer2005: return Sql2005FormatSql(strsql, pageInfo, oleDb); } return null; } private static string Db2FormatSql(string strsql, PageInfo pageInfo, AbstractDatabase oleDb) { if (pageInfo.KeyName == null || pageInfo.KeyName == "") throw new Exception("分页KeyName属性不能为空，如：pageInfo.KeyName==\"Id\" 或 pageInfo.KeyName==\"Id|Desc\""); int starRecordNum = pageInfo.startNum; int endRecordNum = pageInfo.endNum; //int index = strsql.ToLower().LastIndexOf("order by"); //string _strsql = null; //if (index != -1) // _strsql = strsql.Remove(index); //else // _strsql = strsql; string _strsql = strsql; string sql_totalRecord = "select count(*) from (" + _strsql + ") A"; Object obj = oleDb.GetDataResult(sql_totalRecord); pageInfo.totalRecord = Convert.ToInt32(obj == DBNull.Value ? 0 : obj); string _sql = _strsql; string[] orderbys = pageInfo.KeyName.Split(new char[] { ‘|‘ }); string orderbyname, orderby; if (orderbys.Length != 2) { orderbyname = orderbys[0]; orderby = "desc"; } else { orderbyname = orderbys[0]; orderby = orderbys[1]; } strsql = @"select * from ( select rownumber() over(order by {3} {4}) as rowid, t.* from ({0}) t )as a where a.rowid >= {1} AND a.rowid < {2}"; strsql = String.Format(strsql, _sql, starRecordNum, endRecordNum, orderbyname, orderby); return strsql; } private static string Sql2000FormatSql(string strsql, PageInfo pageInfo, AbstractDatabase oleDb) { return null; } private static string Sql2005FormatSql(string strsql, PageInfo pageInfo, AbstractDatabase oleDb) { if (pageInfo.KeyName == null || pageInfo.KeyName == "") throw new Exception("分页KeyName属性不能为空，如：pageInfo.KeyName==\"Id\" 或 pageInfo.KeyName==\"Id|Desc\""); int starRecordNum = pageInfo.startNum; int endRecordNum = pageInfo.endNum; int index = strsql.ToLower().LastIndexOf("order by"); string _strsql = null; if (index != -1) _strsql = strsql.Remove(index); else _strsql = strsql; string sql_totalRecord = "select TOP 1 count(*) from (" + _strsql + ") A"; Object obj = oleDb.GetDataResult(sql_totalRecord); pageInfo.totalRecord = Convert.ToInt32(obj == DBNull.Value ? 0 : obj); string _sql = _strsql; string[] orderbys = pageInfo.KeyName.Split(new char[] { ‘|‘ }); string orderbyname, orderby; if (orderbys.Length != 2) { orderbyname = orderbys[0]; orderby = "desc"; } else { orderbyname = orderbys[0]; orderby = orderbys[1]; } strsql = @"select * from ( select row_number() over(order by {3} {4}) as rownum,t.* from ({0}) t ) as a where rownum between {1} and {2}"; strsql = String.Format(strsql, _sql, starRecordNum, endRecordNum, orderbyname, orderby); return strsql; } private static string MsAccessFormatSql(string strsql, PageInfo pageInfo, AbstractDatabase oleDb) { return null; } private static string MySQLFormatSql(string strsql, PageInfo pageInfo, AbstractDatabase oleDb) { if (pageInfo.KeyName == null || pageInfo.KeyName == "") throw new Exception("分页KeyName属性不能为空，如：pageInfo.KeyName==\"Id\" 或 pageInfo.KeyName==\"Id|Desc\""); int starRecordNum = pageInfo.startNum; int endRecordNum = pageInfo.endNum; //int index = strsql.ToLower().LastIndexOf("order by"); //string _strsql = null; //if (index != -1) // _strsql = strsql.Remove(index); //else // _strsql = strsql; string _strsql = strsql; string sql_totalRecord = "select count(*) from (" + _strsql + ") A"; Object obj = oleDb.GetDataResult(sql_totalRecord); pageInfo.totalRecord = Convert.ToInt32(obj == DBNull.Value ? 0 : obj); string _sql = _strsql; string[] orderbys = pageInfo.KeyName.Split(new char[] { ‘|‘ }); string orderbyname, orderby; if (orderbys.Length != 2) { orderbyname = orderbys[0]; orderby = "desc"; } else { orderbyname = orderbys[0]; orderby = orderbys[1]; } strsql = @"select * from ( select rownumber() over(order by {3} {4}) as rowid, t.* from ({0}) t )as a where a.rowid >= {1} AND a.rowid < {2}"; strsql = String.Format(strsql, _sql, starRecordNum, endRecordNum, orderbyname, orderby); return strsql; } private static string OracleFormatSql(string strsql, PageInfo pageInfo, AbstractDatabase oleDb) { int starRecordNum = pageInfo.startNum; int endRecordNum = pageInfo.endNum; string sql_totalRecord = "select count(*) from (" + strsql + ") A"; Object obj = oleDb.GetDataResult(sql_totalRecord); pageInfo.totalRecord = Convert.ToInt32(obj == DBNull.Value ? 0 : obj); strsql = " select * from( select a.*,rownum rn from ( " + strsql + " ) a ) where rn between " + starRecordNum.ToString() + " and " + endRecordNum.ToString(); return strsql; } }
五、关于oleDb对象创建与销毁的生命周期
关于oleDb对象的生命周期是什么意思，为什么要合理的控制？比如：我们知道Controller是可以调用ObjectModel对象的，也可以调用Dao对象，如果对Controller中的方法加上事务，那ObjectModel和Dao的数据库操作一定要在此事务中；所以oleDb对象创建实例后，是会传递到后面的ObjectModel和Dao的；
如何实现oledb的传递了，就是在创建对象的NewObject（）和NewDao（）方法中处理的；所以为什么说在框架中对象的创建一定得用这两个方法，绝对不能用new关键字创建；
十三、EnterpriseFrameWork框架核心类库之数据库操作（多数据库事务处理）
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。