策略设计模式(Strategy Pattern)是一种行为设计模式,它允许在运行时选择算法或行为。在数据库操作中,我们可以使用策略模式来支持不同类型的数据库操作(如SQL Server、MySQL、Oracle等),而客户端代码无需关心具体实现。
首先,我们定义一个表示数据库操作的通用接口:
public interface IDatabaseStrategy
{
void Connect(string connectionString);
void Disconnect();
DataTable ExecuteQuery(string query);
int ExecuteNonQuery(string command);
// 可以根据需要添加更多数据库操作方法
}
为每种数据库类型创建具体策略类:
public class SqlServerDatabaseStrategy : IDatabaseStrategy
{
private SqlConnection _connection;
public void Connect(string connectionString)
{
_connection = new SqlConnection(connectionString);
_connection.Open();
}
public void Disconnect()
{
if (_connection != null && _connection.State != ConnectionState.Closed)
{
_connection.Close();
}
}
public DataTable ExecuteQuery(string query)
{
var dataTable = new DataTable();
using (var command = new SqlCommand(query, _connection))
{
using (var adapter = new SqlDataAdapter(command))
{
adapter.Fill(dataTable);
}
}
return dataTable;
}
public int ExecuteNonQuery(string commandText)
{
using (var command = new SqlCommand(commandText, _connection))
{
return command.ExecuteNonQuery();
}
}
}
public class MySqlDatabaseStrategy : IDatabaseStrategy
{
private MySqlConnection _connection;
public void Connect(string connectionString)
{
_connection = new MySqlConnection(connectionString);
_connection.Open();
}
public void Disconnect()
{
if (_connection != null && _connection.State != ConnectionState.Closed)
{
_connection.Close();
}
}
public DataTable ExecuteQuery(string query)
{
var dataTable = new DataTable();
using (var command = new MySqlCommand(query, _connection))
{
using (var adapter = new MySqlDataAdapter(command))
{
adapter.Fill(dataTable);
}
}
return dataTable;
}
public int ExecuteNonQuery(string commandText)
{
using (var command = new MySqlCommand(commandText, _connection))
{
return command.ExecuteNonQuery();
}
}
}
上下文类维护对策略对象的引用,并可选择在运行时更改策略:
public class DatabaseContext
{
private IDatabaseStrategy _strategy;
public DatabaseContext(IDatabaseStrategy strategy)
{
_strategy = strategy;
}
public void SetStrategy(IDatabaseStrategy strategy)
{
_strategy = strategy;
}
public void Connect(string connectionString)
{
_strategy.Connect(connectionString);
}
public void Disconnect()
{
_strategy.Disconnect();
}
public DataTable ExecuteQuery(string query)
{
return _strategy.ExecuteQuery(query);
}
public int ExecuteNonQuery(string command)
{
return _strategy.ExecuteNonQuery(command);
}
}
现在可以这样使用数据库助手:
class Program
{
static void Main(string[] args)
{
// 配置连接字符串
string sqlServerConnStr = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
string mySqlConnStr = "Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;";
// 使用SQL Server策略
var context = new DatabaseContext(new SqlServerDatabaseStrategy());
context.Connect(sqlServerConnStr);
// 执行查询
var result = context.ExecuteQuery("SELECT * FROM Customers");
Console.WriteLine($"Retrieved {result.Rows.Count} rows from SQL Server");
context.Disconnect();
// 切换到MySQL策略
context.SetStrategy(new MySqlDatabaseStrategy());
context.Connect(mySqlConnStr);
// 执行非查询命令
int affectedRows = context.ExecuteNonQuery("UPDATE Products SET Price = Price * 1.1");
Console.WriteLine($"Updated {affectedRows} rows in MySQL");
context.Disconnect();
}
}
添加连接池支持:可以在策略实现中添加连接池管理
参数化查询:扩展接口以支持参数化查询
public interface IDatabaseStrategy
{
// 原有方法...
DataTable ExecuteQuery(string query, IDictionary<string, object> parameters);
int ExecuteNonQuery(string command, IDictionary<string, object> parameters);
}
public interface IDatabaseStrategy
{
// 原有方法...
Task<DataTable> ExecuteQueryAsync(string query);
Task<int> ExecuteNonQueryAsync(string command);
}
public interface IDatabaseStrategy
{
// 原有方法...
void BeginTransaction();
void CommitTransaction();
void RollbackTransaction();
}
public static class DatabaseStrategyFactory
{
public static IDatabaseStrategy Create(DatabaseType type)
{
switch (type)
{
case DatabaseType.SqlServer:
return new SqlServerDatabaseStrategy();
case DatabaseType.MySql:
return new MySqlDatabaseStrategy();
case DatabaseType.Oracle:
return new OracleDatabaseStrategy();
default:
throw new ArgumentException("Unsupported database type");
}
}
}
public enum DatabaseType
{
SqlServer,
MySql,
Oracle
}
通过使用策略设计模式实现数据库助手,我们获得了以下优势:
这种设计特别适合需要支持多种数据库类型或在未来可能扩展数据库支持的应用程序。