策略模式实现支持多种类数据库的DBHelp
策略模式实现支持多种类数据库的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/
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
作者： Leo_wl
出处： http://www.cnblogs.com/Leo_wl/
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
版权信息查看更多关于策略模式实现支持多种类数据库的DBHelp的详细内容...