How to properly set itemsources of different control in MVVM pattern ?

Question

I have a SQLite database table with multiple columns.

I want my app to use the unique values from the Party column from the database as the ITEMSOURCE for the combobox on app load and also the datagrid should show the data from the database. Further when I select an item from the combobox the datagrid should filter using the combobox text.

How can I do this using the MVVM pattern ?

Project structure:

MainWindow.xaml

MainWindow.xaml.cs

using MultiWindowMVVM.ViewModel;  
using System;  
using System.Collections.Generic;  
using System.Globalization;  
using System.Windows;  
using System.Windows.Data;  
  
namespace MultiWindowMVVM  
{  
    [ValueConversion(typeof(DateTime), typeof(String))]  
	public class DateTimeConverter : IValueConverter  
	{  
		public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)  
		{  
			return DateTime.ParseExact(value.ToString(), "yyyy-MM-dd", CultureInfo.InvariantCulture);  
		}  
		public object ConvertBack(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)  
		{  
			throw new NotImplementedException();  
		}  
	}  
  
	public partial class MainWindow : Window  
    {  
  
		public MainWindow()  
        {  
            InitializeComponent();  
  
			DataContext = new BillsViewModel();  
  
		}  
    }  
}

Bills.cs

using System.Collections.ObjectModel;  
using MultiWindowMVVM.ViewModel;  
  
namespace MultiWindowMVVM.Model  
{  
    public class Bills : ViewModelBase  
    {  
		private int _id;  
		public int Id  
		{  
			get  
			{  
				return _id;  
			}  
			set  
			{  
				_id = value;  
				OnPropertyChanged("Id");  
			}  
		}  
  
		private string _party;  
		public string Party  
		{  
			get  
			{  
				return _party;  
			}  
			set  
			{  
				_party = value;  
				OnPropertyChanged("Party");  
			}  
		}  
  
		private string _billNo;  
		public string BillNo  
		{  
			get  
			{  
				return _billNo;  
			}  
			set  
			{  
				_billNo = value;  
				OnPropertyChanged("BillNo");  
			}  
		}  
  
  
		private string _billDt;  
		public string BillDt  
		{  
			get  
			{  
				return _billDt;  
			}  
			set  
			{  
				_billDt = value;  
				OnPropertyChanged("BillDt");  
			}  
		}  
  
		private float _amt;  
		public float Amt  
		{  
			get  
			{  
				return _amt;  
			}  
			set  
			{  
				_amt = value;  
				OnPropertyChanged("Amt");  
			}  
		}  
  
		private string _dueDt;  
		public string DueDt  
		{  
			get  
			{  
				return _dueDt;  
			}  
			set  
			{  
				_dueDt = value;  
				OnPropertyChanged("DueDt");  
			}  
		}  
  
		private string _paidOn;  
		public string PaidOn  
		{  
			get  
			{  
				return _paidOn;  
			}  
			set  
			{  
				_paidOn = value;  
				OnPropertyChanged("PaidOn");  
			}  
		}  
  
		private ObservableCollection _bill;  
		public ObservableCollection Bill  
		{  
			get  
			{  
				return _bill;  
			}  
			set  
			{  
				_bill = value;  
				OnPropertyChanged("Bill");  
			}  
		}  
	}  
}

ViewModelBase.cs

using System.ComponentModel;  
  
namespace MultiWindowMVVM.ViewModel  
{  
    public class ViewModelBase : INotifyPropertyChanged  
    {  
		public event PropertyChangedEventHandler PropertyChanged;  
  
		protected void OnPropertyChanged(string propertyName)  
		{  
			if (PropertyChanged != null)  
			{  
				PropertyChanged(this, new PropertyChangedEventArgs(propertyName));  
			}  
		}  
	}  
}

BillsViewModel.cs

using MultiWindowMVVM.Model;  
using System.Collections.Generic;  
using System.Collections.ObjectModel;  
using System.Data.SQLite;  
using System.IO;  
  
namespace MultiWindowMVVM.ViewModel  
{  
    public class BillsViewModel : ViewModelBase  
    {  
  
        public void FillCombos()  
        {  
  
            using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=Bills.db"))  
            {  
                conn.Open();  
  
                string stm = "SELECT DISTINCT Party FROM billdata ORDER BY Party";  
  
                using (SQLiteCommand cmd = new SQLiteCommand(stm, conn))  
                {  
                    using (SQLiteDataReader rdr = cmd.ExecuteReader())  
                    {  
                        while (rdr.Read())  
                        {  
                            MyItems.Add(rdr["Party"].ToString());  
                        }  
                    }  
                }  
  
                conn.Close();  
            }  
  
        }  
  
        public List MyItems { get; set; }  
  
        private string _mySelectedItem;  
		public string MySelectedItem  
		{  
			get { return _mySelectedItem; }  
			set  
			{  
                FillCombos();  
                _mySelectedItem = value;  
            }  
		}  
  
		public BillsViewModel()  
		{  
			var bills = new Bills();  
              
        }  
	}  
}

Answer

You could refer to the code below.

Search for System.Data.SQLite in Manage NuGet Packages .

App.config:

MainWindow.xaml:

MainWindow.xaml.cs:

using System;  
using System.Collections.Generic;  
using System.Collections.ObjectModel;  
using System.ComponentModel;  
using System.Data;  
using System.Data.SQLite;  
using System.Globalization;  
using System.Windows;  
using System.Windows.Data;  
using System.Windows.Input;  
using System.Windows.Threading;  
namespace SqliteCrudDemo  
{  
  ///   
  /// Interaction logic for MainWindow.xaml  
  ///   
  public partial class MainWindow : Window  
  {  
    public MainWindow()  
    {  

      InitializeComponent();  
      DatabaseLayer.LoadData();  
      this.DataContext = new ViewModelUser();  
    }  
  }  
  public class ViewModelUser : ViewModelBase  
  {  

    public ViewModelUser()  
    {  
      personnel = new PersonnelBusinessObject();  
      personnel.EmployeeChanged += new EventHandler(personnel_EmployeeChanged);  
      UpdateBindingGroup = new BindingGroup { Name = "Group1" };  
      CancelCommand = new RelayCommand(DoCancel);  
      SaveCommand = new RelayCommand(DoSave);  
      AddCommand = new RelayCommand(AddUser);  
      DeleteUserCommand = new RelayCommand(DeleteUser);  
    }  
    PersonnelBusinessObject personnel;  
    private ObservableCollection _Employee;  
    public ObservableCollection Employee  
    {  
      get  
      {  
        _Employee = new ObservableCollection(personnel.GetEmployees());  
        return _Employee;  
      }  
    }  
    public int SelectedIndex { get; set; }  
    object _SelectedEmployee;  
    public object SelectedEmployee  
    {  
      get  
      {  
        return _SelectedEmployee;  
      }  
      set  
      {  
        if (_SelectedEmployee != value)  
        {  
          _SelectedEmployee = value;  
          OnPropertyChanged("SelectedEmployee");  
        }  
      }  
    }  
    private BindingGroup _UpdateBindingGroup;  
    public BindingGroup UpdateBindingGroup  
    {  
      get  
      {  
        return _UpdateBindingGroup;  
      }  
      set  
      {  
        if (_UpdateBindingGroup != value)  
        {  
          _UpdateBindingGroup = value;  
          OnPropertyChanged("UpdateBindingGroup");  
        }  
      }  
    }  
    void personnel_EmployeeChanged(object sender, EventArgs e)  
    {  
      Application.Current.Dispatcher.BeginInvoke(DispatcherPriority.Background, new Action(() =>  
      {  
        OnPropertyChanged("Employee");  
      }));  
    }  
    public RelayCommand CancelCommand { get; set; }  
    public RelayCommand SaveCommand { get; set; }  
    public RelayCommand AddCommand { get; set; }  
    public RelayCommand DeleteUserCommand { get; set; }  

    void DoCancel(object param)  
    {  
      UpdateBindingGroup.CancelEdit();  
      if (SelectedIndex == -1)    //This only closes if new - just to show you how CancelEdit returns old values to bindings  
        SelectedEmployee = null;  
    }  

    void DoSave(object param)  
    {  
      UpdateBindingGroup.CommitEdit();  
      var employee = SelectedEmployee as MData;  
      if (SelectedIndex == -1)  
      {  
        personnel.AddEmployee(employee);  
        OnPropertyChanged("Employee"); // Update the list from the data source  
      }  
      else  
        personnel.UpdateEmployee(employee);  

      SelectedEmployee = null;  
    }  

    void AddUser(object param)  
    {  


      SelectedEmployee = null; // Unselects last selection. Essential, as assignment below won't clear other control's SelectedItems  
      var employee = new MData();  
      SelectedEmployee = employee;  
    }  

    void DeleteUser(object parameter)  
    {  
      var employee = SelectedEmployee as MData;  
      if (SelectedIndex != -1)  
      {  
        personnel.DeleteEmployee(employee);  
        OnPropertyChanged("Employee"); // Update the list from the data source  
      }  
      else  
        SelectedEmployee = null; // Simply discard the new object  
    }  
  }  

  public static class DatabaseLayer  
  {  

    public static void LoadData()  
    {  
      SQLiteConnection.CreateFile("MyDatabase.sqlite");  
      SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite");  
      m_dbConnection.Open();  
      string sql = "create table MyData (Id INTEGER PRIMARY KEY AUTOINCREMENT, Party varchar(20), BillNo varchar(20), BillDt varchar(20) ,Amt varchar(20) ,DueDt varchar(20),PaidOn varchar(20)) ";  
      SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);  
      command.ExecuteNonQuery();  
      sql = "INSERT INTO MyData(Party,BillNo, BillDt,Amt,DueDt,PaidOn) VALUES('Aakar Medicos1', 'AM-1877','05-01-2022','3324','26-02-2022','22-02-2022')";  


      command = new SQLiteCommand(sql, m_dbConnection);  

      command.ExecuteNonQuery();  
      sql = "INSERT INTO MyData(Party,BillNo, BillDt,Amt,DueDt,PaidOn) VALUES('Aakar Medicos2', 'AM-1877','05-01-2022','3324','26-02-2022','22-02-2022')";  
      command = new SQLiteCommand(sql, m_dbConnection);  

      command.ExecuteNonQuery();  
      m_dbConnection.Close();  
    }  
    public static List GetEmployeeFromDatabase()  
    {  
      try  
      {  
        SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite");  
        SQLiteCommand sqlCom = new SQLiteCommand("Select * From MyData", m_dbConnection);  
        SQLiteDataAdapter sda = new SQLiteDataAdapter(sqlCom);  
        DataTable dt = new DataTable();  
        sda.Fill(dt);  
        var Employee = new List();  
        foreach (DataRow row in dt.Rows)  
        {  
          var obj = new MData()  
          {  
            Id = Convert.ToInt32(row["Id"]),  
            Party = (string)row["Party"],  
            BillNo = (string)row["BillNo"],  
            BillDt = (string)(row["BillDt"]),  
            Amt = (string)row["Amt"],  
            DueDt = (string)(row["DueDt"]),  
            PaidOn = (string)(row["PaidOn"])  
          };  
          Employee.Add(obj);  
          m_dbConnection.Close();  
        }  
        return Employee;  

      }  
      catch (Exception ex)  
      {  
        throw ex;  
      }  
    }  

    internal static int InsertEmployee(MData employee)  
    {  
      try  
      {  
        const string query = "INSERT INTO MyData(Party,BillNo, BillDt,Amt,DueDt,PaidOn) VALUES(@Party, @BillNo,@BillDt,@Amt,@DueDt,@PaidOn)";  
        var args = new Dictionary  
     {  
           {"@Party", employee.Party},  
           {"@BillNo", employee.BillNo},  
           {"@BillDt", employee.BillDt},  
           {"@Amt", employee.Amt},  
           {"@DueDt", employee.DueDt},  
           {"@PaidOn", employee.PaidOn},  
     };  
        return ExecuteWrite(query, args);  
        MessageBox.Show("Data Saved Successfully.");  
      }  
      catch (Exception ex)  
      {  
        throw ex;  
      }  
      finally  
      {  

      }  
    }  

    internal static int UpdateEmployee(MData employee)  
    {  
      try  
      {  
        const string query = "UPDATE MyData SET Party = @Party, BillNo = @BillNo, BillDt=@BillDt, Amt=@Amt, DueDt=@DueDt , PaidOn=@PaidOn WHERE Id = @Id";  

        var args = new Dictionary  
    {  
         {"@Id", employee.Id},  
         {"@Party", employee.Party},  
           {"@BillNo", employee.BillNo},  
           {"@BillDt", employee.BillDt},  
           {"@Amt", employee.Amt},  
           {"@DueDt", employee.DueDt},  
           {"@PaidOn", employee.PaidOn},  
    };  

        return ExecuteWrite(query, args);  
        MessageBox.Show("Data Updated Successfully.");  
      }  
      catch (Exception ex)  
      {  
        throw ex;  
      }  
      finally  
      {  

      }  
    }  

    internal static int DeleteEmployee(MData employee)  
    {  
      try  
      {  
        const string query = "Delete from MyData WHERE Id = @id";  
      var args = new Dictionary  
        {  
          {"@id", employee.Id}  
        };  
      return ExecuteWrite(query, args);  
      MessageBox.Show("Data Deleted Successfully.");  
    }  
      catch (Exception ex)  
      {  
        throw ex;  
      }  
      finally  
      {  

      }  
    }  
    private static int ExecuteWrite(string query, Dictionary args)  
    {  
      int numberOfRowsAffected;  

      using (var con = new SQLiteConnection("Data Source=MyDatabase.sqlite"))  
      {  
        con.Open();  
        using (var cmd = new SQLiteCommand(query, con))  
        {  
          foreach (var pair in args)  
          {  
            cmd.Parameters.AddWithValue(pair.Key, pair.Value);  
          }  
          numberOfRowsAffected = cmd.ExecuteNonQuery();  
        }  
        return numberOfRowsAffected;  
      }  
    }  
  }  


  public class PersonnelBusinessObject  
  {  
    internal EventHandler EmployeeChanged;  

    List Employee { get; set; }  
    public PersonnelBusinessObject()  
    {  
      Employee = DatabaseLayer.GetEmployeeFromDatabase();  
    }  

    public List GetEmployees()  
    {  
      return Employee = DatabaseLayer.GetEmployeeFromDatabase();  
    }  

    //public List NationalityCollection { get; set; }  


    //public List GetNationality()  
    //{  
    //  return NationalityCollection = DatabaseLayer.GetNationality();  
    //}  
    public void AddEmployee(MData employee)  
    {  
      DatabaseLayer.InsertEmployee(employee);  
      OnEmployeeChanged();  
    }  

    public void UpdateEmployee(MData employee)  
    {  
      DatabaseLayer.UpdateEmployee(employee);  
      OnEmployeeChanged();  
    }  

    public void DeleteEmployee(MData employee)  
    {  
      DatabaseLayer.DeleteEmployee(employee);  
      OnEmployeeChanged();  
    }  

    void OnEmployeeChanged()  
    {  
      if (EmployeeChanged != null)  
        EmployeeChanged(this, null);  
    }  
  }  

  public class MData : ViewModelBase  
  {  
    private int id;  
    private string party;  
    private string billNo;  
    private string billDt;  
    private string amt;  
    private string dueDt;  
    private string paidOn;  

    public MData()  
    {  
    }  

    public int Id  
    {  
      get { return id; }  
      set  
      {  
        id = value;  
        OnPropertyChanged("ID");  
      }  
    }  
    public string Party  
    {  
      get { return party; }  
      set  
      {  
        party = value;  
        OnPropertyChanged("Party");  
      }  
    }  
    public string BillNo  
    {  
      get { return billNo; }  
      set  
      {  
        billNo = value;  
        OnPropertyChanged("BillNo");  
      }  
    }  
    public string BillDt  
    {  
      get { return billDt; }  
      set  
      {  
        billDt = value;  
        OnPropertyChanged("BillDt");  
      }  
    }  
    public string Amt  
    {  
      get { return amt; }  
      set  
      {  
        amt = value;  
        OnPropertyChanged("Amt");  
      }  
    }  

    public string DueDt  
    {  
      get { return dueDt; }  
      set  
      {  
        dueDt = value;  
        OnPropertyChanged("DueDt");  
      }  
    }  

    public string PaidOn  
    {  
      get { return paidOn; }  
      set  
      {  
        paidOn = value;  
        OnPropertyChanged("PaidOn");  
      }  
    }  
  }  

  public class RelayCommand : ICommand  
  {  

    readonly Action

Answer

Hi,
please, correct your BillsViewModel:

  public class BillsViewModel : ViewModelBase
  {
    public void FillCombos()
    {
      using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=Bills.db"))
      {
        conn.Open();

        string stm = "SELECT DISTINCT Party FROM billdata ORDER BY Party";

        using (SQLiteCommand cmd = new SQLiteCommand(stm, conn))
        {
          using (SQLiteDataReader rdr = cmd.ExecuteReader())
          {
            while (rdr.Read())
            {
              MyItems.Add(rdr["Party"].ToString());
            }
          }
        }

        conn.Close();
      }
    }

    public List MyItems { get; set; } = new List(); // <-!!!!!

    private string _mySelectedItem;
    public string MySelectedItem
    {
      get { return _mySelectedItem; }
      set { _mySelectedItem = value; } // <-!!!!!
    }

    public BillsViewModel()
    {
      var bills = new Bills(); // ???
      FillCombos(); // <-!!!!!
    }
  }

How to properly set itemsources of different control in MVVM pattern ?

2 answers