Adding a Local Database using SQLite and Xamarin.Forms

A promise is a promise. I am going to show you how to Add a SQLite database to a Xamarin.Forms application.

Continuing with the next step of the previous post, there are some steps we need to cover before we can integrate a local database to any Xamarin.Forms application.

If you missed the previous post and you don't know where you at right now or what is happening? Please consider completing the first part of the tutorial:

http://15mgm15.ghost.io/2017/02/08/connect-to-a-remote-database-using-xamarin-forms/

So before we start here is the GitHub repository to get you started:

https://github.com/15mgm15/Consume-a-JSON-API-using-Xamarin.Forms

1.- Download this packages on each project (iOS/Android)

Screen Shot 2017-02-12 at 15.03.37.png

  • The first one is from @JamesMontemagno and it will help us to tell if the device has an internet connection. (because what else do we need a local database...right?) https://github.com/jamesmontemagno/ConnectivityPlugin
  • The SQLite.NET PCL is the most important one and it is a fork of the original sqlite-net library.
  • The other two ones (SQLite.Net Extensions) it an amazing library to extend the SQLite functionality (It provides complex relationships on top of SQLite-Net by using reflection. For Example: (1:n) relation)

2.- On your MainActivity.cs and the AppDelegateClass please add this code in stead of the normal LoadApplication method:

iOS
LoadApplication(new App(new SQLite.Net.Platform.XamarinIOS.SQLitePlatformIOS()));

Android
LoadApplication(new App(new SQLite.Net.Platform.XamarinAndroid.SQLitePlatformAndroid()));

And change the App.cs class constructor to include this parameter and set the Database platform:
public App(ISQLitePlatform platform){ DataLayer.Instance.SetDataBasePlatform(platform); ... }

2.5 Important (Android Only)

Add ACCESS_NETWORK_STATE and ACCESS_WIFI_STATE permissions.

3.- Now we are going to start modifying and adding the Database layer itself to our project, The Database.cs class is going to wrap the SQLiteConnection the creation of the Database file, its directory and the most important thing the creation of the tables along side to the extension methods of the most common queries: Insert, Delete, Update and Select.

4.- Next the DataLayer that will contain and instance of the Database class and the methods that are going to decide if we are going to show the Local data or the Remote Data:

5.- Our WebApi.cs class is going to have a couple of changes so we can detect if the user has an internet connection or not:

6.- Remember our currency Model? well now it is going to have base class called BaseModel.cs, The BaseModel is going to be the father of all our Models now:

As you can see the database will have a Table with the same name of our Model and it will also have an Id and Update DateTime and a Creation DateTime that may help us in a near future.

The last thing to do will be to change the use of the DataLayer instead of the WepApi inside the CurrencyListViewModel.cs

_currencyList = await DataLayer.Instance.GetRemoteOrLocalCurrenciesAsync();

Now If you run the application and if the device has a internet connection, the GetRemoteOrLocalCurrenciesAsync method will try to Truncate the Currency Table/Model and then create Database objects every time we start the application, if the device is offline this method will try to get the data back from the local Database :)

Where to go from here?

Databases are a very important part of an application, going further with this topic may involve:

  • Performance improvements to sync the data with the server side.
  • A background thread with a timer to refresh the data every X minutes.
  • Conflict Resolution between local and remote objets while this syncing progress is occurring.

But maybe the most important one may be:
Pull To Refresh lady's and gentleman! our next topic will be how to include Pull to refresh to our ListViews using Xamarin.Forms.

As always please let me know any comments or questions, I will be glad to answer them!

Mario Jesús Galván Miranda

I am a Mexican Mobile developer and a Xamarin Enthusiast.

Guadalajara