Home  >  Article  >  Database  >  Implementing a Database Helper in C# Using the Strategy Design Pattern Step-by-Step Guide

Implementing a Database Helper in C# Using the Strategy Design Pattern Step-by-Step Guide

Linda Hamilton
Linda HamiltonOriginal
2024-11-12 16:20:02976browse

Implementing a Database Helper in C# Using the Strategy Design Pattern Step-by-Step Guide

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 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;
            }
        }
    }
}

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(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 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();
            }
        }
    }
}

Love C#!

The above is the detailed content of Implementing a Database Helper in C# Using the Strategy Design Pattern Step-by-Step Guide. For more information, please follow other related articles on the PHP Chinese website!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn