繼承IDbConnection連接不同數(shù)據(jù)庫(kù)

weiledream 2013-11-04

展開(kāi)全文

本方案可實(shí)現(xiàn)僅修改app.config即可連接不同數(shù)據(jù)庫(kù),，但是設(shè)計(jì)數(shù)據(jù)庫(kù)時(shí)需要注意各種數(shù)據(jù)庫(kù)的數(shù)據(jù)類(lèi)型是不一樣的,。

各種不同數(shù)據(jù)庫(kù)的Connection,、Command,、DataAdapter、Transaction和Parameter都繼承自IDbConnection,、IDbCommand,、IDbDataAdapter,、IDbTransaction和IDbDataParameter,，用一個(gè)工廠來(lái)實(shí)現(xiàn)接口的實(shí)例即可實(shí)現(xiàn)連接不同數(shù)據(jù)庫(kù)。

首先,，需要新建一個(gè)類(lèi)庫(kù),，命名為DbManager，此類(lèi)庫(kù)需要5個(gè)文件,，

1,、創(chuàng)建一個(gè)枚舉類(lèi)型：DataProvider.cs

namespace DbManager
{ 
    public enum DataProvider
    {
        Oracle,
        SqlServer,
        OleDb,
        Odbc,
        MySql
    }
} 

2、創(chuàng)建一個(gè)工廠類(lèi),，用來(lái)產(chǎn)生以上不同數(shù)據(jù)庫(kù)的實(shí)例：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;   //請(qǐng)自行安裝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,、創(chuàng)建一個(gè)接口：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、創(chuàng)建一個(gè)類(lèi)來(lái)實(shí)現(xiàn)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 構(gòu)造方法

        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,、再加一個(gè)DBHelper.cs,，來(lái)調(diào)用DBManager類(lèi)，外部來(lái)直接調(diào)用DBHelper類(lèi)即可,。

using System;
using System.Data;
using System.Configuration;

namespace DbManager
{
    public class DBHelper
    {
        private static readonly IDBManager dbManager = new DBManager(GetDataProvider(), GetConnectionString());

        /// <summary>
        /// 從配置文件中選擇數(shù)據(jù)庫(kù)類(lèi)型
        /// </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>
        /// 關(guān)閉數(shù)據(jù)庫(kù)連接的方法
        /// </summary>
        public static void Close()
        {
            dbManager.Dispose();
        }

        /// <summary>
        /// 創(chuàng)建參數(shù)
        /// </summary>
        /// <param name="paramsCount">參數(shù)個(gè)數(shù)</param>
        public static void CreateParameters(int paramsCount)
        {
            dbManager.CreateParameters(paramsCount);
        }

        /// <summary>
        /// 添加參數(shù)
        /// </summary>
        /// <param name="index">參數(shù)索引</param>
        /// <param name="paramName">參數(shù)名</param>
        /// <param name="objValue">參數(shù)值</param>
        public static void AddParameters(int index, string paramName, object objValue)
        {
            dbManager.AddParameters(index, paramName, objValue);
        }

        /// <summary>
        /// 執(zhí)行增刪改
        /// </summary>
        /// <param name="sqlString">安全的sql語(yǔ)句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>
        /// 執(zhí)行查詢
        /// </summary>
        /// <param name="sqlString">安全的sql語(yǔ)句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);
            }
        }
    }
}

現(xiàn)在,，將上述項(xiàng)目生成一個(gè)DbManager.dll類(lèi)庫(kù)，在具體的DAL層里面就可以直接調(diào)用了,。

DBHelper類(lèi)沒(méi)有全部寫(xiě)完,，只寫(xiě)了ExecuteNonQuery()和ExecuteReader()兩個(gè)方法，對(duì)于有參和無(wú)參的增刪改查操作暫時(shí)夠用,，返回DataSet的方法未寫(xiě),，Transaction相關(guān)的也未寫(xiě)。

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"/>
        <!-- 通過(guò)改變ConnectionString的值來(lái)更換數(shù)據(jù)庫(kù)連接字符串
        <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"/>
        <!-- 通過(guò)改變value值來(lái)更換數(shù)據(jù)庫(kù)
        <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,、程序中的調(diào)用

舉個(gè)簡(jiǎn)單的例子,，我們就創(chuàng)建一個(gè)控制臺(tái)應(yīng)用程序，然后添加DbManager.dll的引用

Program.cs文件的樣子：

using System;
using System.Data;
using DbManager;  //記得引入命名空間

namespace DBDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            SelectWithoutParams();
            Console.WriteLine("------安全sql語(yǔ)句string.Format()的查詢結(jié)果------");
            SelectWithSafeSql(4);
            Console.WriteLine("------參數(shù)化語(yǔ)句的查詢結(jié)果-------");
            SelectWithParams("總統(tǒng)套間");
        }
 
        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();  //記得關(guān)閉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";
 
            //先創(chuàng)建參數(shù),，然后才能添加參數(shù) 
            DBHelper.CreateParameters(1);  //參數(shù)個(gè)數(shù)，1個(gè)
            DBHelper.AddParameters(0, "@TypeName", typeName);
            IDataReader reader = DBHelper.ExecuteReader(sql);
            while (reader.Read())
            {
                Console.WriteLine(reader["TypeName"].ToString());
            }
            DBHelper.Close();
        }
    }
}

OK,！全部完成,！在具體的DAL層中，調(diào)用DBHelper的相關(guān)方法即可,，如果是查詢方法,，記得最后要寫(xiě)關(guān)閉代碼。只要表結(jié)構(gòu)一樣,，可以在app.config中隨意切換數(shù)據(jù)庫(kù),。

最后注意的是：

各個(gè)數(shù)據(jù)庫(kù)的插入語(yǔ)句不一樣，假設(shè)我們有4個(gè)字段,，第一個(gè)字段fieldName1為自增字段,。

對(duì)于SQLServer，不需要寫(xiě)自增字段,，

語(yǔ)句是：INSERT INTO table VALUES(value2, value3, value4);

對(duì)于MySQL,，自增字段位置需要寫(xiě)null代替，

語(yǔ)句是：INSERT INTO table VALUES(NULL, value2, value3, value4);

而對(duì)于ACCESS數(shù)據(jù)庫(kù),，則必須寫(xiě)完整,，

語(yǔ)句是：INSERT INTO table(fieldName2, fieldName3,fieldName4) VALUES(value2, value3, value4);

為了實(shí)現(xiàn)兼容，大家還是都按完整的來(lái)寫(xiě),，就不會(huì)有錯(cuò)了,。

本站是提供個(gè)人知識(shí)管理的網(wǎng)絡(luò)存儲(chǔ)空間，所有內(nèi)容均由用戶發(fā)布,，不代表本站觀點(diǎn),。請(qǐng)注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購(gòu)買(mǎi)等信息,，謹(jǐn)防詐騙,。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)點(diǎn)擊一鍵舉報(bào),。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來(lái)自： weiledream > 《asp.net》

舉報(bào)/認(rèn)領(lǐng)