How to properly switch between identical databases on the go ?

Question

I have multiple databases in my WPF app, different databases for different years e.g. 2020.db, 2021.db, 2022.db etc . I have a combo box whose Items are a list of years (2020, 2021, 2022 ...).

When I select a year from the said combo box I want the database of the entire app to change to that years database and update the UI and items inside the viewmodel class and wherever the database queries are called.

All the databases are identical i.e. the table names, column names and datatypes of the table columns are all same just the data is different.

Currently in my app I have declared a string containing the path of the database like

namespace test  
{  
      
    public static class DB  
    {  
        public static string dbloc = @"Data Source=2022.db";  
    }  
  
    public partial class Window1 : Window  
    {  
          
          
        public Window1()  
        {  
            InitializeComponent();  
  
            this.DataContext = new BillsViewModel();  
        }  
  
    }  
    ...  
}

and I use it like using (var connection = new SQLiteConnection(DB.dbloc)) or something similar inside different methods of the BillsViewModel and other classes wherever required.

Also, inside BillsViewModel I have declared many properties that act as ItemSource of vairous controls like datagrid's, textbox's, combobox's, button's etc. which basically takes data from the database.

So, when I change the selection from the above mentioned combo box those properties also need to get fresh updates from the newly chosen database.

Is it possible to use a switch statement to effectively change the database on combo box selection (something like below) ?

switch (comboboxSelectedItem)  
    {  
        case "2020":  
            dbloc = @"Data Source=2020.db";  
            break;  
  
        case "2021":  
            dbloc = @"Data Source=2021.db";  
            break;  
  
        default:  
            dbloc = @"Data Source=2022.db";  
            break;  
    }

But the propeties in the BillsViewModel also needs to update i.e. get data from a different database now. If I add a new instance of the BillsViewModel class before the break in above switch statement will that work.

What is the proper way of doing this ? Can anyone help ?

Accepted Answer

Hi,
please, lock my demo code.

Insert in your project XAML Window1 and CodeBehind, set as start window.

Result:

Answer

Hi,
build Model for access to database with public property for "Data Source".

if you change database in ViewModel set new public property for "Data Source" in Model, than clear collections (set Source to null) and execute PropertyChanged for Views or with empty string (refresh all objects in UI). Refreshing UI will get Views and in getter you check Source of collection for reloading data.

Or in CodeBehind like in following demo:

XAML:

CodeBehind:

using System;  
using System.Collections.ObjectModel;  
using System.ComponentModel;  
using System.Data;  
using System.Data.SQLite;  
using System.Windows;  
using System.Windows.Controls;  
using System.Windows.Data;  
  
namespace WpfApp1  
{  
  ///   
  /// Interaction logic for Window031.xaml  
  ///   
  public partial class Window031 : Window, INotifyPropertyChanged  
  {  
    public Window031()  
    {  
      InitializeComponent();  
      this.DataContext = this;  
    }  
  
    private void cb_SelectionChanged(object sender, SelectionChangedEventArgs e)  
    {  
      var comboboxSelectedItem = ((ComboBoxItem)cb.SelectedValue).Content;  
      switch (comboboxSelectedItem)  
      {  
        case "2020":  
          dbloc = @"Data Source=2020.db";  
          break;  
  
        case "2021":  
          dbloc = @"Data Source=2021.db";  
          break;  
  
        default:  
          dbloc = @"Data Source=2022.db";  
          break;  
      }  
    }  
  
    private string _dbloc;  
    string dbloc  
    {  
      get => this._dbloc;  
      set  
      {  
        if (this._dbloc != value)  
        {  
          this._dbloc = value;  
          LoadData();  
          OnPropertyChanged(nameof(View));  
        }  
      }  
    }  
  
    ObservableCollection col = new ObservableCollection();  
    CollectionViewSource cvs = new CollectionViewSource();  
  
    public ICollectionView View { get => cvs.View; }  
  
    private void LoadData()  
    {  
      using (SQLiteDataAdapter sda = new SQLiteDataAdapter("SELECT * FROM Tab1", dbloc))  
      {  
        DataTable dt = new DataTable();  
        sda.Fill(dt);  
        col.Clear();  
        foreach (DataRow row in dt.Rows)  
        {  
          col.Add(new Data()  
          {  
            ID = Convert.ToInt32(row["ID"]),  
            Name = (string)row["Name"],  
            Description = (string)row["Description"]  
          });  
        }  
      }  
  
      cvs.Source = col;  
    }  
  
    public event PropertyChangedEventHandler? PropertyChanged;  
    internal void OnPropertyChanged(string propertyName) =>   
      PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));  
  }  
  
  public class Data  
  {  
    public int ID { get; set; }  
    public string Name { get; set; }  
    public string Description { get; set; }  
  }  
}

Answer

As Peter said, I used Sql Server database for testing, you could refer to the code below to use Sqlite.

Xaml:

Codebehind:

266575-combobox-refersh-datagrid-data.txt

The result:

Update:

Codebehind:

268477-comboboxchangedatasource.txt

The result:

----------------------------------------------------------------------------

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

How to properly switch between identical databases on the go ?

2 additional answers