概述
需求
有时我们的系统需要支持多种数据库，如即要支持MSSQL server又要同时支持Oracle database.而如果我们为些实现两套数据库操作的方式，就会不能很好的达到软件设计的目标：高内聚，低偶合。
设计
采取策略模式（Strategy），它定义了一系列的算法，并将每一个算法封装起来，而且使它们还可以相互替换。策略模式让算法的变化不会影响到使用算法的客户。
优点：
1、 简化了单元测试，因为每个算法都有自己的类，可以通过自己的接口单独测试。
2、 避免程序中使用多重条件转移语句，使系统更灵活，并易于扩展。 3、 遵守大部分GRASP原则和常用设计原则，高内聚、低偶合。缺点：
1、 因为每个具体策略类都会产生一个新类，所以会增加系统需要维护的类的数量。 2、 在基本的策略模式中，选择所用具体实现的职责由客户端对象承担，并转给策略模式的Context对象。（这本身没有解除客户端需要选择判断的压力，而策略模式与简单工厂模式结合后，选择具体实现的职责也可以由Context来承担，这就最大化的减轻了客户端的压力。）DBHelp设计目标，同时支持Sqlite、Oracle 、MySql 、MsSql,类UML图设计如下：
有了上面的设计图如后，我们先创建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 { /// /// Gets the connection string /// string ConnectionString { get; set; } /// /// Gets or sets the max connection count /// int MaxConnectionCount { get; set; } /// /// Gets or sets the sql source type /// SqlSourceType DataSqlSourceType { get; } /// /// Execute query by stored procedure /// /// stored procedure /// DataSet DataSet ExecuteQuery(string cmdText); /// /// Execute non query by stored procedure and parameter list /// /// stored procedure /// execute count int ExecuteNonQuery(string cmdText); /// /// Execute scalar by store procedure /// /// store procedure /// return value object ExecuteScalar(string cmdText); /// /// Get data base parameter by parameter name and parameter value /// /// parameter name /// parameter value /// sql parameter DbParameter GetDbParameter(string key, object value); /// /// Get data base parameter by parameter name and parameter value /// and parameter direction /// /// parameter name /// parameter value /// parameter direction /// data base parameter DbParameter GetDbParameter(string key, object value, ParameterDirection direction); /// /// Read entity list by store procedure /// /// entity /// store procedure /// entity list List ReadEntityList (string cmdText) where T : new(); /// /// Get dictionary result by store procedure and parameters and string list /// /// store procedure /// string list /// result list List > GetDictionaryList(string cmdText, List stringlist); /// /// Batch execute ExecuteNonQuery by cmdText list /// /// cmd text list /// execute true or not bool BatchExecuteNonQuery(List 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--; } /// /// Execute query by stored procedure and parameter list /// /// stored procedure and parameter list /// parameter list /// DataSet private DataSet ExecuteQuery(string cmdText, List 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; } } } /// /// Execute non query by stored procedure and parameter list /// /// stored procedure /// parameter list /// execute count private int ExecuteNonQuery(string cmdText, List 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 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(); } } } } /// /// Execute reader by store procedure and parameter list /// /// store procedure /// parameter list /// database connection /// data reader public DbDataReader ExecuteReader(string cmdText, List 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; } /// /// Execute reader by store procedure and parameter list /// /// store procedure /// parameter list /// data reader private List ReadEntityList (string cmdText, List 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 (dataReader); } } } /// /// Read entity list by reader /// /// entity /// data reader /// entity private List ReadEntityListByReader (DbDataReader reader) where T : new() { var listT = new List (); using (reader) { while (reader.Read()) { var fileNames = new List (); 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; } /// /// Get Dictionary list by string list /// /// Store procedure /// parameter list /// string list /// result list private List > GetDictionaryList(string cmdText, List parameters, List 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); } } } /// /// Read dictionary list by reader and string list /// /// Db data reader /// string /// result list private List > ReadStringListByReader(DbDataReader reader, List stringlist) { var listResult = new List >(); using (reader) { while (reader.Read()) { var dicResult = new Dictionary (); 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; } /// /// Execute scalar by store procedure and parameter list /// /// store procedure /// parameter list /// return value private object ExecuteScalar(string cmdText, List parameters) { using (var conn = GetConnection(ConnectionString)) { conn.Open(); using (var command = conn.CreateCommand()) { PrepareCommand(command, conn, cmdText, parameters); return command.ExecuteScalar(); } } } /// /// Prepare the execute command /// /// my sql command /// my sql connection /// stored procedure /// parameter list private void PrepareCommand(DbCommand cmd, DbConnection conn, string cmdText, List 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 /// /// Gets the connection string /// public string ConnectionString { get { if (_connectionString == null) _connectionString = ConfigurationManager.ConnectionStrings[""].ConnectionString; return _connectionString; } set { _connectionString = value; } } /// /// Execute query by stored procedure and parameter list /// /// stored procedure and parameter list /// DataSet public DataSet ExecuteQuery(string cmdText) { try { AddConnection(); return ExecuteQuery(cmdText, new List ()); } finally { RemoveConnection(); } } /// /// Execute non query by stored procedure and parameter list /// /// stored procedure /// execute count public int ExecuteNonQuery(string cmdText) { try { AddConnection(); return ExecuteNonQuery(cmdText, new List ()); } finally { RemoveConnection(); } } /// /// Execute scalar by store procedure and parameter list /// /// store procedure /// return value public object ExecuteScalar(string cmdText) { try { AddConnection(); return ExecuteScalar(cmdText, new List ()); } finally { RemoveConnection(); } } /// /// Get data base parameter by parameter name and parameter value /// /// parameter name /// parameter value /// my sql parameter public abstract DbParameter GetDbParameter(string key, object value); /// /// Get data base parameter by parameter name and parameter value /// and parameter direction /// /// parameter name /// parameter value /// parameter direction /// data base parameter public DbParameter GetDbParameter(string key, object value, ParameterDirection direction) { var parameter = GetDbParameter(key, value); parameter.Direction = direction; return parameter; } /// /// Get Dictionary list by string list /// /// Store procedure /// string list /// result list public List > GetDictionaryList(string cmdText, List stringlist) { return GetDictionaryList(cmdText, new List (), stringlist); } /// /// Execute reader by store procedure /// /// store procedure /// data reader public List ReadEntityList (string cmdText) where T : new() { try { AddConnection(); return ReadEntityList (cmdText, new List ()); } 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 =""; };
欢迎各位参与讨论，如果觉得对你有帮助，请点击 推荐下，万分谢谢.
作者：
出处：
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。