In my previous post, I shared how to get started with SQLite in a Xamarin.Forms and Xamarin.iOS app. This could also be added to a Xamarin.Android app with just a few lines of code, but the project I’m creating right now doesn’t need that.

Here are some of the key concepts I covered:

  • Adding required NuGet packages
  • Creating the Data Model and Tables
  • Establishing a Cross-Platform Database Connection

In this post I’m going to continue this topic and start exposing the database and its contents to my app. To do this we’ll create a helper class that allows us to request, update, add, and delete data from the simple model table we made in the previous post.

Creating the Helper Class

This is a very simple task, to be honest. I simply create a helper class for each Model I’ve already created with a standard set of methods for the tasks I may need to perform. Based on the NLUser model I created earlier, here’s what that might look like (NLUsers.cs file created in the Xamarin.Forms project):

  using System;
 using SQLite;
 using System.Collections.Generic;
 using System.Linq;
 
 
 namespace NorthernLights
 {
     public  class NLUsers
     {
         static SQLiteConnection db = NLData.database;
 
         public NLUsers ()
         {
 
         }
 
         //Get all items in the database
         public static List<NLUser>GetAll(){
             return db.Table<NLUser> ().ToList ();
         }
 
         //Get a specific item from the database
         public static NLUser Get(int Id) {
             return db.Table<NLUser> ().FirstOrDefault (x => x.UserId ==  Id);
         }
 
         //Add an item to the database
         public static int Add(NLUser item) {
             return db.Insert (item);
         }
 
         //Add multiple items at once (better than looping through a collection)
         public static void Add(IEnumerable<NLUser> items) {
             db.InsertAll (items, true);
         }
 
         //Remove a book
         public static void Remove(int Id) {
             db.Delete<NLUser> (Id);
         }
 
         //Remove all books
         public static void RemoveAll() {
             db.DeleteAll<NLUser> ();
         }
 
     }
 
 
 } 

You’ll notice I kept much of the language in these methods very “generic” (Get instead of GetUser, etc.). The reason for this is that this is a template I will be using in several helper classes, so I can quickly copy / paste it in for another model and simply change the class names (from NLUser to NLCustomer, for example).

I could easily add additional methods that pull data from the system by adding more “Get” methods if I needed to.

Creating Relationships

The next big piece my app is going to use over and over again is connecting data together. SQLite doesn’t have strong foreign key support on its own, so I simply create methods within my models and helper classes to connect the data.

Here’s another model in my app. It’s a simple comment table that will be used for adding comments to a ticket in the database.

 using System;
 using SQLite;
 using System.Collections.Generic;
 using System.Linq;
 
 
 namespace NorthernLights
 {
     public class NLTicketComment
     {
 
         [PrimaryKey]
         public Guid CommentId { get; set; }
 
         [Indexed]
         public int CommentTicketId { get; set; }
 
         [Indexed]
         public int CommentUserId { get; set;}
 
         public  DateTime CommentCreatedAt { get; set; }
         public string CommentText {get;set;}
 
         public NLTicketComment ()
         {
             //Create a new GUID for the comment
             CommentId = Guid.NewGuid();
         }
     }
 }

As you can see, this model has a “CommentUserId” property that I’ll want to link to my User Table. I could call the NLUser.GetUser method every time I wanted to access that property, but why bother? Instead, I’ll create a read-only property called NLUser that will take me straight to the related user record. It looks like this:

        [Ignore]
        public NLUser User {
            get {
                return NLUsers.Get (CommentUserId);
            }
        }

Obviously, it would be good to have some error handling in here in the event that the UserId doesn’t exist or hasn’t been set yet, but you get the idea, right?

I could also add a method in my NLUser model to access related comments using a similar structure and method call to the NLTicketComments helper class.

Testing It Out

Now that I have the ability to access the data in my app, let’s make it do something. As a very simple example here, I’m going to add some users to my database and then display them in a list.

First, I added this method to my NLUsers helper class to allow me to easily reset and load sample data into the system.

         //Create sample data
         public static void CreateSampleData() {
             //Remove all users
             RemoveAll ();
 
             //Add some sample users
             Add (new NLUser () {
                 UserId = 1,
                 UserName = "User 1",
                 UserIsActive = true,
                 UserEmail = "nobody@nowhere.com",
                 UserPhone = "555-555-5555"
             });
 
             Add (new NLUser () {
                 UserId = 2,
                 UserName = "User 2",
                 UserIsActive = true,
                 UserEmail = "nobody@nowhere.com",
                 UserPhone = "555-555-5555"
             });
 
             Add (new NLUser () {
                 UserId = 3,
                 UserName = "User 2",
                 UserIsActive = true,
                 UserEmail = "nobody@nowhere.com",
                 UserPhone = "555-555-5555"
             });
 
         }

Once that’s done, I can simply call that method in my Xamarin.Forms class and then bind it to a list. That class (which in this case is inside the “App” class, looks something like this):

 using System;
 using Xamarin.Forms;
 using System.Collections.Generic;
 
 namespace NorthernLights
 {
     public class App : Application
     {
         public App ()
         {
 
             //Create some sample data
             NLUsers.CreateSampleData ();
             List<NLUser> users = NLUsers.GetAll ();
 
             //Create a list bound to the data
             var template = new DataTemplate (typeof(TextCell));
             template.SetBinding(TextCell.TextProperty,"UserName");
 
             ListView listUsers = new ListView () {
                 ItemsSource = users,
                 ItemTemplate = template
             };
 
 
             // The root page of your application
             MainPage = new ContentPage {
                 Content = new StackLayout {
                     VerticalOptions = LayoutOptions.Center,
                     Children = {
                         listUsers
                     }
                 }
             };
         }

     }
 }

This code does a few things the standard App class didn’t:

  • It creates sample user data (clearing the data in the process)
  • It loads that sample data into the users object.
  • It creates a simple DataTemplate that will be used to display the data in a cell
  • It binds the TextProperty of that cell to the UserName field of the current user
  • It creates a ListView based on the DataTemplate and users data source and adds it to the page.

The Finished Product

And that, my friend, is it. We’ve now taken the data models and database we created in the first post and created helper methods to access that data, and now we’ve connected that data to a ListView and displayed it in our cross-platform app.

Here’s a screenshot of the finished app. Ugly at best, but it does the trick!

Data-Bond Xamarin ListView

 

Adding Android Support

The only piece that’s really missing here from what I’ve done in the past is to wire up the GetConnection method for Android use, if you were making a cross-platform app. Here’s what that code looks like, in case you’d like to have it handy. Just create a dependency class like we did for the iOS app in the Android app and add the following code to handle the method of creating / getting the database connection:

 public SQLiteConnection GetConnection(bool ResetDatabaseOnStart)
         {
             //Build the path for storing the Android database
             var filename = "GbrSQLite.db3";
             string folder = System.Environment.GetFolderPath (System.Environment.SpecialFolder.Personal);
             var path = Path.Combine (folder, filename);
 
             if (!_initiated) {
                 //do things just once while the app is running
                 _initiated = true;
                 //Reset the file if requested
                 if (ResetDatabaseOnStart) { 
                     if (File.Exists (path)) {
                         File.Delete (path);
                     }
                 }
             }
 
             var connection = new SQLiteConnection (path);
             return connection;
         }

Still Stuck?

I hope these two posts have helped you. I gained this knowledge over the course of a few weeks as I built my first Xamarin.Forms app and it was good to get it down as we start our second. This is a nice process you can use to quickly get jump-started on a data-driven Xamarin.Forms app. In the near future I plan to write about other data-driven experiences with Xamarin, including syncing local data to a web-based database and API. I hope you’ll check back soon!

Would you like help building your app? We’re available and glad to assist. Simply contact us or leave a comment on this post.