策略模式实现支持多种类数据库的DBHelp
概述
需求
有时我们的系统需要支持多种数据库，如即要支持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
{
/// <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 =""; };
欢迎各位参与讨论，如果觉得对你有帮助，请点击
推荐下，万分谢谢.
作者：spring yang
出处：http://www.cnblogs.com/springyangwc/
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
