本章介绍框架中封装的数据库操作的一些功能，在实现的过程中费了不少心思，针对不同数据库的操作（SQLServer、Oracle、DB2）这方面还是比较简单的，用工厂模式就能很好解决，反而是在多数据库同时操作方面走了不少弯路；现在从以下几个方面进行说明：
本文要点：
1.不同数据库操作
2.多数据库同时操作
3.数据库事务处理
4.数据分页处理
5.关于oleDb对象创建与销毁的生命周期
一、不同数据库操作
此处用到了工厂模式来实现不同数据库操作，看下图
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 ? : ret);
}
ret = database.ExecuteScalar(command);
return Convert.ToInt32(ret == DBNull.Value ? : 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 > )
{
return ds.Tables[];
}
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 > )
{
return ds.Tables[];
}
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 > )
{
return ds.Tables[];
}
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 > )
{
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 = ;
private int _pageNo = ;
private int _totalRecord = ;
private string _keyName;
private int _columnLength = ; /// <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 == ? totalRecord / pageSize : totalRecord / pageSize + ; }
} /// <summary>
/// 总记录数
/// </summary>
public int totalRecord
{
get { return _totalRecord; }
set { _totalRecord = value; }
} public int startNum
{
get { return (pageNo - ) * pageSize + ; }
} public int endNum
{
get { return startNum + pageSize - ; }
} 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 ? : obj); string _sql = _strsql;
string[] orderbys = pageInfo.KeyName.Split(new char[] { '|' });
string orderbyname, orderby;
if (orderbys.Length != )
{
orderbyname = orderbys[];
orderby = "desc";
}
else
{
orderbyname = orderbys[];
orderby = orderbys[];
} 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 != -)
_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 ? : obj); string _sql = _strsql;
string[] orderbys = pageInfo.KeyName.Split(new char[] { '|' });
string orderbyname, orderby;
if (orderbys.Length != )
{
orderbyname = orderbys[];
orderby = "desc";
}
else
{
orderbyname = orderbys[];
orderby = orderbys[];
} 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 ? : obj); string _sql = _strsql;
string[] orderbys = pageInfo.KeyName.Split(new char[] { '|' });
string orderbyname, orderby;
if (orderbys.Length != )
{
orderbyname = orderbys[];
orderby = "desc";
}
else
{
orderbyname = orderbys[];
orderby = orderbys[];
} 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 ? : 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关键字创建；