commandinfo mysql dbhelper_兼容SQLSERVER、Oracle、MYSQL、SQLITE的超级DBHelper

本示例代码的关键是利用.net库自带的DbProviderFactory来生产数据库操作对象。

从下图中，可以看到其的多个核心方法，这些方法将在我们的超级DBHelper中使用。

仔细研究，你会发现每个数据库的官方支持dll都有一个Instance对象，这个对象都是继承了DbProviderFactory了。

因此利用这点，我们就可以实现兼容多种数据的超级DBHelper了。

以下为示例代码,仅供参考学习，代码只是我的ORM框架中的一个片段(其中暂时支持了sqlSERVER、MysqL、sqlITE三种数据库，LoadDbProviderFactory方法是将封装在dll中的数据库操作dll反射加载实例化的方法。)：

///

/// 超级数据库操作类

/// 2015年12月21日

/// v1.0

/// 叶琪

///

public class DBHelper

{

#region 属性

private DbProviderFactory _DbFactory;

private DBConfig mDBConfig;

///

/// 数据库连接配置

///

public DBConfig DBConfig

{

get { return mDBConfig; }

}

///

/// 表示一组方法，这些方法用于创建提供程序对数据源类的实现的实例。

///

public DbProviderFactory DbFactory

{

get { return _DbFactory; }

set { _DbFactory = value; }

}

#endregion

#region 构造函数

public DBHelper(DBConfig aORMConfig)

{

mDBConfig = aORMConfig;

switch (mDBConfig.DBType)

{

case ORMType.DBTypes.sqlSERVER:

_DbFactory = System.Data.sqlClient.sqlClientFactory.Instance;

break;

case ORMType.DBTypes.MysqL:

LoadDbProviderFactory("MysqL.Data.dll","MysqL.Data.MysqLClient.MysqLClientFactory");

break;

case ORMType.DBTypes.sqlITE:

LoadDbProviderFactory("System.Data.sqlite.dll","System.Data.sqlite.sqliteFactory");

break;

}

}

///

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

///

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

/// 工厂路径名称

private void LoadDbProviderFactory(string aDLLName,string aFactoryName)

{

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 = new FileStream(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,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) as DbProviderFactory;

return;

}

}

}

#endregion

#region 数据库操作

///

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

///

/// sql语句及参数

/// 查询结果(object)

public object GetSingle(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

{

return obj;

}

}

}

}

///

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

///

/// sql语句

/// 影响的记录数

public int Executesql(string asql)

{

using (DbConnection conn = _DbFactory.CreateConnection())

{

conn.ConnectionString = mDBConfig.ConnString;

using (DbCommand cmd = _DbFactory.CreateCommand())

{

PrepareCommand(cmd,asql);

int rows = cmd.ExecuteNonQuery();

cmd.Parameters.Clear();

return rows;

}

}

}

///

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

///

/// sql语句及参数

///

public int Executesql(sqlWithParameter asqlWithParameter)

{

using (DbConnection conn = _DbFactory.CreateConnection())

{

conn.ConnectionString = mDBConfig.ConnString;

using (DbCommand cmd = _DbFactory.CreateCommand())

{

PrepareCommand(cmd,asqlWithParameter.Parameters);

int rows = cmd.ExecuteNonQuery();

cmd.Parameters.Clear();

return rows;

}

}

}

///

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

///

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

public string ExecutesqlTran(List asqlWithParameterList)

{

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 = new StringBuilder();

foreach (sqlWithParameter fsqlWithParameter in asqlWithParameterList)

{

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 < fTranCmdList.Count)

{//已执行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 = new StringBuilder();

foreach (sqlWithParameter fsqlWithParameter in asqlWithParameterList)

{

fsql.Append(fsqlWithParameter.sql.ToString() + ";");

}

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

return ex.Message;

}

}

}

///

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

///

/// 查询语句

/// DataSet

public DataSet Query(string sqlString)

{

using (DbConnection conn = _DbFactory.CreateConnection())

{

conn.ConnectionString = mDBConfig.ConnString;

using (DbCommand cmd = _DbFactory.CreateCommand())

{

PrepareCommand(cmd,sqlString);

using (DbDataAdapter da = _DbFactory.CreateDataAdapter())

{

da.SelectCommand = cmd;

DataSet ds = new DataSet();

try

{

da.Fill(ds,"ds");

cmd.Parameters.Clear();

}

catch (Exception ex)

{

}

return ds;

}

}

}

}

///

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

///

/// 查询语句

/// DataSet

public DataSet Query(sqlWithParameter asqlWithParameter)

{

using (DbConnection conn = _DbFactory.CreateConnection())

{

conn.ConnectionString = mDBConfig.ConnString;

using (DbCommand cmd = _DbFactory.CreateCommand())

{

PrepareCommand(cmd,asqlWithParameter.Parameters);

using (DbDataAdapter da = _DbFactory.CreateDataAdapter())

{

da.SelectCommand = cmd;

DataSet ds = new DataSet();

da.Fill(ds,"ds");

cmd.Parameters.Clear();

return ds;

}

}

}

}

#endregion

#region 私有函数

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

{

if (conn.State != ConnectionState.Open)

conn.Open();

cmd.Connection = conn;

cmd.CommandText = cmdText;

}

private void PrepareCommand(DbCommand cmd,string cmdText,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

@H_403_4@