策略模式實現支援多種類資料庫的DBHelp

weixin_34119545發表於2019-01-08

概述

需求

有時我們的系統需要支援多種資料庫,如即要支援MSSQL server又要同時支援Oracle database.而如果我們為些實現兩套資料庫操作的方式,就會不能很好的達到軟體設計的目標:高內聚,低偶合。

設計

採取策略模式(Strategy),它定義了一系列的演算法,並將每一個演算法封裝起來,而且使它們還可以相互替換。策略模式讓演算法的變化不會影響到使用演算法的客戶。

1

優點:

1、 簡化了單元測試,因為每個演算法都有自己的類,可以通過自己的介面單獨測試。
2、 避免程式中使用多重條件轉移語句,使系統更靈活,並易於擴充套件。
3、 遵守大部分GRASP原則和常用設計原則,高內聚、低偶合。

缺點:
1、 因為每個具體策略類都會產生一個新類,所以會增加系統需要維護的類的數量。
2、 在基本的策略模式中,選擇所用具體實現的職責由客戶端物件承擔,並轉給策略模式的Context物件。(這本身沒有解除客戶端需要選擇判斷的壓力,而策略模式與簡單工廠模式結合後,選擇具體實現的職責也可以由Context來承擔,這就最大化的減輕了客戶端的壓力。)

DBHelp設計目標,同時支援Sqlite、Oracle 、MySql 、MsSql,類UML圖設計如下:

DBHelpClassDesignDiagram

有了上面的設計圖如後,我們先建立Enums:

/********************************************************************************
** Class Name:   Enums
** Author:      Spring Yang
** Create date: 2013-3-16
** Modify:      Spring Yang
** Modify Date: 2013-3-16
** Summary:     Enums  class
*********************************************************************************/

namespace BlogDBHelp
{
    using System;

    [Serializable]
    public enum SqlSourceType
    {
        Oracle,
        MSSql,
        MySql,
        SQLite
    }
}

 

再建立IDBHelp介面:

/********************************************************************************
** Class Name:   IDBHelp
** Author:      Spring Yang
** Create date: 2013-3-16
** Modify:      Spring Yang
** Modify Date: 2013-3-16
** Summary:     IDBHelp interface
*********************************************************************************/

namespace BlogDBHelp
{
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;

    public interface IDBHelp
    {
        /// <summary>
        /// Gets the connection string
        /// </summary>
        string ConnectionString { get; set; }

        /// <summary>
        /// Gets or sets the max connection count
        /// </summary>
        int MaxConnectionCount { get; set; }

        /// <summary>
        /// Gets or sets the sql source type
        /// </summary>
        SqlSourceType DataSqlSourceType { get; }

        /// <summary>
        /// Execute query by stored procedure 
        /// </summary>
        /// <param name="cmdText">stored procedure</param>
        /// <returns>DataSet</returns>
        DataSet ExecuteQuery(string cmdText);
 
        /// <summary>
        /// Execute non query by stored procedure and parameter list
        /// </summary>
        /// <param name="cmdText">stored procedure</param>
        /// <returns>execute count</returns>
        int ExecuteNonQuery(string cmdText);
 
        /// <summary>
        /// Execute scalar by store procedure
        /// </summary>
        /// <param name="cmdText">store procedure</param>
        /// <returns>return value</returns>
        object ExecuteScalar(string cmdText);

        /// <summary>
        /// Get data base parameter by parameter name and parameter value
        /// </summary>
        /// <param name="key">parameter name</param>
        /// <param name="value">parameter value</param>
        /// <returns>sql parameter</returns>
        DbParameter GetDbParameter(string key, object value);

        /// <summary>
        /// Get data base parameter by parameter name and parameter value
        /// and parameter direction 
        /// </summary>
        /// <param name="key">parameter name</param>
        /// <param name="value">parameter value</param>
        /// <param name="direction">parameter direction </param>
        /// <returns>data base parameter</returns>
        DbParameter GetDbParameter(string key, object value, ParameterDirection direction);

        /// <summary>
        /// Read entity list by  store procedure
        /// </summary>
        /// <typeparam name="T">entity</typeparam>
        /// <param name="cmdText">store procedure</param>
        /// <returns>entity list</returns>
        List<T> ReadEntityList<T>(string cmdText) where T : new();
 
        /// <summary>
        /// Get dictionary result by store procedure and parameters and string list
        /// </summary>
        /// <param name="cmdText">store procedure</param>
        /// <param name="stringlist">string list</param>
        /// <returns>result list</returns>
        List<Dictionary<string, object>> GetDictionaryList(string cmdText,
                                                           List<string> stringlist);

        /// <summary>
        /// Batch execute ExecuteNonQuery by cmdText list
        /// </summary>
        /// <param name="cmdList">cmd text list</param>
        /// <returns>execute true or not</returns>
        bool BatchExecuteNonQuery(List<string> cmdList);

    }
}

 

再建立AbstractDBHelp 抽象類:

/********************************************************************************
** Class Name:   AbstractDBHelp
** Author:      Spring Yang
** Create date: 2013-3-16
** Modify:      Spring Yang
** Modify Date: 2013-3-16
** Summary:     AbstractDBHelp interface
*********************************************************************************/

namespace BlogDBHelp
{
    using System;
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data;
    using System.Data.Common;
    using System.Reflection;
    using System.Threading;

    public abstract class AbstractDBHelp : IDBHelp
    {
        #region Private Property

        private static int _currentCount;

        private int _maxConnectionCount;

        private string _connectionString;

        #endregion

        #region Private Methods

        private void AddConnection()
        {
            if (_currentCount < MaxConnectionCount)
                _currentCount++;
            else
            {
                while (true)
                {
                    Thread.Sleep(5);
                    if (_currentCount < MaxConnectionCount)
                    {
                        _currentCount++;
                        break;
                    }
                }
            }
        }

        private void RemoveConnection()
        {
            _currentCount--;
        }


        /// <summary>
        /// Execute query by stored procedure and parameter list
        /// </summary>
        /// <param name="cmdText">stored procedure and parameter list</param>
        /// <param name="parameters">parameter list</param>
        /// <returns>DataSet</returns>
        private DataSet ExecuteQuery(string cmdText, List<DbParameter> parameters)
        {
            using (var conn = GetConnection(ConnectionString))
            {
                conn.Open();
                using (var command = conn.CreateCommand())
                {
                    var ds = new DataSet();
                    PrepareCommand(command, conn, cmdText, parameters);
                    var da = GetDataAdapter(command);
                    da.Fill(ds);
                    return ds;
                }
            }
        }


        /// <summary>
        /// Execute non query by stored procedure and parameter list
        /// </summary>
        /// <param name="cmdText">stored procedure</param>
        /// <param name="parameters">parameter list</param>
        /// <returns>execute count</returns>
        private int ExecuteNonQuery(string cmdText, List<DbParameter> parameters)
        {
            using (var conn = GetConnection(ConnectionString))
            {
                conn.Open();
                using (var command = conn.CreateCommand())
                {
                    PrepareCommand(command, conn, cmdText, parameters);
                    return command.ExecuteNonQuery();
                }
            }
        }

        public bool BatchExecuteNonQuery(List<string> cmdList)
        {
            using (var conn = GetConnection(ConnectionString))
            {
                conn.Open();
                using (var transaction = conn.BeginTransaction())
                {
                    foreach (var cmdText in cmdList)
                    {
                        if (string.IsNullOrEmpty(cmdText)) continue;
                        using (var command = conn.CreateCommand())
                        {
                            try
                            {
                                command.CommandText = cmdText;
                                command.Transaction = transaction;
                                command.ExecuteNonQuery();
                            }
                            finally
                            {
                                command.CommandText = null;
                                command.Dispose();
                            }
                        }
                    }
                    try
                    {
                        transaction.Commit();
                        return true;
                    }
                    catch
                    {
                        transaction.Rollback();
                        return false;
                    }
                    finally
                    {
                        transaction.Dispose();
                        conn.Dispose();
                        conn.Close();
                        cmdList.Clear();
                    }
                }
            }

        }

        /// <summary>
        /// Execute reader by store procedure and parameter list
        /// </summary>
        /// <param name="cmdText">store procedure</param>
        /// <param name="parameters">parameter list</param>
        /// <param name="conn">database connection </param>
        /// <returns>data reader</returns>
        public DbDataReader ExecuteReader(string cmdText, List<DbParameter> parameters, out DbConnection conn)
        {
            conn = GetConnection(ConnectionString);
            conn.Open();
            AddConnection();
            var command = conn.CreateCommand();
            PrepareCommand(command, conn, cmdText, parameters);
            var dataReader = command.ExecuteReader();
            RemoveConnection();
            return dataReader;
        }

        /// <summary>
        /// Execute reader by store procedure and parameter list
        /// </summary>
        /// <param name="cmdText">store procedure</param>
        /// <param name="parameters">parameter list</param>
        /// <returns>data reader</returns> 
        private List<T> ReadEntityList<T>(string cmdText, List<DbParameter> parameters) where T : new()
        {
            using (var conn = GetConnection(ConnectionString))
            {
                conn.Open();
                using (var command = conn.CreateCommand())
                {
                    PrepareCommand(command, conn, cmdText, parameters);
                    var dataReader = command.ExecuteReader();
                    return ReadEntityListByReader<T>(dataReader);
                }
            }
        }

        /// <summary>
        /// Read entity list by reader
        /// </summary>
        /// <typeparam name="T">entity</typeparam>
        /// <param name="reader">data reader</param>
        /// <returns>entity</returns>
        private List<T> ReadEntityListByReader<T>(DbDataReader reader) where T : new()
        {
            var listT = new List<T>();
            using (reader)
            {
                while (reader.Read())
                {
                    var fileNames = new List<string>();
                    for (int i = 0; i < reader.VisibleFieldCount; i++)
                    {
                        fileNames.Add(reader.GetName(i));
                    }
                    var inst = new T();
                    foreach (var pi in typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public))
                    {
                        if (!fileNames.Exists(fileName => string.Compare(fileName, pi.Name, StringComparison.OrdinalIgnoreCase) == 0))
                            continue;
                        object obj;
                        try
                        {
                            obj = reader[pi.Name];
                        }
                        catch (Exception)
                        {
                            continue;
                        }

                        if (obj == DBNull.Value || obj == null)
                            continue;
                        var si = pi.GetSetMethod();
                        if (si == null)
                            continue;
                        if (pi.PropertyType == typeof(bool?))
                            pi.SetValue(inst, Convert.ToBoolean(obj), null);
                        else if (pi.PropertyType == typeof(string))
                            pi.SetValue(inst, obj.ToString(), null);
                        else if (pi.PropertyType == typeof(Int32))
                            pi.SetValue(inst, Convert.ToInt32(obj), null);
                        else if (pi.PropertyType == typeof(Int64))
                            pi.SetValue(inst, Convert.ToInt64(obj), null);
                        else if (pi.PropertyType == typeof(decimal))
                            pi.SetValue(inst, Convert.ToDecimal(obj), null);
                        else
                            pi.SetValue(inst, obj, null);
                    }
                    listT.Add(inst);
                }
            }
            return listT;
        }

        /// <summary>
        /// Get Dictionary list by string list
        /// </summary>
        /// <param name="cmdText">Store procedure</param>
        /// <param name="parameters">parameter list</param>
        /// <param name="stringlist">string list</param>
        /// <returns>result list</returns>
        private List<Dictionary<string, object>> GetDictionaryList(string cmdText, List<DbParameter> parameters, List<string> stringlist)
        {
            using (var conn = GetConnection(ConnectionString))
            {
                AddConnection();
                using (var command = conn.CreateCommand())
                {
                    PrepareCommand(command, conn, cmdText, parameters);
                    var dataReader = command.ExecuteReader();
                    RemoveConnection();
                    return ReadStringListByReader(dataReader, stringlist);
                }
            }
        }




        /// <summary>
        /// Read dictionary list by reader and string list
        /// </summary>
        /// <param name="reader">Db data reader</param>
        /// <param name="stringlist">string</param>
        /// <returns>result list</returns>
        private List<Dictionary<string, object>> ReadStringListByReader(DbDataReader reader, List<string> stringlist)
        {
            var listResult = new List<Dictionary<string, object>>();
            using (reader)
            {
                while (reader.Read())
                {
                    var dicResult = new Dictionary<string, object>();
                    foreach (var key in stringlist)
                    {
                        if (!stringlist.Exists(fileName => string.Compare(fileName, key, StringComparison.OrdinalIgnoreCase) == 0))
                            continue;
                        object obj;
                        try
                        {
                            obj = reader[key];
                        }
                        catch (Exception)
                        {
                            continue;
                        }
                        if (obj == DBNull.Value || obj == null)
                            continue;
                        dicResult.Add(key, obj);
                    }
                    listResult.Add(dicResult);
                }
            }
            return listResult;
        }


        /// <summary>
        /// Execute scalar by store procedure and parameter list
        /// </summary>
        /// <param name="cmdText">store procedure</param>
        /// <param name="parameters">parameter list</param>
        /// <returns>return value</returns>
        private object ExecuteScalar(string cmdText, List<DbParameter> parameters)
        {
            using (var conn = GetConnection(ConnectionString))
            {
                conn.Open();
                using (var command = conn.CreateCommand())
                {
                    PrepareCommand(command, conn, cmdText, parameters);
                    return command.ExecuteScalar();
                }
            }
        }

        /// <summary>
        /// Prepare the execute command
        /// </summary>
        /// <param name="cmd">my sql command</param>
        /// <param name="conn">my sql connection</param>
        /// <param name="cmdText">stored procedure</param>
        /// <param name="parameters">parameter list</param>
        private void PrepareCommand(DbCommand cmd, DbConnection conn, string cmdText, List<DbParameter> parameters)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Parameters.Clear();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            cmd.CommandType = CommandType.Text;
            cmd.CommandTimeout = 30;
            if (parameters != null)
                foreach (var parameter in parameters)
                {
                    cmd.Parameters.Add(parameter);
                }
        }

        #endregion

        #region Public Property

        public int MaxConnectionCount
        {
            get
            {
                if (_maxConnectionCount <= 0)
                    _maxConnectionCount = 100;
                return _maxConnectionCount;
            }
            set { _maxConnectionCount = value; }
        }

        public abstract SqlSourceType DataSqlSourceType { get; }

        #endregion

        #region Protected Method


        protected abstract DbDataAdapter GetDataAdapter(DbCommand command);

        protected abstract DbConnection GetConnection(string connectionString);

        #endregion

        #region Public Methods

        /// <summary>
        /// Gets the connection string
        /// </summary>
        public string ConnectionString
        {
            get
            {
                if (_connectionString == null)
                    _connectionString = ConfigurationManager.ConnectionStrings[""].ConnectionString;
                return _connectionString;
            }
            set { _connectionString = value; }
        }

        /// <summary>
        /// Execute query by stored procedure and parameter list
        /// </summary>
        /// <param name="cmdText">stored procedure and parameter list</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteQuery(string cmdText)
        {
            try
            {
                AddConnection();
                return ExecuteQuery(cmdText, new List<DbParameter>());
            }
            finally
            {
                RemoveConnection();
            }

        }

 

 

        /// <summary>
        /// Execute non query by stored procedure and parameter list
        /// </summary>
        /// <param name="cmdText">stored procedure</param>
        /// <returns>execute count</returns>
        public int ExecuteNonQuery(string cmdText)
        {
            try
            {
                AddConnection();
                return ExecuteNonQuery(cmdText, new List<DbParameter>());
            }
            finally
            {
                RemoveConnection();
            }
        }

        /// <summary>
        /// Execute scalar by store procedure and parameter list
        /// </summary>
        /// <param name="cmdText">store procedure</param>
        /// <returns>return value</returns>
        public object ExecuteScalar(string cmdText)
        {
            try
            {
                AddConnection();
                return ExecuteScalar(cmdText, new List<DbParameter>());
            }
            finally
            {
                RemoveConnection();
            }
        }

 
        /// <summary>
        /// Get data base parameter by parameter name and parameter value
        /// </summary>
        /// <param name="key">parameter name</param>
        /// <param name="value">parameter value</param>
        /// <returns>my sql parameter</returns>
        public abstract DbParameter GetDbParameter(string key, object value);

        /// <summary>
        /// Get data base parameter by parameter name and parameter value
        /// and parameter direction 
        /// </summary>
        /// <param name="key">parameter name</param>
        /// <param name="value">parameter value</param>
        /// <param name="direction">parameter direction </param>
        /// <returns>data base parameter</returns>
        public DbParameter GetDbParameter(string key, object value, ParameterDirection direction)
        {
            var parameter = GetDbParameter(key, value);
            parameter.Direction = direction;
            return parameter;
        }

        /// <summary>
        /// Get Dictionary list by string list
        /// </summary>
        /// <param name="cmdText">Store procedure</param>
        /// <param name="stringlist">string list</param>
        /// <returns>result list</returns>
        public List<Dictionary<string, object>> GetDictionaryList(string cmdText, List<string> stringlist)
        {
            return GetDictionaryList(cmdText, new List<DbParameter>(), stringlist);
        }

        /// <summary>
        /// Execute reader by store procedure
        /// </summary>
        /// <param name="cmdText">store procedure</param>
        /// <returns>data reader</returns> 
        public List<T> ReadEntityList<T>(string cmdText) where T : new()
        {
            try
            {
                AddConnection();
                return ReadEntityList<T>(cmdText, new List<DbParameter>());
            }
            finally
            {
                RemoveConnection();
            }
        }
 
        #endregion
    }
}

 

再建立MSSqlHelp 類:

/********************************************************************************
** Class Name:   MySqlHelp
** Author:      Spring Yang
** Create date: 2013-3-16
** Modify:      Spring Yang
** Modify Date: 2013-3-16
** Summary:     MySqlHelp class
*********************************************************************************/

namespace BlogDBHelp
{
    using System.Data.Common;
    using System.Data.SqlClient;

    public class MSSqlHelp : AbstractDBHelp
    {
        #region Protected Method

        protected override DbDataAdapter GetDataAdapter(DbCommand command)
        {
            return new SqlDataAdapter(command as SqlCommand);
        }

        protected override DbConnection GetConnection(string connectionString)
        {
            return new SqlConnection(connectionString);
        }

        #endregion

        #region Public Mehtod

        public override SqlSourceType DataSqlSourceType
        {
            get { return SqlSourceType.MSSql; }
        }

        public override DbParameter GetDbParameter(string key, object value)
        {
            return new SqlParameter(key, value);
        }
 
        #endregion
    }
}

 

再建立MySqlHelp類

/********************************************************************************
** Class Name:   MySqlHelp
** Author:      Spring Yang
** Create date: 2013-3-16
** Modify:      Spring Yang
** Modify Date: 2013-3-16
** Summary:     MySqlHelp class
*********************************************************************************/

namespace BlogDBHelp
{
    using System.Data.Common;
    using MySql.Data.MySqlClient;

    public class MySqlHelp : AbstractDBHelp
    {
        #region Protected Method

        protected override DbDataAdapter GetDataAdapter(DbCommand command)
        {
            return new MySqlDataAdapter();
        }

        protected override DbConnection GetConnection(string connectionString)
        {
            return new MySqlConnection(connectionString);
        }

        #endregion

        #region Public Mehtod

        public override DbParameter GetDbParameter(string key, object value)
        {
            return new MySqlParameter(key, value);
        }
 
        public override SqlSourceType DataSqlSourceType
        {
            get { return SqlSourceType.MySql; }
        }

        #endregion

    }
}

 

再建立OracleHelp類:

/********************************************************************************
** Class Name:   OracleHelp
** Author:      Spring Yang
** Create date: 2013-3-16
** Modify:      Spring Yang
** Modify Date: 2013-3-16
** Summary:     OracleHelp class
*********************************************************************************/

namespace BlogDBHelp
{
    using System.Data.Common;
    using Oracle.DataAccess.Client;

    public class OracleHelp : AbstractDBHelp
    {
        #region Protected Method

        protected override DbDataAdapter GetDataAdapter(DbCommand command)
        {
            return new OracleDataAdapter(command as OracleCommand);
        }

        protected override DbConnection GetConnection(string connectionString)
        {
            return new OracleConnection(connectionString);
        }

        #endregion

        #region Public Mehtod

        public override DbParameter GetDbParameter(string key, object value)
        {
            return new OracleParameter(key, value);
        }

        public override SqlSourceType DataSqlSourceType
        {
            get { return SqlSourceType.Oracle; }
        }

        #endregion
    }
}

 

再建立SQLiteHelp類:

/********************************************************************************
** Class Name:   SQLiteHelp
** Author:      Spring Yang
** Create date: 2013-3-16
** Modify:      Spring Yang
** Modify Date: 2013-3-16
** Summary:     SQLiteHelp class
*********************************************************************************/

namespace BlogDBHelp
{
    using System.Data.Common;
    using System.Data.SQLite;

    public class SQLiteHelp : AbstractDBHelp
    {
        #region Protected Method

        protected override DbDataAdapter GetDataAdapter(DbCommand command)
        {
            return new SQLiteDataAdapter(command as SQLiteCommand);
        }

        protected override DbConnection GetConnection(string connectionString)
        {
            return new SQLiteConnection(connectionString);
        }

        #endregion

        #region Public Mehtod

        public override DbParameter GetDbParameter(string key, object value)
        {
            return new SQLiteParameter(key, value);
        }
 
        public override SqlSourceType DataSqlSourceType
        {
            get { return SqlSourceType.SQLite; }
        }

        #endregion
    }
}

 

仔細觀察上面程式碼,發現每增加一種資料庫的支援,我們只需實現幾個特有抽象方法就可以了,而呼叫只需像如下就可以了。

IDBHelp  _dbHelpInstance = new SQLiteHelp
                    {
                        ConnectionString ="";    };

 

歡迎各位參與討論,如果覺得對你有幫助,請點選image    推薦下,萬分謝謝.

作者:spring yang

出處:http://www.cnblogs.com/springyangwc/

本文版權歸作者和部落格園共有,歡迎轉載,但未經作者同意必須保留此段宣告,且在文章頁面明顯位置給出原文連線,否則保留追究法律責任的權利。

相關文章