Xamarin Forms MVVM and SQLite

This is how I use Xamarin Forms MVVM and SQLite. Create ViewModels for each page (view) you have and make sure each ViewModel inherits the BaseViewModel (#7 below).

  1. In my Xaml page (view) I set the Page’s BindingContext and then bind the ListView’s ItemSource to an ObservableCollection called “Items”.

2. I create my ViewModel class called ListOfItemsViewModel.cs

namespace MyApp.ViewModels
{
public class ListOfItemsViewModel : BaseViewModel
{
public Command LoadToDoDataCommand { get; set; }
public Command UseNoSQLCommand { get; set; }
public Command LoadSQLTableCommand { get; set; }
ObservableCollection<Item> _items { get; set; }
public ObservableCollection<Item> Items
{
get { return _items; }
set
{
_items = value;
OnPropertyChanged("Items");
}
}

private bool _IsListVisible = false;
public bool IsListVisible
{
get { return _IsListVisible; }
set { SetProperty(ref _IsListVisible, value); }
}

private bool _IsMessageVisible = false;
public bool IsMessageVisible
{
get { return _IsMessageVisible; }
set { SetProperty(ref _IsMessageVisible, value); }
}

private double _ListHeight = 2000;
public double ListHeight
{
get { return _ListHeight; }
set { SetProperty(ref _ListHeight, value); }
}
//
public ICommand RefreshCommand
{
get
{
return new Command(async () =>
{
IsRefreshing = true;

await Task.Run(() => TodoData());

IsRefreshing = false;
});
}
}

public ListOfItemsViewModel()
{
Items = new ObservableCollection<Item>();
ListHeight = DeviceDisplay.MainDisplayInfo.Height;
IsListVisible = true;
LoadToDoDataCommand = new Command(async () => await Task.Run(() => TodoData()));
LoadToDoDataCommand.Execute(null);

LoadSQLTableCommand = new Command(async () => await Task.Run(() => GetFromServer()));
LoadSQLTableCommand .Execute(null); 

MessagingCenter.Subscribe<App>(this, "ItemAddedRefresh", async (isTrue) =>
{
await Task.Run(() =>
{
RefreshCommand.Execute(null);
});

});
}

//get initial data and save to SQLite
async Task GetFromServer()
{
Services.SyncService srv = new Services.SyncService();
ObservableCollection<Item> items = new ObservableCollection<Item>();
items = await srv.GetDataFromServer();
if (items.Count > 0)
{
DB.sqlDB sql = new DB.sqlDB();
foreach (Item i in items)
{
await sql.AddNewItem(i);
}
//Message = "Sync FROM Server Complete. You have " + items.Count + " new items";
}
else
{
//Message = "No Items";
}
}

async Task TodoData()
{
//get Items from SQLite
DB.sqlDB sqlDB = new DB.sqlDB();
Items = await sqlDB.GetItems();

if (Items.Count == 0)
{
IsListVisible = false;
IsMessageVisible = true;
Message = @"You have no To-Do items. Please add some." + Environment.NewLine
+ "Select the Settings wheel on the bottom left to GET a default item or " + Environment.NewLine
+ "Select the Add icon on the bottom right to add new items.";
}
else
{
IsMessageVisible = false;
IsListVisible = true;
Message = string.Empty;
}
}
}

3. I create my model class called Item.cs

using System;
using System.Collections.Generic;
using System.Text;

using SQLite;

namespace MyApp.Models
{
public class Item
{
[PrimaryKey, AutoIncrement]
public int ItemID { get; set; }
public string ItemTitle { get; set; }
public string ItemText { get; set; }
public DateTime ItemCreateDate { get; set; }
public bool ItemComplete { get; set; }
public string ItemCompleteDisplay { get; set; }
public string ItemCreateDateDisplay { get; set; }
}
}

4. I create another Class called sqlDB.cs which builds the sql Tables based off of what your Item.cs model is.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using SQLite;
using MyApp.Models;
using System.Collections.ObjectModel;
namespace MyApp.DB
{
public class sqlDB
{
static readonly Lazy<SQLiteAsyncConnection> lazyInitializer = new Lazy<SQLiteAsyncConnection>(() =>
{
return new SQLiteAsyncConnection(DB.DBConstants.DatabasePath, DBConstants.Flags);
});

static SQLiteAsyncConnection Database => lazyInitializer.Value;
static bool initialized = false;

public sqlDB()
{
InitializeAsync().SafeFireAndForget(false);
}

async Task InitializeAsync()
{
if (!initialized)
{
if (!Database.TableMappings.Any(m => m.MappedType.Name == typeof(Item).Name))
{
await Database.CreateTablesAsync(CreateFlags.None, typeof(Item)).ConfigureAwait(false);
initialized = true;
}
}
}
public async Task<ObservableCollection<Item>> GetItems() //GetItemsAsync()
{
List<Item> sort = new List<Item>();
sort = await Database.Table<Item>().ToListAsync();
ObservableCollection<Item> list = new ObservableCollection<Item>();
foreach(Item i in sort.OrderBy(x => x.ItemID))
{
if (i.ItemComplete)
{
i.ItemCompleteDisplay = "Completed";
i.ItemCreateDateDisplay = string.Empty;
}
else
{
i.ItemCompleteDisplay = string.Empty;
i.ItemCreateDateDisplay = i.ItemCreateDate.ToString("MM/dd/yyyy");
}
list.Add(i);
}
return list;
}

public Task<Item> GetItemAsync(int id)
{
return Database.Table<Item>().Where(i => i.ItemID == id).FirstOrDefaultAsync();
}

public async Task<bool> AddNewItem(Item item)
{
int i = await Database.InsertAsync(item);
return true;
}
public async Task<bool> UpdateItem(Item item)
{
if (item.ItemID != 0)
{
int i = await Database.UpdateAsync(item);
return true;
}
else
{
return false;
}
}
public async Task<bool> DeleteItem(Item item)
{
int i = await Database.DeleteAsync(item);
return true;
}
}
}

5. I create a static class called DBConstants.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;

namespace MyApp.DB
{
public static class DBConstants
{

public const string DBName = "Item";
public const string DatabaseFilename = "SQLite.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);
}
}
}
}

6. And finally when I run the App and need to load the SQL tables with data, I run a Task from the ViewModel and call a webservice from somewhere out in the world and add the item(s) to the table

async Task GetFromServer()
{
Services.SyncService srv = new Services.SyncService();
ObservableCollection<Item> items = new ObservableCollection<Item>();
items = await srv.GetDataFromServer();
if (items.Count > 0)
{
DB.sqlDB sql = new DB.sqlDB();
foreach (Item i in items)
{
await sql.AddNewItem(i);
}

//Message = "Sync FROM Server Complete. You have " + items.Count + " new items";
}
else
{
//Message = "No Items";
}
}

7. I Create a BaseViewModel that is inherited in all my ViewModels so you can reuse properties like “IsBusy” to show or hide an ActivityIndicator

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Runtime.CompilerServices;
using System.Text;

namespace MyApp.ViewModels
{
public class BaseViewModel : INotifyPropertyChanged
{
public event PropertyChangedEventHandler PropertyChanged;
public bool SetProperty<T>(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));
}

private string _pageTitle = string.Empty;
public string PageTitle
{
get { return _pageTitle; }
set { SetProperty(ref _pageTitle, value); }
}

private string _message = string.Empty;
public string Message
{
get { return _message; }
set { SetProperty(ref _message, value); }
}
private bool isEditing = false;
public bool IsEditing
{
get { return isEditing; }
set { SetProperty(ref isEditing, value); }
}
private bool _IsLoader = false;
public bool IsLoader
{
get { return _IsLoader; }
set { SetProperty(ref _IsLoader, value); }
}
private bool _isRefreshing = false;
public bool IsRefreshing
{
get { return _isRefreshing; }
set { SetProperty(ref _isRefreshing, value); }
}
private bool _UseNoSQL = false;
public bool UseNoSQL
{
get { return _UseNoSQL; }
set { SetProperty(ref _UseNoSQL, value); }
}
}
}
11140cookie-checkXamarin Forms MVVM and SQLite