策略模式实现支持多种类数据库的DBHelp

以你之姓@ 2022-03-29 07:23 159阅读 0赞

概述

需求

有时我们的系统需要支持多种数据库，如即要支持MSSQL server又要同时支持Oracle database.而如果我们为些实现两套数据库操作的方式，就会不能很好的达到软件设计的目标：高内聚，低偶合。

设计

采取策略模式（Strategy），它定义了一系列的算法，并将每一个算法封装起来，而且使它们还可以相互替换。策略模式让算法的变化不会影响到使用算法的客户。

[![1][]][1 1]

优点：

1、 简化了单元测试，因为每个算法都有自己的类，可以通过自己的接口单独测试。  
2、 避免程序中使用多重条件转移语句，使系统更灵活，并易于扩展。  
3、 遵守大部分GRASP原则和常用设计原则，高内聚、低偶合。

缺点：  
1、 因为每个具体策略类都会产生一个新类，所以会增加系统需要维护的类的数量。  
2、 在基本的策略模式中，选择所用具体实现的职责由客户端对象承担，并转给策略模式的Context对象。（这本身没有解除客户端需要选择判断的压力，而策略模式与简单工厂模式结合后，选择具体实现的职责也可以由Context来承担，这就最大化的减轻了客户端的压力。）

DBHelp设计目标，同时支持Sqlite、Oracle 、MySql 、MsSql,类UML图设计如下：

[![DBHelpClassDesignDiagram][]][DBHelpClassDesignDiagram 1]

有了上面的设计图如后，我们先创建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][]**][image 1]**    推荐下，万分谢谢.**

作者：[spring yang][]

出处：[http://www.cnblogs.com/springyangwc/][http_www.cnblogs.com_springyangwc]

本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。

[1]: /images/20220329/5adec3a627b34aa18597425497f30709.png
[1 1]: https://images0.cnblogs.com/blog/243452/201303/17140310-e1d48eb2054a497dbfab7658473c7422.gif
[DBHelpClassDesignDiagram]: /images/20220329/a9da676f27ba4748b2aba8c9e601e772.png
[DBHelpClassDesignDiagram 1]: https://images0.cnblogs.com/blog/243452/201303/17140314-0400776d235c466f873caf49e08f7dac.png
[image]: /images/20220329/8ffb93873f004e23ae30285f9e0ad893.png
[image 1]: http://images.cnblogs.com/cnblogs_com/springyangwc/201110/201110111252004684.png
[spring yang]: http://home.cnblogs.com/u/springyangwc/
[http_www.cnblogs.com_springyangwc]: http://www.cnblogs.com/springyangwc/