52 Weeks of Xamarin: Week 9 – Databases part 1

Working with SQLite as your database in Xamarin.Forms is not difficult but it does involve some specific steps: Database computers small

  • Add the SQLite-.Net PCL library to all three projects
  • Create the ISQLite interface
  • Add a singleton to your app class
  • Open your database in the appropriate directory and use DependencyService to access it
  • Create your table(s)
  • Write your CRUD operations

Piece of cake.

In this and the next posting I’ll go over these steps in detail and in context.

Implementing a SQLite Database – Getting Started

Begin by creating a new Xamarin.Forms application called DataBases.  Add the NuGet package SQLite-net PCL.  There are a number of similarly named packages, you want the one with a single author, Frank A. Krueger, as shown below:

Sqlite

 

Add this to all of your projects.

Create the normal Model, View and ViewModel folders and add a Data folder.  In the Data folder, add the interface ISQLite.cs

 public interface ISQLite {
     SQLiteConnection GetConnection();
 }

Next, open DataBases.cs and create a singleton to hold the PersonDatabase

         static PersonDatabase database;
 
         public static PersonDatabase Database {
             get {
                 if (database == null) {
                     database = new PersonDatabase();
                 }
                 return database;
             }
         }
 

Create a file in your iOS application named SQLite_iOS.cs and in that file put the address for your database.  In the code below, the correct string is commented out and I’m using a location on my computer to make debugging easier,

  public class SQLite_iOS : ISQLite { 
    public SQLite.SQLiteConnection GetConnection() {
     //var sqliteFilename = "Person.db";
 
                 // documents folder
     //            string documentsPath = Environment.GetFolderPath (
     //                Environment.SpecialFolder.Personal); 
 
                 // Library folder
     //            string libraryPath = Path.Combine (
     //                documentsPath, "..", "Library"); 
 
     //            var path = Path.Combine(libraryPath, sqliteFilename);
 
     var path = "/users/jesseliberty/Data/Person.db";
 
     File.Open(path, FileMode.OpenOrCreate);
 
     var conn = new SQLite.SQLiteConnection(path);
 
     return conn;
 }

Implementing CRUD operations

In your model folder create a new class Person,

 public class Person {
     [PrimaryKeyAttribute, AutoIncrement]
     public int ID { get; set; }
 
     public string FirstName { get; set; }
 
     public string LastName { get; set; }
 }
 

Notice the attributes on the ID.  These will be used by SQLite to make the ID the primary key and to ensure that it is incremented for each record.

Create a new file in the Data folder named PersonDatabase.cs.  In this file we’ll create an instance of the SQLiteConnection that we’ll call database and we’ll instantiate an object that we’ll call locker that we will use to “lock” the database during our operations.

 public class PersonDatabase {
     SQLiteConnection database;
     static object locker = new object();
 

In the constructor we’ll instantiate the database, getting the path from the platform-specific code we saw above.  We’ll then tell the database to create our Person table, which it will do from the Person class.

     public PersonDatabase() {
 
         database = DependencyService.Get<ISQLite>().GetConnection();
 
         database.CreateTable<Person>();
    }
 

We’re now ready for our CRUD operations. We’ll just implement the Save and Get operations for now.  SavePerson takes a Person object (we’ll look at how the ViewModel passes this in next week) and locks the database. It then examines the ID to see if this Person already exists in the database.  If so, it updates that record, otherwise it returns a new record.  In either case, it returns the record ID.

     public int SavePerson(Person person) {
         lock (locker) {
             if (person.ID != 0) {
                 database.Update(person);
                 return person.ID;
             } else {
                 return database.Insert(person);
             }
         }
     }
 

Similarly, GetPeople reaches into the database, and returns a list of Person objects.  It does this by using a lamda expression against the Table<Person> we created earlier,

     public IEnumerable<Person> GetPeople() {
         lock (locker) {
             return (from c in database.Table<Person>()
                     select c).ToList();
         }
     }
 

Note that for “ToList()” to work, you’ll have to add a using System.Linq statement.

Finally, we’ll implement GetPerson to return a single Person object based on ID,

     public Person GetPerson(int id) {
         lock (locker) {
             return database.Table<Person>().Where(c => c.ID == id).FirstOrDefault();
         }
     }

That’s it. Next week we’ll implement the User Interface (View and ViewModel) to see this at work.

Share

About Jesse Liberty

Jesse Liberty is an independent consultant and programmer with three decades of experience writing and delivering software projects. He is the author of 2 dozen books and multiple Pluralsight courses, and has been a Senior Technical Evangelist for Microsoft, a Distinguished Software Engineer for AT&T, a VP for Information Services for Citibank and a Software Architect for PBS. He is a Xamarin Certified Mobile Developer and a Xamarin MVP, Microsoft MVP and Telerik MVP.
This entry was posted in Xamarin. Bookmark the permalink.

2 Responses to 52 Weeks of Xamarin: Week 9 – Databases part 1

  1. Pingback: Jesse Liberty: 52 Weeks of Xamarin: Week 10 – The UI for our database program | XamGeek.com

  2. Is Lazy instantiation syntax supported? Couldn’t that be used instead of that singleton logic (assuming the Lazy mechanism is thread-safe / btw, even your singleton isn’t thread-safe, would need a lock or other way to impose a critical section)

Leave a Reply

Your email address will not be published.