This exercise allows you to get data from different databases (MySQL, PostgreSQL and SQL Server...) via a database helper.
Step 1: Define the Strategy Interface
First, create an interface ISQLStrategy that declares the method GetDataTable which will be implemented by different database strategies.
using System.Data;
namespace MyProject.Util
{
public interface ISQLStrategy
{
DataTable GetDataTable(string sql);
}
}
Step 2: Implement Concrete Strategies
Next, implement the ISQLStrategy interface for each database type (MySQL, PostgreSQL and SQL Server).
MySQL Strategy:
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 Strategy:
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 Strategy:
using System;
using System.Configuration;
using System.Data;
using Microsoft.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;
}
}
}
}
Step 3: Create the Context Class
The SQLStrategy class will use an instance of ISQLStrategy to perform database operations.
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);
}
}
}
Step 4: Implement the Client Code
Finally, write the client code to test the strategy pattern implementation.
using System;
using System.Data;
namespace MyProject.Util
{
public class Client
{
public static void Main()
{
SQLStrategy sqlHelper = new SQLStrategy(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();
}
}
}
}
Summary
- Define the Strategy Interface: Create an interface ISQLStrategy with a method GetDataTable.
- Implement Concrete Strategies: Implement the interface for MySQL, PostgreSQL, and SQL Server.
- Create the Context Class: Use the SQLStrategy class to interact with the strategies.
- Write Client Code: Test the implementation by querying a database.
- This approach allows you to switch between different database implementations easily!
Full Coding
using System;
using System.Configuration;
using System.Data;
using Microsoft.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();
}
}
}
}
Love C#!
Top comments (0)