本方案可实现仅修改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{public sealed classDBManagerFactory {privateDBManagerFactory() { }public staticIDbConnection 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:return null; }returniDbConnection; }public staticIDbCommand GetCommand(DataProvider providerType) {switch(providerType) {caseDataProvider.SqlServer:return newSqlCommand();caseDataProvider.OleDb:return newOleDbCommand();caseDataProvider.Odbc:return newOdbcCommand();caseDataProvider.Oracle:return newOracleCommand();caseDataProvider.MySql:return newMySqlCommand();default:return null; } }public staticIDbDataAdapter GetDataAdapter(DataProvider providerType) {switch(providerType) {caseDataProvider.SqlServer:return newSqlDataAdapter();caseDataProvider.OleDb:return newOleDbDataAdapter();caseDataProvider.Odbc:return newOdbcDataAdapter();caseDataProvider.Oracle:return newOracleDataAdapter();caseDataProvider.MySql:return newMySqlDataAdapter();default:return null; } }public staticIDbTransaction GetTransaction(DataProvider providerType) { IDbConnection iDbConnection=GetConnection(providerType); IDbTransaction iDbTransaction=iDbConnection.BeginTransaction();returniDbTransaction; }public staticIDbDataParameter[] GetParameters(DataProvider providerType,intparamsCount) { IDbDataParameter[] idbParams= newIDbDataParameter[paramsCount];switch(providerType) {caseDataProvider.SqlServer:for(inti= 0; i
3、创建一个接口:IDBManager.cs
usingSystem.Data;namespaceDbManager{public interfaceIDBManager { 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{public sealed classDBManager : IDBManager, IDisposable {#region字段privateDataProvider _providerType;privateIDbConnection _idbConnection;privateIDataReader _iDataReader;privateIDbCommand _idbCommand;privateIDbTransaction _idbTransaction;privateIDbDataParameter[] _idbParameters;private string_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; } }public stringConnectionString {get{return_connectionString; }set{ _connectionString=value; } }#endregion#region公有方法public voidOpen() { Connection=DBManagerFactory.GetConnection(ProviderType); Connection.ConnectionString=ConnectionString;if(Connection.State!=ConnectionState.Open) { Connection.Open(); } Command=DBManagerFactory.GetCommand(ProviderType); }public voidClose() {if(Connection.State!=ConnectionState.Closed) { Connection.Close(); } }public voidDispose() { GC.SuppressFinalize(this); Close(); Command= null; Transaction= null; Connection= null; }public voidCreateParameters(intparamsCount) { Parameters= newIDbDataParameter[paramsCount]; Parameters=DBManagerFactory.GetParameters(ProviderType, paramsCount); }public voidAddParameters(intindex,stringparamName,objectobjValue) {if(index
5、再加一个DBHelper.cs,来调用DBManager类,外部来直接调用DBHelper类即可。
usingSystem;usingSystem.Data;usingSystem.Configuration;namespace DbManager{public classDBHelper {private static readonlyIDBManager dbManager= newDBManager(GetDataProvider(), GetConnectionString());/// ///从配置文件中选择数据库类型/// /// DataProvider枚举值private staticDataProvider 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; }/// ///从配置文件获取连接字符串/// /// 连接字符串private static stringGetConnectionString() {returnConfigurationManager.ConnectionStrings["ConnString"].ConnectionString; }/// ///关闭数据库连接的方法/// public static voidClose() { dbManager.Dispose(); }/// ///创建参数/// /// 参数个数public static voidCreateParameters(intparamsCount) { dbManager.CreateParameters(paramsCount); }/// ///添加参数/// /// 参数索引/// 参数名/// 参数值public static voidAddParameters(intindex,stringparamName,objectobjValue) { dbManager.AddParameters(index, paramName, objValue); }/// ///执行增删改/// /// 安全的sql语句string.Format()/// 操作成功返回truepublic static boolExecuteNonQuery(stringsqlString) {try{ dbManager.Open();returndbManager.ExecuteNonQuery(CommandType.Text, sqlString)> 0 ? true:false; }catch(Exception e) {throw newException(e.Message); }finally{ dbManager.Dispose(); } }/// ///执行查询/// /// 安全的sql语句string.Format()/// 返回IDataReaderpublic staticIDataReader ExecuteReader(stringsqlString) {try{ dbManager.Open();returndbManager.ExecuteReader(CommandType.Text, sqlString); }catch(Exception e) {throw newException(e.Message); } } }}
现在,将上述项目生成一个DbManager.dll类库,在具体的DAL层里面就可以直接调用了。
DBHelper类没有全部写完,只写了ExecuteNonQuery()和ExecuteReader()两个方法,对于有参和无参的增删改查操作暂时够用,返回DataSet的方法未写,Transaction相关的也未写。
6、app.config
<?xml version="1.0"encoding="utf-8" ?>
7、程序中的调用
举个简单的例子,我们就创建一个控制台应用程序,然后添加DbManager.dll的引用
Program.cs文件的样子:
usingSystem;usingSystem.Data;usingDbManager; //记得引入命名空间namespaceDBDemo{classProgram {static voidMain(string[] args) { SelectWithoutParams(); Console.WriteLine("------安全sql语句string.Format()的查询结果------"); SelectWithSafeSql(4); Console.WriteLine("------参数化语句的查询结果-------"); SelectWithParams("总统套间");
}
private static voidSelectWithoutParams(){const stringsql= "select * from RoomType"; IDataReader reader=DBHelper.ExecuteReader(sql);while(reader.Read()) { Console.WriteLine(reader["TypeName"].ToString()); } DBHelper.Close(); //记得关闭reader }private static voidSelectWithSafeSql(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(); }private static voidSelectWithParams(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);
为了实现兼容,大家还是都按完整的来写,就不会有错了。
转自:http://www.cnblogs.com/top5/archive/2011/08/01/2123971.html