In the previous Xamarin post we saw How to use SkiaSharp library to create different Shapes. In this post we will create SQLite database in Xamarin forms application and add data to it. We will use MVVM architecture to code our app. The SQLite database will contain information about Products and its pricing. So let’s begin.
To begin with you need to install sqlite-net-pcl nuget package in your project.
Application will contain two forms
- ProductPage.xaml – For Displaying Products in SQLite database
- AddProduct.xaml – For Adding Products to the database.
My Project structure:
Create a folder in your project with name Database and add following classes inside of this folder.
Constants.cs:
using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
namespace XamSQLite.Database
{
public class Constants
{
public const string dbName = "Products.db3";
public const SQLite.SQLiteOpenFlags Flags = SQLite.SQLiteOpenFlags.ReadWrite |
SQLite.SQLiteOpenFlags.Create | SQLite.SQLiteOpenFlags.SharedCache;
public static string DatabasePath
{
get
{
var basePath = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData);
return Path.Combine(basePath, dbName);
}
}
}
}
This class contains the name and file path of our SQLite database. You can change location of the database by editing DatabasePath string as per your requirements.
TaskExtensions.cs:
using System;
using System.Threading.Tasks;
namespace XamSQLite.Database
{
public static class TaskExtensions
{
public static async void SafeFireAndForget(this Task task,
bool returnToCallingContext,
Action<Exception> onException = null)
{
try
{
await task.ConfigureAwait(returnToCallingContext);
}
catch (Exception ex) when (onException != null)
{
onException(ex);
}
}
}
}
TaskExtensions class will help in creating a database connection in a asynchronous form. This class is being called from the main class below, where we are initializing the SQLite connection object.
ProductDatabase.cs:
using SQLite;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using XamSQLite.Models;
namespace XamSQLite.Database
{
public class ProductDatabase
{
static readonly Lazy<SQLiteAsyncConnection> lazyInitializer = new Lazy<SQLiteAsyncConnection>(() =>
{
return new SQLiteAsyncConnection(Constants.DatabasePath, Constants.Flags);
});
static SQLiteAsyncConnection Database => lazyInitializer.Value;
static bool initialized = false;
public ProductDatabase()
{
InitializeAsync().SafeFireAndForget(false);
}
async Task InitializeAsync()
{
if (!initialized)
{
if (!Database.TableMappings.Any(m => m.MappedType.Name == typeof(Products).Name))
await Database.CreateTablesAsync(CreateFlags.None, typeof(Products)).ConfigureAwait(false);
initialized = true;
}
}
public Task<List<Products>> getProducts()
{
return Database.Table<Products>().ToListAsync();
}
public Task<int> saveProduct(Products product)
{
if (product.ID == 0)
return Database.InsertAsync(product);
else
return Database.UpdateAsync(product);
}
public Task<int> deleteProduct(Products product)
{
return Database.DeleteAsync(product);
}
}
}
This class will create SQLite connection and the table of our Model class (Product.cs) which we are using. The SQLite.NET provides us with Object Relational Mapping (ORM) that allows us to store and retrieve objects without writing SQL statements. You can use SQLite queries also. But here I’m using its inbuilt functionality.
Now create a folder named Models (MVVM structure) and create following model class inside of it.
Models > Products.cs:
using SQLite;
namespace XamSQLite.Models
{
public class Products
{
[PrimaryKey,AutoIncrement]
public int ID { get; set; }
public string Name { get; set; }
public double Price { get; set; }
public int Quantity { get; set; }
}
}
This is our database class and it is also used it ViewModel.
Now create Views folder (MVVM structure) and create following ContentLayout files:
AddProduct.xaml:
<?xml version="1.0" encoding="utf-8" ?>
<ContentPage xmlns="http://xamarin.com/schemas/2014/forms"
xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
x:Class="XamSQLite.Views.AddProduct"
Title="Add Products"
Visual="Material"
BackgroundColor="White">
<ContentPage.Content>
<StackLayout Orientation="Vertical" HorizontalOptions="FillAndExpand" VerticalOptions="FillAndExpand"
Padding="10">
<Entry Text="{Binding product.ID}" Margin="0" FontSize="1" IsVisible="false"
HorizontalOptions="FillAndExpand"/>
<Label Text="Product Name:" Margin="2"/>
<Entry Text="{Binding product.Name}" Margin="2,2,2,5" HorizontalOptions="FillAndExpand"/>
<Label Text="Price" Margin="2"/>
<Entry Text="{Binding product.Price}" Margin="2,2,2,5"
HorizontalOptions="FillAndExpand" Keyboard="Numeric"/>
<Label Text="Quantity" Margin="2"/>
<Entry Text="{Binding product.Quantity}" Margin="2,2,2,5"
HorizontalOptions="FillAndExpand" Keyboard="Numeric"/>
<Label Text="{Binding lblInfo}" Margin="2,2,2,5"/>
<Grid HorizontalOptions="FillAndExpand">
<Grid.ColumnDefinitions>
<ColumnDefinition Width="1*"/>
<ColumnDefinition Width="1*"/>
</Grid.ColumnDefinitions>
<Button Text="SAVE" x:Name="btnSave" HorizontalOptions="FillAndExpand"
BackgroundColor="#ff6633" TextColor="#fff"
Margin="2" Grid.Column="0" Grid.Row="0" Command="{Binding btnSaveProduct}"/>
<Button Text="CLEAR" x:Name="btnClear" HorizontalOptions="FillAndExpand"
BackgroundColor="#bfbfbf" TextColor="#fff"
Margin="2" Grid.Column="1" Grid.Row="0" Command="{Binding btnClearProduct}"/>
</Grid>
</StackLayout>
</ContentPage.Content>
</ContentPage>
Using this form we will add Products to the SQLite database.
Edit its code-behind like below. Please ignore the error of view model, we will add it shortly.
Views > AddProducts.xaml > AddProducts.xaml.cs:
using System;
using Xamarin.Forms;
using Xamarin.Forms.Xaml;
using XamSQLite.ViewModels;
namespace XamSQLite.Views
{
[XamlCompilation(XamlCompilationOptions.Compile)]
public partial class AddProduct : ContentPage
{
public AddProduct()
{
try
{
InitializeComponent();
VMAddProduct vm = new VMAddProduct();
this.BindingContext = vm;
}
catch(Exception ex)
{
}
}
}
}
This is one of the many benefits of Xamarin forms MVVM pattern. Your code-behind of design files will be pretty clean and all the UI elements will be controlled by ViewModel class (i.e. VMAddProduct)
Create another Layout file with name ProductPage.xaml and edit it as below
Views > ProductPage.xaml:
<?xml version="1.0" encoding="utf-8" ?>
<ContentPage xmlns="http://xamarin.com/schemas/2014/forms"
xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
x:Class="XamSQLite.Views.ProductPage"
Visual="Material"
Title="Products"
Background="White">
<ContentPage.Content>
<Grid HorizontalOptions="FillAndExpand" VerticalOptions="FillAndExpand">
<Grid.RowDefinitions>
<RowDefinition Height="8*"/>
<RowDefinition Height="1*"/>
<RowDefinition Height="1*"/>
</Grid.RowDefinitions>
<ListView x:Name="lstProduct" ItemsSource="{Binding lstProducts}"
BackgroundColor="Transparent"
Grid.Row="0" Grid.Column="0" Margin="2">
<ListView.ItemTemplate>
<DataTemplate>
<ViewCell>
<StackLayout Margin="20,0,0,0" Orientation="Horizontal"
HorizontalOptions="FillAndExpand" BackgroundColor="Transparent">
<Label Text="{Binding Name}" VerticalTextAlignment="Center"
HorizontalOptions="StartAndExpand" BackgroundColor="Transparent"/>
</StackLayout>
</ViewCell>
</DataTemplate>
</ListView.ItemTemplate>
</ListView>
<Label Text="{Binding lblInfo}" Grid.Row="1" Grid.Column="0"
Margin="2"/>
<Button x:Name="btnAddProduct" Text="Add New Product" Clicked="btnAddProduct_Clicked"
Margin="2"
Grid.Row="2" Grid.Column="0" BackgroundColor="#ff6633" TextColor="#fff"/>
</Grid>
</ContentPage.Content>
</ContentPage>
This form will display the products added to SQLite database in a listView. The Add New button will open AddProducts form which we created before this one.
And edit its code-behind like below:
Views > ProductPage.xaml > ProductPage.xaml.cs:
using System;
using Xamarin.Forms;
using Xamarin.Forms.Xaml;
using XamSQLite.ViewModels;
namespace XamSQLite.Views
{
[XamlCompilation(XamlCompilationOptions.Compile)]
public partial class ProductPage : ContentPage
{
VMProducts vm;
public ProductPage()
{
InitializeComponent();
vm = new VMProducts();
this.BindingContext = vm;
}
protected override void OnAppearing()
{
if (vm != null)
vm.GetProducts();
base.OnAppearing();
}
private async void btnAddProduct_Clicked(object sender, EventArgs e)
{
await Navigation.PushAsync(new AddProduct());
}
}
}
Now create a new folder with name ViewModels (MVVM structure) and create following ViewModel classes inside it.
ViewModels > VMAddProduct.cs:
using System;
using System.ComponentModel;
using System.Runtime.CompilerServices;
using Xamarin.Forms;
using XamSQLite.Database;
using XamSQLite.Models;
namespace XamSQLite.ViewModels
{
public class VMAddProduct : INotifyPropertyChanged
{
private Products _products { get; set; }
public Products product
{
get { return _products; }
set
{
_products = value;
OnPropertyChanged();
}
}
private string _lblInfo { get; set; }
public string lblInfo
{
get { return _lblInfo; }
set
{
_lblInfo = value;
OnPropertyChanged();
}
}
public Command btnSaveProduct { get; set; }
public Command btnClearProduct { get; set; }
public VMAddProduct()
{
product = new Products();
product.ID = 0;
product.Name = "";
product.Price = 0;
product.Quantity = 0;
lblInfo = "";
btnSaveProduct = new Command(SaveProduct);
btnClearProduct = new Command(ClearProduct);
}
public void SaveProduct()
{
try
{
ProductDatabase productDatabase = new ProductDatabase();
int i = productDatabase.saveProduct(product).Result;
if (i == 1)
{
ClearProduct();
lblInfo = "Your Product saved successfully.";
}
else
lblInfo = "Cannot save Product Information";
}
catch (Exception ex)
{
lblInfo = ex.Message.ToString();
}
}
public void ClearProduct()
{
product = new Products();
product.ID = 0;
product.Name = "";
product.Price = 0;
product.Quantity = 0;
lblInfo = "";
}
public event PropertyChangedEventHandler PropertyChanged;
private void OnPropertyChanged([CallerMemberName] string propertyName = "")
{
if (PropertyChanged != null)
{
PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
}
}
}
}
Here, we are initializing a blank Product class with name product, whose reference we used on the design file AddProduct.xaml. Textbox on this design form is being bonded to this class. And on pressing the SAVE button SaveProduct() function is called. This functions passes Product class with details entered in the form to the database class to save it in SQLite database. On successfully saving records in Database it displays message on the Label control.
ViewModels > VMProducts.cs:
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.ComponentModel;
using System.Runtime.CompilerServices;
using System.Text;
using Xamarin.Forms;
using XamSQLite.Database;
using XamSQLite.Models;
namespace XamSQLite.ViewModels
{
public class VMProducts : INotifyPropertyChanged
{
private ObservableCollection<Products> _lstProducts { get; set; }
public ObservableCollection<Products> lstProducts
{
get { return _lstProducts; }
set
{
_lstProducts = value;
OnPropertyChanged();
}
}
public Command btnAddProduct { get; set; }
private string _lblInfo { get; set; }
public string lblInfo
{
get { return _lblInfo; }
set
{
_lblInfo = value;
OnPropertyChanged();
}
}
public VMProducts()
{
lstProducts = new ObservableCollection<Products>();
}
public void GetProducts()
{
try
{
ProductDatabase productDatabase = new ProductDatabase();
var products = productDatabase.getProducts().Result;
if (products != null && products.Count > 0)
{
lstProducts = new ObservableCollection<Products>();
foreach (var prod in products)
{
lstProducts.Add(new Products
{
ID = prod.ID,
Name = prod.Name,
Price = prod.Price,
Quantity = prod.Quantity
});
}
lblInfo = "Total " + products.Count.ToString() + " record(s) found";
}
else
lblInfo = "No product records found. Please add one";
}
catch (Exception ex)
{
lblInfo = ex.Message.ToString();
}
}
public event PropertyChangedEventHandler PropertyChanged;
private void OnPropertyChanged([CallerMemberName] string propertyName = "")
{
if (PropertyChanged != null)
{
PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
}
}
}
}
This class will display records from SQLite database on a ListView using ObservableCollections of class Products. Edit the App.cs class to set ProductPage form as default form of the Application.
App.xaml.cs:
using Xamarin.Forms;
using XamSQLite.Views;
namespace XamSQLite
{
public partial class App : Application
{
public App()
{
InitializeComponent();
MainPage = new NavigationPage(new ProductPage());
}
protected override void OnStart()
{
}
protected override void OnSleep()
{
}
protected override void OnResume()
{
}
}
}
Now run your application. In the Next post I will share how to update, delete and export data in CSV format from Xamarin forms SQLite Database. Thanks for reading. Please like our Facebook Page to get regular updates on latest post.