第 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#!