VistaDB 6 Introduces Async / Await Support

For more information on what’s new in VistaDB 6 see What’s Coming in VistaDB 6

VistaDB 6 natively supports async/await asynchronous database operations, making it much easier to create responsive UI applications that interact with databases. Instead of writing your own code to put operations that could block on a background thread so your UI stays responsive you can just await the call to VistaDB. While the engine is working on your request your thread can go back to other UI actions.

Here’s a simple ADO.NET async example:

using (VistaDBConnection connection = new VistaDBConnection())
{
    connection.ConnectionString = @"Data Source=C:\mydatabase.vdb5";
    await connection.OpenAsync();

    using (VistaDBCommand command = new VistaDBCommand())
    {
        command.Connection = connection;
        command.CommandText = "INSERT INTO MyTable (MyColumn) VALUES ('Test')";
        await command.ExecuteNonQueryAsync();
    }
}

In this example both opening the connection and executing the database operation are done using async/await. With async/await, while VistaDB is doing the work to open a database or carry out your command the thread you called VistaDB with is free to do other work. Once a result is ready that thread will be reclaimed and used to handle the next steps. So, this doesn’t require experience writing multithreaded code to work.

To use this new feature requires invoking the async versions of common ADO.NET methods (they’re there for nearly all of them, with “Async” tacked on the end) and adding the await keyword before them (and async to the method containing the call). If you haven’t used async/await before the first time you introduce it can be daunting because you need to have the entire call chain be async - all the way back to the thread start in your code. This is necessary so .NET can meaningfully dispatch your thread to other work while your code is waiting on a result.

For more information on async / await see Asynchronous programming with async and await (C#)

Entity Framework Async / Await

Most EF operations also support using async/await to execute without keeping your application thread. For example:

using (var ctx = new Entities("EntitiesConnection"))
{
    var detail = await ctx.Set<Session_Detail>()
        .FirstAsync(sd => sd.PK_Session_Id == sessionId);
}

In this example an Entity Framework context called “Entities” is used and a single line query is run to select an entity by its primary key.

Improving Performance and Responsiveness

When async/await support was added to ADO.NET it originally wrapped the synchronous methods of each provider so you could always use the async methods but just might not get any benefit from them. With VistaDB 6, we’ve added native support for these inside the engine along with cancellation support where safe.

As an embedded database, VistaDB is usually processor bound. To make async operations effective we’ve pushed all the hard work the engine does off to a background thread. This gets to the heart of what async/await does and doesn’t do - contrary to the name it’s not async as in letting operations complete independently and asynchronously but instead about keeping threads available to work on many tasks at the same time.

Where Should Async / Await be used with VistaDB?

Since VistaDB is not generally network or IO bound the main scenario to use async / await with VistaDB is when calling it from the UI thread in a WPF, WinForms or Xamarin application or when calling it from within a web page or controller in a web application.

If you’re already on a background thread in a WPF/WinForms/Xamarin application then async/await will be all overhead, no benefit (since you’re not going to reuse the thread for other useful work while it’s waiting on the database to complete its task).

If you’re in a web page or web controller then async/await can significantly help application scalability by freeing up web request threads to work on other requests while waiting on the database to complete its work. This won’t help the speed of a single request, but will let your web site handle many more users simultaneously before it runs out of worker threads.