July 2016
Volume 31 Number 7
[Xamarin]
Working with Local Databases in Xamarin.Forms Using SQLite
More often than not, applications work with data. This is true not only for desktop and Web applications, but also for mobile apps. In many cases, mobile apps exchange data over networks and take advantage of cloud storage and services such as push notifications. However, there are situations in which mobile apps only need to store data locally. With simple, unstructured data, such as user settings and options, applications can store information inside local files, such as XML or text, or through specific objects offered by the various development platforms. In the case of complex, structured data, applications need a different way to store information.
The good news is that you can easily include local databases in your mobile app using SQLite (sqlite.org). SQLite is an open source, lightweight, serverless database engine that makes it simple to create local databases and perform operations on data. Information is stored inside tables and data operations can be performed writing C# code and LINQ queries. SQLite perfectly suits cross-platform development, because it’s a portable database engine. In fact, it’s pre-installed on both iOS and Android, and it can be easily deployed to Windows, as well. For this reason, SQLite also is the perfect companion to build cross-platform, data-centric mobile apps with Xamarin.Forms that need a local database. In this article, I’ll show how to create a mobile app that targets Android, iOS, and the Universal Windows Platform (UWP) with Xamarin.Forms, and that takes advantage of SQLite to store and retrieve local data. I assume you already know how to create a Xamarin.Forms application with Visual Studio 2015; what XAML is; and how to debug a Xamarin.Forms app using the different emulators included with the various platform SDKs. For further information, you can read the following articles: “Build a Cross-Platform UX with Xamarin.Forms” (msdn.com/magazine/mt595754), “Share UI Code Across Mobile Platforms with Xamarin.Forms” (msdn.com/magazine/dn904669) and “Build a Cross-Platform, Mobile Golf App Using C# and Xamarin” (msdn.com/magazine/dn630648). The latter describes how to work with data over the Microsoft Azure platform. Both this article and the sample code are based on Xamarin.Forms 2.0, which you get by installing Xamarin 4.0.3.
Enabling SQLite on UWP Apps
The SQLite core engine is already included on iOS and Android, but not on Windows. Therefore, you need to include SQLite binaries with your app package. Instead of manually including such binaries with every project, you can take advantage of the SQLite extension for Visual Studio 2015, which provides precompiled binaries for the database engine and automates the task of including the required files with new projects. I describe this before showing how to create new projects because the extension works at the IDE level, not at the project level, and will supply the precompiled SQLite binaries every time you include the SQLite libraries in your solutions. There are several SQLite extensions, each targeting a specific Windows version, that can be downloaded using the Extensions and Updates tool in Visual Studio 2015, as shown in Figure 1.
.png "Downloading the SQLite for Universal Windows Platform Extension in Visual Studio 2015")
Figure 1 Downloading the SQLite for Universal Windows Platform Extension in Visual Studio 2015
In this case, download and install the SQLite for Universal Windows Platform extension. By doing so, a UWP app that uses SQLite will also include the precompiled database engine binaries. If required, restart Visual Studio 2015 after installing the extension.
Creating a Sample Project
The first thing to do is create a new project based on Xamarin Forms. The project template you use in Visual Studio 2015 is called Blank App (Xamarin.Forms Portable) and is located inside the Cross-Platform folder of the Visual C# node in the New Project dialog (see Figure 2).
.png "Creating a New Xamarin Forms Project in Visual Studio 2015")
Figure 2 Creating a New Xamarin Forms Project in Visual Studio 2015
The reason for choosing the Portable project type instead of the Shared type is that you might want to produce a reusable data access layer within a library, whereas a Shared project’s scope is only within the solution to which it belongs. At the end of the article I’ll explain more thoroughly the differences between portable libraries and the Shared projects.
When you click OK, Visual Studio 2015 generates a new solution that contains projects targeting iOS, Android, UWP, Windows Runtime and Windows Phone, plus a Portable Class Library (PCL) project. The latter is where you’ll write most of the code that will be shared across the platform-specific projects.
Installing the SQLite NuGet Package
Once you’ve created the project, you need a managed way to access SQLite databases. There are many libraries that allow working against SQLite databases in the Microsoft .NET Framework, but the one you need is a special portable library that also targets Xamarin apps. It’s called SQLite-net, and it’s an open source and lightweight library for .NET, Mono, and Xamarin applications. It’s available as a NuGet package with the name sqlite-net-pcl. You can install the NuGet package at the solution level from either the NuGet Package Manager console, by typing install sqlite-net-pcl, or from the NuGet UI in Visual Studio 2015, which you enable by right-clicking the solution name in Solution Explorer and then selecting Manage NuGet Packages for Solution. Figure 3 shows how to locate and install the sqlite-net-pcl package via the NuGet UI.
.png "Installing the Proper NuGet Packages")
Figure 3 Installing the Proper NuGet Packages
Now you have everything you need and you’re ready to start coding.
Platform-Specific Code: Supplying the Connection String
As with any kind of database, your code accesses a SQLite database through the connection string, which is the first thing you need to build. Because a SQLite database is a file that resides in a local folder, constructing the connection string requires the database pathname. Though most of the code you’ll write is shared across different platforms, the way Android, iOS and Windows handle pathnames is different, so building the connection string requires platform-specific code. You then invoke the connection string via dependency injection.
In the Portable project, add a new interface called IDatabaseConnection.cs and write the following code:
public interface IDatabaseConnection
{
SQLite.SQLiteConnection DbConnection();
}
This interface exposes a method called DbConnection, which will be implemented in each platform-specific project and will return the proper connection string.
The next step is adding a class to each platform-specific project that implements the interface and returns the proper connection string, based on a sample database I’ll call CustomersDb.db3. (If you’re not familiar with SQLite, .db3 is the file extension that identifies SQLite databases.) In the LocalDataAccess.Droid project, add a new class called DatabaseConnection_Android.cs and write the code shown in Figure 4.
Figure 4 Generating a Connection String in the Android Project
using SQLite;
using LocalDataAccess.Droid;
using System.IO;
[assembly: Xamarin.Forms.Dependency(typeof(DatabaseConnection_Android))]
namespace LocalDataAccess.Droid
{
public class DatabaseConnection_Android : IDatabaseConnection
{
public SQLiteConnection DbConnection()
{
var dbName = "CustomersDb.db3";
var path = Path.Combine(System.Environment.
GetFolderPath(System.Environment.
SpecialFolder.Personal), dbName);
return new SQLiteConnection(path);
}
}
}
An attribute called Xamarin.Forms.Dependency indicates that the specified class implements a necessary interface. This attribute is applied at the namespace level with the assembly keyword. On Android, the database file must be stored inside the Personal folder, so the database pathname is made of the filename (CustomersDb.db3) and the Personal folder path. The resulting pathname is assigned as a parameter to the constructor of the SQLiteConnection class and returned to the caller. On iOS, you use the same API, but the folder in which the SQLite database resides is Personal\Library.
Now, add a new class called DatabaseConnection_iOS.cs to the iOS project and write the code shown in Figure 5.
Figure 5 Generating a Connection String in the iOS Project
using LocalDataAccess.iOS;
using SQLite;
using System;
using System.IO;
[assembly: Xamarin.Forms.Dependency(typeof(DatabaseConnection_iOS))]
namespace LocalDataAccess.iOS
{
public class DatabaseConnection_iOS
{
public SQLiteConnection DbConnection()
{
var dbName = "CustomersDb.db3";
string personalFolder =
System.Environment.
GetFolderPath(Environment.SpecialFolder.Personal);
string libraryFolder =
Path.Combine(personalFolder, "..", "Library");
var path = Path.Combine(libraryFolder, dbName);
return new SQLiteConnection(path);
}
}
}
On Windows 10, the SQLite database resides in the app’s local folder. The API you use to access the local folder is different from the other platforms, because you work with classes from the Windows.Storage namespace instead of System.IO. Add a new class called DatabaseConnection_UWP.cs to the Universal Windows project, and write the code shown in Figure 6.
Figure 6 Generating a Connection String in the Universal Windows Project
using SQLite;
using Xamarin.Forms;
using LocalDataAccess.UWP;
using Windows.Storage;
using System.IO;
[assembly: Dependency(typeof(DatabaseConnection_UWP))]
namespace LocalDataAccess.UWP
{
public class DatabaseConnection_UWP : IDatabaseConnection
{
public SQLiteConnection DbConnection()
{
var dbName = "CustomersDb.db3";
var path = Path.Combine(ApplicationData.
Current.LocalFolder.Path, dbName);
return new SQLiteConnection(path);
}
}
}
This time the app’s local folder path is returned by the Windows.Storage.ApplicationData.Current.LocalFolder.Path property, which is combined with the database name to return the connection string via the SQLiteConnection object. Now you’ve written platform-specific code that allows the generation of the proper connection string based on the platform on which the app is running. From now on, all your code will be shared. The next step is implementing a data model.
Writing a Data Model
The goal of the app is to work with a simplified list of customers stored inside a SQLite database and to support operations over data. The first thing that’s needed at this point is a class to represent a customer that will be mapped to a table in the database. In the Portable project, add a class called Customer.cs. This class must implement the INotifyPropertyChanged interface to notify callers of changes in the data it stores. It will use special attributes in the SQLite namespace to annotate properties with validation rules and other information in a way that’s very close to the data annotations from the System.ComponentModel.DataAnnotations namespace. Figure 7 shows the sample Customer class.
Figure 7 Implementing a Data Model
using SQLite;
using System.ComponentModel;
namespace LocalDataAccess
{
[Table("Customers")
public class Customer: INotifyPropertyChanged
{
private int _id;
[PrimaryKey, AutoIncrement]
public int Id
{
get
{
return _id;
}
set
{
this._id = value;
OnPropertyChanged(nameof(Id));
}
}
private string _companyName;
[NotNull]
public string CompanyName
{
get
{
return _companyName;
}
set
{
this._companyName = value;
OnPropertyChanged(nameof(CompanyName));
}
}
private string _physicalAddress;
[MaxLength(50)]
public string PhysicalAddress
{
get
{
return _physicalAddress;
}
set
{
this._physicalAddress=value;
OnPropertyChanged(nameof(PhysicalAddress));
}
}
private string _country;
public string Country
{
get
{
return _country;
}
set
{
_country = value;
OnPropertyChanged(nameof(Country));
}
}
public event PropertyChangedEventHandler PropertyChanged;
private void OnPropertyChanged(string propertyName)
{
this.PropertyChanged?.Invoke(this,
new PropertyChangedEventArgs(propertyName));
}
}
}
The key point of this class is annotating objects with SQLite attributes. The Table attribute allows assigning a table name, in this case Customers. This isn’t mandatory, but if you don’t specify one, SQLite generates a new table based on the class name, Customer in this case. So, for the sake of consistency, the code generates a new table with a plural name. The PrimaryKey and AutoIncrement attributes applied to the Id property make this the primary key in the Customers table with automatic increment. The NotNull attribute applied to the CompanyName property marks this as required, which implies that the data store validation will fail if the property value is null. The MaxLength attribute applied to the PhysicalAddress property indicates the maximum length for the property value. Another interesting attribute is Column, which you can apply to a property name to provide a different column name in the database.
Implementing Data Access
After writing a simple data model, you need a class that provides methods that perform operations over data. For the sake of clarity and because this is a simple example, I won’t use a Model-View-ViewModel (MVVM) approach here; not all readers have experience with this pattern and, in any case, it’s better used in large projects. You can certainly rewrite the sample as you like.
Let’s start with a new class called CustomersDataAccess.cs in the Portable project, which requires the following using directives:
using SQLite;
using System.Collections.Generic;
using System.Linq;
using Xamarin.Forms;
using System.Collections.ObjectModel;
The first things you need in this class are a private field that stores the connection string and an object that will be used to implement locks on data operations, to avoid database collisions:
private SQLiteConnection database;
private static object collisionLock = new object();
More specifically, locks should have the following form:
// Use locks to avoid database collisions
lock(collisionLock)
{
// Data operation here ...
}
With Xamarin.Forms, you use XAML to build the UI and you can take advantage of data binding to display and enter information; for this reason, you need to expose the data in a way XAML can work with it. The best approach is exposing a property of type ObservableCollection<Customer> like this:
public ObservableCollection<Customer> Customers { get; set; }
The ObservableCollection type has built-in support for change notification; therefore, it’s the most appropriate collection for data binding in XAML-based platforms.
Now it’s time to implement the class constructor, which is also responsible for invoking the platform-specific implementation of the DbConnection method, which returns the proper connection string, as shown in Figure 8.
Figure 8 Implementing the Class Constructor
public CustomersDataAccess()
{
database =
DependencyService.Get<IDatabaseConnection>().
DbConnection();
database.CreateTable<Customer>();
this.Customers =
new ObservableCollection<Customer>(database.Table<Customer>());
// If the table is empty, initialize the collection
if (!database.Table<Customer>().Any())
{
AddNewCustomer();
}
}
Notice how the code invokes the DependencyService.Get method. This is a generic method that returns the platform-specific implementation of the supplied generic type, IDatabaseConnection in this case. With this approach based on dependency injection, the code is invoking the platform-specific implementation of the DbConnection method. The Xamarin runtime then knows how to resolve the method call based on the OS on which the app is running. Invoking this method will also cause the database to be created if one isn’t found. The SQLiteConnection object exposes a generic method called CreateTable<T>, where the generic type is your model class, Customer in this case. With this simple line of code, you create a new Customers table. If the table already exists, it won’t be overwritten. The code also initializes the Customers property; it invokes the Table<T> generic method from SQLiteConnection, where the generic type is still your model class. Table<T> returns an object of type TableQuery<T>, which implements the IEnumerable<T> interface and can also be queried with LINQ. The actual returned result consists of a list of <T> objects; however, binding a TableQuery object to the UI directly is not the proper way to present data, so a new ObservableCollection<Customer> based on the returned result is generated and assigned to the Customers property. The code also invokes a method called AddNewCustomer if the table is empty, defined as follows:
public void AddNewCustomer()
{
this.Customers.
Add(new Customer
{
CompanyName = "Company name...",
PhysicalAddress = "Address...",
Country = "Country..."
});
}
This method simply adds a new Customer to the Customers collection with default property values and avoids binding to an empty collection.
Querying Data
Querying data is very important. SQLite has essentially two ways of implementing queries. The first is using LINQ against the result of an invocation to the Table<T> method, which is an object of type TableQuery<T> and implements the IEnumerable<T> interface. The second is invoking a method called SQLiteConnection.Query<T>, which takes an argument of type strings that represents a query written in SQL. The code in Figure 9 demonstrates how to filter the list of customers by country, using both approaches.
Figure 9 Filtering a List of Customers by Country
public IEnumerable<Customer> GetFilteredCustomers(string countryName)
{
lock(collisionLock)
{
var query = from cust in database.Table<Customer>()
where cust.Country == countryName
select cust;
return query.AsEnumerable();
}
}
public IEnumerable<Customer> GetFilteredCustomers()
{
lock(collisionLock)
{
return database.Query<Customer>(
"SELECT * FROM Item WHERE Country = 'Italy'").AsEnumerable();
}
}
The first overload of GetFilteredCustomers returns the result of a LINQ query that filters data based on the country name, supplied as the method argument. The second overload invokes Query to execute SQL queries directly. This method expects the result to be a generic List, whose generic type is the same passed to Query. A SQLiteException is thrown if the query fails. Of course, you can retrieve a specified object instance using LINQ or extension methods, as in the following code that invokes FirstOrDefault over the list of customers and retrieves the specified customer instance based on its id:
public Customer GetCustomer(int id)
{
lock(collisionLock)
{
return database.Table<Customer>().
FirstOrDefault(customer => customer.Id == id);
}
}
Executing CRUD Operations
Create, read, update and delete (CRUD) operations are also extremely important. Reading data is typically performed using the approaches described in the previous section, so now I’ll discuss how to create, update and delete information in a SQLite database. The SQLiteConnection object exposes the Insert, InsertAll, Update, and UpdateAll methods to insert or update objects in the database. InsertAll and UpdateAll execute an insert or update operation on a collection that implements IEnumerable<T> passed as the argument. The insert or update operation is executed in a batch, and both methods also allow executing the operation as a transaction. Keep in mind that while InsertAll requires that no items in the collection exist in the database, UpdateAll requires that all the items in the collection already exist in the database. In this case, I have an ObservableCollection<Customer> that can contain both objects retrieved from the database and new objects added via the UI that haven’t been saved yet, or pending edits over existing objects. For this reason, using InsertAll and UpdateAll isn’t recommended. A good approach is simply checking if an instance of the Customer class has an Id. If it does, the instance already exists in the database so it just needs to be updated. If the Id is zero, the instance doesn’t exist in the database; therefore, it must be saved. The code in Figure 10 demonstrates how to insert or update a single instance of a Customer object based on the previous consideration.
Figure 10 Insert or Update a Single Instance of a Customer ObjectDepending on the Existence of a Customer Class ID
public int SaveCustomer(Customer customerInstance)
{
lock(collisionLock)
{
if (customerInstance.Id != 0)
{
database.Update(customerInstance);
return customerInstance.Id;
}
else
{
database.Insert(customerInstance);
return customerInstance.Id;
}
}
}
The code in Figure 11, instead, demonstrates how to insert or update all instances of Customer.
Figure 11 Insert or Update All Instances of Customer
public void SaveAllCustomers()
{
lock(collisionLock)
{
foreach (var customerInstance in this.Customers)
{
if (customerInstance.Id != 0)
{
database.Update(customerInstance);
}
else
{
database.Insert(customerInstance);
}
}
}
}
Both the Insert and Update methods return an integer that represents the number of rows added or updated. Insert also offers an overload that accepts a string containing additional SQL statements you might want to execute against the inserted rows. Also, it’s worth mentioning that Insert automatically updates, by reference, the property in your business object that has been mapped to be the primary key, Customer.Id in this case. The SQLiteConnection class also exposes the Delete<T> and DeleteAll<T> methods, which permanently delete one or all objects from a table. The delete operation is irreversible, so be aware of what you’re doing. The following code implements a method called DeleteCustomer that deletes the specified customer instance from both the in-memory Customers collection and the database:
public int DeleteCustomer(Customer customerInstance)
{
var id = customerInstance.Id;
if (id != 0)
{
lock(collisionLock)
{
database.Delete<Customer>(id);
}
}
this.Customers.Remove(customerInstance);
return id;
}
If the specified Customer has an id, it exists in the database, so it’s permanently deleted, and it’s also removed from the Customers collection. Delete<T> returns an integer that represents the number of deleted rows. You can also permanently delete all objects from a table. You can certainly invoke DeleteAll<T>, where the generic type is your business object such as Customer, but I want to show an alternative approach instead, so you can get knowledge of other members. The SQLiteConnection class exposes a method called DropTable<T>, which permanently destroys a table in the database. For instance, you might implement a table deletion as follows:
public void DeleteAllCustomers()
{
lock(collisionLock)
{
database.DropTable<Customer>();
database.CreateTable<Customer>();
}
this.Customers = null;
this.Customers = new ObservableCollection<Customer>
(database.Table<Customer>());
}
The code deletes the Customers table, then creates one, and finally cleans up and recreates the Customers collection. Figure 12 shows the full listing for the CustomersDataAccess.cs class.
Figure 12 The CustomersDataAccess.cs Class
using SQLite;
using System.Collections.Generic;
using System.Linq;
using Xamarin.Forms;
using System.Collections.ObjectModel;
namespace LocalDataAccess
{
public class CustomersDataAccess
{
private SQLiteConnection database;
private static object collisionLock = new object();
public ObservableCollection<Customer> Customers { get; set; }
public CustomersDataAccess()
{
database =
DependencyService.Get<IDatabaseConnection>().
DbConnection();
database.CreateTable<Customer>();
this.Customers =
new ObservableCollection<Customer>(database.Table<Customer>());
// If the table is empty, initialize the collection
if (!database.Table<Customer>().Any())
{
AddNewCustomer();
}
}
public void AddNewCustomer()
{
this.Customers.
Add(new Customer
{
CompanyName = "Company name...",
PhysicalAddress = "Address...",
Country = "Country..."
});
}
// Use LINQ to query and filter data
public IEnumerable<Customer> GetFilteredCustomers(string countryName)
{
// Use locks to avoid database collitions
lock(collisionLock)
{
var query = from cust in database.Table<Customer>()
where cust.Country == countryName
select cust;
return query.AsEnumerable();
}
}
// Use SQL queries against data
public IEnumerable<Customer> GetFilteredCustomers()
{
lock(collisionLock)
{
return database.
Query<Customer>
("SELECT * FROM Item WHERE Country = 'Italy'").AsEnumerable();
}
}
public Customer GetCustomer(int id)
{
lock(collisionLock)
{
return database.Table<Customer>().
FirstOrDefault(customer => customer.Id == id);
}
}
public int SaveCustomer(Customer customerInstance)
{
lock(collisionLock)
{
if (customerInstance.Id != 0)
{
database.Update(customerInstance);
return customerInstance.Id;
}
else
{
database.Insert(customerInstance);
return customerInstance.Id;
}
}
}
public void SaveAllCustomers()
{
lock(collisionLock)
{
foreach (var customerInstance in this.Customers)
{
if (customerInstance.Id != 0)
{
database.Update(customerInstance);
}
else
{
database.Insert(customerInstance);
}
}
}
}
public int DeleteCustomer(Customer customerInstance)
{
var id = customerInstance.Id;
if (id != 0)
{
lock(collisionLock)
{
database.Delete<Customer>(id);
}
}
this.Customers.Remove(customerInstance);
return id;
}
public void DeleteAllCustomers()
{
lock(collisionLock)
{
database.DropTable<Customer>();
database.CreateTable<Customer>();
}
this.Customers = null;
this.Customers = new ObservableCollection<Customer>
(database.Table<Customer>());
}
}
}
A Simple UI with Data Binding
Now that you have the model and the data access layer, you need a UI to present and edit data. As you know, with Xamarin.Forms the UI can be written with either C# or XAML, but the latter provides better separation between the UI and the procedural code and gives you an immediate perception of the hierarchical UI organization, so that’s my choice for this article. It’s worth mentioning that, with Xamarin.Forms 2.0, you can also enable XAML compilation (XamlC) for performance optimization and compile-time error check. For further details about XamlC, visit bit.ly/24BSUC8.
Now let’s write a simple page that shows a list of data with some buttons. In Xamarin.Forms, pages are shared elements, so they’re added to the Portable project. To do so, in Solution Explorer right-click the Portable project and select Add | New Item. In the Add New Item dialog, locate the Cross-Platform node and select the Forms Xaml Page template, as shown in Figure 13. Name the new page CustomersPage.cs and click Add.
.png "Adding a New Page Based on XAML")
Figure 13 Adding a New Page Based on XAML
In order to show the list of customers, the simple UI will be composed of a ListView control that’s data bound to the Customers collection exposed by the CustomersDataAccess class. The items’ DataTemplate consists of four Entry controls, each data bound to a property of the Customer model class. If you have experience with other XAML-based platforms such as Windows Presentation Foundation (WPF) and UWP, you can consider Entry as the equivalent for the TextBox control. The Entry controls are grouped inside a StackLayout panel, which is included in a ViewCell container. For those who come from WPF and UWP, the StackLayout is the Xamarin equivalent of the StackPanel container. ViewCell allows for creating custom cells within item controls (such as ListView). You’ll notice that I’m using an Entry control with the IsEnabled property assigned as False for the Customer.Id property, instead of a Label control, which is read-only by nature. As you might remember, when you invoke the SQLiteConnection.Insert method, this updates the property mapped as the primary key in your model, so the UI should be able to automatically reflect this change. Unfortunately, the Label control doesn’t update itself with the new value, whereas the Entry control does, and this is the reason Entry is used, but set as read-only.
The second part of the UI consists of ToolbarItem buttons, which provide an easy and shared way to offer user interaction via a convenient menu bar that will be available on all platforms. For the sake of simplicity, these buttons will be implemented in the secondary area of the menu bar, which doesn’t require platform-specific icons. Figure 14 shows the full code for the UI.
Figure 14 The CustomersPage User Interface
<?xml version="1.0" encoding="utf-8" ?>
<ContentPage xmlns="https://xamarin.com/schemas/2014/forms"
xmlns:x="https://schemas.microsoft.com/winfx/2009/xaml"
x:Class="LocalDataAccess.CustomersPage">
<ListView x:Name="CustomersView"
ItemsSource="{Binding Path=Customers}"
ListView.RowHeight="150">
<ListView.ItemTemplate>
<DataTemplate>
<ViewCell>
<StackLayout Orientation="Vertical">
<Entry Text="{Binding Id}" IsEnabled="False"/>
<Entry Text="{Binding CompanyName}" />
<Entry Text="{Binding PhysicalAddress}"/>
<Entry Text="{Binding Country}"/>
</StackLayout>
</ViewCell>
</DataTemplate>
</ListView.ItemTemplate>
</ListView>
<ContentPage.ToolbarItems>
<ToolbarItem Name="Add" Activated="OnAddClick"
Priority="0" Order="Secondary" />
<ToolbarItem Name="Remove" Activated="OnRemoveClick"
Priority="1" Order="Secondary" />
<ToolbarItem Name="Remove all" Activated="OnRemoveAllClick"
Priority="2" Order="Secondary" />
<ToolbarItem Name="Save" Activated="OnSaveClick"
Priority="3" Order="Secondary" />
</ContentPage.ToolbarItems>
</ContentPage>
Notice how each ToolbarItem has the Order property assigned as Secondary; if you want to make them available in the primary area of the toolbar and supply some icons, change this to Primary. Also, the Priority property allows specifying the order in which a ToolbarItem appears on the toolbar, while Activated can be compared to a click event and requires an event handler.
The next step is writing C# code that instantiates the CustomersDataAccess class, data binds objects, and performs operations over data. Figure 15 shows the C# codebehind for the page (refer to the comments for more information).
Figure 15 The CustomersPage Codebehind
using System;
using System.Linq;
using Xamarin.Forms;
namespace LocalDataAccess
{
public partial class CustomersPage : ContentPage
{
private CustomersDataAccess dataAccess;
public CustomersPage()
{
InitializeComponent();
// An instance of the CustomersDataAccessClass
// that is used for data-binding and data access
this.dataAccess = new CustomersDataAccess();
}
// An event that is raised when the page is shown
protected override void OnAppearing()
{
base.OnAppearing();
// The instance of CustomersDataAccess
// is the data binding source
this.BindingContext = this.dataAccess;
}
// Save any pending changes
private void OnSaveClick(object sender, EventArgs e)
{
this.dataAccess.SaveAllCustomers();
}
// Add a new customer to the Customers collection
private void OnAddClick(object sender, EventArgs e)
{
this.dataAccess.AddNewCustomer();
}
// Remove the current customer
// If it exist in the database, it will be removed
// from there too
private void OnRemoveClick(object sender, EventArgs e)
{
var currentCustomer =
this.CustomersView.SelectedItem as Customer;
if (currentCustomer!=null)
{
this.dataAccess.DeleteCustomer(currentCustomer);
}
}
// Remove all customers
// Use a DisplayAlert object to ask the user's confirmation
private async void OnRemoveAllClick(object sender, EventArgs e)
{
if (this.dataAccess.Customers.Any())
{
var result =
await DisplayAlert("Confirmation",
"Are you sure? This cannot be undone",
"OK", "Cancel");
if (result == true)
{
this.dataAccess.DeleteAllCustomers();
this.BindingContext = this.dataAccess;
}
}
}
}
}
The BindingContext property is the equivalent of DataContext in WPF and UWP and represents the data source for the current page.
Testing the Application with Emulators
Now it’s time to test the application. In the App.cs file, you need to change the startup page. When you create a Xamarin.Forms project, Visual Studio 2015 generates a page written in procedural code and assigned to an object called MainPage. This assignment is in the App class constructor, so open App.cs and replace the App constructor as follows:
public App()
{
// The root page of your application
MainPage = new NavigationPage(new CustomersPage());
}
You might be surprised that an instance of the CustomersPage isn’t assigned to MainPage directly; rather, it’s encapsulated as the parameter for an instance of the NavigationPage class. The reason is that using a NavigationPage is the only way to show a menu bar on Android, but this does not at all affect UI behavior. Depending on which platform you want to test the app, select the startup project and a proper emulator in the standard toolbar in Visual Studio and press F5. Figure 16 shows the app running on Android and on Windows 10 Mobile.
.png "The Sample App Running on Different Platforms")
Figure 16 The Sample App Running on Different Platforms
Notice how the toolbar items are properly shown in the menu bar and how you can work with data in the same way on different devices and OSes.
Writing Platform-Specific Code with Shared Projects
Sharing code is a key concept in Xamarin.Forms. In fact, all the code that doesn’t leverage platform-specific APIs can be written once and shared across iOS, Android and Windows projects. When you create a Xamarin.Forms project, Visual Studio 2015 offers both the Blank App (Xamarin.Forms Portable) and the Blank App (Xamarin.Forms Shared) project templates, based on PCLs and shared projects, respectively. Generally speaking, in Visual Studio you can share code using either PCLs, which produce reusable .dll libraries that target multiple platforms but don’t allow writing platform-specific code, or shared projects, which don’t produce an assembly, so their scope is limited to the solution to which they belong. Shared projects do allow writing platform-specific code.
In the case of Xamarin.Forms, when Visual Studio 2015 generates a new solution, it adds either a PCL project or a shared project depending on the template you select. In both cases, the selected template is the place you put all the shared code. But in the portable project you code interfaces that will have a platform-specific implementation in the iOS, Android and Windows projects, whose members will then be invoked via dependency injection. This is what you’ve seen in this article.
In the case of shared projects, you can use conditional preprocessor directives (#if, #else, #endif) and environment variables that easily let you understand what platform your app is running on, so you can write the platform-specific code in the shared project directly. In the sample app described in this article, the connection string is constructed using platform-specific APIs. If you use a shared project, you could write the code shown in Figure 17 directly in the shared project. Remember that a shared project doesn’t support NuGet packages, so you must include the SQLite.cs file (available on GitHub at bit.ly/1QU8uiR).
Figure 17 Writing the Connection String in a Shared Project with Conditional Preprocessor Directives
private string databasePath {
get {
var dbName = "CustomersDb.db3";
#if __IOS__
string folder = Environment.GetFolderPath
(Environment.SpecialFolder.Personal);
folder = Path.Combine (folder, "..", "Library");
var databasePath = Path.Combine(folder, dbName);
#else
#if __ANDROID__
string folder = Environment.GetFolderPath
(Environment.SpecialFolder.Personal);
var databasePath = Path.Combine(folder, dbName);
#else // WinPhone
var databasePath =
Path.Combine(Windows.Storage.ApplicationData.Current.
LocalFolder.Path, dbName);
#endif
#endif
return databasePath;
}
}
As you can see, you use the #if and #else directives to detect on which platform the app is running. Each platform is represented by the __IOS__, __ANDROID__ or __WINPHONE__ environment variables, where __WINPHONE__ targets Windows 8.x, Windows Phone 8.x and the UWP. Choosing between portable libraries and shared projects strictly depends on your needs. Portable libraries are reusable and require very clean separation between shared and platform-specific code. Shared projects let you write platform-specific code together with shared code, but they don’t produce reusable libraries and they’re more difficult to maintain when your code base grows very much.
Further Improvements
The sample application described in this article can be certainly improved in many ways. For instance, you might want to implement the MVVM pattern and expose commands to the UI, instead of handling click events, and you could consider moving toolbar items to the primary area in the menu bar, supplying platform-specific icons. From the point of view of data, you might need to work with relationships and foreign keys. Because handling both with the SQLite library isn’t easy, you might want to consider the SQLite-Net Extensions library (bit.ly/24yhhnP), an open source project that simplifies the C# code you need to work with relationships and with more advanced scenarios. This is just a small list of possible improvements you could try for further studies.
Wrapping Up
In many situations, mobile apps need local data storage. With SQLite, Xamarin.Forms applications can easily manage local databases using an open source, serverless, and portable engine that supports C# and LINQ queries. SQLite offers very intuitive objects for working with tables and database objects, making it very easy to implement local data access on any platform. Check out the SQLite documentation (sqlite.org/docs.html) for further information and additional scenarios.
Alessandro Del Sole has been a Microsoft MVP since 2008. Awarded MVP of the Year five times, he has authored many books, eBooks, instructional videos, and articles about .NET development with Visual Studio. Del Sole works as a solution developer expert for Brain-Sys (brain-sys.it), focusing on .NET development, training and consulting. You can follow him on Twitter: @progalex.
Thanks to the following technical experts for reviewing this article: Kevin Ashley and Sara Silva