I need to refine the CRUD methodology in a MVVM app in readiness to host SQLite database functionality

Question

With the help of @JessieZhang-MSFT and @Leon Lu (Shanghai Wicresoft Co,.Ltd.) , I have a working app that reads a short list of people ("Operators"), each of whom have a few properties: OperatorID, OperatorName, OperatorPhone, OperatorEmail and OperatorAvatar. At the moment, these are read in from the app but my goal is to have them populate a locally stored SQLite database table called Operators. Currently there is CRUD functionality, but it is spread around a number of different classes in the app.
To make the app more readable and logical, I would like to bring the CRUD functionality into the OperatorViewModel.cs program where the Create (or Add), Read, Update and Delete functions can then be amended to cater to the SQLite database.
The structure at the moment is that MainPage (level 1) calls MenuPage (level 2) which calls OperatorsPage (level 3) where the CRUD functionality occurs in a number of other pages (level 4).
The data model is set up in OperatorModel.cs and the list is populated in OperatorViewModel.cs, where I'd eventually like the CRUD methods to reside.
I have started to code the database in HFNDatabase.cs but am running into all sorts of difficulties.
I'll include the code here...

OperatorModel.cs

using System;  
using System.Collections.Generic;  
using System.ComponentModel;  
using System.Runtime.CompilerServices;  
using System.Text;  
using SQLite;  
  
namespace HandsFreeNotes.Model  
{  
    //    public class OperatorModel  
    // The line above changed to the line below at the behest of JessieZhang-2116 (Microsoft Q&A) 01/05/2021  
    public class OperatorModel: INotifyPropertyChanged  
    {  
        public int OperatorID { get; set; }  
        //        public string OperatorName { get; set; }  
        // The 1 line above changed to the 6 lines below at the behest of JessieZhang-2116 (Microsoft Q&A) 01/05/2021  
        string _operatorName;  
        public string OperatorName  
        {  
            set { SetProperty(ref _operatorName, value); }  
            get { return _operatorName; }  
        }  
  
        public string OperatorPhone { get; set; }  
        public string OperatorEmail { get; set; }  
        public string OperatorAvatar { get; set; }  
  
        // The 4 lines below added at the behest of JessieZhang-2116 (Microsoft Q&A) 01/05/2021  
        public OperatorModel()  
        {  
  
        }  
  
        public OperatorModel(int operatorID, string operatorName, string operatorPhone, string operatorEmail, string operatorAvatar)  
        {  
            OperatorID = operatorID;  
            OperatorName = operatorName;  
            OperatorPhone = operatorPhone;  
            OperatorEmail = operatorEmail;  
            OperatorAvatar = operatorAvatar;  
        }  
        // All the lines below added at the behest of JessieZhang-2116 (Microsoft Q&A) 01/05/2021  
        bool SetProperty(ref T storage, T value, [CallerMemberName] string propertyName = null)  
        {  
            if (Object.Equals(storage, value))  
                return false;  
  
            storage = value;  
            OnPropertyChanged(propertyName);  
            return true;  
        }  
        protected void OnPropertyChanged([CallerMemberName] string propertyName = null)  
        {  
            PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));  
        }  
  
        public event PropertyChangedEventHandler PropertyChanged;  
    }  
  
  
}

OperatorViewModel.cs

using HandsFreeNotes.Model;  
using System;  
using System.Collections.Generic;  
using System.Collections.ObjectModel;  
using System.ComponentModel;  
using System.Text;  
using System.Threading.Tasks;  
using Xamarin.Forms;  
using SQLite;  
  
namespace HandsFreeNotes.ViewModel  
{  
    public class OperatorViewModel : INotifyPropertyChanged  
    {  
        private string _HFNName;  
  
        public string HFNName  
        {  
            get  
            {  
                return _HFNName;  
            }  
              
            set  
            {  
                _HFNName = value;  
                RaisePropertyChanged("HFNName");  
            }  
        }  
  
        // public List OperatorList;  
        // The line above replaced by the 5 lines below at the behest of LeonLu-MSFT (Microsoft Q&A) 06/05/2021  
        public OperatorViewModel()  
        {  
            GetOperators();  
        }  
        public static ObservableCollection OperatorList;  
  
  
        public OperatorModel SelectedOperator;  
  
        public async Task GetOperators()  
        {  
            // OperatorList = await App.Database.GetOperatorsAsync();  
  
            if (OperatorList == null)  
            {  
                // OperatorList = new List();  
                // The line above replaced by the line below at the behest of LeonLu-MSFT (Microsoft Q&A) 06/05/2021  
                OperatorList = new ObservableCollection();  
  
                OperatorList.Add(new OperatorModel(1, "Grime", "61419659866", "apps@grime.net", "gs300.png"));  
                OperatorList.Add(new OperatorModel(2, "Brookey", "61419659866", "brookey@grime.net", "gmb.jpg"));  
                OperatorList.Add(new OperatorModel(3, "CC", "61422192456", "shaunachick@gmail.com", "cc.jpg"));  
                OperatorList.Add(new OperatorModel(4, "Monte", "61412595878", "monte@aussieweb.com.au", "monte2a.jpg"));  
                OperatorList.Add(new OperatorModel(5, "Jeff", "61409998197", "jeff@content2convert.com.au", "jeff.jpg"));  
            }  
  
            return true;  
        }  
  
        public bool UpdateOperator(OperatorModel opm)  
        {  
            SelectedOperator.OperatorID = opm.OperatorID;  
            SelectedOperator.OperatorName = opm.OperatorName;  
            SelectedOperator.OperatorPhone = opm.OperatorPhone;  
            SelectedOperator.OperatorEmail = opm.OperatorEmail;  
            SelectedOperator.OperatorAvatar = opm.OperatorAvatar;  
                          
            return true;  
        }  
  
        public event PropertyChangedEventHandler PropertyChanged;  
  
        protected void RaisePropertyChanged(string prop)  
        {  
            if (PropertyChanged != null) { PropertyChanged(this, new PropertyChangedEventArgs(prop)); }  
        }  
  
    }  
}

HFNDatabase.cs

using HandsFreeNotes.Model;  
using SQLite;  
using System;  
using System.Collections.Generic;  
using System.Text;  
using System.Threading.Tasks;  
  
// This class added according to Cary Baer:  
// https://www.skillshare.com/classes/Mobile-Application-Development-With-Cross-Platform-Xamarin-Forms/984679771/projects  
  
namespace HandsFreeNotes.Data  
{  
    public class HFNDatabase  
    {  
        readonly SQLiteAsyncConnection database;  
  
        public HFNDatabase(string dbPath)  
        {  
            database = new SQLiteAsyncConnection(dbPath);  
            database.CreateTableAsync().Wait();  
        }  
  
        public async Task> GetOperatorsAsync()  
        {  
            return await database.Table().ToListAsync();  
        }  
  
        public async Task InsertOperator(OperatorModel om)  
        {  
            await database.InsertAsync(om);  
            int ret = 1;  
            return ret;  
        }  
  
        public async Task DeleteOperator(OperatorModel om)  
        {  
            await database.ExecuteScalarAsync("DELETE FROM [OperatorModel] WHERE TITLE = ?", om.OperatorName);  
            int ret = 1;  
            return ret;  
        }  
  
        public async Task UpdatOperator(OperatorModel om)  
        {  
            await database.ExecuteScalarAsync("UPDATE [OperatorModel] SET OPERATORID = ?, OPERATORPHONE = ?, OPERATOREMAIL = ?, OPERATORAVATAR = ? WHERE TITLE = ?", om.OperatorID, om.OperatorPhone, om.OperatorEmail, om.OperatorAvatar, om.OperatorName);  
            int ret = 1;  
            return ret;  
        }  
    }  
}

MainPage.xaml

MainPage.xaml.cs

using System;  
using System.Collections.Generic;  
using System.ComponentModel;  
using System.Linq;  
using System.Text;  
using System.Threading.Tasks;  
using Xamarin.Forms;  
using SQLite;  
using HandsFreeNotes.Model;  
using HandsFreeNotes.View;  
using HandsFreeNotes.ViewModel;  
using Xamarin.Essentials;  
  
namespace HandsFreeNotes  
{  
    public partial class MainPage : ContentPage  
    {  
        OperatorViewModel ovm;  
        // The line below added at the behest of LeonLu-MSFT (Microsoft Q&A) 13/05/2021  
        OperatorModel opm;  
  
        public MainPage()  
        {  
            // add a bit of padding to cater to the "notch" on the iPhone.  
            if (Device.RuntimePlatform == Device.iOS) {Padding = new Thickness(0, 40, 0, 0);}  
  
            InitializeComponent();  
  
            ovm = new OperatorViewModel();  
            // The line below added at the behest of LeonLu-MSFT (Microsoft Q&A) 13/05/2021  
            opm = new OperatorModel();  
  
            BindingContext = ovm;  
        }  
  
        //protected override async void OnAppearing()  
        //{  
        //    base.OnAppearing();  
        //    await RefreshOperatorListView();  
        //}  
  
        //public async Task RefreshOperatorListView()  
        //{  
        //    OperatorListView.ItemSource = null;  
        //    await ovm.GetOperators()  
        //    OperatorListView.ItemSource = ovm.OperatorList;  
        //    return true;  
        //}  
  
        private async void OperatorButton_Clicked(object sender, EventArgs e)  
        {  
            // await Navigation.PushModalAsync(new OperatorsPage());  
            // The line above changed to the lines below (including BackCall) at the behest of LeonLu-MSFT (Microsoft Q&A) 13/05/2021  
            var page = new OperatorsPage(SelectModel);  
            page.ReturnValue += delegate (object s, OperatorModel operatorModel)  
            {  
                BackCall(s, operatorModel);  
            };  
            await Navigation.PushModalAsync(page);  
        }  
  
        OperatorModel SelectModel;  
  
        private void BackCall(object s, OperatorModel model)  
        {  
            SelectModel = model;  
            opm.OperatorName = model.OperatorName;  
            OperatorButton.Text = opm.OperatorName;  
            OperatorButton.TextColor = Color.Green;  
            OperatorButton.FontSize = 20;  
        }  
  
        private async void AboutButton_Clicked(object sender, EventArgs e)  
        {  
            await Navigation.PushModalAsync(new AboutPage());  
        }  
  
        private async void MenuButton_Clicked(object sender, EventArgs e)  
        {  
            var page = new MenuPage(SelectModel);  
            page.ReturnValue += delegate (object s, OperatorModel operatorModel)  
            {  
                BackCall(s, operatorModel);  
            };  
            await Navigation.PushModalAsync(page);  
  
        }  
  
        private async void SendButton_Clicked(object sender, EventArgs e)  
        {  
            await Email.ComposeAsync();  
        }  
    }  
}

MenuPage.xaml

MenuPage.xaml.cs

using HandsFreeNotes.Model;  
using HandsFreeNotes.ViewModel;  
using System;  
using System.Collections.Generic;  
using System.Linq;  
using System.Text;  
using System.Threading.Tasks;  
  
using Xamarin.Forms;  
using Xamarin.Forms.Xaml;  
  
namespace HandsFreeNotes.View  
{  
    [XamlCompilation(XamlCompilationOptions.Compile)]  
    public partial class MenuPage : ContentPage  
    {  
  
        OperatorViewModel ovm;  
        // The line below added at the behest of LeonLu-MSFT (Microsoft Q&A) 14/05/2021  
        OperatorModel SelectModel;  
  
        // public MenuPage()  
        // The line above replaced with the line below at the behest of LeonLu-MSFT (Microsoft Q&A) 14/05/2021  
        public MenuPage(OperatorModel SelectModel)  
        {  
            // add a bit of padding to cater to the "notch" on the iPhone.  
            if (Device.RuntimePlatform == Device.iOS) {Padding = new Thickness(0, 40, 0, 0);}  
  
            InitializeComponent();  
  
            // The line below added at the behest of Leo

Accepted Answer

Hello,

Welcome to our Microsoft Q&A platform!

Based on your code, I created a demo and modified your database HFNDatabase . The insert and

You can refer to the following code:

HFNDatabase.cs public class HFNDatabase
{
static SQLiteAsyncConnection database;

    public static readonly AsyncLazy Instance = new AsyncLazy(async () =>  
    {  
        var instance = new HFNDatabase();  
        CreateTableResult result = await database.CreateTableAsync();  
        return instance;  
    });  

    public HFNDatabase()  
    {  
        database = new SQLiteAsyncConnection(Constants.DatabasePath, Constants.Flags);  
    }  

    public Task> GetOperatorsAsync()  
    {  
        return database.Table().ToListAsync();  
    }  

    public Task DeleteItemAsync(Models.OperatorModel item)  
    {  
        return database.DeleteAsync(item);  
    }  

    public Task SaveItemAsync(Models.OperatorModel item)  
    {  
        if (item.OperatorID != 0)  
        {  
            return database.UpdateAsync(item);  
        }  
        else  
        {  
            return database.InsertAsync(item);  
        }  
    }  
}

Constants.cs

 public static class Constants  
{  
    public const string DatabaseFilename = "OperatorsSQLite.db3";  
  
    public const SQLite.SQLiteOpenFlags Flags =  
        // open the database in read/write mode  
        SQLite.SQLiteOpenFlags.ReadWrite |  
        // create the database if it doesn't exist  
        SQLite.SQLiteOpenFlags.Create |  
        // enable multi-threaded database access  
        SQLite.SQLiteOpenFlags.SharedCache;  
  
    public static string DatabasePath  
    {  
        get  
        {  
            var basePath = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData);  
            return Path.Combine(basePath, DatabaseFilename);  
        }  
    }  
}

AsyncLazy.cs

public class AsyncLazy : Lazy>  
{  
    readonly Lazy> instance;  

    public AsyncLazy(Func factory)  
    {  
        instance = new Lazy>(() => Task.Run(factory));  
    }  

    public AsyncLazy(Func> factory)  
    {  
        instance = new Lazy>(() => Task.Run(factory));  
    }  

    public TaskAwaiter GetAwaiter()  
    {  
        return instance.Value.GetAwaiter();  
    }  
}

OperatorModel.cs

public class OperatorModel: INotifyPropertyChanged  
{  
    [PrimaryKey, AutoIncrement]  
    public int OperatorID { get; set; }  

    string _operatorName;  
    public string OperatorName  
    {  
        set { SetProperty(ref _operatorName, value); }  

        get { return _operatorName; }  
    }  


    public string OperatorPhone { get; set; }  
    public string OperatorEmail { get; set; }  
    public string OperatorAvatar { get; set; }  

    bool SetProperty(ref T storage, T value, [CallerMemberName] string propertyName = null)  
    {  
        if (Object.Equals(storage, value))  
            return false;  

        storage = value;  
        OnPropertyChanged(propertyName);  
        return true;  
    }  

    protected void OnPropertyChanged([CallerMemberName] string propertyName = null)  
    {  
        PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));  
    }  

    public event PropertyChangedEventHandler PropertyChanged;  
}

In page SelectOperatorPage.xaml, you can use data binding :

SelectOperatorPage.xaml.cs

   public partial class SelectOperatorPage : ContentPage  
    {  
        OperatorViewModel ovm;  
        public EventHandler ReturnValue;  
  
        public SelectOperatorPage()  
        {  
            // add a bit of padding to cater to the "notch" on the iPhone.  
            if (Device.RuntimePlatform == Device.iOS)  
            {  
                Padding = new Thickness(0, 40, 0, 0);  
            }  
  
            InitializeComponent();  
        }  
  
        protected override async void OnAppearing()  
        {  
            base.OnAppearing();  
            //await ovm.GetOperators();  
            //OperatorListView.ItemsSource = OperatorViewModel.OperatorList;  
  
            HFNDatabase database = await HFNDatabase.Instance;  
            OperatorListView.ItemsSource = await database.GetOperatorsAsync();  
  
        }  
  
        private async void BackButton_Clicked(object sender, EventArgs e)  
        {  
            await Navigation.PopModalAsync();  
        }  
  
        private async void OperatorListView_ItemSelected(object sender, SelectedItemChangedEventArgs e)  
        {  
            if (e.SelectedItem != null)  
            {  
  
                Models.OperatorModel operatorModel = (Models.OperatorModel)e.SelectedItem;  
                //ovm.SelectedOperator = operatorModel;  
                EventHandler handler = ReturnValue;  
                if (handler != null)  
                {  
                    handler(this, operatorModel);  
                }  
  
                await Navigation.PopModalAsync();  
            }  
        }  
    }

In NewOperatorPage.xaml.cs,we can create a new Operator by code await database.SaveItemAsync(todoItem);

[XamlCompilation(XamlCompilationOptions.Compile)]  
 public partial class NewOperatorPage : ContentPage  
 {  
     public NewOperatorPage()  
     {  
         // add a bit of padding to cater to the "notch" on the iPhone.  
         if (Device.RuntimePlatform == Device.iOS)  
         {  
             Padding = new Thickness(0, 40, 0, 0);  
         }  

         InitializeComponent();  
     }  

     private async void BackButton_Clicked(object sender, EventArgs e)  
     {  
         await Navigation.PopModalAsync();  
     }  

     private async void OKButton_Clicked(object sender, EventArgs e)  
     {  
         //var name = NameEntry.Text;  
         //var Phone = PhoneEntry.Text;  
         //var Email = EmailEntry.Text;  
         //var Avatar = AvatarEntry.Text;  
         //OperatorViewModel.OperatorList.Add(new OperatorModel(OperatorViewModel.OperatorList.Count, name, Phone, Email, Avatar));  
         //await Navigation.PopModalAsync();  

         var todoItem = (Models.OperatorModel)BindingContext;  
         HFNDatabase database = await HFNDatabase.Instance;  
         await database.SaveItemAsync(todoItem);  
         await Navigation.PopModalAsync();  
     }  

     private async void CancelButton_Clicked(object sender, EventArgs e)  
     {  
         await Navigation.PopModalAsync();  
     }  
 }

In NewOperatorPage.xaml, we can also use data binding for the several Entry.

In OperatorsPage.xaml.cs, I changed function AddOperatorButton_Clicked to the following code. In this page, you can also use data binding for SelectedOperatorEntry Entry .

 private async void AddOperatorButton_Clicked(object sender, EventArgs e)  
 {  
     await Navigation.PushModalAsync(new NewOperatorPage {   

         BindingContext = new OperatorModel()  
      });  
 }

The whole code of OperatorsPage .xaml.cs

   public partial class OperatorsPage : ContentPage  
    {  
       // OperatorViewModel ovm;  
        OperatorModel opm;  
        public EventHandler ReturnValue;  
  
        public OperatorsPage(OperatorModel SelectModel)  
  
        {  
            if (Device.RuntimePlatform == Device.iOS) { Padding = new Thickness(0, 40, 0, 0); }  
  
            InitializeComponent();  
  
            //if (SelectModel != null)  
            //{  
            //    opm = SelectModel;  
            //}  
            //else  
            //{  
            //    opm = new OperatorModel();  
            //}  
  
            opm = new OperatorModel();  
            BindingContext = opm;  
        }  
  
        private async void BackButton_Clicked(object sender, EventArgs e)  
        {  
            // The if statements below added at the behest of JessieZhang-2116 (Microsoft Q&A) 13/05/2021  
            if (SelectModel != null)  
            {  
                EventHandler handler = ReturnValue;  
                if (handler != null)  
                {  
                    handler(this, opm);  
                }  
            }  
  
            await Navigation.PopModalAsync();  
            // The line above originally commented out at the behest of JessieZhang-2116 (Microsoft Q&A) 01/05/2021  
        }  
  
        private async void AddOperatorButton_Clicked(object sender, EventArgs e)  
        {  
            await Navigation.PushModalAsync(new NewOperatorPage {   
              
                BindingContext = new OperatorModel()  
             });  
        }  
  
        private async void SelectOperatorButton_Clicked(object sender, EventArgs e)  
        {  
            // The lines below added at the behest of JessieZhang-2116 (Microsoft Q&A) 01/05/2021  
            SelectOperatorPage page = new SelectOperatorPage();  
            page.ReturnValue += delegate (object s, OperatorModel operatorModel)  
            {  
                BackCall(s, operatorModel);  
            };  
  
            await Navigation.PushModalAsync(page);  
        }  
        // The line below added at the behest of LeonLu-MSFT (Microsoft Q&A) 06/05/2021  
        OperatorModel SelectModel;  
  
  
        // The lines below added at the behest of JessieZhang-2116 (Microsoft Q&A) 01/05/2021  
        private void BackCall(object s, OperatorModel model)  
        {  
            SelectModel = model;  
            opm.OperatorName = model.OperatorName;  
        }  
  
        // The lines included below in the DeleteOperatorButton_Clicked added at the behest of LeonLu-MSFT (Microsoft Q&A) 06/05/2021  
        private async void DeleteOperatorButton_Clicked(object sender, EventArgs e)  
        {  
            if (SelectModel != null)  
            {  
                bool answer = await DisplayAlert("Warning!", "Do you really want to delete Operator " + SelectModel.OperatorName, "Yes", "No");  
  
                if (answer == true)  
                {  
                    OperatorViewModel.OperatorList.Remove(SelectModel);  
                    SelectModel = null;  
                    SelectedOperatorEntry.Text = "";  
                }  
            }  
            else  
            {  
                await DisplayAlert("Info", "Please select an Operator to delete", "OK");  
            }  
        }  
        // The lines below in UpdateOperatorButton_Clicked added at the behest of LeonLu-MSFT (Microsoft Q&A) 11/05/2021  
        private async void UpdateOperatorButton_Clicked(object sender, EventArgs e)  
        {  
  
            if (SelectModel != null)  
            {  
                var operatorInfoPage = new OperatorInfoPage(SelectModel);  
                operatorInfoPage.ReturnValue += delegate (object s, OperatorModel operatorModel)  
                {  
                    BackCall(s, operatorModel);  
                };  
                await Navigation.PushModalAsync(operatorInfoPage);  
            }  
            else  
            {  
                await DisplayAlert("Info", "Please select an Operator", "OK");  
            }  
        }  
    }

Note:

I just modified some of your code which is relative to the fuction of GetOperatorsAsync and SaveItemAsync in database HFNDatabase. It works on my side.

You can change above code according to your requirement.

Best Regards,

Jessie Zhang

---
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.

I need to refine the CRUD methodology in a MVVM app in readiness to host SQLite database functionality

0 additional answers