idbconnection mysql_继承IDbConnection连接不同数据库

本方案可实现仅修改app.config即可连接不同数据库，但是设计数据库时需要注意各种数据库的数据类型是不一样的。

各种不同数据库的Connection、Command、DataAdapter、Transaction和Parameter都继承自IDbConnection、IDbCommand、IDbDataAdapter、IDbTransaction和IDbDataParameter，用一个工厂来实现接口的实例即可实现连接不同数据库。

首先，需要新建一个类库，命名为DbManager，此类库需要5个文件，

1、创建一个枚举类型：DataProvider.cs

namespace DbManager

{

public enum DataProvider

{

Oracle,

SqlServer,

OleDb,

Odbc,

MySql

}

}

2、创建一个工厂类，用来产生以上不同数据库的实例：DBManagerFactory.cs

usingSystem.Data;usingSystem.Data.Odbc;usingSystem.Data.SqlClient;usingSystem.Data.OleDb;usingSystem.Data.OracleClient; //需要添加引用usingMySql.Data.MySqlClient;   //请自行安装MySQLConnector/Net后添加引用namespaceDbManager

{publicsealedclassDBManagerFactory

{privateDBManagerFactory()

{

}publicstaticIDbConnection GetConnection(DataProvider providerType)

{

IDbConnection iDbConnection;switch(providerType)

{caseDataProvider.SqlServer:

iDbConnection=newSqlConnection();break;caseDataProvider.OleDb:

iDbConnection=newOleDbConnection();break;caseDataProvider.Odbc:

iDbConnection=newOdbcConnection();break;caseDataProvider.Oracle:

iDbConnection=newOracleConnection();break;caseDataProvider.MySql:

iDbConnection=newMySqlConnection();break;default:returnnull;

}returniDbConnection;

}publicstaticIDbCommand GetCommand(DataProvider providerType)

{switch(providerType)

{caseDataProvider.SqlServer:returnnewSqlCommand();caseDataProvider.OleDb:returnnewOleDbCommand();caseDataProvider.Odbc:returnnewOdbcCommand();caseDataProvider.Oracle:returnnewOracleCommand();caseDataProvider.MySql:returnnewMySqlCommand();default:returnnull;

}

}publicstaticIDbDataAdapter GetDataAdapter(DataProvider providerType)

{switch(providerType)

{caseDataProvider.SqlServer:returnnewSqlDataAdapter();caseDataProvider.OleDb:returnnewOleDbDataAdapter();caseDataProvider.Odbc:returnnewOdbcDataAdapter();caseDataProvider.Oracle:returnnewOracleDataAdapter();caseDataProvider.MySql:returnnewMySqlDataAdapter();default:returnnull;

}

}publicstaticIDbTransaction GetTransaction(DataProvider providerType)

{

IDbConnection iDbConnection=GetConnection(providerType);

IDbTransaction iDbTransaction=iDbConnection.BeginTransaction();returniDbTransaction;

}publicstaticIDbDataParameter[] GetParameters(DataProvider providerType,intparamsCount)

{

IDbDataParameter[] idbParams=newIDbDataParameter[paramsCount];switch(providerType)

{caseDataProvider.SqlServer:for(inti=0; i

{

idbParams[i]=newSqlParameter();

}break;caseDataProvider.OleDb:for(inti=0; i

{

idbParams[i]=newOleDbParameter();

}break;caseDataProvider.Odbc:for(inti=0; i

{

idbParams[i]=newOdbcParameter();

}break;caseDataProvider.Oracle:for(inti=0; i

{

idbParams[i]=newOracleParameter();

}break;caseDataProvider.MySql:for(inti=0; i

{

idbParams[i]=newMySqlParameter();

}break;default:

idbParams=null;break;

}returnidbParams;

}

}

}

3、创建一个接口：IDBManager.cs

usingSystem.Data;namespaceDbManager

{publicinterfaceIDBManager

{

DataProvider ProviderType

{get;set;

}

IDbConnection Connection

{get;set;

}

IDataReader DataReader

{get;set;

}

IDbCommand Command

{get;set;

}

IDbTransaction Transaction

{get;set;

}

IDbDataParameter[] Parameters

{get;set;

}stringConnectionString

{get;set;

}voidOpen();voidClose();voidDispose();voidCreateParameters(intparamsCount);voidAddParameters(intindex,stringparamName,objectobjValue);voidBeginTransaction();voidCommitTransaction();voidCloseReader();

IDataReader ExecuteReader(CommandType commandType,stringcommandText);intExecuteNonQuery(CommandType commandType,stringcommandText);objectExecuteScalar(CommandType commandType,stringcommandText);

DataSet ExecuteDataSet(CommandType commandType,stringcommandText);

}

}

4、创建一个类来实现IDBManager接口：DBManager.cs

usingSystem;usingSystem.Data;namespaceDbManager

{publicsealedclassDBManager : IDBManager, IDisposable

{#region字段privateDataProvider _providerType;privateIDbConnection _idbConnection;privateIDataReader _iDataReader;privateIDbCommand _idbCommand;privateIDbTransaction _idbTransaction;privateIDbDataParameter[] _idbParameters;privatestring_connectionString;#endregion#region构造方法publicDBManager()

{

}publicDBManager(DataProvider providerType)

{

ProviderType=providerType;

}publicDBManager(DataProvider providerType,stringconnectionString)

{

ProviderType=providerType;

ConnectionString=connectionString;

}#endregion#region属性publicDataProvider ProviderType

{get{return_providerType; }set{ _providerType=value; }

}publicIDbConnection Connection

{get{return_idbConnection; }set{ _idbConnection=value; }

}publicIDataReader DataReader

{get{return_iDataReader; }set{ _iDataReader=value; }

}publicIDbCommand Command

{get{return_idbCommand; }set{ _idbCommand=value; }

}publicIDbTransaction Transaction

{get{return_idbTransaction; }set{ _idbTransaction=value; }

}publicIDbDataParameter[] Parameters

{get{return_idbParameters; }set{ _idbParameters=value; }

}publicstringConnectionString

{get{return_connectionString; }set{ _connectionString=value; }

}#endregion#region公有方法publicvoidOpen()

{

Connection=DBManagerFactory.GetConnection(ProviderType);

Connection.ConnectionString=ConnectionString;if(Connection.State!=ConnectionState.Open)

{

Connection.Open();

}

Command=DBManagerFactory.GetCommand(ProviderType);

}publicvoidClose()

{if(Connection.State!=ConnectionState.Closed)

{

Connection.Close();

}

}publicvoidDispose()

{

GC.SuppressFinalize(this);

Close();

Command=null;

Transaction=null;

Connection=null;

}publicvoidCreateParameters(intparamsCount)

{

Parameters=newIDbDataParameter[paramsCount];

Parameters=DBManagerFactory.GetParameters(ProviderType, paramsCount);

}publicvoidAddParameters(intindex,stringparamName,objectobjValue)

{if(index

{

Parameters[index].ParameterName=paramName;

Parameters[index].Value=objValue;

}

}publicvoidBeginTransaction()

{if(Transaction==null)

{

Transaction=DBManagerFactory.GetTransaction(ProviderType);

}

Command.Transaction=Transaction;

}publicvoidCommitTransaction()

{if(Transaction!=null)

{

Transaction.Commit();

}

Transaction=null;

}publicvoidCloseReader()

{if(DataReader!=null)

{

DataReader.Close();

}

}publicIDataReader ExecuteReader(CommandType commandType,stringcommandText)

{

Command=DBManagerFactory.GetCommand(ProviderType);

Command.Connection=Connection;

PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters);

DataReader=Command.ExecuteReader();

Command.Parameters.Clear();returnDataReader;

}publicintExecuteNonQuery(CommandType commandType,stringcommandText)

{

Command=DBManagerFactory.GetCommand(ProviderType);

PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters);intreturnValue=Command.ExecuteNonQuery();

Command.Parameters.Clear();returnreturnValue;

}publicobjectExecuteScalar(CommandType commandType,stringcommandText)

{

Command=DBManagerFactory.GetCommand(ProviderType);

PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters);objectreturnValue=Command.ExecuteScalar();

Command.Parameters.Clear();returnreturnValue;

}publicDataSet ExecuteDataSet(CommandType commandType,stringcommandText)

{

Command=DBManagerFactory.GetCommand(ProviderType);

PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters);

IDbDataAdapter dataAdapter=DBManagerFactory.GetDataAdapter(ProviderType);

dataAdapter.SelectCommand=Command;

DataSet dataSet=newDataSet();

dataAdapter.Fill(dataSet);

Command.Parameters.Clear();returndataSet;

}#endregion#region私有方法privatevoidAttachParameters(IDbCommand command, IDbDataParameter[] commandParameters)

{foreach(IDbDataParameter idbParameterincommandParameters)

{if(idbParameter.Direction==ParameterDirection.InputOutput&&idbParameter.Value==null)

{

idbParameter.Value=DBNull.Value;

}

command.Parameters.Add(idbParameter);

}

}privatevoidPrepareCommand(IDbCommand command, IDbConnection connection, IDbTransaction transaction,

CommandType commandType,stringcommandText, IDbDataParameter[] commandParameters)

{

command.Connection=connection;

command.CommandText=commandText;

command.CommandType=commandType;if(transaction!=null)

{

command.Transaction=transaction;

}if(commandParameters!=null)

{

AttachParameters(command, commandParameters);

}

}#endregion}

}

5、再加一个DBHelper.cs，来调用DBManager类，外部来直接调用DBHelper类即可。

usingSystem;usingSystem.Data;usingSystem.Configuration;namespaceDbManager{publicclassDBHelper

{privatestaticreadonlyIDBManager dbManager=newDBManager(GetDataProvider(), GetConnectionString());//从配置文件中选择数据库类型//DataProvider枚举值privatestaticDataProvider GetDataProvider()

{stringproviderType=ConfigurationManager.AppSettings["DataProvider"];

DataProvider dataProvider;switch(providerType)

{case"Oracle":

dataProvider=DataProvider.Oracle;break;case"SqlServer":

dataProvider=DataProvider.SqlServer;break;case"OleDb":

dataProvider=DataProvider.OleDb;break;case"Odbc":

dataProvider=DataProvider.Odbc;break;case"MySql":

dataProvider=DataProvider.MySql;break;default:returnDataProvider.Odbc;

}returndataProvider;

}//从配置文件获取连接字符串//连接字符串privatestaticstringGetConnectionString()

{returnConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;

}//关闭数据库连接的方法///publicstaticvoidClose()

{

dbManager.Dispose();

}//创建参数//参数个数publicstaticvoidCreateParameters(intparamsCount)

{

dbManager.CreateParameters(paramsCount);

}//添加参数//参数索引///参数名///参数值publicstaticvoidAddParameters(intindex,stringparamName,objectobjValue)

{

dbManager.AddParameters(index, paramName, objValue);

}//执行增删改//安全的sql语句string.Format()///操作成功返回truepublicstaticboolExecuteNonQuery(stringsqlString)

{try{

dbManager.Open();returndbManager.ExecuteNonQuery(CommandType.Text, sqlString)>0?true:false;

}catch(Exception e)

{thrownewException(e.Message);

}finally{

dbManager.Dispose();

}

}//执行查询//安全的sql语句string.Format()///返回IDataReaderpublicstaticIDataReader ExecuteReader(stringsqlString)

{try{

dbManager.Open();returndbManager.ExecuteReader(CommandType.Text, sqlString);

}catch(Exception e)

{thrownewException(e.Message);

}

}

}

}

现在，将上述项目生成一个DbManager.dll类库，在具体的DAL层里面就可以直接调用了。

DBHelper类没有全部写完，只写了ExecuteNonQuery()和ExecuteReader()两个方法，对于有参和无参的增删改查操作暂时够用，返回DataSet的方法未写，Transaction相关的也未写。

6、app.config

7、程序中的调用

举个简单的例子，我们就创建一个控制台应用程序，然后添加DbManager.dll的引用

Program.cs文件的样子：

usingSystem;usingSystem.Data;usingDbManager;  //记得引入命名空间namespaceDBDemo

{classProgram

{staticvoidMain(string[] args)

{

SelectWithoutParams();

Console.WriteLine("------安全sql语句string.Format()的查询结果------");

SelectWithSafeSql(4);

Console.WriteLine("------参数化语句的查询结果-------");

SelectWithParams("总统套间");

}

privatestaticvoidSelectWithoutParams()

{conststringsql="select * from RoomType";

IDataReader reader=DBHelper.ExecuteReader(sql);while(reader.Read())

{

Console.WriteLine(reader["TypeName"].ToString());

}

DBHelper.Close();  //记得关闭reader

}privatestaticvoidSelectWithSafeSql(intTypeId)

{stringsql=string.Format("select * from RoomType where TypeId={0}", TypeId);

IDataReader reader=DBHelper.ExecuteReader(sql);while(reader.Read())

{

Console.WriteLine(reader["TypeName"].ToString());

}

DBHelper.Close();

}privatestaticvoidSelectWithParams(stringtypeName)

{

stringsql="select * from RoomType where TypeName=@TypeName";

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

DBHelper.CreateParameters(1);//参数个数，1个DBHelper.AddParameters(0,"@TypeName", typeName);

IDataReader reader=DBHelper.ExecuteReader(sql);while(reader.Read())

{

Console.WriteLine(reader["TypeName"].ToString());

}

DBHelper.Close();

}

}

}

OK！全部完成！在具体的DAL层中，调用DBHelper的相关方法即可，如果是查询方法，记得最后要写关闭代码。只要表结构一样，可以在app.config中随意切换数据库。

最后注意的是：

各个数据库的插入语句不一样，假设我们有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);

为了实现兼容，大家还是都按完整的来写，就不会有错了