第 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]} "); } console.writeline(); } } } }
登录后复制
总结
- 定义策略接口：使用 getdatatable 方法创建接口 isqlstrategy。
- 实施具体策略：实现 mysql、postgresql 和 sql server 的接口。
- 创建context类：使用sqlstrategy类与策略交互。
- 编写客户端代码：通过查询数据库来测试实现。
- 这种方法可以让您轻松地在不同的数据库实现之间切换！
完整编码
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]} "); } Console.WriteLine(); } } } }
登录后复制
热爱 c#！
