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.