概述
需求
有時我們的系統需要支援多種資料庫，如即要支援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/
本文版權歸作者和部落格園共有，歡迎轉載，但未經作者同意必須保留此段宣告，且在文章頁面明顯位置給出原文連線，否則保留追究法律責任的權利。