第 1 步:定义策略接口
首先创建一个接口 ISQLStrategy,声明 GetDataTable 方法,该方法将由不同的数据库策略实现。
using System.Data; namespace MyProject.Util { public interface ISQLStrategy { DataTable GetDataTable(string sql); } }
第 2 步:实施具体策略
接下来,为每种数据库类型(MySQL、PostgreSQL 和 SQL Server)实现 ISQLStrategy 接口。
MySQL 策略:
using MySql.Data.MySqlClient; using System; using System.Configuration; using System.Data; namespace MyProject.Util { public class MySQLQuery : ISQLStrategy { public DataTable GetDataTable(string sql) { try { using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.AppSettings["MySqlConnectionString"])) { DataTable dt = new DataTable(); conn.Open(); MySqlCommand command = new MySqlCommand(sql, conn); dt.Load(command.ExecuteReader()); conn.Close(); return dt; } } catch (Exception e) { Console.WriteLine(e.ToString()); return null; } } } }
PostgreSQL 策略:
using Npgsql; using System; using System.Configuration; using System.Data; namespace MyProject.Util { public class NpgSqlQuery : ISQLStrategy { public DataTable GetDataTable(string sql) { try { using (NpgsqlConnection conn = new NpgsqlConnection(ConfigurationManager.AppSettings["NpgSqlConnectionString"])) { DataTable dt = new DataTable(); conn.Open(); NpgsqlCommand command = new NpgsqlCommand(sql, conn); NpgsqlDataAdapter _dap = new NpgsqlDataAdapter(command); _dap.Fill(dt); conn.Close(); return dt; } } catch (Exception e) { Console.WriteLine(e.ToString()); return null; } } } }
SQL Server 策略:
using System; using System.Configuration; using System.Data; using System.Data.SqlClient; namespace MyProject.Util { public class TSqlQuery : ISQLStrategy { public DataTable GetDataTable(string sql) { try { using (SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["TSqlConnectionString"])) { DataTable dt = new DataTable(); conn.Open(); SqlCommand command = new SqlCommand(sql, conn); SqlDataAdapter da = new SqlDataAdapter(command); da.Fill(dt); conn.Close(); return dt; } } catch (Exception e) { Console.WriteLine(e.ToString()); return null; } } } }
第 3 步:创建上下文类
SQLStrategy 类将使用 ISQLStrategy 的实例来执行数据库操作。
using System.Data; namespace MyProject.Util { public class SQLStrategy { private readonly ISQLStrategy _sqlStrategy; public SQLStrategy(ISQLStrategy sqlStrategy) { _sqlStrategy = sqlStrategy; } public DataTable GetDataTable(string sql) { return _sqlStrategy.GetDataTable(sql); } } }
第 4 步:实施客户端代码
最后,编写客户端代码来测试策略模式的实现。
using System; using System.Data; namespace MyProject.Util { public class Client { public static void Main() { SQLStrategy sqlHelper = new(new TSqlQuery()); DataTable result = sqlHelper.GetDataTable("SELECT TOP (10) * FROM [Product]"); foreach (DataRow row in result.Rows) { foreach (DataColumn column in result.Columns) { Console.Write($"{column.ColumnName}: {row[column]} \t"); } Console.WriteLine(); } } } }
总结
完整编码
using System; using System.Configuration; using System.Data; using System.Data.SqlClient; using Npgsql; using MySql.Data.MySqlClient; namespace MyProject.Util { //Strategy (Interface) public interface ISQLStrategy { DataTable GetDataTable(string sql); //You could add more methods for "Create", "Update" as well } //Concrete Strategies public class MySQLQuery : ISQLStrategy { public DataTable GetDataTable(string sql) { try { using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.AppSettings["MySqlConnectionString"])) { DataTable dt = new DataTable(); conn.Open(); MySqlCommand command = new MySqlCommand(sql, conn); dt.Load(command.ExecuteReader()); conn.Close(); return dt; } } catch (Exception e) { Console.WriteLine(e.ToString()); return null; } } } public class NpgSqlQuery : ISQLStrategy { public DataTable GetDataTable(string sql) { try { using (NpgsqlConnection conn = new NpgsqlConnection(ConfigurationManager.AppSettings["NpgSqlConnectionString"])) { DataTable dt = new DataTable(); conn.Open(); NpgsqlCommand command = new NpgsqlCommand(sql, conn); NpgsqlDataAdapter _dap = new NpgsqlDataAdapter(command); _dap.Fill(dt); conn.Close(); return dt; } } catch (Exception e) { Console.WriteLine(e.ToString()); return null; } } } public class TSqlQuery : ISQLStrategy { public DataTable GetDataTable(string sql) { try { using (SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["TSqlConnectionString"])) { DataTable dt = new DataTable(); conn.Open(); SqlCommand command = new SqlCommand(sql, conn); SqlDataAdapter da = new SqlDataAdapter(command); da.Fill(dt); conn.Close(); return dt; } } catch (Exception e) { Console.WriteLine(e.ToString()); return null; } } } //Context public class SQLStrategy { public ISQLStrategy _sqlStrategy; public SQLStrategy(ISQLStrategy sqlStrategy) { _sqlStrategy = sqlStrategy; } public DataTable GetDataTable(string sql) { return _sqlStrategy.GetDataTable(sql); } } // Testing the Strategy Design Pattern // Client Code public class Client { public static void Main() { SQLStrategy sqlHelper = new(new TSqlQuery()); DataTable result = sqlHelper.GetDataTable("SELECT TOP (10) * FROM [Product]"); foreach (DataRow row in result.Rows) { foreach (DataColumn column in result.Columns) { Console.Write($"{column.ColumnName}: {row[column]} \t"); } Console.WriteLine(); } } } }
热爱 C#!
以上是使用策略设计模式分步指南在 C# 中实现数据库助手的详细内容。更多信息请关注PHP中文网其他相关文章!