Edit

Using Data in an App

  • Article

The DataAccess_Adv sample shows a working application that allows user-input and CRUD (Create, Read, Update and Delete) database functionality. The application consists of two screens: a list and a data entry form. All the data access code is re-usable in iOS and Android without modification.

After adding some data the application screens look like this on Android:

Android sample list

Android sample detail

The Android Project is shown below – the code shown in this section is contained within the Orm directory:

Android project tree

The native UI code for the Activities in Android is out of scope for this document. Refer to the Android ListViews and Adapters guide for more information on the UI controls.

Read

There are a couple of read operations in the sample:

  • Reading the list
  • Reading individual records

The two methods in the StockDatabase class are:

public IEnumerable<Stock> GetStocks ()
{
    lock (locker) {
        return (from i in Table<Stock> () select i).ToList ();
    }
}
public Stock GetStock (int id)
{
    lock (locker) {
        return Table<Stock>().FirstOrDefault(x => x.Id == id);
    }
}

Android renders the data as a ListView.

Create and Update

To simplify the application code, a single save method is provided that does an Insert or Update depending on whether the PrimaryKey has been set. Because the Id property is marked with a [PrimaryKey] attribute you should not set it in your code. This method will detect whether the value has been previous saved (by checking the primary key property) and either insert or update the object accordingly:

public int SaveStock (Stock item)
{
    lock (locker) {
        if (item.Id != 0) {
            Update (item);
            return item.Id;
    } else {
            return Insert (item);
        }
    }
}

Real world applications will usually require some validation (such as required fields, minimum lengths or other business rules). Good cross-platform applications implement as much of the validation logical as possible in shared code, passing validation errors back up to the UI for display according to the platform's capabilities.

Delete

Unlike the Insert and Update methods, the Delete<T> method can accept just the primary key value rather than a complete Stock object. In this example a Stock object is passed into the method but only the Id property is passed on to the Delete<T> method.

public int DeleteStock(Stock stock)
{
    lock (locker) {
        return Delete<Stock> (stock.Id);
    }
}

Using a pre-populated SQLite database file

Some applications are shipped with a database already populated with data. You can easily accomplish this in your mobile application by shipping an existing SQLite database file with your app and copying it to a writable directory before accessing it. Because SQLite is a standard file format that is used on many platforms, there are a number of tools available to create an SQLite database file:

  • SQLite Manager Firefox Extension – Works on Mac and Windows and produces files that are compatible with iOS and Android.

  • Command Line – See www.sqlite.org/sqlite.html .

When creating a database file for distribution with your app, take care with the naming of tables and columns to ensure they match what your code expects, especially if you're using SQLite.NET which will expect the names to match your C# classes and properties (or the associated custom attributes).

To ensure that some code runs before anything else in your Android app, you can place it in the first activity to load or you can create an Application subclass that is loaded before any activities. The code below shows an Application subclass that copies an existing database file data.sqlite out of the /Resources/Raw/ directory.

[Application]
public class YourAndroidApp : Application {
    public override void OnCreate ()
    {
        base.OnCreate ();
        var docFolder = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
        Console.WriteLine ("Data path:" + Database.DatabaseFilePath);
        var dbFile = Path.Combine(docFolder, "data.sqlite"); // FILE NAME TO USE WHEN COPIED
        if (!System.IO.File.Exists(dbFile)) {
            var s = Resources.OpenRawResource(Resource.Raw.data);  // DATA FILE RESOURCE ID
            FileStream writeStream = new FileStream(dbFile, FileMode.OpenOrCreate, FileAccess.Write);
            ReadWriteStream(s, writeStream);
        }
    }
    // readStream is the stream you need to read
    // writeStream is the stream you want to write to
    private void ReadWriteStream(Stream readStream, Stream writeStream)
    {
        int Length = 256;
        Byte[] buffer = new Byte[Length];
        int bytesRead = readStream.Read(buffer, 0, Length);
        // write the required bytes
        while (bytesRead > 0)
        {
            writeStream.Write(buffer, 0, bytesRead);
            bytesRead = readStream.Read(buffer, 0, Length);
        }
        readStream.Close();
        writeStream.Close();
    }
}