How to do simple CRUD operation on SQLite database with MVVM pattern

Question

I'm totally new to MVVM and I've been going through a lot of online posts regarding how do I implement CRUD operations and properly bind SQLite database data to WPF controls like Datagrid, Combobox, Textbox, Button etc. but struggling to find what I need. Also, for someone who is new to MVVM pattern it seems pretty intimidating to say the least.

Anyways, coming to the point, I've found this post online and tried my best to make it work for SQLite databases but struggling at the moment.

My project structure looks like this:

and the relevant files as follows:

StudentRepository.cs

using System;  
using System.Collections.Generic;  
using System.Data;  
using System.Data.Common;  
using System.Data.SQLite;  
using System.Linq;  
using TestWpfMVVM.Model;  
  
namespace TestWpfMVVM.DataAccess  
{  
    public class StudentRepository  
    {  
  
		public void ExecuteWrite(string query, Dictionary args)  
		{  
  
			//setup the connection to the database  
			using (var con = new SQLiteConnection(@"Data Source=/.Students.db;"))  
			{  
				con.Open();  
  
				//open a new command  
				using (var cmd = new SQLiteCommand(query, con))  
				{  
					//set the arguments given in the query  
					foreach (var pair in args)  
					{  
						cmd.Parameters.AddWithValue(pair.Key, pair.Value);  
					}  
  
					cmd.ExecuteNonQuery();  
				}  
  
			}  
		}  
  
		public DataTable Execute(string query, Dictionary args)  
		{  
			if (string.IsNullOrEmpty(query.Trim()))  
				return null;  
  
			using (var con = new SQLiteConnection(@"Data Source=/.Students.db;"))  
			{  
				con.Open();  
				using (var cmd = new SQLiteCommand(query, con))  
				{  
					foreach (KeyValuePair entry in args)  
					{  
						cmd.Parameters.AddWithValue(entry.Key, entry.Value);  
					}  
  
					var da = new SQLiteDataAdapter(cmd);  
  
					var dt = new DataTable();  
					da.Fill(dt);  
  
					da.Dispose();  
					return dt;  
				}  
			}  
		}  
  
		public void AddStudent(Student student)  
		{  
			const string query = "INSERT INTO tblStud(stdName, stdAge, stdAddress, stdContact) VALUES(@stdName, @stdAge, @stdAddress, @stdContact)";  
  
			var args = new Dictionary  
			{  
				{"@stdName", student.stdName},  
				{"@stdAge", student.stdAge},  
				{"@stdAddress", student.stdAddress},  
				{"@stdContact", student.stdContact}  
			};  
  
			ExecuteWrite(query, args);  
		}  
  
		public void UpdateStudent(Student student)  
		{  
			const string query = "UPDATE tblStud SET stdName = @stdName, stdAge = @stdAge, stdAddress = @stdAddress, stdContact = @stdContact WHERE stdId = @stdId";  
  
			var args = new Dictionary  
			{  
				{"@id", student.stdId},  
				{"@stdName", student.stdName},  
				{"@stdAge", student.stdAge},  
				{"@stdAddress", student.stdAddress},  
				{"@stdContact", student.stdContact}  
			};  
  
			ExecuteWrite(query, args);  
		}  
  
		public void RemoveStudent(Student student)  
		{  
			const string query = "Delete from tblStud WHERE stdId = @stdId";  
  
			var args = new Dictionary  
			{  
				{"@stdId", student.stdId}  
			};  
  
			ExecuteWrite(query, args);  
		}  
  
		public Student Get(int id)  
		{  
			var student = new Student();  
  
			var query = "SELECT * FROM tblStud WHERE stdId = @stdId";  
  
			var args = new Dictionary  
			{  
				{"@stdId", student.stdId}  
			};  
  
			DataTable dt = Execute(query, args);  
  
			if (dt == null || dt.Rows.Count == 0)  
			{  
				return null;  
			}  
  
			student = new Student  
			{  
  
				stdId = Convert.ToInt32(dt.Rows[0]["stdId"]),  
				stdName = Convert.ToString(dt.Rows[0]["stdName"]),  
				stdAge = Convert.ToInt32(dt.Rows[0]["stdAge"]),  
				stdAddress = Convert.ToString(dt.Rows[0]["stdAddress"]),  
				stdContact = Convert.ToString(dt.Rows[0]["stdContact"])  
			};  
  
			return student;  
		}  
  
        public List GetAll()  
        {  
			var student = new Student();  
  
			var query = "SELECT * FROM tblStud";  
  
			var args = new Dictionary  
			{  
				{"@stdId", student.stdId}  
			};  
  
			DataTable dt = Execute(query, args);  
  
			if (dt == null || dt.Rows.Count == 0)  
			{  
				return null;  
			}  
  
			student = new Student  
			{  
  
				stdId = Convert.ToInt32(dt.Rows[0]["stdId"]),  
				stdName = Convert.ToString(dt.Rows[0]["stdName"]),  
				stdAge = Convert.ToInt32(dt.Rows[0]["stdAge"]),  
				stdAddress = Convert.ToString(dt.Rows[0]["stdAddress"]),  
				stdContact = Convert.ToString(dt.Rows[0]["stdContact"])  
			};  
  
			return student.ToList();  
		}  
  
    }  
}

Student.cs

using System;  
  
namespace TestWpfMVVM.Model  
{  
    public class Student  
    {  
        public int stdId { get; set; }  
        public string stdName { get; set; }  
        public int stdAge { get; set; }  
        public string stdAddress { get; set; }  
        public string stdContact { get; set; }  
    }  
}

StudentRecord.cs

using System;  
using System.Collections.Generic;  
using System.Collections.ObjectModel;  
using System.Linq;  
using System.Text;  
using System.Threading.Tasks;  
using TestWpfMVVM.ViewModel;  
  
namespace TestWpfMVVM.Model  
{  
    public class StudentRecord : ViewModelBase  
    {  
        private int _id;  
        public int Id  
        {  
            get  
            {  
                return _id;  
            }  
            set  
            {  
                _id = value;  
                OnPropertyChanged("Id");  
            }  
        }  
  
        private string _name;  
        public string Name  
        {  
            get  
            {  
                return _name;  
            }  
            set  
            {  
                _name = value;  
                OnPropertyChanged("Name");  
            }  
        }  
  
        private int _age;  
        public int Age  
        {  
            get  
            {  
                return _age;  
            }  
            set  
            {  
                _age = value;  
                OnPropertyChanged("Age");  
            }  
        }  
  
        private string _address;  
        public string Address  
        {  
            get  
            {  
                return _address;  
            }  
            set  
            {  
                _address = value;  
                OnPropertyChanged("Address");  
            }  
        }  
  
        private string _contact;  
        public string Contact  
        {  
            get  
            {  
                return _contact;  
            }  
            set  
            {  
                _contact = value;  
                OnPropertyChanged("Contact");  
            }  
        }  
  
        private ObservableCollection _studentRecords;  
        public ObservableCollection StudentRecords  
        {  
            get  
            {  
                return _studentRecords;  
            }  
            set  
            {  
                _studentRecords = value;  
                OnPropertyChanged("StudentRecords");  
            }  
        }  
    }  
}

RelayCommand.cs

using System;  
using System.Windows.Input;  
  
namespace TestWpfMVVM.ViewModel  
{  
    public class RelayCommand : ICommand  
    {  
        private readonly Action

Accepted 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

The method public List GetAll() should return data of type List.
The method might look like the code below：

  public List GetAll()  
        {  
          List students = new  List();  
          var student = new Student();  
    ...  
          students.Add(student);  
          return students;  
        }

The DeleteStudent(int id) method of the StudentViewModel class shows an error when I test it.
I modified it as follows：

 public void DeleteStudent(int id)  
    {  
      if (MessageBox.Show("Confirm delete of this record?", "Student", MessageBoxButton.YesNo)  
          == MessageBoxResult.Yes)  
      {  
        try  
        {  
          var model= _repository.Get(id);  
          _repository.RemoveStudent(model);  
          MessageBox.Show("Record successfully deleted.");  
        }  
      ...  
      }  
   }

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

[5]: https://learn.microsoft.com/en-us/answers/articles/67444/email-notifications.html

How to do simple CRUD operation on SQLite database with MVVM pattern

1 additional answer