The stock database available in Android is SQLite, and chances are you’ll find yourself wanting to use it in your applications. But how do you go about doing so? In this post I’ll go over some options for accessing the database, exploring both the standard Android patterns and also an example of plugging in an ORM to simplify things.

A SQLite database is pretty much exactly what the name implies: it’s a very lightweight relational database that lends itself well to small devices since it doesn’t have much overhead. Databases are each contained to a single file, and you can almost think of it as a relational facade on top of a file, allowing you to do many things that you’ve gotten used to doing in your databases: SQL, indices, triggers, views, etc. I don’t want to too much into the details of the database, but it should generally handle what you’ll want to do.

SQLiteOpenHelper

The first Android class I want to discuss is SQLiteOpenHelper. By extending this class you get an easy hook into the database creationing and versioning process. By implementing OnCreate and OnUpgrade, you can easily create and migrate database versions according to your needs. It will handle things like creating the database if it doesn’t exist, and running creation/migration in a transaction to keep things manageable.

For this post, we are going to create an app that lets the user enter and view notes, each of which consist of a title and the contents of the note. First let’s create our helper class:

public class NoteDatabaseHelper : SQLiteOpenHelper
{
  private const string DATABASE_NAME = "Notes";
  private const int DATABASE_VERSION = 1;

  public NoteDatabaseHelper(Context context)
    : base(context, DATABASE_NAME, null, DATABASE_VERSION)
  {
  }

  public override void OnCreate(SQLiteDatabase db)
  {
    db.ExecSQL(@"
        CREATE TABLE Note (
          Id INTEGER PRIMARY KEY AUTOINCREMENT,
          Title TEXT NOT NULL,
          Contents TEXT NOT NULL
        )");
  }

  public override void OnUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
  {
    db.ExecSQL("DROP TABLE IF EXISTS Note");

    OnCreate(db);
  }
}

You can see that we maintain a constant for the database version so that it can be easily updated in future versions. For the sake of a simple demo, the OnUpgrade method just drops the database if it already exists, but I wouldn’t recommend that for production apps. OnCreate creates a simple Note table to store our data. You could optionally put your data access methods in this class as well, but I like to keep them separate to keep things more organized. This class is best used to just simply handle your schema management.

The Application

Before we can move on, let’s get the application classes out of the way. First, our model class:

public class Note
{
  public long Id { get; set; }

  public string Title { get; set; }

  public string Contents { get; set; }
}

Nothing crazy there, just a POCO for storing a single note. Next, an interface for a note repository:

public interface INoteRepository
{
  IList<Note> GetAllNotes();
  long AddNote(string title, string contents);
}

For our app we’ll need a way to get a list of all notes, and also add a new one to the database. Since we only really need one instance of the repository for an application, we’ll subclass Application and store the reference there so that activities can use it.

[Application]
public class DatabaseDemoApplication : Application
{
  public INoteRepository NoteRepository { get; set; }

  public DatabaseDemoApplication(IntPtr handle, JniHandleOwnership transfer)
    : base(handle, transfer)
  {
  }

  public override void OnCreate()
  {
    base.OnCreate();
  }
}

Later, once we create some INoteRepository implementations, we can plug them in there. For this post I am going to leave out the activity implementations and their layouts since they’re not anything special or interesting, but the full source code is available in the sample project at the end of this post. Instead, let’s move on to what you came here for: implementing the repository.

The Android Way

The helper class we defined earlier provides access to built-in functions for accessing the database. You have the option of performing raw SQL queries, or using some of their managed functions that wrap the SQL up for you into simple function calls. For this repository, we will use those managed functions to simplify things. The repository will internally use an instance of NoteDatabaseHelper to manage everything. It should be noted that the OnCreate/OnUpgrade methods in the helper do not actually get called until the first time you reference the WriteableDatabase or ReadableDatabase properties on the helper class. If you have things in your create/upgrade methods that take some time, you’ll want to make sure that gets run up front, rather than when the user is already in the midst of using the app.

Our repository will be defined as:

public class StandardNoteRepository : INoteRepository
{
  private NoteDatabaseHelper _helper;

  public StandardNoteRepository(Context context)
  {
    _helper = new NoteDatabaseHelper(context);
  }

  public IList<Note> GetAllNotes()
  {
    ICursor noteCursor =
      _helper.ReadableDatabase.Query("Note", null, null, null, null, null, null);
    var notes = new List<Note>();

    while (noteCursor.MoveToNext())
    {
      notes.Add(mapNote(noteCursor));
    }

    return notes;
  }

  public long AddNote(string title, string contents)
  {
    var values = new ContentValues();
    values.Put("Title", title);
    values.Put("Contents", contents);

    return _helper.WritableDatabase.Insert("Note", null, values);
  }

  private Note mapNote(ICursor cursor)
  {
    return new Note
    {
      Id = cursor.GetInt(0),
      Title = cursor.GetString(1),
      Contents = cursor.GetString(2)
    };
  }
}

The managed query functions return an ICursor, which you can then use to iterate through the results. In some cases you might want to just return the cursor from your repository and feed it into something like a SimpleCursorAdapter to keep things simple. In this case I didn’t want to keep things dependent on an ICursor (so we can swap this out later) so I opted not to do that. All that’s left is to plug this implementation into our application and everything is good to go. Just add this to DatabaseDemoApplication’s OnCreate() method:

NoteRepository = new StandardNoteRepository(this);

And that’s how you use the stock Android APIs to access a database. Easy, right? If you’re like me, your next question will probably be “can I use an ORM to clean things up even more?” and fortunately, the answer is yes.

The ORM Way

Now let’s create a new INoteRepository, but this time we’ll use an ORM to do the querying for us. The question is, which ORM do we use? I typically reach for NHibernate for a normal .NET app, but that’s far heavier than I want to use for a mobile app (though I have heard reports of it working on MonoDroid, for whatever that’s worth). I finally settled on sqlite-net, which was pretty much designed for exactly this use case. Another big benefit of this library is that it also works well with MonoTouch, so you can get some good code re-use across platforms. It’s very low overhead, and just requires adding a single C# file to your project and you’re ready to go.

First, let’s annotate our Note model class a bit to specify that the Id column is the primary key, and auto-increments.

public class Note
{
  [PrimaryKey, AutoIncrement]
  public long Id { get; set; }

  public string Title { get; set; }

  public string Contents { get; set; }
}

Since we kept the data access logic out of NoteDatabaseHelper, we can reuse that in the new repository without any modifications and still get all the versioning management from it. The repository is defined as:

public class OrmNoteRepository : INoteRepository
{
  private NoteDatabaseHelper _helper;

  public OrmNoteRepository(Context context)
  {
    _helper = new NoteDatabaseHelper(context);
  }

  public IList<Note> GetAllNotes()
  {
    using (var database = new SQLiteConnection(_helper.WritableDatabase.Path))
    {
      return database
          .Table<Note>()
          .ToList();
    }
  }

  public long AddNote(string title, string contents)
  {
    using (var database = new SQLiteConnection(_helper.WritableDatabase.Path))
    {
      return database.Insert(new Note
      {
        Title = title,
        Contents = contents
      });
    }
  }
}

From sqlite-net we get some nice LINQ action for basic queries, and can still write raw queries if you need to. That said, remember that since the NoteDatabaseHelper is still referenced in the repository, you still have access to all the managed query functions if you find they work better for something. We use the helper to get the full path of the database instead of having to manage that ourselves, and the ORM just opens the file from there. Now if you edit DatabaseDemoApplication to use this class instead of the first repository, you’ll find that things still work as expected.

One thing you might notice is that we’re not using the ORM for table creation in the helper class even though it does include a CreateTable() method, along with some basic migration functionality built in. The reason for this is that while SQLiteOpenHelper.OnCreate() is running, it keeps the database file locked. If you try to use the ORM to access the database while it’s locked, you will get an exception. Of course you could decide not to use the helper altogether and then this wouldn’t be an issue, but in this case I didn’t want to lose the other benefits of the helper. In most cases in my mobile apps the database schema isn’t too difficult to manage, so while a little annoying, I haven’t found this to be a real dealbreaker by any means.

Those are the basics of database access in MonoDroid, using both the standard Android access patterns as well as using a nice .NET ORM for simplifying things. You can get a full copy of the source code for this project over at GitHub.