title: Using SQLite with async in C#
body: <p>I am trying to get my head around the <code>async</code>/<code>await</code> keywords and usage, and I think I got the basics. But something isn't working right in my SQLite code.</p>
  
  <p>I am using the <a href="https://www.nuget.org/packages/System.Data.SQLite.Core/" rel="nofollow">SQLite.core NuGet package</a> in a simple project I've been working on. I noticed that the async code I wrote does not behave asynchronously (like I expected), so I created a simpler test project to test my understanding.</p>
  
  <p>In my test code, I open a connection to an in-memory database (I have the same problem with a file-based database. In-memory was just easier in the test code), and issue a single "create table" command, using <code>ExecuteNonQueryAsync</code>. I don't immediately <code>await</code> for the result, but instead write something to the console, before finally using the <code>await</code> keyword.</p>
  
  <p>I expect the console command to be executed before the <code>ExecuteNonQueryAsync</code> finishes, so in my test I should see "1 2 3 4". But instead I get "1 3 2 4"</p>
  
  <p>I run an identical test using an SQL Server LocalDB connection (running the same code, only the <code>DbConnection</code> is different), and get the expected "1 2 3 4". So I guess my basic understanding of the <code>async</code> is not that far off from the mark.</p>
  
  <p>What am I missing? Do I need to use a special connection string with the SQLite in order to support the <code>async</code> methods? Does it even support it?</p>
  
  <p>My full test project can be found <a href="https://github.com/ATGardner/OfflineMapFileGenerator/tree/DatabaseTest" rel="nofollow">here</a>.</p>
  
  <p>And here is the main program itself:</p>
  
  <pre><code> namespace DatabaseTest
     {
      using System;
      using System.Data.Common;
      using System.Data.SqlClient;
      using System.Data.SQLite;
      using System.Threading.Tasks;
  class Program
  {
      static void Main(string[] args)
      {
          Task.WaitAll(TestDatabase(true), TestDatabase(false));
      }
  
      private static async Task TestDatabase(bool sqLite)
      {
          Console.WriteLine("Testing database, sqLite: {0}", sqLite);
          using (var connection = CreateConnection(sqLite))
          {
              connection.Open();
              var task = ExecuteNonQueryAsync(connection);
              Console.WriteLine("2");
              await task;
              Console.WriteLine("4");
          }
      }
  
      private static DbConnection CreateConnection(bool sqLite)
      {
          return sqLite ?
              (DbConnection)new SQLiteConnection(string.Format("Data Source=:memory:;")) :
              new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\DatabaseTest.mdf;Integrated Security=True;Connect Timeout=30");
      }
  
      private static async Task ExecuteNonQueryAsync(DbConnection connection)
      {
          var command = connection.CreateCommand();
          command.CommandText = "CREATE TABLE test (col1 integer);";
          Console.WriteLine("1");
          await command.ExecuteNonQueryAsync();
          Console.WriteLine("3");
      }
  }
  </code></pre>
  
  <p>And the output:</p>
  
  <pre><code>Testing database, sqLite: True
  1
  3
  2
  4
  Testing database, sqLite: False
  1
  2
  3
  4
  </code></pre>