///
/// 超级数据库操作类
/// 2015年12月21日
/// v1.0
/// 叶琪
///
publicclassDBHelper
{
#region 属性
privateDbProviderFactory _DbFactory;
privateDBConfig mDBConfig;
///
/// 数据库连接配置
///
publicDBConfig DBConfig
{
get{returnmDBConfig; }
}
///
/// 表示一组方法,这些方法用于创建提供程序对数据源类的实现的实例。
///
publicDbProviderFactory DbFactory
{
get{return_DbFactory; }
set{ _DbFactory = value; }
}
#endregion
#region 构造函数
publicDBHelper(DBConfig aORMConfig)
{
mDBConfig = aORMConfig;
switch(mDBConfig.DBType)
{
caseORMType.DBTypes.SQLSERVER:
_DbFactory = System.Data.SqlClient.SqlClientFactory.Instance;
break;
caseORMType.DBTypes.MYSQL:
LoadDbProviderFactory("MySql.Data.dll","MySql.Data.MySqlClient.MySqlClientFactory");
break;
caseORMType.DBTypes.SQLITE:
LoadDbProviderFactory("System.Data.SQLite.dll","System.Data.SQLite.SQLiteFactory");
break;
}
}
///
/// 动态载入数据库封装库
///
/// 数据库封装库文件名称
/// 工厂路径名称
privatevoidLoadDbProviderFactory(stringaDLLName,stringaFactoryName)
{
stringdllPath =string.Empty;
if(System.AppDomain.CurrentDomain.RelativeSearchPath !=null)
{
dllPath = System.AppDomain.CurrentDomain.RelativeSearchPath+"\\"+ aDLLName;
}
else
{
dllPath = System.AppDomain.CurrentDomain.BaseDirectory + aDLLName;
}
if(!File.Exists(dllPath))
{//文件不存在,从库资源中复制输出到基目录下
FileStream fdllFile = newFileStream(dllPath,FileMode.Create);
byte[] dllData =null;
if(aDLLName =="System.Data.SQLite.dll")
{
dllData = YFmk.ORM.Properties.Resources.System_Data_SQLite;
}
elseif(aDLLName =="MySql.Data.dll")
{
dllData = YFmk.ORM.Properties.Resources.MySql_Data;
}
fdllFile.Write(dllData, 0, dllData.Length);
fdllFile.Close();
}
Assembly libAssembly = Assembly.LoadFile(dllPath);
Type type = libAssembly.GetType(aFactoryName);
foreach(FieldInfo fiintype.GetFields(BindingFlags.Static | BindingFlags.Public))
{
if(fi.Name =="Instance")
{
_DbFactory = fi.GetValue(null)asDbProviderFactory;
return;
}
}
}
#endregion
#region 数据库操作
///
/// 执行一条计算查询结果语句,返回查询结果
///
/// SQL语句及参数
/// 查询结果(object)
publicobjectGetSingle(SQLWithParameter aSQLWithParameter)
{
using(DbConnection conn = _DbFactory.CreateConnection())
{
conn.ConnectionString = mDBConfig.ConnString;
using(DbCommand cmd = _DbFactory.CreateCommand())
{
PrepareCommand(cmd, conn, aSQLWithParameter.SQL.ToString(), aSQLWithParameter.Parameters);
objectobj = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if((Object.Equals(obj,null)) || (Object.Equals(obj, System.DBNull.Value)))
{
returnnull;
}
else
{
returnobj;
}
}
}
}
///
/// 执行SQL语句,返回影响的记录数
///
/// SQL语句
/// 影响的记录数
publicintExecuteSql(stringaSQL)
{
using(DbConnection conn = _DbFactory.CreateConnection())
{
conn.ConnectionString = mDBConfig.ConnString;
using(DbCommand cmd = _DbFactory.CreateCommand())
{
PrepareCommand(cmd, conn, aSQL);
introws = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
returnrows;
}
}
}
///
/// 执行SQL语句,返回影响的记录数
///
/// SQL语句及参数
///
publicintExecuteSql(SQLWithParameter aSQLWithParameter)
{
using(DbConnection conn = _DbFactory.CreateConnection())
{
conn.ConnectionString = mDBConfig.ConnString;
using(DbCommand cmd = _DbFactory.CreateCommand())
{
PrepareCommand(cmd, conn, aSQLWithParameter.SQL.ToString(), aSQLWithParameter.Parameters);
introws = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
returnrows;
}
}
}
///
/// 执行多条SQL语句,实现数据库事务。
///
/// 参数化的SQL语句结构体对象集合
publicstringExecuteSqlTran(List aSQLWithParameterList)
{
using(DbConnection conn = _DbFactory.CreateConnection())
{
conn.ConnectionString = mDBConfig.ConnString;
conn.Open();
DbTransaction fSqlTransaction = conn.BeginTransaction();
try
{
List fTranCmdList = newList();
//创建新的CMD
DbCommand fFirstCMD = _DbFactory.CreateCommand();
fFirstCMD.Connection = conn;
fFirstCMD.Transaction = fSqlTransaction;
fTranCmdList.Add(fFirstCMD);
intNowCmdIndex = 0;//当前执行的CMD索引值
intExecuteCount = 0;//已经执行的CMD次数
StringBuilder fSQL = newStringBuilder();
foreach(SQLWithParameter fSQLWithParameterinaSQLWithParameterList)
{
fSQL.Append(fSQLWithParameter.SQL.ToString() + ";");
fTranCmdList[NowCmdIndex].Parameters.AddRange(fSQLWithParameter.Parameters.ToArray());
if(fTranCmdList[NowCmdIndex].Parameters.Count > 2000)
{ //参数达到2000个,执行一次CMD
fTranCmdList[NowCmdIndex].CommandText = fSQL.ToString();
fTranCmdList[NowCmdIndex].ExecuteNonQuery();
DbCommand fNewCMD = _DbFactory.CreateCommand();
fNewCMD.Connection = conn;
fNewCMD.Transaction = fSqlTransaction;
fTranCmdList.Add(fNewCMD);
NowCmdIndex++;
ExecuteCount++;
fSQL.Clear();//清空SQL
}
}
if(ExecuteCount
{//已执行CMD次数小于总CMD数,执行最后一条CMD
fTranCmdList[fTranCmdList.Count - 1].CommandText = fSQL.ToString();
fTranCmdList[fTranCmdList.Count - 1].ExecuteNonQuery();
}
fSqlTransaction.Commit();
returnnull;
}
catch(Exception ex)
{
fSqlTransaction.Rollback();
StringBuilder fSQL = newStringBuilder();
foreach(SQLWithParameter fSQLWithParameterinaSQLWithParameterList)
{
fSQL.Append(fSQLWithParameter.SQL.ToString() + ";");
}
YFmk.Lib.LocalLog.WriteByDate(fSQL.ToString()+" 错误:"+ex.Message,"ORM");
returnex.Message;
}
}
}
///
/// 执行查询语句,返回DataSet
///
/// 查询语句
/// DataSet
publicDataSet Query(stringSQLString)
{
using(DbConnection conn = _DbFactory.CreateConnection())
{
conn.ConnectionString = mDBConfig.ConnString;
using(DbCommand cmd = _DbFactory.CreateCommand())
{
PrepareCommand(cmd, conn, SQLString);
using(DbDataAdapter da = _DbFactory.CreateDataAdapter())
{
da.SelectCommand = cmd;
DataSet ds = newDataSet();
try
{
da.Fill(ds, "ds");
cmd.Parameters.Clear();
}
catch(Exception ex)
{
}
returnds;
}
}
}
}
///
/// 执行查询语句,返回DataSet
///
/// 查询语句
/// DataSet
publicDataSet Query(SQLWithParameter aSQLWithParameter)
{
using(DbConnection conn = _DbFactory.CreateConnection())
{
conn.ConnectionString = mDBConfig.ConnString;
using(DbCommand cmd = _DbFactory.CreateCommand())
{
PrepareCommand(cmd, conn, aSQLWithParameter.SQL.ToString(), aSQLWithParameter.Parameters);
using(DbDataAdapter da = _DbFactory.CreateDataAdapter())
{
da.SelectCommand = cmd;
DataSet ds = newDataSet();
da.Fill(ds, "ds");
cmd.Parameters.Clear();
returnds;
}
}
}
}
#endregion
#region 私有函数
privatevoidPrepareCommand(DbCommand cmd, DbConnection conn,stringcmdText)
{
if(conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
}
privatevoidPrepareCommand(DbCommand cmd, DbConnection conn,stringcmdText, List cmdParms)
{
if(conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if(cmdParms !=null&& cmdParms.Count>0)
{
cmd.Parameters.AddRange(cmdParms.ToArray());
}
}
#endregion