SQLite In Xamarin

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 and this 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 nugget 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 now the SQLite engine exposes a C++ API, which is an access by dot net through a c-sharp wrapper.

We can then use a third-party library that provides a higher level c-sharp API to that SQLite database and there are several great options to choose from the SQLite mono libraries using a  dot net style which is fairly well known and relatively easy to work with .However as 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's the high-performance wrapper directly over the C++ API but there's minimal documentation it does support iOS Android and those phone doesn't support you WP and finally we have SQLite net and this is the most common approach being used today it's simple to use performance is strong supports all of our target platforms and it's very stable and very widely used and since this is the most common approach this will be the one we're going to focus on today

 but just keep in mind you have other options sequel a net is an ORM or object relational mapping and ORM simplify 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 is remove the need to write sequel statements and they allow us to interact their data using c-sharp expressions to use sequel like net.

We need to add a nugget package and you must add the components to each of your projects so this would include each platform specific project and anything else that need 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 sqlites-net PCL is the core assembly and this 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 sequel like PCL raw that provides the raw c-sharp 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 raw nugget  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 and to do that you'll open the references go to extensions and select visual C++ 2013 runtime now 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 and if you forget to add this reference you will get a type initialization error from SQLite being unable to load the runtime DLL