An Introduction to Xamarin.Forms and SQLite_part1

At some point in your mobile development career, you are going to need to deal with data. Dealing with data means more than processing and displaying information to the end user. You are going to need to store this information somewhere and be able to get at it easily. Thanks to Xamarin and open source software, you can easily store your data with an industry tested platform, SQLite.

1. Storing Data

So why do you need to care about data when it comes to your app? Because it is all around you. You can't escape it. No matter what kind of app you are writing, whether it's a game or some sort of utility, you are going to need to store data at some point. That data could be user data, statistics, or anything else of interest that either you or the user will be interested in at some point in the use of your app.

At this point, let's assume that you have decided to go the Xamarin.Forms route, because you are interested in targeting several platforms not only in the logic for your app, but also for the user interface layer.

Great. But what do you do now that you need to store information within your app? Don't worry, there's a very simple solution to this problem, SQLite.

2. Introduction to SQLite

You have now seen the term SQLite a couple of times in this tutorial, it's time to get down to the meat. What exactly is SQLite? SQLite is a public domain, zero configuration, transactional SQL database engine. All this means is that you have a full featured mechanism to store your data in a structured way. Not only do you get all of this, you also have access to the source code, because it's open source.

We will not be covering all the features of SQLite in this tutorial simply because there are too many to go through. Rest assured that you will have the ability to easily create a table structure to store data and retrieve it in your app. These are the concepts that we will be focusing on in this tutorial.

In the world of Xamarin.Forms, SQLite is a natural fit for a very simple reason. The SQLite engine is readily available on both iOS and Android. This means that you can use this technology right out of the box when you choose to write a Xamarin.Forms app.

Getting access to SQLite functionality in Windows Phone apps requires one additional step that we will go over a little later. All this functionality and cross platform accessibility is great, but how will we get access to the native platform implementations from our C# code in Xamarin.Forms? From a nice NuGet package, that's how. Let's take a look.

3. Creating an App

Let's start by creating a simple Xamarin.Forms application. In this tutorial, I will be using a Mac running Xamarin Studio, but you can just as easily be using Xamarin Studio or Visual Studio running on a PC.

Step 1: Create a Project

We start the process by creating a new Xamarin.Forms app. To do this, simply select the Mobile Apps project template family on the left and choose one of the Xamarin.Forms templates on the right. You can use either the PCL or Shared version of the template, but for this case, I will be using the PCL. You can follow along using either one, but there will be a slight difference if you choose the Shared template later on.

You can give the project any name you like. I will call this project IntroToSQLite. After you click the OK button, your IDE will go through the process of creating your solution. Your solution will contain four projects:

1. IntroToSQLite - PCL project
2. IntroToSQLite.Android - Android project
3. IntroToSQLite.iOS - iOS project
4. IntroToSQLite.WinPhone - Windows Phone project (only on a PC)

Step 2: Add SQLite Support

Now that we have our basic project structure set up, we can start to add access to SQLite to our PCL project. We need to install a new package into our project named SQLite.Net. This is a .NET wrapper around SQLite that will allow us to access the native SQLite functionality from a Xamarin.Forms PCL or Shared project.

We access this NuGet package by right-clicking on either Packages or References, depending on which IDE you are using, and select Add Package (or Reference). In the search box, type sqlite.net. This will show you a rather large collection of packages that you can include in your project.

Since I chose to go the PCL route for my Xamarin.Forms project, I will need to select the SQLite.Net PCL package to include into my project. Which one do you choose if you went the Shared project route? None.

SQLite and Shared Projects

If you've chosen the Shared project template earlier in the tutorial, you may be wondering how to get access to the SQLite package. The short answer is that you can't. If you remember from a previous tutorial, you can't add references to a Shared project. To get access to SQLite from a Shared project, you simply add the source code to the project.

Add Some Code

The final step in adding SQLite functionality to the PCL project is to create an interface that will allow us access into the SQLite world. The reason we are doing this is because we need to access the native functionality on the different platforms as we saw in a previous tutorial.

Let's start by defining an interface that is going to give us access to the SQLite database connection. Within your PCL project, create a new interface named ISQLite and replace the implementation with the following:

1. using System;
2. using SQLite.Net;
3.  
4. namespace IntroToSQLite
5. {
6.     public interface ISQLite
7.     {
8.         SQLiteConnection GetConnection();
9.     }
10. }

This is the interface that we will implement and get access to via the DependencyService from the native implementations.

Step 3: Define the Database

We now have access to the SQLite functionality, let's define our database. This particular application is going to be quite simple and we are just going to store some of our random thoughts as we come up with them.

We start by creating a class that will represent the data stored in a particular table. Let's call this class RandomThought.

1. using System;
2. using SQLite.Net.Attributes;
3.  
4. namespace IntroToSQLite
5. {
6.     public class RandomThought
7.     {
8.         [PrimaryKey, AutoIncrement]
9.         public int ID { get; set; }
10.         public string Thought { get; set; }
11.         public DateTime CreatedOn { get; set; }
12.  
13.         public RandomThought ()
14.         {
15.         }
16.     }
17. }

As you can see, this is a very simple class with three properties. Two of those properties are just your normal everyday properties, Thought and CreatedOn. These two properties are going to represent columns in the SQLite database, which will contain a table named RandomThought. The third property, ID, is also going to represent a column within the table and contain a unique id that we can use to refer to a specific RandomThought row within the table.

The interesting thing about the ID property is that it is decorated with two attributes, PrimaryKey and AutoIncrement. PrimaryKey tells SQLite that this column is going to be the primary key of the table, which means that, by default, it needs to be unique and there is an index applied to it to speed up retrievals from this table when referring to a row by this column.

AutoIncrement means that, when we insert a new RandomThought into this table, the ID column will be populated automatically with the next available integer value. The next step is to create this table in the database.

I like to create a class that represents my database and keep all the logic to access the database and its tables within this class. For this, I will create a class named RandomThoughtDatabase:

1. using System;
2. using SQLite.Net;
3. using Xamarin.Forms;
4. using System.Collections.Generic;
5. using System.Linq;
6.  
7. namespace IntroToSQLite
8. {
9.     public class RandomThoughtDatabase
10.     {
11.         private SQLiteConnection _connection;
12.  
13.         public RandomThoughtDatabase ()
14.         {
15.             _connection = DependencyService.Get<ISQLite> ().GetConnection ();
16.             _connection.CreateTable<RandomThought> ();
17.         }
18.  
19.         public IEnumerable<RandomThought> GetThoughts() {
20.             return (from t in _connection.Table<RandomThought> ()
21.                     select t).ToList ();
22.         }
23.  
24.         public RandomThought GetThought(int id) {
25.             return _connection.Table<RandomThought> ().FirstOrDefault (t => t.ID == id);
26.         }
27.  
28.         public void DeleteThought(int id) {
29.             _connection.Delete<RandomThought> (id);
30.         }
31.  
32.         public void AddThought(string thought) {
33.             var newThought = new RandomThought {
34.                 Thought = thought,
35.                 CreatedOn = DateTime.Now
36.             };
37.  
38.             _connection.Insert (newThought);
39.         }
40.     }
41. }

This is a very simple implementation as it only contains a few methods. These are typically some of the basic operations you perform when dealing with a database. One point of note is the constructor. Within the constructor we are doing two things.

First, we are using the DependencyService class to get a registered class that implements the ISQLite interface and call its GetConnection method.

Second, we use the CreateTable method on the SQLiteConnection class to create a table called RandomThought. This method will create the table, if it doesn't already exist, and exit gracefully if it already exists.

Obviously, you can get as sophisticated with this class as you want by adding all sorts of functionality, but these operations are typically a good starting point.

Step 4: Add the iOS Implementation

Most of the code that we use to interact with the database is going to be found in the PCL (or Shared) project. But we still need to do a little wiring up in the native implementations to get everything working correctly.

The main obstacle that we need to work around on the native side when using SQLite is where we are going to store the actual database file. This differs from platform to platform. Here is what we need for iOS.

Before we can actually add any sort of SQLite functionality to the iOS project, we need to add the SQLite.Net PCL as well as the SQLite.NET PCL - XamarinIOS Platform packages to this project. You can follow the same steps that you took in Step 2, making sure to add both to the project. Once you have added this package, you can start to write some SQLite code within the iOS project.

Let's create an implementation of the ISQLite interface for iOS. Start by creating a new class, naming it SQLite_iOS.

1. using System;
2. using System.IO;
3. using SQLite;
4. using IntroToSQLite.iOS;
5. using Xamarin.Forms;
6.  
7. [assembly: Dependency(typeof(SQLite_iOS))]
8.  
9. namespace IntroToSQLite.iOS
10. {
11.     public class SQLite_iOS: ISQLite
12.     {
13.         public SQLite_iOS ()
14.         {
15.         }
16.  
17.         #region ISQLite implementation
18.  
19.         public SQLite.Net.SQLiteConnection GetConnection ()
20.         {
21.             var fileName = "RandomThought.db3";
22.             var documentsPath = Environment.GetFolderPath (Environment.SpecialFolder.Personal);
23.             var libraryPath = Path.Combine (documentsPath, "..", "Library");
24.             var path = Path.Combine (libraryPath, fileName);
25.  
26.             var platform = new SQLite.Net.Platform.XamarinIOS.SQLitePlatformIOS ();
27.             var connection = new SQLite.Net.SQLiteConnection (platform, path);
28.  
29.             return connection;
30.         }
31.  
32.         #endregion
33.     }
34. }

We get access to the correct location to store the database file, create a new SQLiteConnection object, and pass it back to our PCL (or Shared) project. The assembly attribute at the top of the file is used to identify this class as a Dependency that can be retrieved via the Get method on the DependencyService class.

If you found this post interesting, follow and support us.
Suggest for you:

Ionic 2 Tutorial with Two Complete Apps

Android Application Programming - Build 20+ Android Apps

The Complete Android Developer Course - Build 14 Apps

The Complete Android & Java Course - Build 21 Android Apps

Android: From Beginner to Paid Professional