SQLite comes with a c/c++ api that can be used for access, however it is far more common to use a c# wrapper. SQLite is a Object Relational Mapping or ORM for short, an orm is simply put an orm is a way to map object in memory to datatables just like entity framework if you're familiar with that technology. Is it right, sometimes and sometimes not, but in the context of a mobile application more often then not the benefits far outweigh the drawbacks. The benefit of ORMs is that the eliminate the need for SQL statements and let us work with our data using LINQ statements, in my opinion SQL statements are great if you are leveraging stored procedures, however since SQLite doesn't support stored procedures then the benefit of SQL statements is eliminated.
To map our data objects to datatables we decorate our models with attributes that let SQLite now how to translate our objects to memory and vice versa
SQLite supports the following types and maps the appropriately
| C# | SQLite Type |
| int | long |
| long | bigint |
| bool | integer (1=true,0=false) |
| enum | integer |
| float | real |
| double | real |
| decimal | real |
| string | varchar |
| GUID | varchar |
| DateTime | Numeric or text |
| byte[] | BLOB |
SQLite mono libraries use a ado.net approach for IO, but the performance isn't as great as it could be and it's not the most modern approach. Microsoft has a PCL that is highly preferment but not very popular, because of poor documentation and other reasons and finally SQLite has a SQLite.net nuget that is the most common and stable approach for Xamarin, odds are you'll want to leverage the third option.
We are going to use the SQLite.net approach. add the SQLite.net nuget
- Package Name:sqlite-net-pcl
- Author: Frank A. Krueger
- Id:sqlite-net-pcl
- url: nuget.org/pacakges/sqlite-net-pcl
To get started let's put together a model that will be represented inside our DB as a table
[Table("TBL_wine")]
class Wine
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
[MaxLength(250), Unique, NotNull]
public string Name { get; set; }
[MaxLength(100), NotNull]
public string Country
{ get; set; }
[NotNull]
public int Year { get; set; }
[NotNull]
public WineType WineType { get; set; }
}
with that done to access our data we are going to have to set up a connection and ensure that our table exists
//create
connection ensure that winetable exists
SQLiteConnection conn =
GetConnection();
conn.CreateTable<Wine>();
now the createTable method will create a table if it doesn't exist, update an existing table to match the schema, however it wont update existing rows.
so now that we've created our table and connected to our database, let's insert some rows.
SQLiteConnection
conn;
Wine wine
= new Wine();
private async Task<bool> SaveDataAsync(string name, string year, string country)
{
wine.Name = name;
wine.Year = int.Parse(year);
wine.Country = country;
return conn.Insert(wine)!= -1;
}
As simple as that if the SQLite insert function is successful it returns the incremented primary key of the table row, otherwise a -1 if it fails.
to read data we can do the following
private async Task<ArrayAdapter<string>> GetDataAsync()
{
var data = conn.Table<Wine>().ToList().Select(w =>
w.Name).ToList();
return new
ArrayAdapter<string>(this,
Android.Resource.Layout.SimpleListItem1, data);
}
Now we are reading and writing to our database, but we've created a serious performance challenge, and that is all of our IO is being done synchronously, that is to say that our application will be freezing while it reads and writes data. SQLite is thread agile, but not thread safe, this means that we can access our database from any thread but not safely, meaning that if we make parallel wrights most likely our database will become corrupted. If this happens SQLite will through a database corrupted exception meaning that you've just hosed all your data and need start fresh.
Thankfully SQLite comes with an async api via the SQLiteAsyncConnection Class, it comes with various Async functions and methods such as
- CreateTableAsync(tableName)
- DropTableAsync(tableName)
- GetAsync(predicate)
- InsertAsync(object)
- InsertAllAsync(objects)
- InsertOrReplace(object)
- UpdateAsync(object)
- UpadteAllAsync(objects)
- DeleteAsync(object)
- QueryAsync(sql parameters)
- ExecuteAsync()
- ExecuteScalerAsync(sql, parameters)
- RunInTransactionAsync(action<SQLiteConnection>)
The RunInStransactionAsync function will execute ACID transactions that is
- Atomic: Everything is successful or nothing is
- Consistent: Reads are consistent until the transaction commits
- Isolated: Changes are not seen until they are all complete and committed
- Durable: All transactions must complete, if one fails they all fail
Async calls are great if we are going to update the UI thread, but if our UI will not be updated then there is no real reason to come back to it so we can use ConfigureAwait(false) to not return to the UI thread, but continue our work on a background thread.
Let's take a look at our application, firstly let's create an async connection
SQLiteAsyncConnection
GetConnection()
{
var dbName = Path.Combine(PATH, "dataBaseName.db3");
return new
SQLiteAsyncConnection(dbName);
}
next let's make sure that our table exists and create it if it doesn't.
await conn.CreateTableAsync<Wine>();
after that, let's take a look at reading in our data
private async
Task<ArrayAdapter<string>>
GetDataAsync()
{
//load data
form db
var data = (await
conn.Table<Wine>().ToListAsync()).Select(w => w.Name).ToList();
return new
ArrayAdapter<string>(this,
Android.Resource.Layout.SimpleListItem1, data);
}
and finally let's add a snippet to insert our data
private async Task<bool> SaveDataAsync(string name, string year, string country)
{
wine.Name = name;
wine.Year = int.Parse(year);
wine.Country = country;
//save wine to
db
return await
conn.InsertAsync(wine) != -1;
}
Here's a view
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:a="http://schemas.android.com/apk/res/android"
a:orientation="vertical"
a:layout_width="match_parent" a:layout_height="match_parent">
<EditText
a:id="@+id/WineName_TextBox" a:hint="enter in
wine name"
a:layout_width="fill_parent" a:layout_height="wrap_content" />
<EditText
a:id="@+id/WineYear_TextBox" a:hint="enter in
wine year"
a:layout_width="fill_parent" a:layout_height="wrap_content"
a:numeric="integer" />
<EditText
a:id="@+id/WineCountry_TextBox" a:hint="enter in
wine country"
a:layout_width="fill_parent" a:layout_height="wrap_content" />
<RadioGroup
a:id="@+id/WineType_RadioGroup"
a:minWidth="25px" a:minHeight="25px"
a:orientation="horizontal" a:layout_gravity="center_horizontal"
a:layout_width="wrap_content" a:layout_height="wrap_content" >
<RadioButton
a:id="@+id/Red_RadioButton"
a:layout_width="wrap_content" a:layout_height="wrap_content"
a:text="Red" a:paddingEnd="10dp" />
<RadioButton
a:layout_width="wrap_content"
a:layout_height="wrap_content"
a:text="White"
a:paddingEnd="10dp"
a:id="@+id/White_RadioButton" />
<RadioButton
a:id="@+id/Rose_RadioButton"
a:layout_width="wrap_content" a:layout_height="wrap_content"
a:text="Rose" a:paddingEnd="10dp" />
<RadioButton
a:id="@+id/Bubbles_RadioButton" a:text="Bubble"
a:layout_width="wrap_content" a:layout_height="wrap_content" />
</RadioGroup>
<Button
a:id="@+id/SaveButton" a:text="Save"
a:layout_width="fill_parent" a:layout_height="wrap_content" />
<ListView
a:id="@+id/DataListView"
a:layout_width="fill_parent" a:layout_height="fill_parent" />
</LinearLayout>
and here's codebehind
using Android.App;
using Android.OS;
using Android.Support.V7.App;
using Android.Widget;
using SQLite;
using System;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
namespace pav.sqlite.Activities
{
enum WineType { red, white, rose, bubble }
[Activity(Label = "@string/app_name", Theme = "@style/AppTheme", MainLauncher
= true)]
public class MainActivity : AppCompatActivity
{
Button SaveButton;
EditText NameTextBox, YearTextBox,
CountryTextBox;
RadioGroup TypeRadioGroup;
RadioButton RedRadioButton,
WhiteRadioButton, RoseRadioButton, BubbleRadioButton;
ListView DataListView;
ArrayAdapter<string> DataAdapter;
SQLiteAsyncConnection conn;
Wine wine = new Wine();
//Andriod path
for sandbox for file IO
static readonly string PATH =
System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
protected async override void OnCreate(Bundle savedInstanceState)
{
base.OnCreate(savedInstanceState);
// Set our view
from the "main" layout resource
SetContentView(Resource.Layout.activity_main);
//wire up view
objects with backend refrences
NameTextBox = base.FindViewById<EditText>(Resource.Id.WineName_TextBox);
YearTextBox = base.FindViewById<EditText>(Resource.Id.WineYear_TextBox);
CountryTextBox = base.FindViewById<EditText>(Resource.Id.WineCountry_TextBox);
TypeRadioGroup = base.FindViewById<RadioGroup>(Resource.Id.WineType_RadioGroup);
RedRadioButton = base.FindViewById<RadioButton>(Resource.Id.Red_RadioButton);
WhiteRadioButton = base.FindViewById<RadioButton>(Resource.Id.Red_RadioButton);
RoseRadioButton = base.FindViewById<RadioButton>(Resource.Id.Red_RadioButton);
BubbleRadioButton = base.FindViewById<RadioButton>(Resource.Id.Red_RadioButton);
SaveButton = base.FindViewById<Button>(Resource.Id.SaveButton);
DataListView = base.FindViewById<ListView>(Resource.Id.DataListView);
//create
connection ensure that winetable exists
conn = GetConnection();
await conn.CreateTableAsync<Wine>();
//populate
listview with existing items
DataAdapter = await GetDataAsync();
DataListView.Adapter = DataAdapter;
//wire up evnet
receivers
SaveButton.Click +=
SaveButton_Click;
TypeRadioGroup.CheckedChange +=
TypeRadioGroup_CheckedChange;
}
//update wine
type from radio buttons
private void
TypeRadioGroup_CheckedChange(object sender, RadioGroup.CheckedChangeEventArgs e)
{
if (e.CheckedId == RedRadioButton.Id)
wine.WineType = WineType.red;
else if (e.CheckedId
== WhiteRadioButton.Id)
wine.WineType = WineType.white;
else if (e.CheckedId
== RoseRadioButton.Id)
wine.WineType = WineType.rose;
else if (e.CheckedId
== BubbleRadioButton.Id)
wine.WineType =
WineType.bubble;
}
//save button
click
private async void SaveButton_Click(object sender, EventArgs e)
{
var name = NameTextBox.Text;
var year = YearTextBox.Text;
var country = CountryTextBox.Text;
var type = TypeRadioGroup.CheckedRadioButtonId;
if (String.IsNullOrEmpty(name) || String.IsNullOrEmpty(year) ||
String.IsNullOrEmpty(country) || type == -1)
{
Toast.MakeText(this, $"Failed
to add", ToastLength.Short).Show();
}
else
{
await SaveDataAsync(name, year, country);
DataAdapter.Add(name);
Toast.MakeText(this, $"added:
{name} ({year})", ToastLength.Short).Show();
wine = new Wine();
TypeRadioGroup.ClearCheck();
NameTextBox.Text =
YearTextBox.Text = CountryTextBox.Text = String.Empty;
}
}
private async Task<bool> SaveDataAsync(string name, string year, string country)
{
wine.Name = name;
wine.Year = int.Parse(year);
wine.Country = country;
//save wine to
db
return await
conn.InsertAsync(wine) != -1;
}
private async
Task<ArrayAdapter<string>>
GetDataAsync()
{
//load data
form db
var data = (await
conn.Table<Wine>().ToListAsync()).Select(w => w.Name).ToList();
return new
ArrayAdapter<string>(this,
Android.Resource.Layout.SimpleListItem1, data);
}
SQLiteAsyncConnection GetConnection()
{
var dbName = Path.Combine(PATH, "dataBaseName.db3");
return new
SQLiteAsyncConnection(dbName);
}
}
[Table("TBL_wine")]
class Wine
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
[MaxLength(250), Unique, NotNull]
public string Name { get; set; }
[MaxLength(100), NotNull]
public string Country
{ get; set; }
[NotNull]
public int Year { get; set; }
[NotNull]
public WineType WineType { get; set; }
}
}
