Google Xamarin.Forms MVVM: SQLite Sample for Beginners (C# - Xaml) | SubramanyamRaju Xamarin & Windows App Dev Tutorials

Saturday 24 March 2018

Xamarin.Forms MVVM: SQLite Sample for Beginners (C# - Xaml)

Introduction:
This article will show you step by step guide on how to use an SQLite database with a Xamarin.Forms application in Android and iOS. 

Requirements:
  • This article source code is prepared by using Visual Studio Community for Mac (7.4). And it is better to install latest visual studio updates from here.
  • This sample project is Xamarin.Forms PCL project and tested in Android emulator and iOS simulators.
  • Storage: Used SQLite.Net-PCL Nuget package version is 3.1.1
  • Validation: Used FluentValidation Nuget Package version is V7.5.2.

Description:
In this article we will take a sample that would helpful to understand SQLite operations in Xamarin.Forms and sample project will have below functionality.
  • Add Contact (AddContact, AddContactViewModel): Insert new contact details to SQLite database. Also will have a option to "View All Contacts" if contacts exist in database.
  • Contact List (ContactList, ContactListViewModel): Read all contacts from SQLite and displaying contact list. Also will have a option to delete all/add the contact.
  • Contact Details (DetailsPage, DetailsViewModel): Read specific contact from SQLite and showing full information for the same. Also will have a option to delete/update the contact.
So this article can explain you below concepts:
1. How to create Xamarin.Forms PCL project with Visual Studio for Mac?
2. How to setup SQLite and perform SQLite operations in Xamarin.Forms PCL project?
3. How to validate Contact Details with Fluent Validation before data insert?
4. How to use default MVVM to create ViewModels in Xamarin.Forms app?
5. How to bind Contact Views with ViewModels?

1. How to create Xamarin.Forms PCL project using Visual studio for Mac?
First we need to create the new Xamarin.Forms project. 
  • Launch Visual Studio for Mac.
  • On the File menu, select New Solution.
  • The New Project dialog appears. In the left pane Multiplatform App Xamarin.Forms > Blank Forms App and click on Next.
  • Enter your App Name (Ex: SQLiteSample). Select Target Platforms to Android & iOS and Shared Code to Portable Class Library  after that click on Next button.


  • You can choose your project location like below and Create new project.

And project structure will be.
  • SQLiteSample: It is for PCL shared code.
  • SQLiteSample.Droid: It is for Android.
  • SQLiteSample.iOS: It is for iOS.
2. How to setup SQLite and perform SQLite operations in Xamarin.Forms PCL project?
After creating Xamarin.Forms application, we need a managed way to access SQLite database. For that we need to add relevant nuget package name is SQLite.Net-PCL to PCL, Android & iOS projects. SQLite.Net-PCL is a .NET wrapper around SQLite that will allow us to access the native SQLite functionality from a Xamarin.Forms PCL or shared project.
So Right on your PCL project Packages and click on Add Packages.
After that Add Packages dialog window will open, search for "SQLite.Net-PCL" and click on Add Package like below.

After that above package will add to your Xamarin.PCL project like below.

Also please add above package to SQLiteSample.Droid and SQLiteSample.iOS projects by following same above steps like SQLiteSample PCL project. And make sure SQLite.Net-PCL same version package should add to Android and iOS project Packages folder like below.

We completed the SQLite setup for our xamarin forms project. So it is time to perform all SQLite(Create, Read, Update, Delete, Insert) operations. But before that even tough the "SQLite.Net-PCL" package will provide us the functionality of manipulating the SQLite database, it can’t automatically initialize the database connection object as the location of the database file varies on different platforms. So in order to solve this issue we will use dependency service to load the database file in connection object and please follow below few steps.

Step 1: Create Interface (ISQLite) in PCL.
We are following default MVVM design pattern. And here we will place helpers in Helpers folder. So right click on your project name SQLiteSample => Add => New Folder name is "Helpers". After that right click on your newly created the folder => Add => New File => General => Empty Interface and name it ISQLite and add below code:




  • using System;  
  • using SQLite.Net;  
  •   
  • namespace SQLiteSample.Helpers  
  • {  
  •     public interface ISQLite  
  •     {  
  •         SQLiteConnection GetConnection();  
  •     }  
  • }  

  • Step 2: Android Implementation for ISQLite.
    In SQLiteSample.Droid project, create folder "Implementations" and create class "AndroidSQLite". After that add below code:
    1. using System.IO;  
    2. using SQLite.Net;  
    3. using SQLiteSample.Droid.Implementaions;  
    4. using SQLiteSample.Helpers;  
    5.   
    6. [assembly: Xamarin.Forms.Dependency(typeof(AndroidSQLite))]  
    7. namespace SQLiteSample.Droid.Implementations {  
    8.     public class AndroidSQLite : ISQLite {  
    9.         public SQLiteConnection GetConnection() {  
    10.             string documentsPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);  
    11.   
    12.             // Documents folder  
    13.             var path = Path.Combine(documentsPath, DatabaseHelper.DbFileName);  
    14.             var plat = new SQLite.Net.Platform.XamarinAndroid.SQLitePlatformAndroid();  
    15.             var conn = new SQLiteConnection(plat, path);  
    16.   
    17.             // Return the database connection  
    18.             return conn;  
    19.         }  
    20.     }  
    21. }  
    Step 3: iOS Implementation for ISQLite.
    In SQLiteSample.iOS project, create folder "Implementations" and create class "iOSSQLite". After that add below code:
    1. using System;  
    2. using System.IO;  
    3. using Xamarin.Forms;  
    4. using SQLite.Net;  
    5. using SQLiteSample.Helpers;  
    6. using SQLiteSample.iOS.Implementaions;  
    7.   
    8. [assembly: Dependency(typeof(IOSSQLite))]  
    9. namespace SQLiteSample.iOS.Implementations {  
    10.     public class IOSSQLite : ISQLite {  
    11.         public SQLiteConnection GetConnection() {  
    12.             string documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal); // Documents folder  
    13.             string libraryPath = Path.Combine(documentsPath, "..""Library"); // Library folder  
    14.             var path = Path.Combine(libraryPath, DatabaseHelper.DbFileName);  
    15.             // Create the connection  
    16.             var plat = new SQLite.Net.Platform.XamarinIOS.SQLitePlatformIOS();  
    17.             var conn = new SQLiteConnection(plat, path);  
    18.             // Return the database connection  
    19.             return conn;  
    20.         }  
    21.     }  
    22. }  
    Step 4: Performing SQLite operations with Database Helper class in PCL
    In SQLiteSample PCL project, create a class in Helpers folder that should have methods to perform SQLite operation (create, insert, delete, read, update).
    1. using SQLite.Net;  
    2. using Xamarin.Forms;  
    3. using System.Collections.Generic;  
    4. using System.Linq;  
    5. using SQLiteSample.Models;  
    6. using System;  
    7.   
    8. namespace SQLiteSample.Helpers {  
    9.     public class DatabaseHelper {  
    10.           
    11.         static SQLiteConnection sqliteconnection;  
    12.         public const string DbFileName = "Contacts.db";  
    13.   
    14.         public DatabaseHelper() {  
    15.             sqliteconnection = DependencyService.Get<ISQLite>().GetConnection();  
    16.             sqliteconnection.CreateTable<ContactInfo>();  
    17.         }  
    18.   
    19.         // Get All Contact data      
    20.         public List<ContactInfo> GetAllContactsData() {  
    21.             return (from data in sqliteconnection.Table<ContactInfo>()  
    22.                     select data).ToList();  
    23.         }  
    24.   
    25.         //Get Specific Contact data  
    26.         public ContactInfo GetContactData(int id) {  
    27.             return sqliteconnection.Table<ContactInfo>().FirstOrDefault(t => t.Id == id);  
    28.         }  
    29.   
    30.         // Delete all Contacts Data  
    31.         public void DeleteAllContacts() {  
    32.             sqliteconnection.DeleteAll<ContactInfo>();  
    33.         }  
    34.   
    35.         // Delete Specific Contact  
    36.         public void DeleteContact(int id) {  
    37.             sqliteconnection.Delete<ContactInfo>(id);  
    38.         }  
    39.   
    40.         // Insert new Contact to DB   
    41.         public void InsertContact(ContactInfo contact) {  
    42.             sqliteconnection.Insert(contact);  
    43.         }  
    44.   
    45.         // Update Contact Data  
    46.         public void UpdateContact(ContactInfo contact) {  
    47.             sqliteconnection.Update(contact);  
    48.         }  
    49.     }  
    50. }  
    In above SQLite helper class, we created queries for ContactInfo Table means that we need to create ContactInfo class that should have columns.
    So create Models folder in PCL and add below Table class.
    1. using System;  
    2. using SQLite.Net.Attributes;  
    3.   
    4. namespace SQLiteSample.Models  
    5. {  
    6.     [Table("ContactInfo")]  
    7.     public class ContactInfo  
    8.     {  
    9.         [PrimaryKey, AutoIncrement]  
    10.         public int Id { getset; }  
    11.         public string Name { getset; }  
    12.         public string Age { getset; }  
    13.         public string Gender { getset; }  
    14.         public DateTime DOB { getset; }  
    15.         public string Address { getset; }  
    16.         public string MobileNumber { getset; }  
    17.     }  
    18. }  
    Please note that in above class, Id property is marked as the Primary Key and AutoIncrement which will helpful for contact row identification.
    Step 5: Create interface for ContactInfo Table operations
    We will create service that will talk to only ContactInfo Table. So create Services folder in PCL and add below IContactRepository interface
    1. using System;  
    2. using System.Collections.Generic;  
    3. using SQLiteSample.Models;  
    4.   
    5. namespace SQLiteSample.Servcies  
    6. {  
    7.     public interface IContactRepository  
    8.     {  
    9.         List<ContactInfo> GetAllContactsData();  
    10.   
    11.         //Get Specific Contact data  
    12.         ContactInfo GetContactData(int contactID);  
    13.   
    14.         // Delete all Contacts Data  
    15.         void DeleteAllContacts();  
    16.   
    17.         // Delete Specific Contact  
    18.         void DeleteContact(int contactID);  
    19.   
    20.         // Insert new Contact to DB   
    21.         void InsertContact(ContactInfo contact);  
    22.   
    23.         // Update Contact Data  
    24.         void UpdateContact(ContactInfo contact);  
    25.     }  
    26. }  
    And after that create a class that should implement above interface.
    1. using System.Collections.Generic;  
    2. using SQLiteSample.Helpers;  
    3. using SQLiteSample.Models;  
    4.   
    5. namespace SQLiteSample.Servcies  
    6. {  
    7.     public class ContactRepository : IContactRepository  
    8.     {  
    9.         DatabaseHelper _databaseHelper;  
    10.         public ContactRepository(){  
    11.             _databaseHelper = new DatabaseHelper();  
    12.         }  
    13.   
    14.         public void DeleteContact(int contactID)  
    15.         {  
    16.             _databaseHelper.DeleteContact(contactID);  
    17.         }  
    18.   
    19.         public void DeleteAllContacts()  
    20.         {  
    21.             _databaseHelper.DeleteAllContacts();  
    22.         }  
    23.   
    24.         public List<ContactInfo> GetAllContactsData()  
    25.         {  
    26.             return _databaseHelper.GetAllContactsData();  
    27.         }  
    28.   
    29.         public ContactInfo GetContactData(int contactID)  
    30.         {  
    31.             return _databaseHelper.GetContactData(contactID);  
    32.         }  
    33.   
    34.         public void InsertContact(ContactInfo contact)  
    35.         {  
    36.             _databaseHelper.InsertContact(contact);  
    37.         }  
    38.   
    39.         public void UpdateContact(ContactInfo contact)  
    40.         {  
    41.             _databaseHelper.UpdateContact(contact);  
    42.         }  
    43.     }  
    44. }  

    3. How to validate Contact Details with Fluent Validation?
    It will be always good to validate details before save them to database. In this article we are validating ContactInfo table information before inserting into SQLite. Please read to understand how to validate particular class object with FluentValidation from this article.

    In SQLiteSample PCL project, we have to add ContactValidator class in Validator folder.
    1. using FluentValidation;  
    2. using SQLiteSample.Models;  
    3.   
    4. namespace SQLiteSample.Validator  
    5. {  
    6.     public class ContactValidator : AbstractValidator<ContactInfo>    
    7.     {    
    8.         public ContactValidator()    
    9.         {    
    10.             RuleFor(c => c.Name).Must(n=>ValidateStringEmpty(n)).WithMessage("Contact name should not be empty.");  
    11.             RuleFor(c => c.MobileNumber).NotNull().Length(10);  
    12.             RuleFor(c => c.Age).Must(a => ValidateStringEmpty(a)).WithMessage("Contact Age should not be empty.");  
    13.             RuleFor(c => c.Gender).Must(g => ValidateStringEmpty(g)).WithMessage("Contact Gender should not be empty.");  
    14.             RuleFor(c => c.DOB).Must(d => ValidateStringEmpty(d.ToString())).WithMessage("Contact DOB should not be empty.");  
    15.             RuleFor(c => c.Address).Must(a => ValidateStringEmpty(a)).WithMessage("Contact Adress should not be empty.");  
    16.         }    
    17.   
    18.         bool ValidateStringEmpty(string stringValue){  
    19.             if (!string.IsNullOrEmpty(stringValue))  
    20.                 return true;  
    21.             return false;  
    22.         }  
    23.     }   
    24. }  

    4. How to use default MVVM to create ViewModels in Xamarin.Forms app?
    It is best practices to write business logic in view models before start to build views user interface. And we are adding functionality for the view models below.
    • AddContactViewModel: Saving new contact details in database. 
    • ContactListViewModel: Read all contacts from database.
    • DetailsViewModel: Read specific contact from database and delete/update same contact.

    Let's start write business logic to viewmodels
    AddContactViewModel
    Above UI screen is for AddContactView. So ViewModel should have below properties and commands.
    Properties: Name, Mobile Number, Age, Gender, DOB, Address. And these properties will be the same for Contact DetailsViewModel. So we will add these properties into common view model name is BaseContactViewModel.
    1. using System;  
    2. using System.Collections.Generic;  
    3. using System.ComponentModel;  
    4. using System.Runtime.CompilerServices;  
    5. using FluentValidation;  
    6. using SQLiteSample.Helpers;  
    7. using SQLiteSample.Models;  
    8. using SQLiteSample.Servcies;  
    9. using Xamarin.Forms;  
    10.   
    11. namespace SQLiteSample.ViewModels  
    12. {  
    13.     public class BaseContactViewModel : INotifyPropertyChanged {  
    14.   
    15.         public ContactInfo _contact;  
    16.   
    17.         public INavigation _navigation;  
    18.         public IValidator _contactValidator;  
    19.         public IContactRepository _contactRepository;  
    20.   
    21.         public string Name    
    22.         {    
    23.             get => _contact.Name;   
    24.             set{  
    25.                 _contact.Name = value;  
    26.                 NotifyPropertyChanged("Name");  
    27.             }  
    28.         }     
    29.   
    30.         public string MobileNumber    
    31.         {  
    32.             get => _contact.MobileNumber;   
    33.             set {   
    34.                 _contact.MobileNumber = value;   
    35.                 NotifyPropertyChanged("MobileNumber");  
    36.             }  
    37.         }    
    38.   
    39.         public string Age    
    40.         {  
    41.             get => _contact.Age;   
    42.             set {   
    43.                 _contact.Age = value;   
    44.                 NotifyPropertyChanged("Age");  
    45.             }  
    46.         }   
    47.   
    48.         public string Gender    
    49.         {  
    50.             get => _contact.Gender;   
    51.             set {   
    52.                 _contact.Gender = value;   
    53.                 NotifyPropertyChanged("Gender");  
    54.             }  
    55.         }   
    56.   
    57.         public DateTime DOB    
    58.         {  
    59.             get => _contact.DOB;   
    60.             set {  
    61.                 _contact.DOB = value;   
    62.                 NotifyPropertyChanged("DOB");  
    63.             }  
    64.         }   
    65.   
    66.         public string Address    
    67.         {  
    68.             get => _contact.Address;   
    69.             set {   
    70.                 _contact.Address = value;   
    71.                 NotifyPropertyChanged("Address");  
    72.             }  
    73.         }  
    74.   
    75.         List<ContactInfo> _contactList;  
    76.         public List<ContactInfo> ContactList  
    77.         {  
    78.             get => _contactList;  
    79.             set  
    80.             {  
    81.                 _contactList = value;  
    82.                 NotifyPropertyChanged("ContactList");  
    83.             }  
    84.         }  
    85.  
    86.         #region INotifyPropertyChanged      
    87.         public event PropertyChangedEventHandler PropertyChanged;  
    88.         protected void NotifyPropertyChanged([CallerMemberName] string propertyName = ""){  
    89.             PropertyChanged?.Invoke(thisnew PropertyChangedEventArgs(propertyName));  
    90.         }  
    91.         #endregion  
    92.     }  
    93. }  
    In above BaseContactViewModel, we also added below common interfaces
    • INavigation: For page navigation
    • IValidator: To validae contact object.
    • IContactRepository: To access ContactInfo Table operations.
    Commands: 
    • AddContactCommand(for Save Contact Button click)
    • ViewAllContactsCommand (View All Contacts Label tap). 
    So create ViewModels folder in PCL project and add below AddContactViewModel class.
    1. using System.Threading.Tasks;  
    2. using System.Windows.Input;  
    3. using SQLiteSample.Helpers;  
    4. using SQLiteSample.Models;  
    5. using SQLiteSample.Servcies;  
    6. using SQLiteSample.Validator;  
    7. using SQLiteSample.Views;  
    8. using Xamarin.Forms;  
    9.   
    10. namespace SQLiteSample.ViewModels {  
    11.     public class AddContactViewModel : BaseContactViewModel {  
    12.   
    13.         public ICommand AddContactCommand { getprivate set; }  
    14.         public ICommand ViewAllContactsCommand { getprivate set; }  
    15.   
    16.         public AddContactViewModel(INavigation navigation){  
    17.             _navigation = navigation;  
    18.             _contactValidator = new ContactValidator();  
    19.             _contact = new ContactInfo();  
    20.             _contactRepository = new ContactRepository();  
    21.   
    22.             AddContactCommand = new Command(async () => await AddContact());   
    23.             ViewAllContactsCommand = new Command(async () => await ShowContactList());   
    24.         }  
    25.   
    26.         async Task AddContact() {  
    27.             var validationResults = _contactValidator.Validate(_contact);      
    28.       
    29.             if (validationResults.IsValid){  
    30.                 bool isUserAccept = await Application.Current.MainPage.DisplayAlert("Add Contact""Do you want to save Contact details?""OK""Cancel");  
    31.                 if (isUserAccept) {  
    32.                     _contactRepository.InsertContact(_contact);  
    33.                     await _navigation.PushAsync(new ContactList());  
    34.                 }  
    35.             }      
    36.             else {      
    37.                 await Application.Current.MainPage.DisplayAlert("Add Contact", validationResults.Errors[0].ErrorMessage, "Ok");      
    38.             }       
    39.         }  
    40.   
    41.         async Task ShowContactList(){   
    42.             await _navigation.PushAsync(new ContactList());  
    43.         }  
    44.   
    45.         public bool IsViewAll => _contactRepository.GetAllContactsData().Count > 0 ? true : false;  
    46.     }  
    47. }  
    In AddContact method, we are validating ContactInfo object with help of ContactValidator before insert new contact to database. 
    ContactListViewModel
    Above UI screen is for ContactListView. So ViewModel should have below properties and commands.
    Properties: ContactList, SelectedContactItem
    CommandsAddCommand, DeleteAllContactsCommand
    In PCL project ViewModels folder, Add below ContactListViewModel class.

    1. using System.Threading.Tasks;  
    2. using System.Windows.Input;  
    3. using SQLiteSample.Models;  
    4. using SQLiteSample.Servcies;  
    5. using SQLiteSample.Views;  
    6. using Xamarin.Forms;  
    7.   
    8. namespace SQLiteSample.ViewModels  
    9. {  
    10.     public class ContactListViewModel : BaseContactViewModel {  
    11.   
    12.         public ICommand AddCommand { getprivate set; }  
    13.         public ICommand DeleteAllContactsCommand { getprivate set; }  
    14.   
    15.         public ContactListViewModel(INavigation navigation) {  
    16.             _navigation = navigation;  
    17.             _contactRepository = new ContactRepository();  
    18.   
    19.             AddCommand = new Command(async () => await ShowAddContact());   
    20.             DeleteAllContactsCommand = new Command(async () => await DeleteAllContacts());  
    21.   
    22.             FetchContacts();  
    23.         }  
    24.   
    25.         void FetchContacts(){  
    26.             ContactList = _contactRepository.GetAllContactsData();  
    27.         }  
    28.   
    29.         async Task ShowAddContact() {  
    30.             await _navigation.PushAsync(new AddContact());  
    31.         }  
    32.   
    33.         async Task DeleteAllContacts(){  
    34.             bool isUserAccept = await Application.Current.MainPage.DisplayAlert("Contact List""Delete All Contacts Details ?""OK""Cancel");  
    35.             if (isUserAccept){  
    36.                 _contactRepository.DeleteAllContacts();  
    37.                 await _navigation.PushAsync(new AddContact());  
    38.             }  
    39.         }  
    40.   
    41.         async void ShowContactDetails(int selectedContactID){  
    42.             await _navigation.PushAsync(new DetailsPage(selectedContactID));  
    43.         }  
    44.   
    45.         ContactInfo _selectedContactItem;  
    46.         public ContactInfo SelectedContactItem {  
    47.             get => _selectedContactItem;  
    48.             set {  
    49.                 if (value != null){  
    50.                     _selectedContactItem = value;  
    51.                     NotifyPropertyChanged("SelectedContactItem");  
    52.                     ShowContactDetails(value.Id);  
    53.                 }  
    54.             }  
    55.         }  
    56.     }  
    57. }  
       
    DetailsViewModel
    Above UI screen is for Contact DetailsView. So ViewModel should have below properties and commands.
    Properties: All properties of BaseContactViewModel as it is very similar to AddContactViewModel.
    CommandsUpdateContactCommand, DeleteContactCommand
    In PCL project ViewModels folder, Add below DetailsViewModel class.
    1. using System.Threading.Tasks;  
    2. using System.Windows.Input;  
    3. using SQLiteSample.Helpers;  
    4. using SQLiteSample.Models;  
    5. using SQLiteSample.Servcies;  
    6. using SQLiteSample.Validator;  
    7. using Xamarin.Forms;  
    8.   
    9. namespace SQLiteSample.ViewModels {  
    10.     public class DetailsViewModel: BaseContactViewModel {  
    11.   
    12.         public ICommand UpdateContactCommand { getprivate set; }  
    13.         public ICommand DeleteContactCommand { getprivate set; }  
    14.   
    15.         public DetailsViewModel(INavigation navigation, int selectedContactID) {  
    16.             _navigation = navigation;  
    17.             _contactValidator = new ContactValidator();  
    18.             _contact = new ContactInfo();  
    19.             _contact.Id = selectedContactID;  
    20.             _contactRepository = new ContactRepository();  
    21.   
    22.             UpdateContactCommand = new Command(async () => await UpdateContact());  
    23.             DeleteContactCommand = new Command(async () => await DeleteContact());  
    24.   
    25.             FetchContactDetails();  
    26.         }  
    27.   
    28.         void FetchContactDetails(){  
    29.             _contact = _contactRepository.GetContactData(_contact.Id);  
    30.         }  
    31.   
    32.         async Task UpdateContact() {  
    33.             var validationResults = _contactValidator.Validate(_contact);  
    34.   
    35.             if (validationResults.IsValid) {  
    36.                 bool isUserAccept = await Application.Current.MainPage.DisplayAlert("Contact Details""Update Contact Details""OK""Cancel");  
    37.                 if (isUserAccept) {  
    38.                     _contactRepository.UpdateContact(_contact);  
    39.                     await _navigation.PopAsync();  
    40.                 }  
    41.             }  
    42.             else {  
    43.                 await Application.Current.MainPage.DisplayAlert("Add Contact", validationResults.Errors[0].ErrorMessage, "Ok");  
    44.             }  
    45.         }  
    46.   
    47.         async Task DeleteContact() {  
    48.             bool isUserAccept = await Application.Current.MainPage.DisplayAlert("Contact Details""Delete Contact Details""OK""Cancel");  
    49.             if (isUserAccept) {  
    50.                 _contactRepository.DeleteContact(_contact.Id);  
    51.                 await _navigation.PopAsync();  
    52.             }  
    53.         }  
    54.     }  
    55. }  
    5. How to bind Contact Views with ViewModels?
    We completed the business logic in all above view models and now we can create UI in Views folder and need bind it to the view models.
    Add Contact
    We need to create a page that can provide UI to insert new contact details. If we observe the above UI android screens, AddContact and Contacts Details UI is almost similar. So we can build common UI with help of ContentView and then reuse it in AddContact and Details Pages.

    So create Views folder in PCL and add below ContentView. Make sure UI elements should bind to relevant view model properties (Name, Mobile Number, Age, Gender, DOB, Address).
    1. <?xml version="1.0" encoding="UTF-8"?>  
    2. <ContentView xmlns="http://xamarin.com/schemas/2014/forms" xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml" x:Class="SQLiteSample.Views.ContactView">  
    3.      <StackLayout Spacing="12">  
    4.         <Entry x:Name="txtContactName" Text="{Binding Name}" HeightRequest="40" BackgroundColor="White" Placeholder="Contact Name" HorizontalOptions="FillAndExpand"/>  
    5.         <Entry  x:Name="txtMobileNumber" Keyboard="Telephone" Text="{Binding MobileNumber}" BackgroundColor="White" HeightRequest="40" Placeholder="Mobile Number" HorizontalOptions="FillAndExpand"/>  
    6.         <StackLayout Orientation="Horizontal">  
    7.           <Entry x:Name="txtAge" Text="{Binding Age}" HeightRequest="40" BackgroundColor="White" Placeholder="Age" HorizontalOptions="FillAndExpand"/>  
    8.           <Picker x:Name="GenderPicker" SelectedItem="{Binding Gender}" Title="Gender" BackgroundColor="White" HeightRequest="40" HorizontalOptions="FillAndExpand">  
    9.                 <Picker.ItemsSource>  
    10.                     <x:Array Type="{x:Type x:String}">  
    11.                         <x:String>Male</x:String>  
    12.                         <x:String>FeMale</x:String>  
    13.                     </x:Array>  
    14.                 </Picker.ItemsSource>  
    15.             </Picker>  
    16.         </StackLayout>  
    17.           <DatePicker x:Name="txtDOB" Date="{Binding DOB}" BackgroundColor="White" HeightRequest="40" HorizontalOptions="FillAndExpand"/>  
    18.          <Entry x:Name="txtAddress" Text="{Binding Address}" BackgroundColor="White"  HeightRequest="40" Placeholder="Address"  HorizontalOptions="FillAndExpand"/>  
    19.     </StackLayout>  
    20. </ContentView>  
    Declare above ContentView name space in Xaml to reuse it in AddContact.
    1. <?xml version="1.0" encoding="UTF-8"?>  
    2. <ContentPage xmlns="http://xamarin.com/schemas/2014/forms" xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml" xmlns:local="clr-namespace:SQLiteSample.Views">  
    3.     <StackLayout Padding="20" Spacing="12">  
    4.         <local:ContactView />  
    5.     </StackLayout>  
    6. </ContentPage>  
    Now in Views folder create AddContact.xaml page and add below xaml code
    1. <?xml version="1.0" encoding="UTF-8"?>  
    2. <ContentPage xmlns="http://xamarin.com/schemas/2014/forms" Title="Add Contact" BackgroundColor="#D5E7FA" xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"  
    3.  x:Class="SQLiteSample.Views.AddContact" xmlns:local="clr-namespace:SQLiteSample.Views">  
    4.     <StackLayout Padding="20" Spacing="12">  
    5.         <local:ContactView />  
    6.         <Button x:Name="SubmitButton" Command="{Binding AddContactCommand}" BorderRadius="0" Text="Save Contact" FontAttributes="Bold" TextColor="White" BackgroundColor="#5989B5"/>  
    7.         <Label Text="View All Contacts" IsVisible="{Binding IsViewAll}" x:Name="ViewLbl" TextColor="Black" HorizontalOptions="EndAndExpand" FontSize="15">    
    8.         <Label.GestureRecognizers>    
    9.         <TapGestureRecognizer Command="{Binding ViewAllContactsCommand}" NumberOfTapsRequired="1" />    
    10.         </Label.GestureRecognizers>    
    11.         </Label>    
    12.     </StackLayout>  
    13. </ContentPage>  
    Set BindingContext with viewmodel in AddContact.xaml.cs like below
    1. using SQLiteSample.ViewModels;  
    2. using Xamarin.Forms;  
    3.   
    4. namespace SQLiteSample.Views  
    5. {  
    6.     public partial class AddContact : ContentPage {  
    7.         public AddContact() {  
    8.             InitializeComponent();  
    9.             BindingContext = new AddContactViewModel(Navigation);  
    10.         }  
    11.     }  
    12. }  
    Contact List
    We need to create a page that can display all contacts details, also to add new contact details and delete all contacts.
    In Views folder, create ContactList.xaml and add below code.
    1. <?xml version="1.0" encoding="UTF-8"?>  
    2. <ContentPage xmlns="http://xamarin.com/schemas/2014/forms" Title="Contact List" xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml" x:Class="SQLiteSample.Views.ContactList">  
    3.    <Grid Padding="10,20,10,40">    
    4.         <Grid.RowDefinitions>    
    5.             <RowDefinition Height="Auto" />    
    6.             <RowDefinition Height="*" />    
    7.             <RowDefinition Height="Auto" />    
    8.         </Grid.RowDefinitions>    
    9.         <Grid Grid.Row="0">    
    10.         <Grid.ColumnDefinitions>    
    11.         <ColumnDefinition Width="*" />    
    12.         <ColumnDefinition Width="*" />    
    13.         </Grid.ColumnDefinitions>   
    14.         <Button Text="New Contact" Grid.Row="0" Grid.Column="1" FontAttributes="Bold" BorderRadius="0" HeightRequest="40" BorderColor="Black" BackgroundColor="Transparent" BorderWidth="1"  TextColor="Black"  Command="{Binding AddCommand}" />    
    15.         </Grid>    
    16.         <ListView x:Name="ContactListView" HasUnevenRows="true" Grid.Row="1" SeparatorColor="Black" ItemsSource="{Binding ContactList}"  
    17.          SelectedItem="{Binding SelectedContactItem, Mode=TwoWay}">    
    18.             <ListView.ItemTemplate>    
    19.                 <DataTemplate>    
    20.                     <ViewCell>    
    21.                             <Grid  Padding="10">    
    22.                                 <Grid.RowDefinitions>    
    23.                                     <RowDefinition Height="Auto" />    
    24.                                     <RowDefinition Height="Auto" />    
    25.                                 </Grid.RowDefinitions>    
    26.                                 <Grid.ColumnDefinitions>    
    27.                                     <ColumnDefinition Width="Auto" />    
    28.                                     <ColumnDefinition Width="*" />    
    29.                                 </Grid.ColumnDefinitions>    
    30.                                 <Label Text="{Binding Name}" Grid.Row="0" Font="20" TextColor="Black" />   
    31.                                 <Label Text="{Binding MobileNumber}" Grid.Row="0" Font="20" Grid.Column="1" HorizontalOptions="EndAndExpand" HorizontalTextAlignment="End" TextColor="Black" />    
    32.                                 <Label Text="{Binding Address}" Grid.Row="1" Grid.Column="0" HorizontalOptions="FillAndExpand" TextColor="Black" />    
    33.                         </Grid>    
    34.                     </ViewCell>    
    35.                 </DataTemplate>    
    36.             </ListView.ItemTemplate>    
    37.         </ListView>   
    38.         <Button Text="Delete All Contacts" Grid.Row="2" BorderRadius="0" VerticalOptions="EndAndExpand" FontAttributes="Bold" TextColor="White" BackgroundColor="#5989B5" Command="{Binding DeleteAllContactsCommand}" />    
    39.     </Grid>    
    40. </ContentPage>    
    In Above xaml code we added
    • Two buttons (New Contact, Delete All Contacts)
    • ListView (To display contacts list and to set ItemsSource ContactList)  

    Now set BindingContext with viewmodel in ContactList.xaml.cs like below
    1. using System;  
    2. using SQLiteSample.Helpers;  
    3. using SQLiteSample.Models;  
    4. using SQLiteSample.ViewModels;  
    5. using Xamarin.Forms;  
    6.   
    7. namespace SQLiteSample.Views {  
    8.     public partial class ContactList : ContentPage {  
    9.         public ContactList() {  
    10.             InitializeComponent();  
    11.         }  
    12.   
    13.         protected override void OnAppearing() {  
    14.             this.BindingContext = new ContactListViewModel(Navigation);  
    15.         }  
    16.     }  
    17. }  
    DetailsPage:
    We need to create a page that can display particular contact details, also to update/delete contact details.
    In Views folder, create DetailsPage.xaml and add below code.
    1. <?xml version="1.0" encoding="UTF-8"?>  
    2. <ContentPage xmlns="http://xamarin.com/schemas/2014/forms" Title="Contact Details" BackgroundColor="#D5E7FA"  
    3.              xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml" xmlns:local="clr-namespace:SQLiteSample.Views" x:Class="SQLiteSample.Views.DetailsPage">  
    4.     <StackLayout Padding="20" Spacing="12">  
    5.         <local:ContactView />  
    6.          <Button Text="Update" BorderRadius="0" Margin="0,30,0,0" FontAttributes="Bold" TextColor="White" BackgroundColor="#5989B5" Command="{Binding UpdateContactCommand}" />    
    7.         <Button Text="Delete" BorderRadius="0" Margin="0,30,0,0" FontAttributes="Bold" TextColor="White" BackgroundColor="#5989B5" Command="{Binding DeleteContactCommand}" />    
    8.     </StackLayout>  
    9. </ContentPage>  
    Please note that in above xaml, we are reusing the ContactView. Also we added two extra buttons (Delete, Update).
    Now set BindingContext with viewmodel in DetailsPage.xaml.cs like below
    1. using SQLiteSample.ViewModels;  
    2. using Xamarin.Forms;  
    3.   
    4. namespace SQLiteSample.Views {  
    5.     public partial class DetailsPage : ContentPage {  
    6.           
    7.         public DetailsPage(int contactID) {  
    8.             InitializeComponent();  
    9.             this.BindingContext = new DetailsViewModel(Navigation, contactID);  
    10.         }  
    11.     }  
    12. }  


    Demo screens from iOS:
    You can directly work on below sample source code to understand the this article. 

    SQLiteSample

    You can also see to run this article source code from below youtube video. Also for more videos please don't forget to SUBSCRIBE our youtube channel from 
    here.


    FeedBack Note: Please share your thoughts, what you think about this post, Is this post really helpful for you? I always welcome if you drop comments on this post and it would be impressive.

    Follow me always at @Subramanyam_B
    Have a nice day by  :)

    17 comments:

    1. This article is excellent. Thank you.

      ReplyDelete
    2. Hi Author just now i found your blog its really awesome. Keep this work. It will more helpful for xamarin app developers. Hire Xamarin Developer

      ReplyDelete
    3. Error CS0246 The type or namespace name 'SQLite' could not be found (are you missing a using directive or an assembly reference?) SQLiteSample C:\Users\1011696\source\repos\SQLiteSample\SQLiteSample\SQLiteSample\Helpers\ISQLite .cs


      Help to resolve the issue. Tried in many ways. Could not sort

      ReplyDelete
    4. Just started on Xamarin I already managed to work with SQLite internally but this is a huge kick-starter for me on MVVM. Thank you.

      ReplyDelete
    5. Unhandled Exception: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. occurred - getting this error

      ReplyDelete
    6. Nice article !
      one question, in case we have a few models, what kind of changes should be done in Database Helper ? Or there should be created helper file for each model ?

      ReplyDelete

    7. Can you send the link from the source code? above doesn't work

      ReplyDelete
    8. Why was this link directed to https://docs.microsoft.com/en-us/samples/browse/?redirectedfrom=MSDN-samples. Where can I get the source code.. Great Article I am getting an exception null pointer database is not being connected sqliteconnection = DependencyService.Get().GetConnection();
      System.NullReferenceException: 'Object reference not set to an instance of an object.'


      Source code would help
      Thanks

      ReplyDelete
    9. Why is the code link directed to https://docs.microsoft.com/en-us/samples/browse/?redirectedfrom=MSDN-samples. Where can I get the a link to the source its great example and I have learned a lot just compiling it on my own.
      But

      I am getting a System.NullReferenceException: 'Object reference not set to an instance of an object.' from this line
      sqliteconnection = DependencyService.Get().GetConnection(); GetConnection is not connecting to the database and passing the pointer back

      Thanks

      ReplyDelete
    10. Great article - where is the source code please?

      Thanks

      ReplyDelete
    11. Hello Guys
      Sorry say that, Microsoft broken all MSDN code sample gallery links. So I have to start prepare this article source code again and need to push them to Github. Will share the download link as soon as possible.

      ReplyDelete
    12. hi can you please give me the source code link..i really need it..thanks

      ReplyDelete
    13. Great article!!! But where is the source code please?

      Thanks

      ReplyDelete
    14. I found the source code here: https://github.com/microsoftarchive/msdn-code-gallery-community-s-z/tree/master/SQLite%20sample%20for%20beginners%20in%20Xamarin.Forms%20(C%23%20-%20Xaml)

      In order to download, you need to download the whole archive and extract out only what you want: https://github.com/microsoftarchive/msdn-code-gallery-community-s-z

      Thanks for the article.

      ReplyDelete
    15. Hi Guys,

      I updated the download link, please click "Download Code" from bottom of the article.

      Thanks
      Subramanyam Raju

      ReplyDelete

    Search Engine Submission - AddMe