This content originally appeared on DEV Community and was authored by David Au Yeung
Introduction
In this exercise, we build upon the previous implementation by adding the ability to generate and execute SQL commands (INSERT, UPDATE, DELETE, etc.) across various databases, including MySQL, PostgreSQL, and SQL Server. To achieve this, we utilize the DataTable object, which is widely adopted in many .NET legacy systems for data manipulation. This approach ensures compatibility and flexibility when working with existing systems. For demonstration purposes, we’ll use the Products table as our example.
Table Schema
The Products table schema:
CREATE TABLE Products (
ProductID BIGINT IDENTITY(1,1) NOT NULL,
ProductName NVARCHAR(1000) NOT NULL,
ProductCode NVARCHAR(1000) NOT NULL,
AvailableQuantity INT NOT NULL,
IsDeleted BIT NOT NULL DEFAULT 0,
CreateBy NVARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
CreateDate DATETIME NOT NULL DEFAULT GETDATE(),
ModifyBy NVARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
ModifyDate DATETIME NOT NULL DEFAULT GETDATE(),
CHECK (AvailableQuantity >= 0),
PRIMARY KEY (ProductID)
);
Step 1: Extend the Strategy Interface
We extend the ISQLStrategy interface to include two new methods:
-
GenerateInsertSQL: Generates an INSERT SQL statement based on a DataTable. -
ExecuteSQL: Executes any SQL command (INSERT, UPDATE, DELETE, etc.).
using System.Data;
namespace MyProject.Util
{
public interface ISQLStrategy
{
DataTable GetDataTable(string sql); // Retrieve data
string GenerateInsertSQL(DataTable dataTable); // Generate INSERT SQL for a DataTable
void ExecuteSQL(string sql); // Execute any SQL command
}
}
Step 2: Implement Concrete Strategies
MySQL Strategy:
using MySql.Data.MySqlClient;
using System;
using System.Configuration;
using System.Data;
using System.Text;
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;
}
}
public string GenerateInsertSQL(DataTable dataTable)
{
if (dataTable == null || dataTable.Rows.Count == 0)
return string.Empty;
StringBuilder sqlBuilder = new StringBuilder();
string tableName = dataTable.TableName;
foreach (DataRow row in dataTable.Rows)
{
StringBuilder columnNames = new StringBuilder();
StringBuilder values = new StringBuilder();
for (int i = 0; i < dataTable.Columns.Count; i++)
{
var column = dataTable.Columns[i];
if (i > 0)
{
columnNames.Append(", ");
values.Append(", ");
}
columnNames.Append($"`{column.ColumnName}`");
values.Append(row[column] is DBNull ? "NULL" : $"'{row[column].ToString().Replace("'", "''")}'");
}
sqlBuilder.AppendLine($"INSERT INTO `{tableName}` ({columnNames}) VALUES ({values});");
}
return sqlBuilder.ToString();
}
public void ExecuteSQL(string sql)
{
try
{
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.AppSettings["MySqlConnectionString"]))
{
conn.Open();
MySqlCommand command = new MySqlCommand(sql, conn);
command.ExecuteNonQuery();
conn.Close();
}
}
catch (Exception e)
{
Console.WriteLine($"Error executing MySQL SQL: {e.Message}");
}
}
}
}
PostgreSQL Strategy:
using Npgsql;
using System;
using System.Configuration;
using System.Data;
using System.Text;
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;
}
}
public string GenerateInsertSQL(DataTable dataTable)
{
if (dataTable == null || dataTable.Rows.Count == 0)
return string.Empty;
StringBuilder sqlBuilder = new StringBuilder();
string tableName = dataTable.TableName;
foreach (DataRow row in dataTable.Rows)
{
StringBuilder columnNames = new StringBuilder();
StringBuilder values = new StringBuilder();
for (int i = 0; i < dataTable.Columns.Count; i++)
{
var column = dataTable.Columns[i];
if (i > 0)
{
columnNames.Append(", ");
values.Append(", ");
}
columnNames.Append($"\"{column.ColumnName}\"");
values.Append(row[column] is DBNull ? "NULL" : $"'{row[column].ToString().Replace("'", "''")}'");
}
sqlBuilder.AppendLine($"INSERT INTO \"{tableName}\" ({columnNames}) VALUES ({values});");
}
return sqlBuilder.ToString();
}
public void ExecuteSQL(string sql)
{
try
{
using (NpgsqlConnection conn = new NpgsqlConnection(ConfigurationManager.AppSettings["NpgSqlConnectionString"]))
{
conn.Open();
NpgsqlCommand command = new NpgsqlCommand(sql, conn);
command.ExecuteNonQuery();
conn.Close();
}
}
catch (Exception e)
{
Console.WriteLine($"Error executing PostgreSQL SQL: {e.Message}");
}
}
}
}
SQL Server Strategy
using System;
using System.Configuration;
using System.Data;
using System.Text;
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;
}
}
public string GenerateInsertSQL(DataTable dataTable)
{
if (dataTable == null || dataTable.Rows.Count == 0)
return string.Empty;
StringBuilder sqlBuilder = new StringBuilder();
string tableName = dataTable.TableName;
foreach (DataRow row in dataTable.Rows)
{
StringBuilder columnNames = new StringBuilder();
StringBuilder values = new StringBuilder();
for (int i = 0; i < dataTable.Columns.Count; i++)
{
var column = dataTable.Columns[i];
if (i > 0)
{
columnNames.Append(", ");
values.Append(", ");
}
columnNames.Append($"[{column.ColumnName}]");
values.Append(row[column] is DBNull ? "NULL" : $"'{row[column].ToString().Replace("'", "''")}'");
}
sqlBuilder.AppendLine($"INSERT INTO [{tableName}] ({columnNames}) VALUES ({values});");
}
return sqlBuilder.ToString();
}
public void ExecuteSQL(string sql)
{
try
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["TSqlConnectionString"]))
{
conn.Open();
SqlCommand command = new SqlCommand(sql, conn);
command.ExecuteNonQuery();
conn.Close();
}
}
catch (Exception e)
{
Console.WriteLine($"Error executing T-SQL SQL: {e.Message}");
}
}
}
}
Step 3: Update the Context Class
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);
}
public string GenerateInsertSQL(DataTable dataTable)
{
return _sqlStrategy.GenerateInsertSQL(dataTable);
}
public void ExecuteSQL(string sql)
{
_sqlStrategy.ExecuteSQL(sql);
}
}
}
Step 4: Implement the Client Code
Here’s how you can generate and execute INSERT TSQL statements for the Products table:
using System;
using System.Data;
namespace MyProject.Util
{
public class Client
{
public static void Main()
{
DataTable productsTable = new DataTable("Products");
productsTable.Columns.Add("ProductName", typeof(string));
productsTable.Columns.Add("ProductCode", typeof(string));
productsTable.Columns.Add("AvailableQuantity", typeof(int));
productsTable.Columns.Add("CreateBy", typeof(string));
productsTable.Columns.Add("ModifyBy", typeof(string));
productsTable.Rows.Add("Product A", "P001", 100, "Admin", "Admin");
productsTable.Rows.Add("Product B", "P002", 200, "Admin", "Admin");
productsTable.Rows.Add("Product C", "P003", 50, "Admin", "Admin");
SQLStrategy sqlHelper = new SQLStrategy(new TSqlQuery());
string insertSQL = sqlHelper.GenerateInsertSQL(productsTable);
Console.WriteLine("Generated SQL:");
Console.WriteLine(insertSQL);
Console.WriteLine("Executing SQL...");
sqlHelper.ExecuteSQL(insertSQL);
}
}
}
Run and See the Result
Summary
GenerateInsertSQL: Dynamically creates INSERT SQL statements from a DataTable.
ExecuteSQL: Executes any SQL command against the database.
Extensibility: Easily supports additional SQL operations or databases.
This design keeps your code modular, reusable, and database-agnostic!
Love C#!
This content originally appeared on DEV Community and was authored by David Au Yeung