Many apps need to use databases, whether the app is database-centric or even just to store pieces of data in a structured form. A lightweight ORM can be used to make data access easier.
Accessing the database is very easy when using SQLite.NET, especially since we don't have to write a single line of SQL. Let's take a look at the following steps:
- Before we can make use of SQLite.NET, we need to add the component. Right-click on the Components folder in the project and click on Get More Components…. In the dialog that appears, we search for and add the
SQLite.NETcomponent. - Once the component is downloaded, we can start creating the C# types that will represent the tables:
public class MyTable { [AutoIncrement, PrimaryKey] public int Id { get; set; } public string FirstName { get; set; } public string LastName { get; set; } } - Now that we have our tables, we can choose where we want the database to be created to or accessed from:
var databasePath = Path.Combine( FilesDir.AbsolutePath, "database.sqlite");
- The next thing to do is to create and open a connection to the database using the path:
using (var conn = new SQLiteConnection(databasePath)) { // connection is now open for use } - With our open connection, we can create the table without having to write a SQL query:
conn.CreateTable<MyTable>();
- Now that we have a table, we can populate it, again without any SQL:
var row = new MyTable { FirstName = "Bill", LastName = "Gates" }; conn.Insert(row); - Reading values from the table is just as easy, and still does not require any SQL:
List<MyTable> allRows = conn .Table<MyTable>() .ToList();
- If we want to do filtering or ordering, we can use the normal
IEnumerableextension methods:List<MyTable> filtered = conn .Table<MyTable>() .Where(r => r.LastName == lastName) .OrderBy(r => r.FirstName) .ToList();
- When we need to use transactions, there are two ways to do this. We can use the
BeginTransaction(),Commit(), andRollback()methods:try { conn.BeginTransaction(); // do something conn.Commit(); } catch { conn.Rollback(); } - Alternatively, we can use the
RunInTransaction()method:conn.RunInTransaction(delegate { // do something });
SQLite.NET is a lightweight ORM for SQLite, providing a means to map tables and rows to objects. As this mapping takes place automatically, we have to write very few, if any, SQL statements. SQLite.NET is not a fully featured ORM, but it is often sufficient to be used in most mobile applications.
One of the main advantages of using an ORM is that there is greater type-safety as we don't have to use string-based column names for each query. And, we don't have to use string queries at all as this is generated automatically, making the code far neater and much more maintainable.
Instead of creating SQL to generate a table in the database, we create a C# type that is annotated with attributes to describe how the table is to be constructed in the database. Column types and names are pulled from the object's properties automatically.
There are also several attributes that are used to describe how keys and indexes will be created. Some of these attributes, such as [PrimaryKey], [Unique], and [AutoIncrement], provide additional information on the column type. There are also the [Table] and [Column] attributes that provide a means to override default names.
There are a few features of SQLite.NET that we can use to manage the database, such as creating and dropping tables and indexes. These methods, such as CreateTable, are found on the SQLiteConnection type. These methods allow us to manage the database without having to write SQL.
Inserting rows into a table in the database is as simple as creating a new instance of an object and passing it to the Insert() method. SQLite.NET will automatically discover the correct table and add the values from the properties into a new row in that table. We can also update and delete records in a table by passing the appropriate object to the Update() or Delete() methods.
Reading values from the table is also easy and we can make use of LINQ. The query is started by requesting a table from the connection using the Table() method and then we use LINQ to build up our query from that object. The executions of these queries are deferred until we actually start reading the values. We can make use of most of the typical LINQ commands, such as Where, OrderBy, Skip, and Take, although joins aren't supported at this time.
We can also use transactions in two ways. We have the usual begin and commit actions when using the BeginTransaction(), Commit(), and Rollback() methods. Also, we have a helper method, RunInTransaction(), which takes a delegate or an action. This method wraps the action in a transaction and automatically rolls it back in case of an exception.
SQLite.NET also has an asynchronous API. We access this using SQLiteAsyncConnection instead of the SQLiteConnection type. The advantage of using the asynchronous API is that the tasks can run on a different thread to the UI, making our apps more responsive. This asynchronous API is very similar to the synchronous API, except that the names of the methods end in Async.
For example, CreateTable becomes CreateTableAsync. Let's take a look at the following commands:
using (var conn = new SQLiteAsyncConnection(databasePath)) {
await conn.CreateTableAsync<MyTable>();
}