In this article

Databases in Xamarin.Mac

03/14/2017

42 minutes to read

Contributors

In this article

This article covers using key-value coding and key-value observing to allow for data binding between SQLite databases and UI elements in Xcode's Interface Builder. It also covers using the SQLite.NET ORM to provide access to SQLite data.

Overview

When working with C# and .NET in a Xamarin.Mac application, you have access to the same SQLite databases that a Xamarin.iOS or Xamarin.Android application can access.

In this article we will be covering two ways to access SQLite data:

Direct Access - By directly accessing a SQLite Database, we can use data from the database for key-value coding and data binding with UI elements created in Xcode's Interface Builder. By using key-value coding and data binding techniques in your Xamarin.Mac application, you can greatly decrease the amount of code that you have to write and maintain to populate and work with UI elements. You also have the benefit of further decoupling your backing data (Data Model) from your front end User Interface (Model-View-Controller), leading to easier to maintain, more flexible application design.

SQLite.NET ORM - By using the open source SQLite.NET Object Relationship Manager (ORM) we can greatly reduce the amount of code required to read and write data from a SQLite database. This data can then be used to populate a user interface item such as a Table View.

In this article, we'll cover the basics of working with key-value coding and data binding with SQLite Databases in a Xamarin.Mac application. It is highly suggested that you work through the Hello, Mac article first, specifically the Introduction to Xcode and Interface Builder and Outlets and Actions sections, as it covers key concepts and techniques that we'll be using in this article.

Since we will be using key-value coding and data binding, please work through the Data binding and key-value coding first, as core techniques and concepts will be covered that will be used in this documentation and its sample application.

Direct SQLite access

For SQLite data that is going to be bound to UI elements in Xcode's Interface Builder, it is highly suggested that you access the SQLite database directly (as opposed to using a technique such as an ORM), since you have total control over the way the data is written and read from the database.

As we have seen in the Data Binding and Key-Value Coding documentation, by using key-value coding and data binding techniques in your Xamarin.Mac application, you can greatly decrease the amount of code that you have to write and maintain to populate and work with UI elements. When combined with direct access to a SQLite database, it can also greatly reduce the amount of code required to read and write data to that database.

In this article, we will be modifying the sample app from the data binding and key-value coding document to use a SQLite Database as the backing source for the binding.

Including SQLite database support

Before we can continue, we need to add SQLite database support to our application by including References to a couple of .DLLs files.

Do the following:

In the Solution Pad, right-click on the References folder and select Edit References.

Select both the Mono.Data.Sqlite and System.Data assemblies:

Click the OK button to save your changes and add the references.

Modifying the data model

Now that we have added support for directly accessing a SQLite database to our application, we need to modify our Data Model Object to read and write data from the database (as well as provide key-value coding and data binding). In the case of our sample application, we'll edit the PersonModel.cs class and make it look like the following:

Any changes made to the Name, Occupation or isManager properties will be sent to the database if the data has been saved there before (e.g. if the _conn variable is not null). Next, let's look at the methods that we've added to Create, Update, Load and Delete people from the database.

Create a new record

The following code was added to create a new record in the SQLite database:

We are using a SQLiteCommand to create the new record in the database. We get a new command from the SQLiteConnection (conn) that we passed into the method by calling CreateCommand. Next, we set the SQL instruction to actually write the new record, providing parameters for the actual values:

Later we set the values for the parameters using the Parameters.AddWithValue method on the SQLiteCommand. By using parameters, we ensure that values (such as a single quote) get properly encoded before being sent to SQLite. Example:

command.Parameters.AddWithValue ("@COL1", ID);

Finally, since a person can be a manager and have a collection of employees under them, we are recursively calling the Create method on those people to save them to the database as well:

Because the routine can be called recursively from a parent object (such as a manager object loading their employees object), special code was added to handle opening and closing the connection to the database:

Initializing the database

With the changes to our Data Model in place to support reading and writing to the database, we need to open a connection to the database and initialize it on the first run. Let's add the following code to our MainWindow.cs file:

Loading bound data

With all the components for directly accessing bound data from a SQLite database in place, we can load the data in the different views that our application provides and it will automatically be displayed in our UI.

Loading a single record

To load a single record where the ID is know, we can use the following code:

Person = new PersonModel (Conn, "0");

Loading all records

To load all people, regardless if they are a manager or not, use the following code:

The only real difference in the in SQL statement (which loads only managers command.CommandText = "SELECT ID FROM [People] WHERE isManager = 1") but works the same as the section above otherwise.

Databases and comboboxes

The Menu Controls available to macOS (such as the Combo Box) can be set to populate the dropdown list either from an internal list (that can be pre-defined in Interface Builder or populated via code) or by providing your own custom, external data source. See Providing Menu Control Data for more details.

As an example, edit the Simple Binding example above in Interface Builder, add a Combo Box and expose it using an outlet named EmployeeSelector:

In the Attributes Inspector, check the Autocompletes and Uses Data Source properties:

Save your changes and return to Visual Studio for Mac to sync.

Providing combobox data

Next, add a new class to the project called ComboBoxDataSource and make it look like the following:

SQLite.NET ORM

As stated above, by using the open source SQLite.NET Object Relationship Manager (ORM) we can greatly reduce the amount of code required to read and write data from a SQLite database. This may not be the best route to take when binding data because of several of the requirements that key-value coding and data binding place on an object.

According to the SQLite.Net website, "SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed database engine in the world. The source code for SQLite is in the public domain."

In the following sections, we'll show how to use SQLite.Net to provide data for a Table View.

Including the SQLite.net NuGet

SQLite.NET is presented as a NuGet Package that you include in your application. Before we can add database support using SQLite.NET, we need to include this package.

Do the following to add the package:

In the Solution Pad, right-click the Packages folder and select Add Packages...

Enter SQLite.net in the Search Box and select the sqlite-net entry:

Click the Add Package button to finish.

Creating the data model

Let's add a new class to the project and call in OccupationModel. Next, let's edit the OccupationModel.cs file and make it look like the following:

First, we include SQLite.NET (using Sqlite), then we expose several Properties, each of which will be written to the database when this record is saved. The first property we make as the primary key and set it to auto increment as follows:

[PrimaryKey, AutoIncrement]
public int ID { get; set; }

Initializing the database

With the changes to our Data Model in place to support reading and writing to the database, we need to open a connection to the database and initialize it on the first run. Let's add the following code:

When we create an instance of this class later, we'll pass in our open SQLite.NET database connection. The LoadOccupations method queries the database and copies the found records into memory (using our OccupationModel data model).

Creating the table delegate

The final class we need is a custom Table Delegate to display the information that we have loaded from the SQLite.NET database. Let's add a new TableORMDelegate to our project and make it look like the following:

First, we gain access to our SQLite.NET database, creating and populating it if it doesn't already exist. Next, we create a new instance of our custom Table Data Source, pass in our database connection and we attach it to the Table. Finally, we create a new instance of our custom Table Delegate, pass in our Data Source and attach it to the table.

Summary

This article has taken a detailed look at working with data binding and key-value coding with SQLite databases in a Xamarin.Mac application. First, it looked at exposing a C# class to Objective-C by using key-value coding (KVC) and key-value observing (KVO). Next, it showed how to use a KVO compliant class and Data Bind it to UI elements in Xcode's Interface Builder. The article also covered working with SQLite data via the SQLite.NET ORM and displaying that data in a Table View.