To be totally honest, this post Xamarin.Forms Local Databases covers everything you need to know.

Simply add Frank Krueger’s NuGet package to your shared code project. This library is both a database access library and ORM.

We’ll need to create the database file, hence we can create our file location using the following

private static readonly string DatabaseLocation =
   Path.Combine(
      Environment.GetFolderPath
        (Environment.SpecialFolder.LocalApplicationData), 
        "Capture.db3");

Create the database

var database = new SQLiteAsyncConnection(DatabaseLocation);
await database.CreateTableAsync<DataItem>();

Depending on the use case it may be best to create the database connection when the application starts and keep available whilst the application is running. In this instance my calls to the DB are going to be dictated by the user, i.e. pressing a button, so we can create the connection, then create a table for our data objects.

Note: The data object stored into the database must have a default constructor and not be abstract etc. for it to work with the ORM part of this library.

Closing the database

The database connection does not support the IDisposable interface, so we need to explicitly call

await database.CloseAsync();

Note: I’ve not checked the source for the library and had no issues with not closing the connection.

Inserting items into the database

We can insert new items into the database using

await database.InsertAsync(dataItem);

// or a batch insert using 

await database.InsertAllAsync(dataItems)

// or an insert OR replace using

await database.InsertOrReplaceAsync(dataItem)

Updating rows

We saw that we can InsertOrReplace rows but this is based upon whether the object is unique. In other words the data object is first inserted and then a key returned, if another object matches this newly inserted one, then it’s deleted.

We can call UpdateAsync if we know a data object has already been saved to the database and we just want to update it, i.e.

await database.UpdateAsync(dataItem);

and thus if we’re tracking, within our data object whether it’s been saved and decide whether to update or insert using

if(dataItem.Id != 0)
{
   await database.UpdateAsync(dataItem);
}
else
{
   dataItem.Id = await database.InsertAsync(dataItem);
}

Querying the database

It wouldn’t be much of a database if we couldn’t run queries on it. We can use

var list = 
   await database.QueryAsync<DataItem>(
      "SELECT * FROM [DataItem] WHERE [Success] = 0"
   );

We can also use Linq like this

var dataItem = 
   database.Table<DataItem>()
      .Where(i => i.Name == "Samsung").ToListAsync();

Hence here we’ll get a list of all DataItems with the name “Samsung”.

Deleting data

We can use

await database.DeleteAsync(dataItem);

Deleting the database

As the database is stored as a file we can simply delete the file from the file system using

if (File.Exists(DatabaseLocation))
{
   File.Delete(DatabaseLocation);
}

More on our data objects

The only thing we’ve specified in any of the above code, for our data objects, is that they must have a default constructor on a concrete class, but as this SQLite NuGet package is acting like an ORM (as opposed to us creating our tables etc. in SQL), we’ll probably need some of the following properties added to our data object…

In the above we’ve showed that we can get a key back from an insert etc. but we can also add a key to the data objects themselves and have SQLite supply the keys. So for example we can add the following to our data item class

[PrimaryKey, AutoIncrement]
public int Id { get; set; }

Now the id is our primary key and is automatically supplied during an insert using an AutoIncrement algorithm.

We can also add indexed properties, for example

[Indexed]
public string Name { get; set; }

We can ignore properties by marking them with the IgnoreAttribute, i.e.

[Ignore]
public int AverageDb { get; set; }

Other attributes, include the following UniqueAttribute, MaxLengthAttribute , NotNullAttribute, StoreAsTextAttribute (for enums) and CollationAttribute (BINARY, NOCASE, RTRIM are supported with BINARY being the default) for comparisons.