Getting Started With SQLite

Now that we know where to store a database on each of our platforms, let's move on to integrating a SQLite database into our Project. A SQLite is a lightweight local database and it's become the industry standard for mobile apps. Also, it's open source. It does not run on a Server and doesn't require any configuration. SQLite database is stored in a single file in the local file system with those reads and writes being performed against this file then obviously, SQLite is cross-platform making it an ideal choice to use across your Android, iOS, and Windows apps.

Now, the SQLite native libraries are built into Android and iOS but they are not present in Windows, which means that if you are targeting any of the Windows platforms, then it must be added to your application. Now, we are going to use a nuGet package which includes a version of the SQLite runtime when it is added to a Windows application

However, there is one extra step you will need to perform for Windows, which we will talk about in just a minute but first, let's take a deeper look at SQLite. The SQLite engine exposes a C++ API, which is an access by .NET through a C# wrapper.
We can then use a third-party library that provides a higher level C# API to that SQLite database and there are several great options to choose from the SQLite mono libraries using a .NET style which is fairly well known and relatively easy to work with. However, as the performance is not as strong as some of the other approaches, it does work on iOS and Android but there's no official support for Windows Phone or UWP; and generally, it's not considered a modern approach and it's not preferred anymore. There's also a Microsoft created lightweight library for SQLite and it's fairly new and it has the high-performance wrapper directly over the C++ API but there's minimal documentation. It does support iOS, Android. For those phones which don't support UWP, finally, we have SQLite net.

This is the most common approach being used today. It's simple to use, its performance is strong, and it supports all of our target platforms and it's very stable and very widely used. Since this is the most common approach, this will be the one we're going to focus today.

But, just keep in mind that you have other options too. SQLite.NET is an ORM or object relational mapping. ORM simplifies the process of defining database schemas by mapping model objects in your source code to tables in your database. Now, the schema is ID through attributes and we'll discuss these in the next section.

However, ORM removes the need to write SQL statements and they allow us to interact their data using C# expressions to use SQLite.NET.

We need to add a nuGet package and must add the components to each of our projects. So, this would include each platform specific project and anything else that needs to access the SQLite database. There are several published implementations, so make sure to install the version labelled SQLite-net PCL.

Moreover, it should have a single author Frank Krueger and when you add the component, you will get two packages.

 

The SQLite-net PCL is the core assembly that includes everything to identify the data entities as well as the high-level classes used to create query and work with those entities. Now, a second component of SQLite PCL is raw that provides the raw C# wrapper around that C++ API. This is used by the core assembly to access the database and unless you need to talk to the underlying API, you can generally ignore this component and just let it do its work and remember that SQLite native libraries are built into Android and iOS. But, they're not present in Windows. So, in Windows, the SQLite runtime is provided. In that, included is raw nuGet package.  now that database engine is written in C++ and it's included as a binary and that binary has a dependency on the visual C++ runtime
And, you'll need to add that reference.For that, open the references >> go to extensions >> select Visual C++ 2013 runtime. Currently, you need to use a 2013 version even when later versions are available as this is what the SQLite PCL raw is compiled against; and here, we're showing Windows Phone 8.1 but this same requirement applies to Windows 8 and UWP. If you forget to add this reference, you will get a type initialization error from SQLite being unable to load the runtime DLL.