CODEWITHSUNDEEP
c#databasedesign-patternsconnection-stringoledb
disasterkid
disasterkid

Reputation: 7278

A design pattern to decide what SQL query to choose

I am developing a Winforms application that contains a connection string to connect to a SQL Server database and say I have 15 different queries that I need to run throughout my program called query1, query2, ...query15.

I would like to extend my program's abilities so that the user can also use other database types such as Oracle, MySql and Sqlite. That means there will be different versions of my 15 queries for each type.

I want to collect all my query scripts in one place so my calling methods can just refer to them by names e.g. InsertNewCustomer(string name) and then my pattern - looking at the type of connection I have set inside my program will run the corresponding method.

How can I achieve this behaviour?

Upvotes: 1

Views: 1481

Answers (1)

DevEstacion
DevEstacion

Reputation: 1967

I suggest you do a Factory and Strategy pattern. What it does is that the Factory holds all the instances/type of your strategies (in this case your database types).

Here is a mock code for you to get you started at.

public class DatabaseStrategyFactory
{
    private static DatabaseStrategyFactory _instance;
    private Dictionary<string, Strategy> _collection;

    private DatabaseStrategyFactory()
    {

    }

    // singleton pattern
    public static DatabaseStrategyFactory Instance { get { return _instance ?? (_instance = new DatabaseStrategyFactory()); }}

    public static Initialize()
    {
        // load all strategies either by creating instances or storing the type
        if(_collection == null)
        {
            _collection = new Dictionary<string, Strategy>();
            _collection.Add(*string key either by class name/enum or whatever you want*, instance or type);
        }
    }

    public Strategy GetStrategy(string name)
    {
        if(_collection == null)
            throw new Exception();
        Strategy strategy = null;
        _collection.TryGetValue(name, out strategy);
        return strategy;
    }
}

read more for Factory and Strategy;

Upvotes: 1

Related Questions