mysql兼容sqlserver吗_兼容SQLSERVER、Oracle、MYSQL、SQLITE的超级DBHelper

/// ///超级数据库操作类/// 2015年12月21日/// v1.0/// 叶琪///

public classDBHelper

{#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;

}

}///

///动态载入数据库封装库///

/// 数据库封装库文件名称

/// 工厂路径名称

private void LoadDbProviderFactory(string aDLLName, stringaFactoryName)

{string dllPath = 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;

}else if (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 fi in type.GetFields(BindingFlags.Static |BindingFlags.Public))

{if (fi.Name == "Instance")

{

_DbFactory= fi.GetValue(null) asDbProviderFactory;return;

}

}

}#endregion

#region 数据库操作

///

///执行一条计算查询结果语句，返回查询结果///

/// SQL语句及参数

/// 查询结果(object)

public objectGetSingle(SQLWithParameter aSQLWithParameter)

{using (DbConnection conn =_DbFactory.CreateConnection())

{

conn.ConnectionString=mDBConfig.ConnString;using (DbCommand cmd =_DbFactory.CreateCommand())

{

PrepareCommand(cmd, conn, aSQLWithParameter.SQL.ToString(), aSQLWithParameter.Parameters);object obj =cmd.ExecuteScalar();

cmd.Parameters.Clear();if ((Object.Equals(obj, null)) ||(Object.Equals(obj, System.DBNull.Value)))

{return null;

}else{returnobj;

}

}

}

}///

///执行SQL语句，返回影响的记录数///

/// SQL语句

/// 影响的记录数

public int ExecuteSql(stringaSQL)

{using (DbConnection conn =_DbFactory.CreateConnection())

{

conn.ConnectionString=mDBConfig.ConnString;using (DbCommand cmd =_DbFactory.CreateCommand())

{

PrepareCommand(cmd, conn, aSQL);int rows =cmd.ExecuteNonQuery();

cmd.Parameters.Clear();returnrows;

}

}

}///

///执行SQL语句，返回影响的记录数///

/// SQL语句及参数

///

public intExecuteSql(SQLWithParameter aSQLWithParameter)

{using (DbConnection conn =_DbFactory.CreateConnection())

{

conn.ConnectionString=mDBConfig.ConnString;using (DbCommand cmd =_DbFactory.CreateCommand())

{

PrepareCommand(cmd, conn, aSQLWithParameter.SQL.ToString(), aSQLWithParameter.Parameters);int rows =cmd.ExecuteNonQuery();

cmd.Parameters.Clear();returnrows;

}

}

}///

///执行多条SQL语句，实现数据库事务。///

/// 参数化的SQL语句结构体对象集合

public string ExecuteSqlTran(ListaSQLWithParameterList)

{using (DbConnection conn =_DbFactory.CreateConnection())

{

conn.ConnectionString=mDBConfig.ConnString;

conn.Open();

DbTransaction fSqlTransaction=conn.BeginTransaction();try{

List fTranCmdList = new List();//创建新的CMD

DbCommand fFirstCMD =_DbFactory.CreateCommand();

fFirstCMD.Connection=conn;

fFirstCMD.Transaction=fSqlTransaction;

fTranCmdList.Add(fFirstCMD);int NowCmdIndex = 0;//当前执行的CMD索引值

int ExecuteCount = 0;//已经执行的CMD次数

StringBuilder fSQL = newStringBuilder();foreach (SQLWithParameter fSQLWithParameter inaSQLWithParameterList)

{

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();return null;

}catch(Exception ex)

{

fSqlTransaction.Rollback();

StringBuilder fSQL= newStringBuilder();foreach (SQLWithParameter fSQLWithParameter inaSQLWithParameterList)

{

fSQL.Append(fSQLWithParameter.SQL.ToString()+ ";");

}

YFmk.Lib.LocalLog.WriteByDate(fSQL.ToString()+"错误："+ex.Message, "ORM");returnex.Message;

}

}

}///

///执行查询语句，返回DataSet///

/// 查询语句

/// DataSet

public DataSet 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 私有函数

private void PrepareCommand(DbCommand cmd, DbConnection conn, stringcmdText)

{if (conn.State !=ConnectionState.Open)

conn.Open();

cmd.Connection=conn;

cmd.CommandText=cmdText;

}private void PrepareCommand(DbCommand cmd, DbConnection conn, string cmdText, ListcmdParms)

{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