WIN10 UWP Development Series: Support for asynchronous SQLite

The previous article has implemented the use of SQLite in the UWP as a local storage, as a mobile program, the timely response to user action is an important way to improve the user experience, so many of the UWP API is asynchronous. So how do you make SQLite support async?

Refer to Sqlite.net-pcl's GitHub page: Https://github.com/oysteinkrog/SQLite.Net-PCL

You can see that SQLITE.NET-PCL is asynchronous, and when you create a database link, you can create a synchronous sqliteconnection, or you can create an asynchronous sqliteasyncconnection:

sqliteasyncconnection The Sqliteasyncconnection class now takes a Func in the constructor instead of a path. This was done because the async classes was now just meant to be wrappers around the normal SQLite connection. To use sqliteasyncconnection just create a instance of a sqliteconnectionwithlock and pass in that through a func, e.g.: New Sqliteasyncconnection (() =>_sqliteconnectionwithlock); Aware the Task.run pattern used in sqliteasyncconnection can is considered an anti-pattern (libraries Shoul D not provide async methods unless they is truly async). This was maintained for backwards compatability and for use-cases where Async-isolation is handy.

In previous versions, the creation of Sqliteasyncconnection and Sqliteconnection was similar in that it was passed in a database file address, but the async constructor in the new version changed a bit and needed to pass in a func.

Next, let's look at how to use SQLite asynchronously.

First, add SQLITE.NET.ASYNC-PCL support

Or just change it in the last example, first the SQLITE.NET-PCL we added earlier is not asynchronous, and we need to add another NuGet package:

This can be used asynchronously.

Ii. Creating an Asynchronous Database link 

 Public sqliteasyncconnection Getdbconnectionasync () {varnewnew Sqliteconnectionwithlock (newnewfalse)); var New sqliteasyncconnection (connectionfactory); return asyncconnection;

Change the initialization method to:

 Public Async  = path.combine (Windows.Storage.ApplicationData.Current.LocalFolder.Path, dbfilename); var db = getdbconnectionasync (); await db. Createtableasync<useritem>();}

Note To call this asynchronous in the StartupFunctions.cs file, comment out the original Init method.

iii. Asynchronous Access to the database

In fact, there is not much to say, is to change the original synchronization method to asynchronous, such as inserting data:

 Public Async task<int> insertuserasync (Useritem item) {int0; var conn =await  Conn. Insertasync (item); return result;}

Get all data:

 Public Async Task<list<useritem>> Getalluserasync () {Listnew list<useritem>(); var conn =await Conn. Table<useritem>(). Tolistasync (); return result;}

Queries can look like this:

 Public Async Task<list<useritem>> Getuserlistasync (string  key) {Listnew List<useritem >(); var conn =await Conn. Table<useritem> (). Where (x = x.username.contains (key)). Tolistasync (); return result;}

Several other update and delete also have corresponding asynchronous methods, and then do not write.

There are a few other ways that Queryasync, Executeasync, Executescalarasync, and so on can execute SQL statements directly, such as:

 Public Async task<int> getusercount () {var conn = DbContext.Instance.GetDbConnectionAsync () ; return await Conn. executescalarasync<int> ("Select COUNT (*) from Useritem");}

It is recommended to use asynchronous methods for better performance.

WIN10 UWP Development Series: Support for asynchronous SQLite