Using a Database in MonoDroid Applications

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.

Update (12/13/2011): Updating to work with Mono for Android 4.0

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.

Tags: ,

19 Responses to “Using a Database in MonoDroid Applications”

  1. Using a Database in MonoDroid Applications…

    Thank you for submitting this cool story – Trackback from DotNetShoutout…

  2. [...] This post was mentioned on Twitter by Greg Shackles, Pablo Escribano. Pablo Escribano said: RT @gshackles: Blogged: Using a Database in MonoDroid Applications http://shackl.es/dKuVmo #monodroid #dotnet #android [...]

  3. Our ORM, EntitySpaces, supports both SQLite and Mono …

  4. [...] Using a Database in MonoDroid Applications – Greg [...]

  5. Hello,

    Is there any similar example (using ORM) for MonoTouch?

    Peter

  6. I don’t have any myself but there are some out there. Here’s one example from Craig Dunn: http://conceptdev.blogspot.com/2009/09/monotouch-with-sqlite-corporate.html

  7. Hi Greg

    I am new to android development using monodroid platform.

    Found a very good example from your site afa understanding of sqllite db create,manipulate is concerned in android application.

    But when I’ve tried implementing the same my code doesn’t get compile at all and the ERROR is shown in VS2010 is
    ———————————————————-
    ‘Android.App.Application’ does not contain a constructor that takes 1 arguments
    ———————————————————-
    public DatabaseDemoApplication(IntPtr handle)
    : base(handle)
    {
    }

    The code fails to compile on above constructor declaration. Kindly help me out for the solution on above or where I’m going wrong.

    I have downloaded full source code from GITHUB also but same also fails compiling

    Thanks,

    Sachin

  8. Sorry about that, this just changed in v4.0 which was just released. In the meantime, all you need to do is change the constructor to:

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

  9. Thanks Greg

    Its working now !!

    Sachin

  10. Great! I updated the post and GitHub samples to be compatible with Mono for Android 4.0.

  11. Hi Greg

    It would be appreciable if you can update all the projects at below link to run according to monodroid 4.0 as it seems you have only fixed DatabaseDemo project only.I’d downloaded your code for studying how things work in monodroid. but there were some errors when I tried to compile the whole solution. As a temporary fix I commented lines wherever compiler was throwing errors.

    https://github.com/gshackles/Sample-Projects/tree/master/MonoDroid/

    Thanks

  12. I had updated most of them before but it looks like I had missed BrowserDemo, which I just updated. Everything seems to be compiling on my end, so if you still run into any issues please shoot me an email and we’ll get that straightened out.

  13. Which version of Android should this work on? I can’t seem to get this to work, it just force closes when the app is started. If I take out the attributes “[PrimaryKey, AutoIncrement]“, the app runs, but is not functional. Please help. Otherwise a great article on using SQLite.

  14. It depends which approach you’re using. If you’re trying to use ADO.NET (or sqlite-net) then I know you can run into problems on versions prior to v2.2, but it depends on what you’re trying to do. Are you just running the sample from this post? You can take a look at the debug log to find out why the app is crashing: http://docs.xamarin.com/android/advanced_topics/android_debug_log

  15. Thanks for your reply Greg. It seems I was trying to add data to a column in the DB Table “Notes” that didn’t exist thanks to a typo! I appreciate your quick response, and that debug log will come in very handy

  16. I’m using ORM and running into a problem when adding a record getting back the id of the record added. I’m using this function in my Repositoy but when I check for the return value it is always 1. public long AddAddress(long CoeMID, string addressone,string city, string state, string zipcode, string POBox, string pocity, string postate, string pozipcode, string phn, string CellPhn, string Comment)
    {
    using (var database = new SQLiteConnection(_helper.WritableDatabase.Path))
    {
    return database.Insert(new Address
    {
    CoeMID = CoeMID,
    AddressOne = addressone,
    City = city,
    State = state,
    ZipCode = zipcode,
    POBox = POBox,
    POCity = pocity,
    POState = postate,
    POZipCode = pozipcode,
    Phn = phn,
    CellPhn = CellPhn,
    Comment = Comment
    });
    }
    }

  17. [...] Greg Shackles wrote about using either TinyIoC or Func with Mono for Android back in Feb 2011. [...]

  18. Muneer Natha

    Mike,

    The Insert method signature indicates that the int value returned is the ID value of the last record inserted, but the underlying method actually returns the number of records inserted (which is why you’re always getting a return value of 1).

    This bug bit me as well!

  19. Thanks for posting this example. I found it really helpful.

Leave a Reply