学步园

本方案可实现仅修改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

using System.Data;
 using System.Data.Odbc;
 using System.Data.SqlClient;
 using System.Data.OleDb;
 using System.Data.OracleClient; //需要添加引用
 using MySql.Data.MySqlClient;   //请自行安装MySQLConnector/Net后添加引用
 
namespace DbManager
 {
     public sealed class DBManagerFactory
     {
         private DBManagerFactory()
         {
         }
 
        public static IDbConnection GetConnection(DataProvider providerType)
         {
             IDbConnection iDbConnection;
             switch (providerType)
             {
                 case DataProvider.SqlServer:
                     iDbConnection = new SqlConnection();
                     break;
                 case DataProvider.OleDb:
                     iDbConnection = new OleDbConnection();
                     break;
                 case DataProvider.Odbc:
                     iDbConnection = new OdbcConnection();
                     break;
                 case DataProvider.Oracle:
                     iDbConnection = new OracleConnection();
                     break;
                 case DataProvider.MySql:
                     iDbConnection = new MySqlConnection();
                     break;
                 default:
                     return null;
             }
             return iDbConnection;
         }
 
        public static IDbCommand GetCommand(DataProvider providerType)
         {
             switch (providerType)
             {
                 case DataProvider.SqlServer:
                     return new SqlCommand();
                 case DataProvider.OleDb:
                     return new OleDbCommand();
                 case DataProvider.Odbc:
                     return new OdbcCommand();
                 case DataProvider.Oracle:
                     return new OracleCommand();
                 case DataProvider.MySql:
                     return new MySqlCommand();
                 default:
                     return null;
             }
         }
 
        public static IDbDataAdapter GetDataAdapter(DataProvider providerType)
         {
             switch (providerType)
             {
                 case DataProvider.SqlServer:
                     return new SqlDataAdapter();
                 case DataProvider.OleDb:
                     return new OleDbDataAdapter();
                 case DataProvider.Odbc:
                     return new OdbcDataAdapter();
                 case DataProvider.Oracle:
                     return new OracleDataAdapter();
                 case DataProvider.MySql:
                     return new MySqlDataAdapter();
                 default:
                     return null;
             }
         }
 
        public static IDbTransaction GetTransaction(DataProvider providerType)
         {
             IDbConnection iDbConnection = GetConnection(providerType);
             IDbTransaction iDbTransaction = iDbConnection.BeginTransaction();
             return iDbTransaction;
         }
 
        public static IDbDataParameter[] GetParameters(DataProvider providerType, int paramsCount)
         {
             IDbDataParameter[] idbParams = new IDbDataParameter[paramsCount];
             switch (providerType)
             {
                 case DataProvider.SqlServer:
                     for (int i = 0; i < paramsCount; i++)
                     {
                         idbParams[i] = new SqlParameter();
                     }
                     break;
                 case DataProvider.OleDb:
                     for (int i = 0; i < paramsCount; i++)
                     {
                         idbParams[i] = new OleDbParameter();
                     }
                     break;
                 case DataProvider.Odbc:
                     for (int i = 0; i < paramsCount; i++)
                     {
                         idbParams[i] = new OdbcParameter();
                     }
                     break;
                 case DataProvider.Oracle:
                     for (int i = 0; i < paramsCount; i++)
                     {
                         idbParams[i] = new OracleParameter();
                     }
                     break;
                 case DataProvider.MySql:
                     for (int i = 0; i < paramsCount; i++)
                     {
                         idbParams[i] = new MySqlParameter();
                     }
                     break;
                 default:
                     idbParams = null;
                     break;
             }
             return idbParams;
         }
     }
 }

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

using System.Data;
 
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接口：DBManager.cs

using System;
 using System.Data;
 
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 = DBManagerFactory.GetConnection(ProviderType);
             Connection.ConnectionString = ConnectionString;
             if (Connection.State != ConnectionState.Open)
             {
                 Connection.Open();
             }
             Command = DBManagerFactory.GetCommand(ProviderType);
         }
 
        public void Close()
         {
             if (Connection.State != ConnectionState.Closed)
             {
                 Connection.Close();
             }
         }
 
        public void Dispose()
         {
             GC.SuppressFinalize(this);
             Close();
             Command = null;
             Transaction = null;
             Connection = null;
         }
 
        public void CreateParameters(int paramsCount)
         {
             Parameters = new IDbDataParameter[paramsCount];
             Parameters = DBManagerFactory.GetParameters(ProviderType, paramsCount);
         }
 
        public void AddParameters(int index, string paramName, object objValue)
         {
             if (index < Parameters.Length)
             {
                 Parameters[index].ParameterName = paramName;
                 Parameters[index].Value = objValue;
             }
         }
 
        public void BeginTransaction()
         {
             if (Transaction == null)
             {
                 Transaction = DBManagerFactory.GetTransaction(ProviderType);
             }
             Command.Transaction = Transaction;
         }
 
        public void CommitTransaction()
         {
             if (Transaction != null)
             {
                 Transaction.Commit();
             }
             Transaction = null;
         }
 
        public void CloseReader()
         {
             if (DataReader != null)
             {
                 DataReader.Close();
             }
         }
 
        public IDataReader ExecuteReader(CommandType commandType, string commandText)
         {
             Command = DBManagerFactory.GetCommand(ProviderType);
             Command.Connection = Connection;
             PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters);
             DataReader = Command.ExecuteReader();
             Command.Parameters.Clear();
             return DataReader;
         }
 
        public int ExecuteNonQuery(CommandType commandType, string commandText)
         {
             Command = DBManagerFactory.GetCommand(ProviderType);
             PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters);
             int returnValue = Command.ExecuteNonQuery();
             Command.Parameters.Clear();
             return returnValue;
         }
 
        public object ExecuteScalar(CommandType commandType, string commandText)
         {
             Command = DBManagerFactory.GetCommand(ProviderType);
             PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters);
             object returnValue = Command.ExecuteScalar();
             Command.Parameters.Clear();
             return returnValue;
         }
 
        public DataSet ExecuteDataSet(CommandType commandType, string commandText)
         {
             Command = DBManagerFactory.GetCommand(ProviderType);
             PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters);
             IDbDataAdapter dataAdapter = DBManagerFactory.GetDataAdapter(ProviderType);
             dataAdapter.SelectCommand = Command;
             DataSet dataSet = new DataSet();
             dataAdapter.Fill(dataSet);
             Command.Parameters.Clear();
             return dataSet;
         }
 
        #endregion
 
        #region 私有方法
 
        private void AttachParameters(IDbCommand command, IDbDataParameter[] commandParameters)
         {
             foreach (IDbDataParameter idbParameter in commandParameters)
             {
                 if (idbParameter.Direction == ParameterDirection.InputOutput && idbParameter.Value == null)
                 {
                     idbParameter.Value = DBNull.Value;
                 }
                 command.Parameters.Add(idbParameter);
             }
         }
 
        private void PrepareCommand(IDbCommand command, IDbConnection connection, IDbTransaction transaction,
                                     CommandType commandType, string commandText, 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类即可。

using System;
 using System.Data;
 using System.Configuration;
 
namespace DbManager
 {
     public class DBHelper
     {
         private static readonly IDBManager dbManager = new DBManager(GetDataProvider(), GetConnectionString());
 
        /// <summary>
         /// 从配置文件中选择数据库类型
         /// </summary>
         /// <returns>DataProvider枚举值</returns>
         private static DataProvider GetDataProvider()
         {
             string providerType = 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:
                     return DataProvider.Odbc;
             }
             return dataProvider;
         }
 
        /// <summary>
         /// 从配置文件获取连接字符串
         /// </summary>
         /// <returns>连接字符串</returns>
         private static string GetConnectionString()
         {
             return ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
         }
 
        /// <summary>
         /// 关闭数据库连接的方法
         /// </summary>
         public static void Close()
         {
             dbManager.Dispose();
         }
 
        /// <summary>
         /// 创建参数
         /// </summary>
         /// <param name="paramsCount">参数个数</param>
         public static void CreateParameters(int paramsCount)
         {
             dbManager.CreateParameters(paramsCount);
         }
 
        /// <summary>
         /// 添加参数
         /// </summary>
         /// <param name="index">参数索引</param>
         /// <param name="paramName">参数名</param>
         /// <param name="objValue">参数值</param>
         public static void AddParameters(int index, string paramName, object objValue)
         {
             dbManager.AddParameters(index, paramName, objValue);
         }
 
        /// <summary>
         /// 执行增删改
         /// </summary>
         /// <param name="sqlString">安全的sql语句string.Format()</param>
         /// <returns>操作成功返回true</returns>
         public static bool ExecuteNonQuery(string sqlString)
         {
             try
             {
                 dbManager.Open();
                 return dbManager.ExecuteNonQuery(CommandType.Text, sqlString) > 0 ? true : false;
             }
             catch (Exception e)
             {
                 throw new Exception(e.Message);
             }
             finally
             {
                 dbManager.Dispose();
             }
         }
 
        /// <summary>
         /// 执行查询
         /// </summary>
         /// <param name="sqlString">安全的sql语句string.Format()</param>
         /// <returns>返回IDataReader</returns>
         public static IDataReader ExecuteReader(string sqlString)
         {
             try
             {
                 dbManager.Open();
                 return dbManager.ExecuteReader(CommandType.Text, sqlString);
             }
             catch (Exception e)
             {
                 throw new Exception(e.Message);
             }
         }
     }
 }
 

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

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

6、app.config

<?xml version="1.0" encoding="utf-8" ?>
 <configuration>
     <connectionStrings>
         <add name="ConnString" connectionString="server=localhost;database=yourDbName;Persist Security Info=False;uid=root;pwd=mysqladmin"/>
         <!-- 通过改变ConnectionString的值来更换数据库连接字符串
         <add name="ConnString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=DBDemo.mdb;Jet OLEDB:Database Password=1234"/>
         <add name="ConnString" connectionString="server=localhost;database=yourDbName;Persist Security Info=False;Integrated Security=SSPI"/>
         <add name="ConnString" connectionString="server=localhost;database=yourDbName;Persist Security Info=False;uid=sa;pwd=1234"/>
         <add name="ConnString" connectionString="server=localhost;database=yourDbName;Persist Security Info=False;uid=root;pwd=mysqladmin"/>
         -->
     </connectionStrings>
     <appSettings>
         <add key="DataProvider" value="MySql"/>
         <!-- 通过改变value值来更换数据库
         <add key="DataProvider" value="Oracle"/>
         <add key="DataProvider" value="SqlServer"/>
         <add key="DataProvider" value="OleDb"/>
         <add key="DataProvider" value="Odbc"/> 
         <add key="DataProvider" value="MySql"/>
         -->
     </appSettings>
 </configuration>

7、程序中的调用

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

Program.cs文件的样子：

using System;
 using System.Data;
 using DbManager;  //记得引入命名空间
 
namespace DBDemo
 {
     class Program
     {
         static void Main(string[] args)
         {
             SelectWithoutParams();
             Console.WriteLine("------安全sql语句string.Format()的查询结果------");
             SelectWithSafeSql(4);
             Console.WriteLine("------参数化语句的查询结果-------");
             SelectWithParams("总统套间");
 
        }
 
 
 
        private static void SelectWithoutParams()
 
        {
             const string sql = "select * from RoomType";
             IDataReader reader = DBHelper.ExecuteReader(sql);
             while (reader.Read())
             {
                 Console.WriteLine(reader["TypeName"].ToString());
             }
             DBHelper.Close();  //记得关闭reader
         }
 
        private static void SelectWithSafeSql(int TypeId)
         {
             string sql = 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 void SelectWithParams(string typeName)
         {
 
            string sql = "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);

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