Good Day,

I have 2 DataSets in my project, they are identical except 1 is for SQLite and 1 for MSSQL. I am looking for a way to use only one of these datasets in my forms at a time, depending on which connection the client wishes to use.

For example 1 client can choose to use SQLite whereas another client can choose MSSQL.

When searching online I only find ways to change the connection string of a single DataSet. Of course when I change the connection string of the SQLite Dataset to the MSSQL connection string, I get an error.

How can I change the Default DataSet to use in my application depending on what the client chooses?

I honestly have no idea and I have been searching for a long while.

Edit - Is there a way to change the data provider of the dataset? That way it should be possible to change the connection string without getting the error.

Any help would be much appreciated. Thank you.

EDIT Connection Strings Screenshot

App.config Connection Strings Screenshot

EDIT 2 Error when changing connection string

CodePudding user response：

Here's the kind of thing I am thinking when it comes to the strategy pattern.

First start with an interface that describes all of the data access that you need to do that is completely database agnostic:

public interface IDataAccess
{
    DataTable GetPeople();
}

Now you can implement concrete examples of how to get that data.

public class SQLiteDataAccess : IDataAccess
{
    public SQLiteDataAccess(string connectionString)
    { }

    public DataTable GetPeople()
    {
        var dt = new DataTable();
        dt.Columns.Add("Name");
        dt.Columns.Add("Age");
        dt.Rows.Add("Fred", 40);
        dt.Rows.Add("Barney", 39);
        return dt;
    }
}

public class MSSqlDataAccess : IDataAccess
{
    public MSSqlDataAccess(string connectionString)
    { }

    public DataTable GetPeople()
    {
        var dt = new DataTable();
        dt.Columns.Add("Name");
        dt.Columns.Add("Age");
        dt.Rows.Add("Abbot", 50);
        dt.Rows.Add("Costello", 49);
        return dt;
    }
}

There's no limitation to using a database. I could make a file system version.

public class FileDataAccess : IDataAccess
{
    private readonly string _fileName;

    public FileDataAccess(string fileName)
    {
        _fileName = fileName;
    }

    public DataTable GetPeople()
    {
        var dt = new DataTable();
        dt.Columns.Add("Name");
        dt.Columns.Add("Age");
        foreach (var line in File.ReadLines(_fileName))
        {
            dt.Rows.Add(line.Split(','));
        }
        return dt;
    }
}

Now you can program your UI to use the strategy that you want:

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        IDataAccess da = new SQLiteDataAccess("SQL1");
        //IDataAccess da = new MSSqlDataAccess("MS1");
        //IDataAccess da = new FileDataAccess(@"C:\text.csv");
        dataGridView1.DataSource = da.GetPeople();
    }
}

By changing that one line you change where the data is coming from in your app.