Le Post Infeeny

Les articles des consultants et experts Infeeny

Xamarin – Using SQLite.NET Async with PCL

With Xamarin, it is now easier than ever to share most of the logic between your apps.
Most today’s apps use a lot of data, so deciding how to store them locally can prove a challenge.
SQLite provides a great way to store and query those easily and does support Xamarin. Hurray!

In this post, we’ll see how to setup SQLite in a portable project (PCL) shared by two Xamarin projects (Android, iOS) and a UWP project in Visual Studio.
It should be more or less the same with Xamarin Studio.

If you’re interested in using SQLite.NET with Xamarin.Forms, see the Xamarin.Forms documentation instead.

Portable class library and NuGet packages

First of all, we’ll need to create a portable class library supporting Android, iOS and Windows.
Conveniently, Visual Studio provides a template which exactly fits our needs.

PCL.PNG

If for any reason you can’t or won’t use this template, you can use the generic « Class Library (Portable) » template instead.
See Xamarin’s Introduction to Portable Class Libraries for more information on it.

Then, we’ll need to add references to SQLite.NET via the NuGet package SQLite.NET.Async-PCL v3.1.1 which comes along with its dependency SQLite.Net.Core-PCL.

Nuget1.PNG

That’s all we need to setup the PCL in order to use SQLite.NET.

Using SQLite.NET

Now for the tricky part.

We can now use SQLite in the PCL and thus write it once and be able to use it on each platform. But in order to do so, we’ll need a connection to the SQLite database (and need to create it if it’s not the case).

As we’re targeting three different platforms, each with its own rules on how to access the storage space, SQLite.NET needs several things in order to work : a path to where SQLite should store the database file and a custom implementation of the wrapper around the native sqlite3.

For that, as we’re using SQLite.NET Async, we need to create a SQLiteAsyncConnection.
It’s fairly simple.

public class SQLiteDatabase
{
    public static SQLiteAsyncConnection GetConnection(string path, ISQLitePlatform sqlitePlatform)
    {
        var connectionFactory = new Func<SQLiteConnectionWithLock>(() => new SQLiteConnectionWithLock(sqlitePlatform, new SQLiteConnectionString(path, storeDateTimeAsTicks: false)));
        return new SQLiteAsyncConnection(connectionFactory);
    }
}

See what we did there?
We have created a connection to the SQLite database but all the platform-specific parts are passed as parameters, allowing us to have a common method in the PCL project.

The path parameter is a file path which SQLite will use to store its db file.
The ISQLitePlatform parameter is described in the SQLite NuGet package and represents an internal implementation of SQLite for a given platform. Don’t worry, it has already been done for us for Android, iOS and UWP.

Of course, you can use dependency injection or any architecture that fits your needs instead. The key point here is to leave the implementation detail to the output projects.

We’ll come back shortly after to these platform-specific parameters.

For now enjoy!

public class MyEntityRepository
{
    private SQLiteAsyncConnection _connection;

    public async Task InitializeAsync(string path, ISQLitePlatform sqlitePlatform)
    {
        _connection = SQLiteDatabase.GetConnection(path, sqlitePlatform);

        // Create MyEntity table if need be
        await _connection.CreateTableAsync<MyEntity>();
    }

    public async Task<MyEntity> CreateAsync(string name)
    {
        var entity = new MyEntity()
        {
            Name = name
        };
        var count = await _connection.InsertAsync(entity);
        return (count == 1) ? entity : null;
    }

    public async Task<IEnumerable<MyEntity>> GetAllAsync()
    {
        var entities = await _connection.Table<MyEntity>().OrderBy(m => m.Name).ToListAsync();
        return entities;
    }
}

 

Android / iOS / UWP projects

Xamarin.Android and Xamarin.iOS will both need the exact same configuration regarding SQLite.NET, but UWP will need one extra small step.

Along with the obvious reference to the PCL project, we’ll need to reference the SQLite.NET-PCL v3.1.1 NuGet package as well.
Warning : it is not the same package as the one we used earlier.

This one automatically includes the previously seen ISQLitePlatform implementation of the targeted platform.
When referenced in an output project, it brings another dependency with it where all the platform-specific implementation is present.

  • SQLite.Net.Platform.XamarinAndroid when referenced in a Xamarin.Android project
  • SQLite.Net.Platform.XamarinIOS in a Xamarin.iOS project
  • SQLite.Net.Platform.WinRT in a UWP project

Last step is to use our MyEntityRepository by giving it all the platform specific parameters.

Android

private string GetDbPath()
{
    string documentsPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
    return Path.Combine(documentsPath, "Todo.db3");
}

protected override async void OnCreate(Bundle bundle)
{
    base.OnCreate(bundle);

    // Set our view from the "main" layout resource
    SetContentView(Resource.Layout.Main);
    var addButton = FindViewById<Button>(Resource.Id.AddButton);
    var todoListView = FindViewById<ListView>(Resource.Id.TodoListView);
    addButton.Click += OnAddButtonClicked;

    _todoRepository = new TodoRepository();

    var path = GetDbPath();

    await _todoRepository.InitializeAsync(path, new SQLitePlatformAndroid());
    var items = await _todoRepository.GetAllAsync();

    todoListView.Adapter = new ArrayAdapter<string>(this, global::Android.Resource.Layout.SimpleListItem1, items.Select(i => i.Text).ToList());
}

private async void OnAddButtonClicked(object sender, EventArgs e)
{
    var todoEditText = FindViewById<EditText>(Resource.Id.TodoEditText);
    var text = todoEditText.Text;
    todoEditText.Text = string.Empty;

    var todoItem = await _todoRepository.CreateAsync(text);
            
    var todoListView = FindViewById<ListView>(Resource.Id.TodoListView);
    var adapter = todoListView.Adapter as ArrayAdapter<string>;
    adapter.Add(todoItem.Text);
}

 

iOS

private string GetDbPath()
{
    string documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
    return Path.Combine(documentsPath, "..", "Library", "Todo.db3");
}

public override async void ViewDidLoad()
{
    base.ViewDidLoad();
    AddButton.TouchUpInside += OnAddButtonTouchUpInside;
            
    _todoRepository = new TodoRepository();

    var path = GetDbPath();

    await _todoRepository.InitializeAsync(path, new SQLitePlatformIOS());
    var items = await _todoRepository.GetAllAsync();
            
    TodoTable.Source = new TodoTableDataSource(items, TodoTable);
    TodoTable.ReloadData();
}

private async void OnAddButtonTouchUpInside(object sender, EventArgs e)
{
    var text = TodoTextField.Text;
    TodoTextField.Text = string.Empty;

    var todoItem = await _todoRepository.CreateAsync(text);

    var source = TodoTable.Source as TodoTableDataSource;
    source.Add(todoItem);
}

 

Universal Windows Platform

Remember that one extra small step ? Now is the time to take care of it.

Windows, contrary to Android and iOS, doesn’t have SQLite3 installed by default. So we need to bring sqlite3.dll along in our appx package otherwise we’ll get an exception « Unable to load sqlite3.dll ».
To do so, we’ll need to install the Visual Studio extension for UWP which can be found on the SQLite official website.

Then we just have to add two references in our UWP project.

  • SQLite for Universal Windows Platform (that’s our sqlite3.dll added by the VSIX)
  • Visual C++ 2015 Runtime for Universal Windows Platform (it’s a dependency of the former)

 
Capture.PNG
And that’s all for that extra small step.

Now we can use SQLite like in the Android and iOS projects.

public ObservableCollection<TodoItem> Items { get; set; } = new ObservableCollection<TodoItem>();

private async Task<string> GetDbPathAsync()
{
    var folder = Windows.Storage.ApplicationData.Current.LocalFolder;
    var file = await folder.CreateFileAsync("Todo.db3", CreationCollisionOption.OpenIfExists);
    return file.Path;
}

protected override async void OnNavigatedTo(NavigationEventArgs e)
{
    _todoRepository = new TodoRepository();

    var path = await GetDbPathAsync();

    await _todoRepository.InitializeAsync(path, new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT());

    var items = await _todoRepository.GetAllAsync();
    foreach (var todoItem in items)
    {
        Items.Add(todoItem);
    }
}

private async void OnAddTodoItemButtonClicked(object sender, RoutedEventArgs e)
{
    var text = TodoTextBox.Text;
    TodoTextBox.Text = string.Empty;

    var item = await _todoRepository.CreateAsync(text);
    Items.Add(item);
}

 
And there we are! That’s all for today.
Go make some happy users with great apps using SQLite.NET and Xamarin!
 
I’ve put up a sample project on GitHub, it’s a simple Todo list app working on Android, iOS and UWP : https://github.com/TimLariviere/XamarinSQLite

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s

%d blogueurs aiment cette page :