Web Development
Published May 09th, 2017 by

Setting up SQLite .Net in Xamarin for iOS, Android and Windows

SQLite has become the accepted industry standard database for mobile apps in the last 10 years, because of its lightweight and open source nature. It is highly portable, since it does not run on server and does not require any major configuration. Just create an SQLite file and bundle it locally with your application and you are ready to roll!

Although it is cross-platform compatible because of its single file format, it has a minor glitch on the Windows based mobile apps. Android and iOS have built-in support for SQLite through their native libraries, while some programming is involved in making SQLite run with Windows apps.

The SQLite engine exposes a C++ API that can be accessed through .Net by using a C# wrapper. Any third party API that provides a higher-level C# API for this purpose can be used. There are several options like SQLite Mono, which has easy .Net coding resources. However, its performance is hampered because Windows support is not available in addition to the usual Android and iOS support. There is a similar predicament with Microsoft library offering for SQLite, as there is no adequate documentation.

SQLite in Xamarin

Methods of using SQLite in Xamarin

When coding with Xamarin, SQLite.Net is the ORM (Object Relational Mapping) API that allows accessing the database objects without writing SQL statements. In this tutorial, experts of xamarin app development India will help you to focus on how to integrate SQLite.Net with the mobile platforms.

There are basically two ways of doing this:

  1. Use NuGet

The SQLite.Net PCL NuGet package supports all 3 platforms – iOS, Android, and Windows.

  1. Use Component Store

Xamarin component store has SQLite.Net available for iOS and Android only.

Hence, when developing for all the 3 major platforms, it makes sense to use the NuGet package.

Setting up SQLite .Net in Xamarin

iOS, Android, and Windows (after configuration)

The following points walk you through the process of using SQLite .Net, which is the same for both approaches:

  1. Add a using statement
  1. Create a blank database
vardb=newSQLiteConnection (dbPath);
  1. Save data
db.CreateTable<Stock> (); db.Insert (newStock); // after creating the newStock object
  1. Retrieve data
var stock =db.Get<Stock>(10); // primary key id of 10varstockList=db.Table<Stock>();

Configuring SQLite .Net

For Windows, there is an additional process of activating the SQLite runtime. When you download the SQLite .Net PCL NuGet package in Xamarin, install the component authored by Frank Krueger. This will create two packages:

  1. SQLite-net-PCL

This is the core assembly that contains code to identify the data entities and high level classes that create queries to access those entities.

  1. raw

This provides the C# wrapper around the C++ APIs exposed by SQLite. This is used internally by the SQLite-net-PCL package to access the database. Unless you wish to use the API programmatically, it is not required to code for this package.

Special instructions for Windows

In case of Windows though, where native library support for accessing SQLite is not available, it is necessary to activate the SQL runtime. This database engine is written in C++ and included as a binary in the raw package. In order for it to function, there is a dependency on visual C++ runtime.

For this, a reference needs to be added in the system.

  1. In the section of Windows Phone in the Xamarin Studio, open the References.
  2. Go to extensions.
  3. Select Visual Studio C++ 2013 runtime. (Please note that even though later versions are available, only this runtime should be used as the raw package is compiled against it. This applies for Windows 8, 8.1, and uwp as well.)

This step is crucial as a type initialization error is encountered if this reference is not added, and the SQLite will not be able to load the runtime DLL.

Now that you have covered setting up SQLite.Net on all 3 platforms, you are set to use the lightweight database in Xamarin.

Rosina De Palma
Follow Me

Rosina De Palma

Technical Writer at Nex Mobility
I write technical articles especially for iOS, Android and Xamarin mobile app development. Analyse on Cross Platform Apps and interested to learn android app development.
Rosina De Palma
Follow Me

Our rankings are completely independent, transparent, and community driven; they are based on user reviews and client sentiment. These web development companies had to earn their way up and didn't just pay their way up.

View Rankings of Best Web Development Companies