Xamarin.Forms MVVM How does the ViewModel Interact with the Database?

Question

I am completely new to the MVVM pattern, and I've looked all over for information about working with MVVM and SQLite, and I'm still totally lost. I have a project that has several models that populate tables in my database. I have a database service that adds, edits, sets, and gets the objects for and from my tables. The ViewModel has an ObservableCollection of objects, which appear in the View, but how is that updated in my database? So basically, I don't want to populate my ObservableCollection inside of the ViewModel. I want to populate it from the corresponding table in the database. I just don't understand, if the ViewModel's ObservableCollection is being updated from the View, what then updates the information in my Database? To add, the get method in my database service returns the table ToList. I considered trying to do something in my ViewModel like setting the ObservableCollection's property to my get method, but that doesn't work, because one is a list and one is an ObservableCollection. Any thoughts or advice on this would be super helpful. Thanks everybody.

Answer 1

If I understand the question, you're looking for the two-way glue between the database and the ObservableCollection that your view is bound to. I remember being in that "totally lost" place and wanting an explanation from the ground up in uncomplicated terms and have decided to post a slightly longer-than-usual answer because this is what would have helped me.

If you read though, I will explain how to Insert, Update and Delete records and also query a SQLite local database, displaying the returned recordsets in a ListView like so:

In the beginning your View binding might be something very minimal like this:

<StackLayout>
    <ListView ItemsSource="{Binding Recordset}" />
    <Grid>
        <Button Grid.Column="0"
                Text="Query" 
                Command="{Binding QueryCommand}"/>
        <Button Grid.Column="1"
                Text="Clear"
                Command="{Binding ClearCommand}" />
    </Grid>
</StackLayout>

Where the BindingContext has been set like so:

public partial class MainPage : ContentPage
{
    public MainPage()
    {
        BindingContext = new MainPageBinding();
        InitializeComponent();
    }
}
class MainPageBinding : INotifyPropertyChanged
{
    public ObservableCollection<Record> Recordset { get; } = 
        new ObservableCollection<Record>();
    .
    .
    .
}

And this is going to display a Record/Model that looks similar to this:

[Table("items")]
class Record
{
    [PrimaryKey]
    public string guid { get; set; } = Guid.NewGuid().ToString().Trim().TrimStart('{').TrimEnd('}');
    public string Description { get; set; } = string.Empty;
    public override string ToString() => Description;
}

At this point, each and any new Record that we put into Recordset shows up in the view. Specifically, you want to create these records by doing an SQLite query.

public ICommand QueryCommand { get; private set; }
private void OnQuery(object o)
{
    Recordset.Clear();
    List<Record> queryResult;
    using (var cnx = new SQLiteConnection(mockConnectionString))
    {
        queryResult = cnx.Query<Record>("SELECT * FROM items");
        foreach (var record in queryResult)
        {
            Recordset.Add(record);
        }
    }
}

Now this is just one approach, right? But I hope it gives you some ideas. I uploaded my working example to GitHub if you'd like to experiment with it.

Now for the other direction.

(This will answer the part of the question if the ViewModel's ObservableCollection is being updated from the View, what then updates the information in my Database?)

Suppose a Description editor pops up so you can edit SelectedItem. When committed it sets the Description property of the SQLite record.

We can simulate that interaction like so:

    public ICommand EditCommand { get; private set; }
    private void OnEdit(object o)
    {
        if(SelectedItem != null)
        {
            // Some kind of UI interaction that changes the bound record
            SelectedItem.Description = $"{SelectedItem.Description} (Edited)";

            // You'll need to decide what kind of UI action merits a SQL
            // update, but when you're ready to do that here's the command:
            using (var cnx = new SQLiteConnection(mockConnectionString))
            {
                cnx.Update(SelectedItem);
            }
        }
    }

Meanwhile we've modified Record to implement INotifyPropertyChanged...

[Table("items")]
class Record : INotifyPropertyChanged
{
    [PrimaryKey]
    public string guid { get; set; } = Guid.NewGuid().ToString().Trim().TrimStart('{').TrimEnd('}');

    public event PropertyChangedEventHandler PropertyChanged;
    protected virtual void OnPropertyChanged([CallerMemberName] string propertyName = null)
    {
        PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
    }
    string _Description = string.Empty;
    public string Description
    {
        get => _Description;
        set
        {
            if(_Description != value)
            {
                _Description = value;
                OnPropertyChanged();
            }
        }
    }
    public override string ToString() => Description;
} 

...and since we want the view to update not only on changes to the Collection but to programmatic changes to individual properties we need a data template in the xaml now to properly display the bound Description property:

<StackLayout>
    <ListView ItemsSource="{Binding Recordset}"
              SelectedItem="{Binding SelectedItem}">
        <ListView.ItemTemplate>
            <DataTemplate>
                <ViewCell>
                    <Grid>
                        <Label Text="{Binding Description}" />
                    </Grid>
                </ViewCell>
            </DataTemplate>
        </ListView.ItemTemplate>
    </ListView>
    <Grid>
        <Button Grid.Column="0"
                Text="Query" 
                Command="{Binding QueryCommand}"/>
        <Button Grid.Column="1"
                Text="Clear"
                Command="{Binding ClearCommand}" />
        <Button Grid.Column="2"
                Text="Edit"
                Command="{Binding EditCommand}" />
    </Grid>
</StackLayout>

OK! So Select:

Click the Edit button:

Click the Clear button:

Now click the Query button to confirm that the database is updated.

INSERT and DELETE operations

To ADD a new record:

    public ICommand AddCommand { get; private set; }
    private async void OnAdd(object o)
    {
        var result = await App.Current.MainPage.DisplayPromptAsync("New Item", "Describe the item");
        if(!string.IsNullOrWhiteSpace(result))
        {
            var newRecord = new Record { Description = result };
            Recordset.Add(newRecord);

            using (var cnx = new SQLiteConnection(mockConnectionString))
            {
                cnx.Insert(newRecord);
            }
        }
    }

To DELETE:

    public ICommand DeleteSelectedCommand { get; private set; }
    private void OnDeleteSelected(object o)
    {
        if (SelectedItem != null)
        {
            var removed = SelectedItem;
            Recordset.Remove(SelectedItem);
            using (var cnx = new SQLiteConnection(mockConnectionString))
            {
               cnx.Delete(removed);
            }
        }
    }

SEARCH BAR

To have a comprehensive answer to How does the ViewModel Interact with the Database? requires one more thing: a Search Bar on the main page so we can perform simple queries on the new database: This is the final version of code posted on GitHub.

TO SEARCH:

public ICommand SearchCommand { get; }
private void OnSearch(string expr)
{
    Recordset.Clear();
    List<Record> queryResult;
    using (var cnx = new SQLiteConnection(mockConnectionString))
    {
        queryResult = cnx.Query<Record>($"SELECT * FROM items WHERE Description LIKE'%{expr}%'");
        foreach (var record in queryResult)
        {
            Recordset.Add(record);
        }
    }
}