2023年6月21日发(作者：)

继承IDbConnection连接不同数据库

本⽅案可实现仅修改即可连接不同数据库，但是设计数据库时需要注意各种数据库的数据类型是不⼀样的。各种不同数据库的Connection、Command、DataAdapter、Transaction和Parameter都继承⾃IDbConnection、IDbCommand、IDbDataAdapter、IDbTransaction和IDbDataParameter，⽤⼀个⼯⼚来实现接⼝的实例即可实现连接不同数据库。⾸先，需要新建⼀个类库，命名为DbManager，此类库需要5个⽂件，

1、创建⼀个枚举类型：space DbManager{

public enum DataProvider { Oracle, SqlServer, OleDb, Odbc, MySql }}2、创建⼀个⼯⼚类，⽤来产⽣以上不同数据库的实例：g ;using ;using ent;using ;using Client; //需要添加引⽤using lient; //请⾃⾏安装MySQLConnector/Net后添加引⽤namespace DbManager{ public sealed class DBManagerFactory { private DBManagerFactory() { } public static IDbConnection GetConnection(DataProvider providerType) { IDbConnection iDbConnection; switch (providerType) { case ver: iDbConnection = new SqlConnection(); break; case : iDbConnection = new OleDbConnection(); break; case : iDbConnection = new OdbcConnection(); break; case : iDbConnection = new OracleConnection(); break; case : iDbConnection = new MySqlConnection(); break; default: return null; } return iDbConnection; } } public static IDbCommand GetCommand(DataProvider providerType) { switch (providerType) { case ver: return new SqlCommand(); case : return new OleDbCommand(); case : return new OdbcCommand(); case : return new OracleCommand(); case : return new MySqlCommand(); default: return null; } } public static IDbDataAdapter GetDataAdapter(DataProvider providerType) { switch (providerType) { case ver: return new SqlDataAdapter(); case : return new OleDbDataAdapter(); case : return new OdbcDataAdapter(); case : return new OracleDataAdapter(); case : return new MySqlDataAdapter(); default: return null; } } public static IDbTransaction GetTransaction(DataProvider providerType) { IDbConnection iDbConnection = GetConnection(providerType); IDbTransaction iDbTransaction = ransaction(); return iDbTransaction; } public static IDbDataParameter[] GetParameters(DataProvider providerType, int paramsCount) { IDbDataParameter[] idbParams = new IDbDataParameter[paramsCount]; switch (providerType) { case ver: for (int i = 0; i < paramsCount; i++) { idbParams[i] = new SqlParameter(); } break; case : for (int i = 0; i < paramsCount; i++) { idbParams[i] = new OleDbParameter(); } break; case : for (int i = 0; i < paramsCount; i++) { idbParams[i] = new OdbcParameter(); } break; break; case : for (int i = 0; i < paramsCount; i++) { idbParams[i] = new OracleParameter(); } break; case : for (int i = 0; i < paramsCount; i++) { idbParams[i] = new MySqlParameter(); } break; default: idbParams = null; break; } return idbParams; } }}3、创建⼀个接⼝：g ;namespace DbManager{ public interface IDBManager { DataProvider ProviderType { get; set; } IDbConnection Connection { get; set; } IDataReader DataReader { get; set; } IDbCommand Command { get; set; } IDbTransaction Transaction { get; set; } IDbDataParameter[] Parameters { get; set; } string ConnectionString { get; set; } void Open(); void Close(); void Dispose(); void CreateParameters(int paramsCount); void AddParameters(int index, string paramName, object objValue); void BeginTransaction(); void CommitTransaction(); void CloseReader(); IDataReader ExecuteReader(CommandType commandType, string commandText); int ExecuteNonQuery(CommandType commandType, string commandText); object ExecuteScalar(CommandType commandType, string commandText); DataSet ExecuteDataSet(CommandType commandType, string commandText); }}4、创建⼀个类来实现IDBManager接⼝：g System;using ;using ;namespace DbManager{ public sealed class DBManager : IDBManager, IDisposable { #region 字段 private DataProvider _providerType; private IDbConnection _idbConnection; private IDataReader _iDataReader; private IDbCommand _idbCommand; private IDbTransaction _idbTransaction; private IDbDataParameter[] _idbParameters; private string _connectionString; #endregion #region 构造⽅法 public DBManager() { } public DBManager(DataProvider providerType) { ProviderType = providerType; } public DBManager(DataProvider providerType, string connectionString) { ProviderType = providerType; ConnectionString = connectionString; } #endregion #region 属性 public DataProvider ProviderType { get { return _providerType; } set { _providerType = value; } } public IDbConnection Connection { get { return _idbConnection; } set { _idbConnection = value; } } public IDataReader DataReader { get { return _iDataReader; } set { _iDataReader = value; } } public IDbCommand Command { get { return _idbCommand; } set { _idbCommand = value; } } public IDbTransaction Transaction { get { return _idbTransaction; } set { _idbTransaction = value; } } public IDbDataParameter[] Parameters { get { return _idbParameters; } set { _idbParameters = value; } } public string ConnectionString { get { return _connectionString; } set { _connectionString = value; } } #endregion #region 公有⽅法 public void Open() { Connection = nection(ProviderType); tionString = ConnectionString; if ( != ) { (); } Command = mand(ProviderType); } public void Close() { if ( != ) { (); } } public void Dispose() { ssFinalize(this); Close(); Command = null; Transaction = null; Connection = null; } public void CreateParameters(int paramsCount) { Parameters = new IDbDataParameter[paramsCount]; Parameters = ameters(ProviderType, paramsCount); } public void AddParameters(int index, string paramName, object objValue) { if (index < ) { Parameters[index].ParameterName = paramName; Parameters[index].Value = objValue; } } public void BeginTransaction() { if (Transaction == null) { Transaction = nsaction(ProviderType); } ction = Transaction; } public void CommitTransaction() public void CommitTransaction() { if (Transaction != null) { (); } Transaction = null; } public void CloseReader() { if (DataReader != null) { (); } } public IDataReader ExecuteReader(CommandType commandType, string commandText) { Command = mand(ProviderType); tion = Connection; PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters); DataReader = eReader(); (); return DataReader; } public int ExecuteNonQuery(CommandType commandType, string commandText) { Command = mand(ProviderType); PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters); int returnValue = eNonQuery(); (); return returnValue; } public object ExecuteScalar(CommandType commandType, string commandText) { Command = mand(ProviderType); PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters); object returnValue = eScalar(); (); return returnValue; } public DataSet ExecuteDataSet(CommandType commandType, string commandText) { Command = mand(ProviderType); PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters); IDbDataAdapter dataAdapter = aAdapter(ProviderType); Command = Command; DataSet dataSet = new DataSet(); (dataSet); (); return dataSet; } #endregion #region 私有⽅法 private void AttachParameters(IDbCommand command, IDbDataParameter[] commandParameters) { foreach (IDbDataParameter idbParameter in commandParameters) { if (ion == utput && == null) { = ; } (idbParameter); (idbParameter); } } private void PrepareCommand(IDbCommand command, IDbConnection connection, IDbTransaction transaction, CommandType commandType, string commandText, IDbDataParameter[] commandParameters) { tion = connection; dText = commandText; dType = commandType; if (transaction != null) { ction = transaction; } if (commandParameters != null) { AttachParameters(command, commandParameters); } } #endregion }}5、再加⼀个，来调⽤DBManager类，外部来直接调⽤DBHelper类即可。using System;using ;using uration;namespace DbManager{ public class DBHelper { private static readonly IDBManager dbManager = new DBManager(GetDataProvider(), GetConnectionString()); ///

/// 从配置⽂件中选择数据库类型 /// /// DataProvider枚举值 private static DataProvider GetDataProvider() { string providerType = tings["DataProvider"]; DataProvider dataProvider; switch (providerType) { case "Oracle": dataProvider = ; break; case "SqlServer": dataProvider = ver; break; case "OleDb": dataProvider = ; break; case "Odbc": dataProvider = ; break; case "MySql": dataProvider = ; break; default: return ; } return dataProvider; } } /// /// 从配置⽂件获取连接字符串 /// /// 连接字符串 private static string GetConnectionString() { return tionStrings["ConnString"].ConnectionString; } /// /// 关闭数据库连接的⽅法 /// public static void Close() { e(); } /// /// 创建参数 /// /// 参数个数 public static void CreateParameters(int paramsCount) { Parameters(paramsCount); } /// /// 添加参数 /// /// 参数索引 /// 参数名 /// 参数值 public static void AddParameters(int index, string paramName, object objValue) { ameters(index, paramName, objValue); } /// /// 执⾏增删改 /// /// 安全的sql语句() /// 操作成功返回true public static bool ExecuteNonQuery(string sqlString) { try { (); return eNonQuery(, sqlString) > 0 ? true : false; } catch (Exception e) { throw new Exception(e); } finally { e(); } } /// /// 执⾏查询 /// /// 安全的sql语句() /// 返回IDataReader public static IDataReader ExecuteReader(string sqlString) { try { { (); return eReader(, sqlString); } catch (Exception e) { throw new Exception(e); } } }}现在，将上述项⽬⽣成⼀个类库，在具体的DAL层⾥⾯就可以直接调⽤了。DBHelper类没有全部写完，只写了ExecuteNonQuery()和ExecuteReader()两个⽅法，对于有参和⽆参的增删改查操作暂时够⽤，返回DataSet的⽅法未写，Transaction相关的也未写。6、 7、程序中的调⽤举个简单的例⼦，我们就创建⼀个控制台应⽤程序，然后添加的引⽤⽂件的样⼦：using System;using ;using DbManager; //记得引⼊命名空间namespace DBDemo{ class Program { static void Main(string[] args) { SelectWithoutParams(); ine("------安全sql语句()的查询结果------"); SelectWithSafeSql(4); ine("------参数化语句的查询结果-------"); SelectWithParams("总统套间"); }

private static void SelectWithoutParams() { const string sql = "select * from RoomType"; IDataReader reader = eReader(sql); while (()) { ine(reader["TypeName"].ToString()); } (); //记得关闭reader } private static void SelectWithSafeSql(int TypeId) { string sql = ("select * from RoomType where TypeId={0}", TypeId); IDataReader reader = eReader(sql); while (()) { ine(reader["TypeName"].ToString()); } (); } private static void SelectWithParams(string typeName) { string sql = "select * from RoomType where TypeName=@TypeName";

//先创建参数，然后才能添加参数

Parameters(1); //参数个数，1个 ameters(0, "@TypeName", typeName); IDataReader reader = eReader(sql); while (()) { ine(reader["TypeName"].ToString()); } (); } }}

OK！全部完成！在具体的DAL层中，调⽤DBHelper的相关⽅法即可，如果是查询⽅法，记得最后要写关闭代码。只要表结构⼀样，可以在中随意切换数据库。最后注意的是：各个数据库的插⼊语句不⼀样，假设我们有4个字段，第⼀个字段fieldName1为⾃增字段。对于SQLServer，不需要写⾃增字段，语句是：INSERT INTO table VALUES(value2, value3, value4);对于MySQL，⾃增字段位置需要写null代替，语句是：INSERT INTO table VALUES(NULL, value2, value3, value4);⽽对于ACCESS数据库，则必须写完整，语句是：INSERT INTO table(fieldName2, fieldName3,fieldName4) VALUES(value2, value3, value4);为了实现兼容，⼤家还是都按完整的来写，就不会有错了。

2023年6月21日发(作者：)

继承IDbConnection连接不同数据库

本⽅案可实现仅修改即可连接不同数据库，但是设计数据库时需要注意各种数据库的数据类型是不⼀样的。各种不同数据库的Connection、Command、DataAdapter、Transaction和Parameter都继承⾃IDbConnection、IDbCommand、IDbDataAdapter、IDbTransaction和IDbDataParameter，⽤⼀个⼯⼚来实现接⼝的实例即可实现连接不同数据库。⾸先，需要新建⼀个类库，命名为DbManager，此类库需要5个⽂件，

1、创建⼀个枚举类型：space DbManager{

public enum DataProvider { Oracle, SqlServer, OleDb, Odbc, MySql }}2、创建⼀个⼯⼚类，⽤来产⽣以上不同数据库的实例：g ;using ;using ent;using ;using Client; //需要添加引⽤using lient; //请⾃⾏安装MySQLConnector/Net后添加引⽤namespace DbManager{ public sealed class DBManagerFactory { private DBManagerFactory() { } public static IDbConnection GetConnection(DataProvider providerType) { IDbConnection iDbConnection; switch (providerType) { case ver: iDbConnection = new SqlConnection(); break; case : iDbConnection = new OleDbConnection(); break; case : iDbConnection = new OdbcConnection(); break; case : iDbConnection = new OracleConnection(); break; case : iDbConnection = new MySqlConnection(); break; default: return null; } return iDbConnection; } } public static IDbCommand GetCommand(DataProvider providerType) { switch (providerType) { case ver: return new SqlCommand(); case : return new OleDbCommand(); case : return new OdbcCommand(); case : return new OracleCommand(); case : return new MySqlCommand(); default: return null; } } public static IDbDataAdapter GetDataAdapter(DataProvider providerType) { switch (providerType) { case ver: return new SqlDataAdapter(); case : return new OleDbDataAdapter(); case : return new OdbcDataAdapter(); case : return new OracleDataAdapter(); case : return new MySqlDataAdapter(); default: return null; } } public static IDbTransaction GetTransaction(DataProvider providerType) { IDbConnection iDbConnection = GetConnection(providerType); IDbTransaction iDbTransaction = ransaction(); return iDbTransaction; } public static IDbDataParameter[] GetParameters(DataProvider providerType, int paramsCount) { IDbDataParameter[] idbParams = new IDbDataParameter[paramsCount]; switch (providerType) { case ver: for (int i = 0; i < paramsCount; i++) { idbParams[i] = new SqlParameter(); } break; case : for (int i = 0; i < paramsCount; i++) { idbParams[i] = new OleDbParameter(); } break; case : for (int i = 0; i < paramsCount; i++) { idbParams[i] = new OdbcParameter(); } break; break; case : for (int i = 0; i < paramsCount; i++) { idbParams[i] = new OracleParameter(); } break; case : for (int i = 0; i < paramsCount; i++) { idbParams[i] = new MySqlParameter(); } break; default: idbParams = null; break; } return idbParams; } }}3、创建⼀个接⼝：g ;namespace DbManager{ public interface IDBManager { DataProvider ProviderType { get; set; } IDbConnection Connection { get; set; } IDataReader DataReader { get; set; } IDbCommand Command { get; set; } IDbTransaction Transaction { get; set; } IDbDataParameter[] Parameters { get; set; } string ConnectionString { get; set; } void Open(); void Close(); void Dispose(); void CreateParameters(int paramsCount); void AddParameters(int index, string paramName, object objValue); void BeginTransaction(); void CommitTransaction(); void CloseReader(); IDataReader ExecuteReader(CommandType commandType, string commandText); int ExecuteNonQuery(CommandType commandType, string commandText); object ExecuteScalar(CommandType commandType, string commandText); DataSet ExecuteDataSet(CommandType commandType, string commandText); }}4、创建⼀个类来实现IDBManager接⼝：g System;using ;using ;namespace DbManager{ public sealed class DBManager : IDBManager, IDisposable { #region 字段 private DataProvider _providerType; private IDbConnection _idbConnection; private IDataReader _iDataReader; private IDbCommand _idbCommand; private IDbTransaction _idbTransaction; private IDbDataParameter[] _idbParameters; private string _connectionString; #endregion #region 构造⽅法 public DBManager() { } public DBManager(DataProvider providerType) { ProviderType = providerType; } public DBManager(DataProvider providerType, string connectionString) { ProviderType = providerType; ConnectionString = connectionString; } #endregion #region 属性 public DataProvider ProviderType { get { return _providerType; } set { _providerType = value; } } public IDbConnection Connection { get { return _idbConnection; } set { _idbConnection = value; } } public IDataReader DataReader { get { return _iDataReader; } set { _iDataReader = value; } } public IDbCommand Command { get { return _idbCommand; } set { _idbCommand = value; } } public IDbTransaction Transaction { get { return _idbTransaction; } set { _idbTransaction = value; } } public IDbDataParameter[] Parameters { get { return _idbParameters; } set { _idbParameters = value; } } public string ConnectionString { get { return _connectionString; } set { _connectionString = value; } } #endregion #region 公有⽅法 public void Open() { Connection = nection(ProviderType); tionString = ConnectionString; if ( != ) { (); } Command = mand(ProviderType); } public void Close() { if ( != ) { (); } } public void Dispose() { ssFinalize(this); Close(); Command = null; Transaction = null; Connection = null; } public void CreateParameters(int paramsCount) { Parameters = new IDbDataParameter[paramsCount]; Parameters = ameters(ProviderType, paramsCount); } public void AddParameters(int index, string paramName, object objValue) { if (index < ) { Parameters[index].ParameterName = paramName; Parameters[index].Value = objValue; } } public void BeginTransaction() { if (Transaction == null) { Transaction = nsaction(ProviderType); } ction = Transaction; } public void CommitTransaction() public void CommitTransaction() { if (Transaction != null) { (); } Transaction = null; } public void CloseReader() { if (DataReader != null) { (); } } public IDataReader ExecuteReader(CommandType commandType, string commandText) { Command = mand(ProviderType); tion = Connection; PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters); DataReader = eReader(); (); return DataReader; } public int ExecuteNonQuery(CommandType commandType, string commandText) { Command = mand(ProviderType); PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters); int returnValue = eNonQuery(); (); return returnValue; } public object ExecuteScalar(CommandType commandType, string commandText) { Command = mand(ProviderType); PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters); object returnValue = eScalar(); (); return returnValue; } public DataSet ExecuteDataSet(CommandType commandType, string commandText) { Command = mand(ProviderType); PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters); IDbDataAdapter dataAdapter = aAdapter(ProviderType); Command = Command; DataSet dataSet = new DataSet(); (dataSet); (); return dataSet; } #endregion #region 私有⽅法 private void AttachParameters(IDbCommand command, IDbDataParameter[] commandParameters) { foreach (IDbDataParameter idbParameter in commandParameters) { if (ion == utput && == null) { = ; } (idbParameter); (idbParameter); } } private void PrepareCommand(IDbCommand command, IDbConnection connection, IDbTransaction transaction, CommandType commandType, string commandText, IDbDataParameter[] commandParameters) { tion = connection; dText = commandText; dType = commandType; if (transaction != null) { ction = transaction; } if (commandParameters != null) { AttachParameters(command, commandParameters); } } #endregion }}5、再加⼀个，来调⽤DBManager类，外部来直接调⽤DBHelper类即可。using System;using ;using uration;namespace DbManager{ public class DBHelper { private static readonly IDBManager dbManager = new DBManager(GetDataProvider(), GetConnectionString()); ///

/// 从配置⽂件中选择数据库类型 /// /// DataProvider枚举值 private static DataProvider GetDataProvider() { string providerType = tings["DataProvider"]; DataProvider dataProvider; switch (providerType) { case "Oracle": dataProvider = ; break; case "SqlServer": dataProvider = ver; break; case "OleDb": dataProvider = ; break; case "Odbc": dataProvider = ; break; case "MySql": dataProvider = ; break; default: return ; } return dataProvider; } } /// /// 从配置⽂件获取连接字符串 /// /// 连接字符串 private static string GetConnectionString() { return tionStrings["ConnString"].ConnectionString; } /// /// 关闭数据库连接的⽅法 /// public static void Close() { e(); } /// /// 创建参数 /// /// 参数个数 public static void CreateParameters(int paramsCount) { Parameters(paramsCount); } /// /// 添加参数 /// /// 参数索引 /// 参数名 /// 参数值 public static void AddParameters(int index, string paramName, object objValue) { ameters(index, paramName, objValue); } /// /// 执⾏增删改 /// /// 安全的sql语句() /// 操作成功返回true public static bool ExecuteNonQuery(string sqlString) { try { (); return eNonQuery(, sqlString) > 0 ? true : false; } catch (Exception e) { throw new Exception(e); } finally { e(); } } /// /// 执⾏查询 /// /// 安全的sql语句() /// 返回IDataReader public static IDataReader ExecuteReader(string sqlString) { try { { (); return eReader(, sqlString); } catch (Exception e) { throw new Exception(e); } } }}现在，将上述项⽬⽣成⼀个类库，在具体的DAL层⾥⾯就可以直接调⽤了。DBHelper类没有全部写完，只写了ExecuteNonQuery()和ExecuteReader()两个⽅法，对于有参和⽆参的增删改查操作暂时够⽤，返回DataSet的⽅法未写，Transaction相关的也未写。6、 7、程序中的调⽤举个简单的例⼦，我们就创建⼀个控制台应⽤程序，然后添加的引⽤⽂件的样⼦：using System;using ;using DbManager; //记得引⼊命名空间namespace DBDemo{ class Program { static void Main(string[] args) { SelectWithoutParams(); ine("------安全sql语句()的查询结果------"); SelectWithSafeSql(4); ine("------参数化语句的查询结果-------"); SelectWithParams("总统套间"); }

private static void SelectWithoutParams() { const string sql = "select * from RoomType"; IDataReader reader = eReader(sql); while (()) { ine(reader["TypeName"].ToString()); } (); //记得关闭reader } private static void SelectWithSafeSql(int TypeId) { string sql = ("select * from RoomType where TypeId={0}", TypeId); IDataReader reader = eReader(sql); while (()) { ine(reader["TypeName"].ToString()); } (); } private static void SelectWithParams(string typeName) { string sql = "select * from RoomType where TypeName=@TypeName";

//先创建参数，然后才能添加参数

Parameters(1); //参数个数，1个 ameters(0, "@TypeName", typeName); IDataReader reader = eReader(sql); while (()) { ine(reader["TypeName"].ToString()); } (); } }}

OK！全部完成！在具体的DAL层中，调⽤DBHelper的相关⽅法即可，如果是查询⽅法，记得最后要写关闭代码。只要表结构⼀样，可以在中随意切换数据库。最后注意的是：各个数据库的插⼊语句不⼀样，假设我们有4个字段，第⼀个字段fieldName1为⾃增字段。对于SQLServer，不需要写⾃增字段，语句是：INSERT INTO table VALUES(value2, value3, value4);对于MySQL，⾃增字段位置需要写null代替，语句是：INSERT INTO table VALUES(NULL, value2, value3, value4);⽽对于ACCESS数据库，则必须写完整，语句是：INSERT INTO table(fieldName2, fieldName3,fieldName4) VALUES(value2, value3, value4);为了实现兼容，⼤家还是都按完整的来写，就不会有错了。