Working with SQLite in C# & VB – DEVELOPPARADISE
17/07/2018

Working with SQLite in C# & VB

Working with SQLite in C# & VB

See Included Projects (Sample code) for a detailed description of what is inside the zip…

Contents

Background

Over the last few years, I have worked quite a bit with SQLite and built a library wrapper called DotNet.Core.SQLite. I like to work as close to the metal as possible to ensure that I can get the best performance.

The library has developed into a stable solution and I felt that others could benefit from it. So I am releasing it to the public domain in this article.

Introduction

This is not an SQLite introductory article, nor is it advanced. It assumes that you have had some experience with SQLite or other databases, however, will cover some code samples of how to use SQLite. The article covers the features found in the DotNet.Core.SQLite library and how to use them.

If you are getting started, there are other articles that can be found on and off CodeProject:

If you are expecting this article to cover Entity Framework for SQLite, sorry, this is not for you.

The article is in three parts. The first covers how to use it and the second part benchmarks some core functions, and the third part digs into sections of the library code.

I have to apologise up front for the length of the article. It was put together over a 6-month period in my spare time and covers the DotNet.Core.SQLite library in detail covering usage, benchmarking, and a look at how it works internally.

Overview

This DotNet.Core.SQLite library is not a replacement for the standard SQLite library methods and classes but is complimentary. So you can use parts with existing code or replace entire logic with these DotNet.Core.SQLite library classes and extension methods.

Included are a set of classes and extensions that I use for logging, debugging and general development. The DotNet.Core.SQLite library has the following support:

  1. Multiple database support via DBContext and DbRegistry classes
  2. Table & View contexts with methods via DbResourceContext<T> -> DbTableContext<T> & DbViewContext<T>
  3. Full Async support
  4. Unit of Work for transactions with automatic rollback
  5. Support for SQLite Savepoints (nested transactions)
  6. Full Pragma command support – GetPragma, SetPragma, & GetPragmas (bulk query)
  7. Custom tracing & logging support – Select types of events to track via custom classes
  8. DB Schema, Settings, and Connection state information reporting
  9. DDL (Data Definition Language) generation

The DotNet.Core.SQLite library and following article will give you the tools and sample code to enable you to do the same.

Requirements

The DotNet.Core.SQLite library is built using:

  • .Net Framework version 4.5 (benchmarking is using 4.6.1)
  • C# language version 7.0
  • System.Data.SQLite version 1.0.106

Official Website: http://www.sqlite.org & System.Data.SQLite

Nuget: System.Data.SQLite

Tools

Following is a list of 3rd-party tools for working with SQLite:

How to Use

Before we look at how the DotNet.Core.SQLite library is put together, let’s see how simple it is used.

Connecting and Querying a Database

Before we look at how the DotNet.Core.SQLite library can simplify working with databases, tables, views and queries, let’s review how it is normally done.

To connect and execute a query manually, you would normally write something like the following:

var connectionString = "data source=northwind.db;version=3";

using (var conn = new SQLiteConnection(connectionString))
using (var cmd = new SQLiteCommand("SELECT COUNT (*) FROM Products", conn))
{
    conn.Open();
    using (var coreReader = cmd.ExecuteReader())
    {
        coreReader.Read();
        var productCount = coreReader.HasRows ? coreReader.GetInt64(0) : 0;
        Console.WriteLine($"/n** Total Products: {productCount}/n");
    }
}
Dim connectionString = "data source=northwind.db;version=3"

Using conn = New SQLiteConnection(connectionString)
    Using cmd = New SQLiteCommand("SELECT COUNT (*) FROM Products", conn)
        conn.Open()

        Using coreReader = cmd.ExecuteReader()
            coreReader.Read()
            Dim productCount = If(coreReader.HasRows, coreReader.GetInt64(0), 0)
            Console.WriteLine($"** Total Products: {productCount}")
        End Using
    End Using
End Using

Using the DotNet.Core.SQLite library, we would do almost the same:

var connectionString = "data source=northwind.db;version=3";

using (var dbCtx = new DbContext(connectionString))
using (var conn = dbCtx.ConnectionFactory())
using (var cmd = new SQLiteCommand("SELECT COUNT (*) FROM Products", conn))
{
    conn.Open();
    using (var coreReader = cmd.ExecuteReader())
    {
        coreReader.Read();
        var productCount = coreReader.HasRows ? coreReader.GetInt64(0) : 0;
        Console.WriteLine($"/n** Total Products: {productCount}/n");
    }
}
Dim connectionString = "data source=northwind.db;version=3"

Using dbCtx = New DbContext(connectionString)
    Using conn = dbCtx.ConnectionFactory()
        Using cmd = New SQLiteCommand("SELECT COUNT (*) FROM Products", conn)
            conn.Open()

            Using coreReader = cmd.ExecuteReader()
                coreReader.Read()
                Dim productCount = If(coreReader.HasRows, coreReader.GetInt64(0), 0)
                Console.WriteLine($"** Total Products: {productCount}")
            End Using
        End Using
    End Using
End Using

The above example using the DotNet.Core.SQLite library looks like more code when wrapping the normal code. You would not do this except for legacy code to add tracing (see below). What it does demonstrate, along with the next sections, is how the DotNet.Core.SQLite library works seamlessly with the existing SQLite library allowing the choice of level of integration without compromising performance.

The next section will cover how the DotNet.Core.SQLite library simplifies the above example.

So why do we need a DbContext?

The DbContext represents a database and holds the connection details and a SqliteEventTraceBase class reference (more about this in the next section). DbContext has a number of extension methods for simplifying both basic and complex operations against a single database. You can have multiple DbContext objects, one for each main database (with attached database(s)).

To simplify many tasks, the DotNet.Core.SQLite library has a number of extension methods. So we could simplify this by calling the SQLiteConnection extension method RecordCount:

var connectionString = "data source=northwind.db;version=3";

using (var dbCtx = new DbContext(connectionString))
using (var conn = dbCtx.ConnectionFactory()) // Create & Trace (if listening)
{
    var productCount = conn.RecordCount("Products");
    Console.WriteLine($"/n** Total Products: {productCount}/n");
}
Dim connectionString = "data source=northwind.db;version=3"

Using dbCtx = New DbContext(connectionString)
    Using conn = dbCtx.ConnectionFactory()
        Dim productCount = conn.RecordCount("Products")
        Console.WriteLine($"** Total Products: {productCount}")
    End Using
End Using

Or we can simplify this further by calling the DbContext extension method RecordCount:

var connectionString = "data source=northwind.db;version=3";

using (var dbCtx = new DbContext(connectionString))
{
    var productCount = dbCtx.RecordCount("Products");
    Console.WriteLine($"/n** Total Products: {productCount}/n");
}
Dim connectionString = "data source=northwind.db;version=3"

Using dbCtx = New DbContext(connectionString)
    Dim productCount = dbCtx.RecordCount("Products")
    Console.WriteLine($"** Total Products: {productCount}")
End Using

If with have a DbResourceContext<T> (see below) configured for the table, view, or query, then we could reduce it to a single line of code:

var productCount = dbRegistry.Resolve<Product>().Count();
Dim productCount = dbRegistry.Resolve(Of Product)().Count()

This will locate the DbResourceContext<T> object for the Product POCO with the associated DbContext, open the connection, execute the query, release resources, and return the result. Later on, the article will also cover another syntax that works against the SQLiteConnection and support custom queries.

The output for all 5 versions of the above code would be:

** Total Products: 77

The RecordCount extension works against the DbContext and the SQLiteConnection classes. Which one gets used is dependent on where a single command is executed against a connection or if there are multiple commands to be executed against a single connection. There are a number of other extension methods that can do the same. The next section will show you why this matters.

The DbContext class has a number of constructs and methods for setting the connection string. Above we pass the connection string in the constructor of the DbContext class.

SQLiteConnectionStringBuilder Support

Instead of passing a connection string, there is support for the SQLite SQLiteConnectionStringBuilder class:

var builder = new SQLiteConnectionStringBuilder
{
    Version = 3,
    DataSource = "northwind.db"
};

using (var dbCtx = new DbContext(builder))
{
    //...
}
Dim builder = New SQLiteConnectionStringBuilder With {
        .Version = 3,
        .DataSource = "northwind.db",
    }

Using dbCtx = New DbContext(connectionString)
    '...
End Using

or

var builder = new SQLiteConnectionStringBuilder
{
    Version = 3,
    DataSource = "northwind.db"
};

using (var dbCtx = new DbContext())
{
    dbCtx.SetConnectionString(builder);
    //...
}
Dim builder = New SQLiteConnectionStringBuilder With {
        .Version = 3,
        .DataSource = "northwind.db",
    }

Using dbCtx = New DbContext()
    dbx.SetConnectionString(builder)
    '...
End Using

And we can do the same for the ConnectionString:

var connectionString = "data source=northwind.db;version=3";

using (var dbCtx = new DbContext())
{
    dbCtx.SetConnectionString(connectionString);
    //...
}
Dim connectionString = "data source=northwind.db;version=3"

Using dbCtx = New DbContext()
    dbCtx.SetConnectionString(connectionString)
    '...
End Using

Connecting and Querying a Database with Tracing

To optimize or debug any code, being able to see what is happening within a 3rd-party library is not always easy. Luckily, SQLite has a number of events that we can subscribe to and see what is happening and where optimizations can be applied.

namespace System.Data.SQLite
{
    public sealed class SQLiteConnection : DbConnection, ICloneable, IDisposable
    {
        //
        // Summary:
        //     This event is raised when events related to the lifecycle of a SQLiteConnection
        //     object occur.
        public static event SQLiteConnectionEventHandler Changed;
        //
        // Summary:
        //     This event is raised whenever SQLite is committing a transaction. Return non-zero
        //     to trigger a rollback.
        public event SQLiteCommitHandler Commit;
        //
        // Summary:
        //     This event is raised whenever SQLite makes an update/delete/insert into the database
        //     on this connection. It only applies to the given connection.
        public event SQLiteUpdateEventHandler Update;
        //
        // Summary:
        //     This event is raised whenever SQLite encounters an action covered by the authorizer
        //     during query preparation. Changing the value of the
        //     System.Data.SQLite.AuthorizerEventArgs.ReturnCode
        //     property will determine if the specific action will be allowed, ignored, or denied.
        //     For the entire duration of the event, the associated connection and statement
        //     objects must not be modified, either directly or indirectly, by the called code.
        public event SQLiteAuthorizerEventHandler Authorize;
        //
        // Summary:
        //     This event is raised periodically during long running queries. Changing the value
        //     of the System.Data.SQLite.ProgressEventArgs.ReturnCode property will determine
        //     if the operation in progress will continue or be interrupted. For the entire
        //     duration of the event, the associated connection and statement objects must not
        //     be modified, either directly or indirectly, by the called code.
        public event SQLiteProgressEventHandler Progress;
        //
        // Summary:
        //     This event is raised whenever the database is opened or closed.
        public override event StateChangeEventHandler StateChange;
        //
        // Summary:
        //     This event is raised whenever SQLite statement first begins executing on this
        //     connection. It only applies to the given connection.
        public event SQLiteTraceEventHandler Trace;
        //
        // Summary:
        //     This event is raised whenever SQLite is rolling back a transaction.
        public event EventHandler RollBack;
    }
}

Tracing is done with a Tracing class and a Writer class. There are a couple of predefined classes that can be used or you can implement your own using the base classes provided.

To use tracing, we would modify the above code as follows:

var connectionString = "data source=northwind.db;version=3";

// Handles output from trace
var writer = new SimpleConsoleTextWriter();

// Set what to trace and where to output the results
var trace = new SqliteCsvEventTrace(writer, SqliteConnectionTraceType.Full);

using (var dbCtx = new DbContext(connectionString, trace))
{
    // ...
}
Dim connectionString = "data source=northwind.db;version=3"

' Handles output from trace
Dim writer = New SimpleConsoleTextWriter()

' Set what to trace and where to output the results
Dim trace = New SqliteCsvEventTrace(writer, SqliteConnectionTraceType.Full)

Using dbCtx = New DbContext(connectionString, trace)
    '...
End Using

Now if we re-write the code above in the last example, it would look something like this:

var connectionString = "data source=northwind.db;version=3";

// Handles output from trace
var writer = new SimpleConsoleTextWriter();

// Set what to trace and where to output the results
var trace = new SqliteCsvEventTrace(writer, SqliteConnectionTraceType.Full);

using (var dbCtx = new DbContext(connectionString, trace))
{
    var result = dbCtx.RecordCount("Products");
    Console.WriteLine($"/n** Total Products: {result}");
}
Dim connectionString = "data source=northwind.db;version=3"

' Handles output from trace
Dim writer = New SimpleConsoleTextWriter()

' Set what to trace and where to output the results
Dim trace = New SqliteCsvEventTrace(writer, SqliteConnectionTraceType.Full)

Using dbCtx = New DbContext(connectionString, trace)
    Dim productCount = dbCtx.RecordCount("Products")
    Console.WriteLine()
    Console.WriteLine($"** Total Products : {productCount}")
    End Using
End Using

The RecordCount extension method is called against the DbContext, then will automatically listen to the SQLite‘s events if a Trace exists.

And the output would be:

_TRACE: "northwind.db","Listening","74DB74"
_TRACE: "74DB74","Event","Opening",""
_TRACE: "74DB74","Event","Closing",""
_TRACE: "74DB74","Event","Closed",""
_TRACE: "74DB74","Event","ConnectionString","version=3;data source=northwind.db"
_TRACE: "northwind.db","Event","NewCriticalHandle","C:/SQLiteWrapper/Samples/Tracing/bin/Debug/northwind.db"
_TRACE: "74DB74","Event","NewCommand",""
_TRACE: "74DB74","Event","DisposingCommand",""
_TRACE: "74DB74","State","Open"
_TRACE: "74DB74","Event","Opened",""
_TRACE: "74DB74","Event","NewCommand",""
_TRACE: "74DB74","Event","NewDataReader",""
_TRACE: "74DB74","Auth","","Select","","","Ok"
_TRACE: "74DB74","Auth","","Function","","count","Ok"
_TRACE: "74DB74","Auth","","Read","Products","","Ok"
_TRACE: "northwind.db","Event","NewCriticalHandle","SELECT Count(*) FROM Products"
_TRACE: "74DB74","Event","DisposingDataReader",""
_TRACE: "74DB74","Event","ClosingDataReader",""
_TRACE: "74DB74","Event","DisposingCommand",""
_TRACE: "74DB74","Event","Closing",""
_TRACE: "74DB74","State","Closed"
_TRACE: "74DB74","Event","Closed",""
_TRACE: "74DB74","Disposing"

** Total Products: 77

Now we can see exactly what the SQLite engine is doing. Every Connection is given an Id to track all output associated with that connection.

Example of Optimizing Code Using Tracing

To demonstrate the benefits of tracing, the following optimization example will walk through the process of querying the record counts of two tables.

We are enabling tracing and calling the RecordCount for each table against the DbContext:

var connectionString = "data source=northwind.db;version=3";

// Handles output from trace
var writer = new SimpleConsoleTextWriter();

// Set what to trace and where to output the results
var trace = new SqliteCsvEventTrace(writer, SqliteConnectionTraceType.Full);

using (var dbCtx = new DbContext(connectionString, trace))
{
    var productCount = dbCtx.RecordCount("Products");
    var supplierCount = dbCtx.RecordCount("Supplierss");

    Console.WriteLine($"/n** Total Products : {productCount}");
    Console.WriteLine($"** Total Suppliers: {supplierCount}");
}
Dim connectionString = "data source=northwind.db;version=3"

' Handles output from trace
Dim writer = New SimpleConsoleTextWriter()

' Set what to trace and where to output the results
Dim trace = New SqliteCsvEventTrace(writer, SqliteConnectionTraceType.Full)

Using dbCtx = New DbContext(connectionString, trace)
    Dim productCount = dbCtx.RecordCount("Products")
    Dim supplierCount = dbCtx.RecordCount("Suppliers")

    Console.WriteLine()
    Console.WriteLine($"** Total Products : {productCount}")
    Console.WriteLine($"** Total Suppliers: {supplierCount}")
End Using

The tracing output would be:

_TRACE: "northwind.db","Listening","74DB74"
_TRACE: "74DB74","Event","Opening",""
_TRACE: "74DB74","Event","Closing",""
_TRACE: "74DB74","Event","Closed",""
_TRACE: "74DB74","Event","ConnectionString","data source=northwind.db;version=3"
_TRACE: "northwind.db","Event","NewCriticalHandle","C:/SQLiteWrapper/Samples/Tracing/bin/Debug/northwind.db"
_TRACE: "74DB74","Event","NewCommand",""
_TRACE: "74DB74","Event","DisposingCommand",""
_TRACE: "74DB74","State","Open"
_TRACE: "74DB74","Event","Opened","data source=northwind.db;version=3"
_TRACE: "74DB74","Event","NewCommand",""
_TRACE: "74DB74","Event","NewDataReader",""
_TRACE: "74DB74","Auth","","Select","","","Ok"
_TRACE: "74DB74","Auth","","Function","","count","Ok"
_TRACE: "74DB74","Auth","","Read","Products","","Ok"
_TRACE: "northwind.db","Event","NewCriticalHandle","SELECT Count(*) FROM Products"
_TRACE: "74DB74","Event","DisposingDataReader",""
_TRACE: "74DB74","Event","ClosingDataReader",""
_TRACE: "74DB74","Event","DisposingCommand",""
_TRACE: "74DB74","Event","Closing",""
_TRACE: "74DB74","State","Closed"
_TRACE: "74DB74","Event","Closed",""
_TRACE: "74DB74","Disposing"

_TRACE: "northwind.db","Listening","F96FC5"
_TRACE: "F96FC5","Event","Opening",""
_TRACE: "F96FC5","Event","Closing",""
_TRACE: "F96FC5","Event","Closed",""
_TRACE: "F96FC5","Event","ConnectionString","version=3;data source=northwind.db"
_TRACE: "northwind.db","Event","NewCriticalHandle","C:/SQLiteWrapper/Samples/Tracing/bin/Debug/northwind.db"
_TRACE: "F96FC5","Event","NewCommand",""
_TRACE: "F96FC5","Event","DisposingCommand",""
_TRACE: "F96FC5","State","Open"
_TRACE: "F96FC5","Event","Opened","data source=northwind.db;version=3"
_TRACE: "F96FC5","Event","NewCommand",""
_TRACE: "F96FC5","Event","NewDataReader",""
_TRACE: "F96FC5","Auth","","Select","","","Ok"
_TRACE: "F96FC5","Auth","","Function","","count","Ok"
_TRACE: "F96FC5","Auth","","Read","Suppliers","","Ok"
_TRACE: "northwind.db","Event","NewCriticalHandle","SELECT Count(*) FROM Suppliers"
_TRACE: "F96FC5","Event","DisposingDataReader",""
_TRACE: "F96FC5","Event","ClosingDataReader",""
_TRACE: "F96FC5","Event","DisposingCommand",""
_TRACE: "F96FC5","Event","Closing",""
_TRACE: "F96FC5","State","Closed"
_TRACE: "F96FC5","Event","Closed",""
_TRACE: "F96FC5","Disposing"

** Total Products : 77
** Total Suppliers: 30

The code looks pretty simple however two SQLiteConnection objects and two SQLiteCommand commands are created, executed, and destroyed.

We can optimize the code by calling the RecordCount extension method against a single SQLiteConnection as follows:

using (var dbCtx = new DbContext(connectionString, trace))
{
    using (var conn = dbCtx.ConnectionFactory())
    {
        var productCount = conn.RecordCount("Products");
        var supplierCount = conn.RecordCount("Suppliers");

        Console.WriteLine($"/n** Total Products : {productCount}");
        Console.WriteLine($"** Total Suppliers: {supplierCount}/n");
    }
}
Dim connectionString = "data source=northwind.db;version=3"

' Handles output from trace
Dim writer = New SimpleConsoleTextWriter()

' Set what to trace and where to output the results
Dim trace = New SqliteCsvEventTrace(writer, SqliteConnectionTraceType.Full)

Using dbCtx = New DbContext(connectionString, trace)
    Using conn = dbCtx.ConnectionFactory()
        Dim productCount = conn.RecordCount("Products")
        Dim supplierCount = conn.RecordCount("Suppliers")

        Console.WriteLine()
        Console.WriteLine($"** Total Products : {productCount}")
        Console.WriteLine($"** Total Suppliers: {supplierCount}")
        Console.WriteLine()
    End Using
End Using

We call the ConnectionFactory concrete method for the DbContext to listen to the SQLite events and return a SQLiteConnection object.

Now the output is:

_TRACE: "northwind.db","Listening","74DB74"
_TRACE: "74DB74","Event","Opening",""
_TRACE: "74DB74","Event","Closing",""
_TRACE: "74DB74","Event","Closed",""
_TRACE: "74DB74","Event","ConnectionString","data source=northwind.db;version=3"
_TRACE: "northwind.db","Event","NewCriticalHandle","C:/SQLiteWrapper/Samples/Tracing/bin/Debug/northwind.db"
_TRACE: "74DB74","Event","NewCommand",""
_TRACE: "74DB74","Event","DisposingCommand",""
_TRACE: "74DB74","State","Open"
_TRACE: "74DB74","Event","Opened","data source=northwind.db;version=3"

_TRACE: "74DB74","Event","NewCommand",""
_TRACE: "74DB74","Event","NewDataReader",""
_TRACE: "74DB74","Auth","","Select","","","Ok"
_TRACE: "74DB74","Auth","","Function","","count","Ok"
_TRACE: "74DB74","Auth","","Read","Products","","Ok"
_TRACE: "northwind.db","Event","NewCriticalHandle","SELECT Count(*) FROM Products"
_TRACE: "74DB74","Event","DisposingDataReader",""
_TRACE: "74DB74","Event","ClosingDataReader",""
_TRACE: "74DB74","Event","DisposingCommand",""

_TRACE: "74DB74","Event","NewCommand",""
_TRACE: "74DB74","Event","NewDataReader",""
_TRACE: "74DB74","Auth","","Select","","","Ok"
_TRACE: "74DB74","Auth","","Function","","count","Ok"
_TRACE: "74DB74","Auth","","Read","Suppliers","","Ok"
_TRACE: "northwind.db","Event","NewCriticalHandle","SELECT Count(*) FROM Suppliers"
_TRACE: "74DB74","Event","DisposingDataReader",""
_TRACE: "74DB74","Event","ClosingDataReader",""
_TRACE: "74DB74","Event","DisposingCommand",""

** Total Products : 77
** Total Suppliers: 30

_TRACE: "74DB74","Event","Closing",""
_TRACE: "74DB74","State","Closed"
_TRACE: "74DB74","Event","Closed",""
_TRACE: "74DB74","Disposing"

Now only one connection is opened and two SQLiteCommand commands executed using a single SQLiteConnection.

We can optimize this further by passing the two SQL queries to a single SQLiteCommand:

var query = @"SELECT COUNT (*) FROM Products;
              SELECT COUNT (*) FROM Suppliers;";

using (var dbCtx = new DbContext(connectionString, trace))
using (var conn = dbCtx.ConnectionFactory())
using (var cmd = new SQLiteCommand(query, conn))
{
    conn.Open();
    using (var coreReader = cmd.ExecuteReader())
    {
        coreReader.Read();
        var result = coreReader.HasRows ? coreReader.GetInt64(0) : 0;
        Console.WriteLine($"/n** Total Products: {result}/n");

        coreReader.NextResult();

        coreReader.Read();
        result = coreReader.HasRows ? coreReader.GetInt64(0) : 0;
        Console.WriteLine($"** Total Suppliers: {result}/n");
    }
}
Dim query = "SELECT COUNT (*) FROM Products;
             SELECT COUNT (*) FROM Suppliers;"

Using dbCtx = New DbContext(connectionString, trace)
    Using conn = dbCtx.ConnectionFactory()
        Using cmd = New SQLiteCommand(query, conn)
            conn.Open()

            Using coreReader = cmd.ExecuteReader()
                coreReader.Read()
                Dim result = If(coreReader.HasRows, coreReader.GetInt64(0), 0)
                Console.WriteLine()
                Console.WriteLine($"** Total Products: {result}")
                Console.WriteLine()

                coreReader.NextResult()

                coreReader.Read()
                result = If(coreReader.HasRows, coreReader.GetInt64(0), 0)
                Console.WriteLine()
                Console.WriteLine($"** Total Suppliers: {result}")
                Console.WriteLine()
            End Using
        End Using
    End Using
End Using

Now the output is:

_TRACE: "northwind.db","Listening","74DB74"
_TRACE: "74DB74","Event","NewCommand",""
_TRACE: "74DB74","Event","Opening",""
_TRACE: "74DB74","Event","Closing",""
_TRACE: "74DB74","Event","Closed",""
_TRACE: "74DB74","Event","ConnectionString","data source=northwind.db;version=3"
_TRACE: "northwind.db","Event","NewCriticalHandle","C:/SQLiteWrapper/Samples/Tracing/bin/Debug/northwind.db"
_TRACE: "74DB74","Event","NewCommand",""
_TRACE: "74DB74","Event","DisposingCommand",""
_TRACE: "74DB74","State","Open"
_TRACE: "74DB74","Event","Opened","data source=northwind.db;version=3"
_TRACE: "74DB74","Event","NewDataReader",""
_TRACE: "74DB74","Auth","","Select","","","Ok"
_TRACE: "74DB74","Auth","","Function","","count","Ok"
_TRACE: "74DB74","Auth","","Read","Products","","Ok"
_TRACE: "northwind.db","Event","NewCriticalHandle","SELECT COUNT (*) FROM Products;
                              SELECT COUNT (*) FROM Suppliers;"

** Total Products: 77

_TRACE: "74DB74","Auth","","Select","","","Ok"
_TRACE: "74DB74","Auth","","Function","","count","Ok"
_TRACE: "74DB74","Auth","","Read","Suppliers","","Ok"
_TRACE: "northwind.db","Event","NewCriticalHandle","SELECT COUNT (*) FROM Suppliers;"

** Total Suppliers: 30

_TRACE: "74DB74","Event","DisposingDataReader",""
_TRACE: "74DB74","Event","ClosingDataReader",""
_TRACE: "74DB74","Event","DisposingCommand",""
_TRACE: "74DB74","Event","Closing",""
_TRACE: "74DB74","State","Closed"
_TRACE: "74DB74","Event","Closed",""
_TRACE: "74DB74","Disposing"

Whilst this last example’s code is a little bit more verbose, the result is, as seen in the trace output, only a single SQLiteCommand is executed using a single SQLiteConnection. It also demonstrates how the DotNet.Core.SQLite library works seamlessly with standard SQLite coding when needed.

In the above examples, full tracing is turned on. You can select the level of tracing required by setting the option flag in the trace constructor:

[Flags]
public enum SqliteConnectionTraceType
{
    None = 0,
    Authorize = 1,
    Commit = 2,
    Progress = 4,
    RollBack = 8,
    StateChange = 16,
    Trace = 32,
    Update = 64,
    Events = 128,
    Compact = Commit | RollBack | StateChange | Update,
    Detailed = Compact | Events,
    Full = Detailed | Authorize | Progress
}
<Flags>
Public Enum SqliteConnectionTraceType
    None = 0
    Authorize = 1
    Commit = 2
    Progress = 4
    RollBack = 8
    StateChange = 16
    Trace = 32
    Update = 64
    Events = 128
    Compact = Commit Or RollBack Or StateChange Or Update
    Detailed = Compact Or Events
    Full = Detailed Or Authorize Or Progress
End Enum

This is a very simple example of how easy it is to trace events using the library, however, is very beneficial when building and debugging an app or seeing what is happening on an end-users computer.

Tables, SQL Data Queries, Views, and POCOs

Typically, when working with Tables/Views you would write code something like:

List<Category> Categories = new List<Category>();

var connectionString = "data source=northwind.db;version=3";

using (var conn = new SQLiteConnection(connectionString))

// Get all categories
using (var cmd = new SQLiteCommand("SELECT * FROM Categories;", conn))
{
    conn.Open();
    using (var coreReader = cmd.ExecuteReader())
    {
        while (coreReader.Read())
        {
            var category = new Category
            {
                Id = (long)coreReader["CategoryID"],
                Name = (string)coreReader["CategoryName"],
                Description = (string)coreReader["Description"],
                Picture = (byte[])coreReader["Picture"]
            };
            Categories.Add(category);
        }
    }
}
Dim Categories As List(Of Category) = New List(Of Category)()

Dim connectionString = "data source=northwind.db;version=3"

Using conn = New SQLiteConnection(connectionString)

    ' Get all categories
    Using cmd = New SQLiteCommand("SELECT * FROM Categories;", conn)
        conn.Open()

        Using coreReader = cmd.ExecuteReader()
            While coreReader.Read()
                Dim category = New Category With {
                    .Id = CLng(coreReader("CategoryID")),
                    .Name = CStr(coreReader("CategoryName")),
                    .Description = CStr(coreReader("Description")),
                    .Picture = CType(coreReader("Picture"), Byte())
                }
                Categories.Add(category)
            End While
        End Using
    End Using
End Using

And here is the Category class:

public class Category
{
    public long Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public byte[] Picture { get; set; }
}
Public Class Category
    Public Property Id As Long
    Public Property Name As String
    Public Property Description As String
    Public Property Picture As Byte()
End Class

The DotNet.Core.SQLite library has a set of base classes for wrapping operations against Tables and Views to simplify the creation of the Table/View and CRUD operations. The DotNet.Core.SQLite library does not use reflection (except for reporting) to keep performance tight. An example of this would be:

public class CategoriesContext : DbTableContext<Category>
{
    public const string Name = "Categories";
    protected override string Identifier => Name;

    private const string AddSqlFieldList = Name +
        " (CategoryName, Description, Picture)" +
        " VALUES (@CategoryName, @Description, @Picture);";

    private const string UpdateSqlFieldList = Name + 
        " (CategoryID, CategoryName, Description, Picture)" +
        " VALUES (@CategoryID, @CategoryName, @Description, @Picture);";

    protected override void CheckAndCreateTable(SQLiteConnection conn)
    {
        conn.CheckAndCreateTable(Name,
            $@"CREATE TABLE [{Name}] (
                [CategoryID] integer PRIMARY KEY AUTOINCREMENT NOT NULL,
                [CategoryName] nvarchar(15) NOT NULL COLLATE NOCASE,
                [Description] nvarchar COLLATE NOCASE,
                [Picture] varbinary);");
    }

    protected override void CheckAndCreateIndicies(SQLiteConnection conn)
    {
        var ix = $"IX_{Name}_CategoryName";
        conn.CheckAndCreateIndex(ix, $"CREATE INDEX [{ix}] ON [{Name}] ([CategoryName]);");
    }

    protected override void CheckAndCreateTriggers(SQLiteConnection conn)
    {
        // not required
    }

    protected override Category FromSQLiteDataReader(SQLiteDataReader coreReader)
        => new Category
        {
            Id = coreReader.GetLong("CategoryID", 0L),
            Name = coreReader.GetString("CategoryName"),
            Description = coreReader.GetString("Description"),
            Picture = coreReader.GetBinary("Picture")
        };

    protected override SqlQuery ToSqlQuery(Category data, ToSqlQueryType type)
        => (type == ToSqlQueryType.Update)
        ? new SqlQuery(GetMethod(type) + UpdateSqlFieldList, new Dictionary<string, object>
        {
            ["@CategoryID"] = data.Id,
            ["@CategoryName"] = data.Name.ToSqLiteText(),
            ["@Description"] = data.Description.ToSqLiteText(),
            ["@Picture"] = data.Picture
        })
        : new SqlQuery(GetMethod(type) + AddSqlFieldList, new Dictionary<string, object>
        {
            ["@CategoryName"] = data.Name.ToSqLiteText(),
            ["@Description"] = data.Description.ToSqLiteText(),
            ["@Picture"] = data.Picture
        });

    protected override SqlQuery ToDeleteSqlQuery(Category data)
        => new SqlQuery(SqlDeleteAllPrefix + Name + " WHERE CategoryID = @CategoryID",
                        new Dictionary<string, object> { ["@CategoryID"] = data.Id });
}
Friend Class CategoriesContext : Inherits DbTableContext(Of Category)

    Public Const Name As String = "Categories"

    Protected Overrides ReadOnly Property Identifier As String
        Get
            Return Name
        End Get
    End Property

    Private Const AddSqlFieldList As String = Name & 
        " (CategoryName, Description, Picture)" &
        " VALUES (@CategoryName, @Description, @Picture);"

    Private Const UpdateSqlFieldList As String = Name &
        " (CategoryID, CategoryName, Description, Picture)" &
        " VALUES (@CategoryID, @CategoryName, @Description, @Picture);"

    Protected Overrides Sub CheckAndCreateTable(ByVal conn As SQLiteConnection)

        conn.CheckAndCreateTable(Name, $"CREATE TABLE [{Name}] (
                    [CategoryID] integer PRIMARY KEY AUTOINCREMENT NOT NULL,
                    [CategoryName] nvarchar(15) NOT NULL COLLATE NOCASE,
                    [Description] nvarchar COLLATE NOCASE,
                    [Picture] varbinary);")

    End Sub

    Protected Overrides Sub CheckAndCreateIndicies(ByVal conn As SQLiteConnection)
        Dim ix = $"IX_{Name}_CategoryName"
        conn.CheckAndCreateIndex(ix, $"CREATE INDEX [{ix}] ON [{Name}] ([CategoryName]);")


    End Sub

    Protected Overrides Sub CheckAndCreateTriggers(ByVal conn As SQLiteConnection)
        ' Not required
    End Sub

    Protected Overrides Function FromSQLiteDataReader(ByVal coreReader As SQLiteDataReader)
        As Category

        Return New Category With {
            .Id = coreReader.GetLong("CategoryID", 0L),
            .Name = coreReader.GetString("CategoryName"),
            .Description = coreReader.GetString("Description"),
            .Picture = coreReader.GetBinary("Picture")
        }

    End Function

    Protected Overrides Function ToSqlQuery(ByVal data As Category,
                                            ByVal type As ToSqlQueryType) As SqlQuery

        Return If((type = ToSqlQueryType.Update), 
            New SqlQuery(GetMethod(type) & UpdateSqlFieldList,
                New Dictionary(Of String, Object) From {
                    {"@CategoryID", data.Id},
                    {"@CategoryName", data.Name.ToSqLiteText()},
                    {"@Description", data.Description.ToSqLiteText()},
                    {"@Picture", data.Picture}
            }),
            New SqlQuery(GetMethod(type) & AddSqlFieldList,
                New Dictionary(Of String, Object) From {
                    {"@CategoryName", data.Name.ToSqLiteText()},
                    {"@Description", data.Description.ToSqLiteText()},
                    {"@Picture", data.Picture}
            }))

    End Function

    Protected Overrides Function ToDeleteSqlQuery(ByVal data As Category) As SqlQuery
        Return New SqlQuery(SqlDeleteAllPrefix & Name & " WHERE CategoryID = @CategoryID",
            New Dictionary(Of String, Object) From {{"@CategoryID", data.Id}})

    End Function

End Class

First, somewhere in the code, register the Database and a CategoriesContext class.

// Using a library registery service to link tables/views to a database with lookup
var dbRegistry = new DbRegistry();

// Create the database context
var dbCtx = new DbContext("data source=northwind.db;version=3");

// Register the database
var dbKey = dbRegistry.Register(dbCtx);

// Register the Table/View Context
dbRegistry.Register(new CategoriesContext(), dbKey);
' Using a library registery service to link tables/views to a database with lookup
Dim dbRegistry = New DbRegistry()

' Create the database context
Dim dbCtx = New DbContext("data source=northwind.db;version=3")

' Register the database
Dim dbKey = dbRegistry.Register(dbCtx)

' Register the Table/View Context
dbRegistry.Register(New CategoriesContext(), dbKey)

Now we can get the data in a single line:

// Get all categories
var categories = dbRegistry.Resolve<Category>().Get();
' Get all categories 
Dim categories = dbRegistry.Resolve(Of Category)().Get()

If you want to get a subset of the data, you could do the following:

// Get the data
var query = "SELECT * FROM Categories WHERE CategoryName Like 'c%'";
var categories = dbRegistry.Resolve<Category>().Get(query);
' Get the data 
Dim query = "SELECT * FROM Categories WHERE CategoryName Like 'c%'";
Dim categories = dbRegistry.Resolve(Of Category)().Get(query)

If you don’t want to use the DbRegistry service, you can pass a SQLiteConnection object directly to the DbTableContext<T> Get method:

using (var conn = dbCtx.ConnectionFactory())
{
    var categoriesCtx = new CategoriesContext();
    var categories = categoriesCtx.Get(conn);
}
Using conn = dbCtx.ConnectionFactory()
    Dim categoriesCtx = New CategoriesContext()
    Dim categories = categoriesCtx.Get(conn)
End Using

The CategoriesContext will automatically handle all database operations. The CategoriesContext inherits from the DbTableContext<T> base class. For Views, you would use the DbViewContext<T> base class. The difference between the two DbXxxxContext<T> classes is that the DbViewContext<T> is readonly.

Both the DbTableContext<T> and DbViewContext<T> inherit the base class DbResourceContext<T> which has support for the following actions:

Command Table View Return Type
CheckOrCreateTable() Y Y
Count() Y Y long
Count(string query) Y Y long
Count(SqlQuery query) Y Y long
Count(SQLiteConnection conn) Y Y long
Count(SQLiteConnection conn, string query) Y Y long
Count(SQLiteConnection conn, SqlQuery query) Y Y long
Count(IUnitOfWork unitOfWork) Y Y long
Count(IUnitOfWork unitOfWork, string query) Y Y long
Count(IUnitOfWork unitOfWork, SqlQuery query) Y Y long
GetResourceCounts() Dictionary<string, int>
Get() Y Y IEnumerable<T>
Get(string query) Y Y IEnumerable<T>
Get(SqlQuery query) Y Y IEnumerable<T>
Get(SQLiteConnection conn) Y Y IEnumerable<T>
Get(SQLiteConnection conn, string query) Y Y IEnumerable<T>
Get(SQLiteConnection conn, SqlQuery query) Y Y IEnumerable<T>
Get(IUnitOfWork unitOfWork) Y Y IEnumerable<T>
Get(IUnitOfWork unitOfWork, string query) Y Y IEnumerable<T>
Get(IUnitOfWork unitOfWork, SqlQuery query) Y Y IEnumerable<T>
GetAsync() Y Y Task<IEnumerable<T>>
GetAsync(string query) Y Y Task<IEnumerable<T>>
GetAsync(SqlQuery query) Y Y Task<IEnumerable<T>>
GetAsync(SQLiteConnection conn) Y Y Task<IEnumerable<T>>
GetAsync(SQLiteConnection conn, string query) Y Y Task<IEnumerable<T>>
GetAsync(SQLiteConnection conn, SqlQuery query) Y Y Task<IEnumerable<T>>
GetAsync(IUnitOfWork unitOfWork) Y Y Task<IEnumerable<T>>
GetAsync(IUnitOfWork unitOfWork, string query) Y Y Task<IEnumerable<T>>
GetAsync(IUnitOfWork unitOfWork, SqlQuery query) Y Y Task<IEnumerable<T>>
AddAsync(DbQuery<t> dbQuery) Y Task<int>
AddAsync(SQLiteConnection conn, DbQuery<t> dbQuery) Y Task<int>
AddAsync(IUnitOfWork unitOfWork, DbQuery<t> dbQuery) Y Task<int>
UpdateAsync(DbQuery<t> dbQuery) Y Task<int>
UpdateAsync(SQLiteConnection conn, DbQuery<t> dbQuery) Y Task<int>
UpdateAsync(IUnitOfWork unitOfWork, DbQuery<t> dbQuery) Y Task<int>
DeleteAsync(DbQuery<t> dbQuery) Y Task<int>
DeleteAsync(SQLiteConnection conn, DbQuery<t> dbQuery) Y Task<int>
DeleteAsync(IUnitOfWork unitOfWork, DbQuery<t> dbQuery) Y Task<int>
KillAsync() Y Task<int>

Transactions

When working with Transactions in SQLite to do bulk operations, it could be written something like:

var builder = new SQLiteConnectionStringBuilder
{
    Version = 3,
    DataSource = "northwind.db",
    JournalMode = SQLiteJournalModeEnum.Wal
};

const string insertSql = "INSERT INTO Categories (CategoryName, Description, Picture)"
    + " VALUES (@CategoryName, @Description, @Picture);";

const string deleteSql = "DELETE FROM Categories WHERE CategoryID = @CategoryID;";

using (var conn = new SQLiteConnection(builder.ConnectionString))
{
    await conn.OpenAsync().ConfigureAwait(false);
    using (var trans = conn.BeginTransaction())
    {
        try
        {
            using (var addCmd = new SQLiteCommand(insertSql, conn))
            using (var delCmd = new SQLiteCommand(deleteSql, conn))
            {
                for (int i = 0; i < 50000; i++)
                {
                    addCmd.Parameters.AddWithValue("@CategoryName", "test category " + i);
                    addCmd.Parameters.AddWithValue("@Description", "this is a test category");
                    addCmd.Parameters.AddWithValue("@Picture", new byte[0]);
                    await addCmd.ExecuteNonQueryAsync().ConfigureAwait(false);

                    delCmd.Parameters.AddWithValue("@CategoryID", conn.LastInsertRowId);
                    await delCmd.ExecuteNonQueryAsync().ConfigureAwait(false);
                }
            }

            trans.Commit();
        }
        catch (Exception ex)
        {
            // failed, so log here and the transaction
            // will auto rollback (see UnitOfWork class)
            // write detailed exception to trace output
            Console.WriteLine("!! ERROR: " + ex.Message + " ... "
                + ex.InnerException.Message.Replace("/r/n", " >> "));
            trans.Rollback();
        }
    }
}
Dim builder = New SQLiteConnectionStringBuilder With {
        .Version = 3,
        .DataSource = "northwind.db",
        .JournalMode = SQLiteJournalModeEnum.Wal
    }

Const insertSql As String = "INSERT INTO Categories (CategoryName, Description, Picture)" &
                                           " VALUES (@CategoryName, @Description, @Picture);"

Const deleteSql As String = "DELETE FROM Categories WHERE CategoryID = @CategoryID;"

Using conn = New SQLiteConnection(builder.ConnectionString)
    Await conn.OpenAsync().ConfigureAwait(False)

    Using trans = conn.BeginTransaction()
        Try
            Using addCmd = New SQLiteCommand(insertSql, conn)
                Using delCmd = New SQLiteCommand(deleteSql, conn)

                    For i As Integer = 0 To 50000 - 1
                        addCmd.Parameters.AddWithValue("@CategoryName", "test category " & i)
                        addCmd.Parameters.AddWithValue("@Description", "this is a test category")
                        addCmd.Parameters.AddWithValue("@Picture", New Byte(-1) {})
                        Await addCmd.ExecuteNonQueryAsync().ConfigureAwait(False)

                        delCmd.Parameters.AddWithValue("@CategoryID", conn.LastInsertRowId)
                        Await delCmd.ExecuteNonQueryAsync().ConfigureAwait(False)
                    Next
                End Using
            End Using

            trans.Commit()

        Catch ex As Exception
            ' failed, so log here and the transaction
            ' will auto rollback (see UnitOfWork class)
            ' write detailed exception to trace output
            Console.WriteLine("!! ERROR: " & ex.Message & " ... " &
                ex.InnerException.Message.Replace(vbCrLf, " >> "))
            trans.Rollback()
        End Try

    End Using
End Using

The included UnitOfWork class will simplify working with Transactions. The UnitOfWork class wraps a common shared connection, automatically begins a transactions, requires the SaveChanges method to be called to commit changes, and automatic rollback if the commit fails.

When the SQLiteConnection is passed to the constructor, the connection is opened, if not already open, and begins the transaction. The automatic rollback, if required, is called when the UnitOfWork class is disposed of.

Using the UnitOfWork class is quite simple. Also, the UnitOfWork class is not specific to the DotNet.Core.SQLite library, so it can also work with your existing SQLite code by adding only two lines of code – first to initialize the UnitOfWork class, then the SaveChanges method to commit. See below:

using (var conn = new SQLiteConnection(builder.ConnectionString))
{
    await conn.OpenAsync().ConfigureAwait(false);
    using (var uow = new UnitOfWork(conn))
    {
        try
        {
            using (var addCmd = new SQLiteCommand(insertSql, conn))
            using (var delCmd = new SQLiteCommand(deleteSql, conn))
            {
                for (int i = 0; i < 50000; i++)
                {
                    addCmd.Parameters.AddWithValue("@CategoryName", "test category " + i);
                    addCmd.Parameters.AddWithValue("@Description", "this is a test category");
                    addCmd.Parameters.AddWithValue("@Picture", new byte[0]);
                    await addCmd.ExecuteNonQueryAsync().ConfigureAwait(false);

                    delCmd.Parameters.AddWithValue("@CategoryID", conn.LastInsertRowId);
                    await delCmd.ExecuteNonQueryAsync().ConfigureAwait(false);
                }
            }

            uow.SaveChanges();
        }
        catch (Exception ex)
        {
            // failed, so log here and the transaction
            // will auto rollback (see UnitOfWork class)
            // write detailed exception to trace output
            Console.WriteLine("!! ERROR: " + ex.Message + " ... "
                + ex.InnerException.Message.Replace("/r/n", " >> "));
        }
    }
}
Dim builder = New SQLiteConnectionStringBuilder With {
        .Version = 3,
        .DataSource = "northwind.db",
        .JournalMode = SQLiteJournalModeEnum.Wal
    }

Const insertSql As String = "INSERT INTO Categories (CategoryName, Description, Picture)" &
                                           " VALUES (@CategoryName, @Description, @Picture);"

Const deleteSql As String = "DELETE FROM Categories WHERE CategoryID = @CategoryID;"

Using conn = New SQLiteConnection(builder.ConnectionString)
    Await conn.OpenAsync().ConfigureAwait(False)

    Using uow = New UnitOfWork(conn)
        Try
            Using addCmd = New SQLiteCommand(insertSql, conn)
                Using delCmd = New SQLiteCommand(deleteSql, conn)

                    For i As Integer = 0 To 50000 - 1
                        addCmd.Parameters.AddWithValue("@CategoryName", "test category " & i)
                        addCmd.Parameters.AddWithValue("@Description", "this is a test category")
                        addCmd.Parameters.AddWithValue("@Picture", New Byte(-1) {})
                        Await addCmd.ExecuteNonQueryAsync().ConfigureAwait(False)

                        delCmd.Parameters.AddWithValue("@CategoryID", conn.LastInsertRowId)
                        Await delCmd.ExecuteNonQueryAsync().ConfigureAwait(False)
                    Next
                End Using
            End Using

            uow.SaveChanges()

        Catch ex As Exception
            ' failed, so log here and the transaction
            ' will auto rollback (see UnitOfWork class)
            ' write detailed exception to trace output
            Console.WriteLine("!! ERROR: " & ex.Message & " ... " &
                ex.InnerException.Message.Replace(vbCrLf, " >> "))
        End Try

    End Using
End Using

Here is an example, using the dbRegistry code from above, that will add and remove entries from a table in bulk with Tracing:

var categories = dbRegistry.Resolve<Category>();

using (var uow = categories.UnitOfWorkFactory())
{
    try
    {
        for (int i = 0; i < 50000; i++)
        {
            var newCat = new Category
            {
                CategoryName = "test category " + i,
                Description = "this is a test category",
                Picture = new byte[0]
            };

            await categories.AddAsync(uow, new DbQuery<Category>(newCat));
            newCat.CategoryID = uow.Connection.LastInsertRowId;

            await categories.DeleteAsync(uow, new DbQuery<Category>(newCat));
        }

        uow.SaveChanges();
    }
    catch (Exception ex)
    {
            // failed, so log here and the transaction
            // will auto rollback (see UnitOfWork class)
            // write detailed exception to trace output
        writer.WriteLine("!! ERROR: " + ex.Message
            + (ex.InnerException?.Message?.Replace("/r/n", " >> "));
    }
}
Dim categories = dbRegistry.Resolve(Of Category)()

Using uow = categories.UnitOfWorkFactory()
    Try
        For i As Integer = 0 To 50000 - 1
            Dim newCat = New Category With {
                    .Name = "test category " & i,
                    .Description = "this is a test category",
                    .Picture = New Byte(-1) {}
                }

            Await categories.AddAsync(uow,
                New DbQuery(Of Category)(newCat)).ConfigureAwait(False)
            newCat.Id = uow.Connection.LastInsertRowId

            Await categories.DeleteAsync(uow,
                New DbQuery(Of Category)(newCat)).ConfigureAwait(False)
        Next

        uow.SaveChanges()

    Catch ex As Exception
        ' failed, so log here and the transaction
        ' will auto rollback (see UnitOfWork class)
        ' write detailed exception to trace output
        Console.WriteLine("!! ERROR: " & ex.Message & " ... " &
            ex.InnerException.Message.Replace(vbCrLf, " >> "))
    End Try
End Using

To see an automatic rollback occur, we can force an error when adding a record:

var categories = dbRegistry.Resolve<Category>();

using (var uow = categories.UnitOfWorkFactory())
{
    try
    {
        for (int i = 0; i < 50000; i++)
        {
            var newCat = new Category
            {
                // force an error by leaving the property CategoryName empty (null)
                Description = "this is a test category",
                Picture = new byte[0]
            };

            await categories.AddAsync(uow, new DbQuery<Category>(newCat));
            newCat.CategoryID = uow.Connection.LastInsertRowId;
        }

        // Never reached as the add record failed...
        uow.SaveChanges();
    }
    catch (Exception ex)
    {
            // failed, so log here and the transaction
            // will auto rollback (see UnitOfWork class)
            // write detailed exception to trace output
        writer.WriteLine("!! ERROR: " + ex.Message
            + (ex.InnerException?.Message?.Replace("/r/n", " >> "));
    }
}
Dim categories = dbRegistry.Resolve(Of Category)()

Using uow = categories.UnitOfWorkFactory()
    Try
        For i As Integer = 0 To 50000 - 1
            ' force an error by leaving the property CategoryName empty (null)
            Dim newCat = New Category With {
                    .Description = "this is a test category",
                    .Picture = New Byte(-1) {}
                }

            Await categories.AddAsync(uow,
                New DbQuery(Of Category)(newCat)).ConfigureAwait(False)
            newCat.Id = uow.Connection.LastInsertRowId

            Await categories.DeleteAsync(uow,
                New DbQuery(Of Category)(newCat)).ConfigureAwait(False)
        Next

        uow.SaveChanges()

    Catch ex As Exception
        ' failed, so log here and the transaction
        ' will auto rollback (see UnitOfWork class)
        ' write detailed exception to trace output
        Console.WriteLine("!! ERROR: " & ex.Message & " ... " &
            ex.InnerException.Message.Replace(vbCrLf, " >> "))
    End Try
End Using

With tracing enabled, we can see the automatic rollback in the trace log:

_TRACE: "northwind.db","Listening","2EC5D9C"
_TRACE: "2EC5D9C","Event","Opening",""
_TRACE: "2EC5D9C","Event","Closing",""
_TRACE: "2EC5D9C","Event","Closed",""
_TRACE: "2EC5D9C","Event","ConnectionString","version=3;data source=northwind.db;journal mode=Wal"
_TRACE: "northwind.db","Event","NewCriticalHandle","C:/SQLiteWrapper/Samples/Tracing/bin/Debug/northwind.db"
_TRACE: "2EC5D9C","Event","NewCommand",""
_TRACE: "2EC5D9C","Event","NewDataReader",""
_TRACE: "northwind.db","Event","NewCriticalHandle","PRAGMA journal_mode=Wal"
_TRACE: "2EC5D9C","Event","DisposingDataReader",""
_TRACE: "2EC5D9C","Event","ClosingDataReader",""
_TRACE: "2EC5D9C","Event","DisposingCommand",""
_TRACE: "2EC5D9C","State","Open"
_TRACE: "2EC5D9C","Event","Opened","version=3;data source=northwind.db;journal mode=Wal"
_TRACE: "2EC5D9C","Event","NewCommand",""
_TRACE: "2EC5D9C","Event","NewDataReader",""

_TRACE: "2EC5D9C","Auth","","Transaction","BEGIN","","Ok"

_TRACE: "northwind.db","Event","NewCriticalHandle","BEGIN IMMEDIATE;"
_TRACE: "2EC5D9C","Event","DisposingDataReader",""
_TRACE: "2EC5D9C","Event","ClosingDataReader",""
_TRACE: "2EC5D9C","Event","DisposingCommand",""
_TRACE: "2EC5D9C","Event","NewTransaction",""

_TRACE: "2EC5D9C","Event","NewCommand",""
_TRACE: "2EC5D9C","Event","NewDataReader",""
_TRACE: "2EC5D9C","Auth","","Insert","Categories","","Ok"
_TRACE: "northwind.db","Event","NewCriticalHandle","INSERT INTO Categories (CategoryName, Description, Picture) VALUES (@CategoryName, @Description, @Picture);"
_TRACE: "2EC5D9C","Event","DisposingCommand",""

_TRACE: !! ERROR: One or more errors occurred. constraint failed >> NOT NULL constraint failed: Categories.CategoryName

_TRACE: "2EC5D9C","Event","NewCommand",""
_TRACE: "2EC5D9C","Event","NewDataReader",""

_TRACE: "2EC5D9C","Auth","","Transaction","ROLLBACK","","Ok"
_TRACE: "northwind.db","Event","NewCriticalHandle","ROLLBACK;"
_TRACE: "2EC5D9C","RollBack"

_TRACE: "2EC5D9C","Event","DisposingDataReader",""
_TRACE: "2EC5D9C","Event","ClosingDataReader",""
_TRACE: "2EC5D9C","Event","DisposingCommand",""

Other Database Functions

DbContext Extension Methods

The DbContext class only contains the core code required to track a connection to a database. All actions that can be performed are coded as extension methods.

The current list of actions are:

Command Return Type
AttatchDB
CheckAndCreateIndex
CheckAndCreateTable
CheckAndCreateTrigger
CheckAndCreateView
CopyTable int
CreateDB
CompactDB
DetatchDB
DropTable
ExecuteNonQuery int
ExecuteNonQueryAsync Task<int>
ExecuteScalar
ExecuteScalarAsync Task
Exists bool
GenerateDDL string
Get IEnumerable<T>
GetAsync Task<IEnumerable<T>>
GetConnectionParameters IEnumerable<KeyValuePair<string, string>>
GetPragma KeyValuePair<string, string>
GetResourceCounts IDictionary<string, long>
HasOpenConnections bool
IndexExists bool
IndicesExist IDictionary<string, bool>
LiveBackup bool
ManualReindex
ManualReindexAll
MoveTable int
RecordCount long
Reindex
RenameTable
Report string
SetPragma KeyValuePair<string, string>
TableExists bool
TablesExist IDictionary<string, bool>
TriggerExists bool
TriggersExist IDictionary<string, bool>
ViewExists bool
ViewsExist IDictionary<string, bool>

SQLiteConnection Extension Methods

A number of extension methods are also available for the standard SQLite SQLiteConnection class:

Command Return Type
CheckAndCreateIndex
CheckAndCreateTable
CheckAndCreateTrigger
CheckAndCreateView
CommandFactory SQLiteCommand
ExecuteNonQuery int
ExecuteNonQueryAsync Task<int>
ExecuteScalar
ExecuteScalarAsync Task
GenerateDDL string
Get IEnumerable<T>
GetAsync Task<IEnumerable<T>>
GetPragma KeyValuePair<string, string>
GetPragma KeyValuePair<string, string>
GetResourceCounts IDictionary<string, long>
IndexExists bool
IndicesExist IDictionary<string, bool>
IsOpen bool
OpenIfRequired
OpenIfRequiredAsync Task
RecordCount long
SetPragma KeyValuePair<string, string>
TableExists bool
TablesExist IDictionary<string, bool>
TriggerExists bool
TriggersExist IDictionary<string, bool>
ViewExists bool
ViewsExist IDictionary<string, bool>

Backup and Restore a Database

SQLite comes with a BackupDatabase method. This method will automatically override the destination file if it already exists. To simplify usage, there is a wrapper extension function for the DbContext called LiveBackup.

To Backup a database:

var connectionString = "data source=northwind.db;version=3";

using (var dbCtx = new DbContext(connectionString))
{
    var success = dbCtx.LiveBackup(
        System.IO.Path.Combine(dbCtx.DbPath,
                               "northwindEF.bak"));
}
Dim connectionString = "data source=northwind.db;version=3"

Using dbCtx = New DbContext(connectionString)
    Dim success = 
        dbCtx.LiveBackup(System.IO.Path.Combine(dbCtx.DbPath,
                         "northwindEF.bak"))
End Using

And to Restore:

var connectionString = "data source=northwind.bak;version=3";

using (var dbCtx = new DbContext(connectionString))
{
    var success = dbCtx.LiveBackup(
        System.IO.Path.Combine(dbCtx.DbPath,
                               "northwindEF.db"));
}
Dim connectionString = "data source=northwind.bak;version=3"

Using dbCtx = New DbContext(connectionString)
    Dim success = 
        dbCtx.LiveBackup(System.IO.Path.Combine(dbCtx.DbPath,
                         "northwindEF.db"))
End Using

If while backing up progress feedback is required, you can add a callback and set the pageSize and retryMilliseconds timeout.

var connectionString = "data source=northwind.db;version=3";

using (var dbCtx = new DbContext(connectionString))
{
    var success = dbCtx.LiveBackup(
        System.IO.Path.Combine(dbCtx.DbPath, "northwindEF.bak"), 
        BackupProgress, 100);
}
Dim connectionString = "data source=northwind.db;version=3"

Using dbCtx = New DbContext(connectionString)
    Dim success = dbCtx.LiveBackup(System.IO.Path.Combine(dbCtx.DbPath,
        "northwindEF.bak"), AddressOf BackupProgress, 100)
End Using

And the callback method:

private static bool BackupProgress(SQLiteConnection source,
                                   string sourceName,
                                   SQLiteConnection destination,
                                   string destinationName,
                                   int pages,
                                   int remainingPages,
                                   int totalPages,
                                   bool retry)
{
    Console.WriteLine($"___ Progress : {100 * (totalPages - remainingPages) / totalPages}%");
    return true;
}
Private Function BackupProgress(ByVal source As SQLiteConnection,
                                ByVal sourceName As String,
                                ByVal destination As SQLiteConnection,
                                ByVal destinationName As String,
                                ByVal pages As Integer,
                                ByVal remainingPages As Integer,
                                ByVal totalPages As Integer,
                                ByVal retry As Boolean) As Boolean
    Console.WriteLine($"___ Progress : {100 * (totalPages - remainingPages) / totalPages}%")
    Return True
End Function

If the full trace is enabled, as above in the Tracing section, the output would be as follows:

_TRACE: "northwind.db","Listening","F96FC5"
_TRACE: "F96FC5","Event","Opening",""
_TRACE: "F96FC5","Event","Closing",""
_TRACE: "F96FC5","Event","Closed",""
_TRACE: "F96FC5","Event","ConnectionString","version=3;data source=northwind.db"
_TRACE: "northwind.db","Event","NewCriticalHandle","C:/SQLiteWrapper/Samples/Tracing/bin/Debug/northwind.db"
_TRACE: "F96FC5","Event","NewCommand",""
_TRACE: "F96FC5","Event","DisposingCommand",""
_TRACE: "F96FC5","State","Open"
_TRACE: "F96FC5","Event","Opened",""
_TRACE: "2EC5D9C","Event","Opening",""
_TRACE: "2EC5D9C","Event","Closing",""
_TRACE: "2EC5D9C","Event","Closed",""
_TRACE: "2EC5D9C","Event","ConnectionString","version=3;data source=northwindEF.bak"
_TRACE: "northwind.db","Event","NewCriticalHandle","C:/SQLiteWrapper/Samples/Tracing/bin/Debug/northwindEF.bak"
_TRACE: "2EC5D9C","Event","NewCommand",""
_TRACE: "2EC5D9C","Event","DisposingCommand",""
_TRACE: "2EC5D9C","Event","Opened",""
_TRACE: "northwind.db","Event","NewCriticalHandle",""
___ Progress : 12%
___ Progress : 24%
___ Progress : 36%
___ Progress : 49%
___ Progress : 61%
___ Progress : 73%
___ Progress : 86%
___ Progress : 98%
_TRACE: "2EC5D9C","Event","Closing",""
_TRACE: "2EC5D9C","Event","Closed",""
_TRACE: "F96FC5","Event","Closing",""
_TRACE: "F96FC5","State","Closed"
_TRACE: "F96FC5","Event","Closed",""
_TRACE: "F96FC5","Disposing"

Copying a Table

The CopyTable method allows flexible copying of all data or specific subsets plus a custom definition of the destination table along with associated resources like triggers and indices.

First, a method is created for the resource creation:

private static void CreateProducts(SQLiteConnection conn, string tableName)
{
    var query = $@"CREATE TABLE [{tableName}](
                    [ProductID] integer primary key autoincrement NOT NULL,
                    [ProductName] nvarchar(40) NOT NULL COLLATE NOCASE,
                    [SupplierID] integer NULL,
                    [CategoryID] integer NULL,
                    [QuantityPerUnit] nvarchar(20) NULL COLLATE NOCASE,
                    [UnitPrice] money NULL CONSTRAINT [DF_Products_UnitPrice]  DEFAULT (0),
                    [UnitsInStock] smallint NULL CONSTRAINT [DF_Products_UnitsInStock]  DEFAULT (0),
                    [UnitsOnOrder] smallint NULL CONSTRAINT [DF_Products_UnitsOnOrder]  DEFAULT (0),
                    [ReorderLevel] smallint NULL CONSTRAINT [DF_Products_ReorderLevel]  DEFAULT (0),
                    [Discontinued] bit NOT NULL CONSTRAINT [DF_Products_Discontinued]  DEFAULT (0),
                    [DiscontinuedDate] datetime NULL,
                    CONSTRAINT [CK_Products_UnitPrice] CHECK  ([UnitPrice] >= 0),
                    CONSTRAINT [CK_ReorderLevel] CHECK ([ReorderLevel] >= 0),
                    CONSTRAINT [CK_UnitsInStock] CHECK ([UnitsInStock] >= 0),
                    CONSTRAINT [CK_UnitsOnOrder] CHECK ([UnitsOnOrder] >= 0),
                    CONSTRAINT [FK_Products_Categories] FOREIGN KEY([CategoryID])
                    REFERENCES [Categories] ([CategoryID]),
                    CONSTRAINT [FK_Products_Suppliers] FOREIGN KEY([SupplierID])
                    REFERENCES [Suppliers] ([SupplierID])
                );
                CREATE INDEX [IX_{tableName}_CategoriesProducts] ON [{tableName}]
                (
                    [CategoryID] ASC
                );
                CREATE INDEX [IX_{tableName}_CategoryID] ON [{tableName}]
                (
                    [CategoryID] ASC
                );
                CREATE INDEX [IX_{tableName}_ProductName] ON [{tableName}]
                (
                    [ProductName] ASC
                );
                CREATE INDEX [IX_{tableName}_SupplierID] ON [{tableName}]
                (
                    [SupplierID] ASC
                );
                CREATE INDEX [IX_{tableName}_SuppliersProducts] ON [{tableName}]
                (
                    [SupplierID] ASC
                );";

    conn.ExecuteNonQuery(new SqlQuery(query));
}
Dim query = $"CREATE TABLE [{tableName}](
        [ProductID] integer primary key autoincrement NOT NULL,
        [ProductName] nvarchar(40) NOT NULL COLLATE NOCASE,
        [SupplierID] integer NULL,
        [CategoryID] integer NULL,
        [QuantityPerUnit] nvarchar(20) NULL COLLATE NOCASE,
        [UnitPrice] money NULL CONSTRAINT [DF_Products_UnitPrice]  DEFAULT (0),
        [UnitsInStock] smallint NULL CONSTRAINT [DF_Products_UnitsInStock]  DEFAULT (0),
        [UnitsOnOrder] smallint NULL CONSTRAINT [DF_Products_UnitsOnOrder]  DEFAULT (0),
        [ReorderLevel] smallint NULL CONSTRAINT [DF_Products_ReorderLevel]  DEFAULT (0),
        [Discontinued] bit NOT NULL CONSTRAINT [DF_Products_Discontinued]  DEFAULT (0),
        [DiscontinuedDate] datetime NULL,
        CONSTRAINT [CK_Products_UnitPrice] CHECK  ([UnitPrice] >= 0),
        CONSTRAINT [CK_ReorderLevel] CHECK ([ReorderLevel] >= 0),
        CONSTRAINT [CK_UnitsInStock] CHECK ([UnitsInStock] >= 0),
        CONSTRAINT [CK_UnitsOnOrder] CHECK ([UnitsOnOrder] >= 0),
        CONSTRAINT [FK_Products_Categories] FOREIGN KEY([CategoryID])
        REFERENCES [Categories] ([CategoryID]),
        CONSTRAINT [FK_Products_Suppliers] FOREIGN KEY([SupplierID])
        REFERENCES [Suppliers] ([SupplierID])
    );
    CREATE INDEX [IX_{tableName}_CategoriesProducts] ON [{tableName}]
    (
        [CategoryID] ASC
    );
    CREATE INDEX [IX_{tableName}_CategoryID] ON [{tableName}]
    (
        [CategoryID] ASC
    );
    CREATE INDEX [IX_{tableName}_ProductName] ON [{tableName}]
    (
        [ProductName] ASC
    );
    CREATE INDEX [IX_{tableName}_SupplierID] ON [{tableName}]
    (
        [SupplierID] ASC
    );
    CREATE INDEX [IX_{tableName}_SuppliersProducts] ON [{tableName}]
    (
        [SupplierID] ASC
    );"
conn.ExecuteNonQuery(New SqlQuery(query))

Next, we perform the copying of the table:

var oldTable = "Products";          // Source table name
var newTable = "ReorderProducts";   // Destination table name

// Create a delegate to wrap the call and pass to CopyTable & MoveTable
var createNewProducts = new Action<SQLiteConnection>((conn) => CreateProducts(conn, newTable));

// Copy condition (after 'WHERE' sql keyword)
var condition = new SqlQuery("UnitsInStock = @uis AND UnitsOnOrder = @uoo",
                             new Dictionary<string, object>
                             { { "@uis", 0 }, { "@uoo", 0 } });

// Perform copy to new table
using (var dbCtx = new DbContext(connectionString))
{
    var count1 = dbCtx.CopyTable(oldTable, newTable, createNewProducts, condition);
    Console.WriteLine($"{count1} records copied"); 
}
Dim oldTable = "Products"           ' Source table name
Dim newTable = "ReorderProducts"    ' Destination table name

' Create a delegate to wrap the call and pass to CopyTable & MoveTable
Dim createNewProducts = New Action(Of SQLiteConnection)(Sub(conn) CreateProducts(conn, newTable))

' Copy condition (after 'WHERE' sql keyword)
Dim condition = New SqlQuery("UnitsInStock = @uis AND UnitsOnOrder = @uoo",
    New Dictionary(Of String, Object) From {{"@uis", 0}, {"@uoo", 0}})

' Perform copy to new table
Using dbCtx = New DbContext(connectionString)
    Dim count = dbCtx.CopyTable(oldTable, newTable, createNewProducts, condition)
    Console.WriteLine($"{count} records copied")
End Using

Moving a Table

The MoveTable method works the same as the CopyTable method except for the old table, and optionally remove all associated indices, is removed upon completion:

var oldTable = "Products";          // Source table name
var newTable = "ReorderProducts";   // Destination table name

// Create a delegate to wrap the call and pass to CopyTable & MoveTable
var createNewProducts = new Action<SQLiteConnection>((conn) => CreateProducts(conn, newTable));

// Copy condition (after 'WHERE' sql keyword)
SqlQuery condition = null;  // set to only do a partial move and delete remainder

// Perform move to new table
using (var dbCtx = new DbContext(connectionString))
{
    var count = dbCtx.MoveTable(oldTable, newTable, createNewProducts, condition);
    Console.WriteLine($"{count} records moved");
}
Dim oldTable = "Products"           ' Source table name
Dim newTable = "ReorderProducts"    ' Destination table name

' Create a delegate to wrap the call and pass to CopyTable & MoveTable
Dim createNewProducts = New Action(Of SQLiteConnection)(Sub(conn) CreateProducts(conn, newTable))

' Copy condition (after 'WHERE' sql keyword)
Dim condition As SqlQuery = Nothing ' set to only do a partial move and delete remainder

' Perform move to new table
Using dbCtx = New DbContext(connectionString)
    Dim count1 = dbCtx.MoveTable(oldTable, newTable, createNewProducts, condition)
    Console.WriteLine($"{count1} records moved")
End Using

Dropping a Table

The DropTable method will remove a table and optionally remove all associated indices:

var oldTable = "Products";          // Source table name

using (var dbCtx = new DbContext(connectionString))
{
    dbCtx.DropTable(oldTable, dropIndicies: true);
}
Dim oldTable = "Products"           ' Source table name

Using dbCtx = New DbContext(connectionString)
    dbCtx.DropTable(oldTable, dropIndicies:=True)
End Using

Renaming a Table

The RenameTable method will rename the table.

var oldTable = "Products";          // Source table name
var newTable = "ReorderProducts";   // Destination table name

using (var dbCtx = new DbContext(connectionString))
{
    dbCtx.RenameTable(oldTable, newTable);
}
Dim oldTable = "Products"           ' Source table name
Dim newTable = "ReorderProducts"    ' Destination table name

Using dbCtx = New DbContext(connectionString)
    dbCtx.RenameTable(oldTable, newTable)
End Using

Compacting a Database

Databases can grow quickly in size over time, get fragmented, and sometimes impede performance. It is a good idea to compact the database from time to time to maintain performance.

using (var dbCtx = new DbContext(connectionString))
{
    dbCtx.CompactDB();
}
Using dbCtx = New DbContext(connectionString)
    dbCtx.CompactDB()
End Using

Reindexing

An index requires maintenance when the fragmentation level adversely affects performance. This level of fragmentation could vary based on the size and composition of the index. Index maintenance process should be done regularly. However, the index maintenance process should only rebuild/reorganize the indexes that specifically require it.

Normal reindexing

SQLite has a REINDEX SQL command. Below is a wrapper method to simplify its usage.

To reindex all tables:

using (var dbCtx = new DbContext(connectionString))
{
    dbCtx.Reindex();
}
Using dbCtx = New DbContext(connectionString)
    dbCtx.Reindex()
End Using

To reindex a specific table, for example “Categories”:

using (var dbCtx = new DbContext(connectionString))
{
    dbCtx.Reindex("Categories");
}
Using dbCtx = New DbContext(connectionString)
    dbCtx.Reindex("Categories")
End Using
Full reindexing

Sometimes, the standard REINDEX is not enough and a more thorough re-indexing is required. For these special cases, you need to drop the index and recreate it. This process will take a lot longer than standard re-indexing. The DotNet.Core.SQLite library has an extension method that will do this:

To reindex all tables:

using (var dbCtx = new DbContext(connectionString))
{
    dbCtx.ManualReindexAll();
}
Using dbCtx = New DbContext(connectionString)
    dbCtx.ManualReindexAll()
End Using

To reindex a specific table, for example, “Categories”:

using (var dbCtx = new DbContext(connectionString))
{
    dbCtx.ManualReindex(SQLiteResourceType.Table, "");
}
Using dbCtx = New DbContext(connectionString)
    dbCtx.ManualReindex(SQLiteResourceType.Table, "Categories")
End Using

To rebuild a specific index, for example, “IX_CatCategoriesegories_CategoryName”:

using (var dbCtx = new DbContext(connectionString))
{
    dbCtx.ManualReindex(SQLiteResourceType.Index, "IX_Categories_CategoryName");
}
Using dbCtx = New DbContext(connectionString)
    dbCtx.ManualReindex(SQLiteResourceType.Index, "IX_Categories_CategoryName");
End Using

Working with Pragmas

The PRAGMA statement is an SQL extension specific to SQLite and used to modify the operation of the SQLite library or to query the SQLite library for internal (non-table) data.

All current Pragmas are supported in DbContextPragmaExtension. There are currently 49 SQLite Pragma commands and they are all supported. If you need to manually execute a Pragma command, there are two functions that can be called against the DbContext class or the standard SQLite SQLiteConnection class called GetPragma and SetPragma.

Pragma commands supported are:

Command Get Set Return Type
ApplicationID Y Y int
AutoVacuum Y Y SQLitePragmaAutoVacuumType
AutomaticIndex Y Y bool
BusyTimeout Y Y int
CacheSize Y Y int
CacheSpill Y Y int
CaseSensitiveLike Y
CellSizeCheck Y Y bool
CheckpointFullfsync Y Y bool
CollationList Y IEnumerable<string>
CompileOptions Y IEnumerable<string>
DatabaseList Y IEnumerable<string>
DataVersion Y int
DeferForeignKeys Y Y bool
Encoding Y Y SQLitePragmaEncodingType
ForeignKeyCheck Y IEnumerable<KeyValuePair<string, string>>
ForeignKeyList Y IEnumerable<SQLitePragmaForeignKey>
ForeignKeys Y Y bool
FreelistCount Y int
Fullfsync Y Y bool
IgnoreCheckConstraints Y Y bool
IncrementalVacuum Y
IndexInfo Y IEnumerable<SQLitePragmaIndexInfo>
IndexList Y IEnumerable<SQLitePragmaIndexListItem>
IndexXInfo Y IEnumerable<SQLitePragmaIndexInfo>
IntegrityCheck Y string
JournalMode Y Y SQLitePragmaJournalModeType
JournalSizeLimit Y Y int
LegacyFileFormat Y Y bool
LockingMode Y Y SQLitePragmaLockingModeType
MaxPageCount Y Y int
MmapSize Y Y int
Optimize Y string
PageCount Y int
PageSize Y Y int
QueryOnly Y Y bool
QuickCheck Y string
ReadUncommitted Y Y bool
RecursiveTriggers Y Y bool
ReverseUnorderedSelects Y Y bool
SecureDelete Y Y SQLitePragmaSecureDeleteType
ShrinkMemory Y
SoftHeapLimit Y Y SoftHeapLimit
Synchronous Y Y SQLitePragmaSynchronousType
TableInfo Y Y IEnumerable<SQLitePragmaTableInfo>
TempStore Y Y SQLitePragmaTempStoreType
Threads Y Y int
UserVersion Y Y int
WalAutocheckpoint Y Y bool

To get Pragma data:

using (var dbCtx = new DbContext(connectionString))
{
    // Get the Application Identiciation number
    var appId = dbCtx.ApplicationId();

    // Get the User version
    var userVersion = dbCtx.UserVersion();

    // Get the Data revision version (read only)
    var dataVersion = dbCtx.DataVersion();
}
Using dbCtx = New DbContext(connectionString)

    ' Get the Application Identiciation number
    Dim appId = dbCtx.ApplicationId()

    ' Get the User version
    Dim userVersion = dbCtx.UserVersion()

    ' Get the Data revision version (read only)
    Dim dataVersion = dbCtx.DataVersion()
End Using

To set Pragma data:

using (var dbCtx = new DbContext(connectionString))
{
    // Set the Application Identiciation number
    dbCtx.ApplicationId(1234);

    // Set the User version
    dbCtx.UserVersion(5678);
}
Using dbCtx = New DbContext(connectionString)

    ' Set the Application Identiciation number
    dbCtx.ApplicationId(1234)

    ' Set the User version
    dbCtx.UserVersion(5678)
End Using

Individual Pragma commands may have different returned data and the information returned or passed is encapsulated where required.

There are also reporting methods that efficiently retrieve SQLite Pragma information:

  • GetPragmas – Gets the pragma values for one or more Pragma types.
  • GetPragmaTypes – gets a list of Pragma types based on Mode, Access scope, and Property scope

Generating DDL (Data Definition Language)

DDL is used for defining data structures and database schemas. There are a number of overloaded GenerateDDL that can be called against the DbContext or SQLiteConnection class.

  1. Extract all definitions
using (var dbCtx = new DbContext(connectionString))
{
    var ddl = dbCtx.GenerateDDL();
}
Using dbCtx = New DbContext(connectionString)
    Dim ddl = dbCtx.GenerateDDL()
End Using
  1. Extract Table/View/Index/Trigger definition
using (var dbCtx = new DbContext(connectionString))
{
    var ddlTable = dbCtx.GenerateDDL("Products");
    var ddlView = dbCtx.GenerateDDL(viewName: "ProductsView");
    var ddlIndex = dbCtx.GenerateDDL(indexName: "IX_Products_CategoriesProducts");
    var ddlCombo = dbCtx.GenerateDDL(tableName: "Products", indexName: "IX_Products_CategoriesProducts");
}
Using dbCtx = New DbContext(connectionString)
    Dim ddlTable = dbCtx.GenerateDDL("Products")
    Dim ddlView = dbCtx.GenerateDDL(viewName:="ProductsView")
    Dim ddlIndex = dbCtx.GenerateDDL(indexName:="IX_Products_CategoriesProducts")
    Dim ddlCombo = dbCtx.GenerateDDL(tableName:="Products", indexName:="IX_Products_CategoriesProducts")
End Using

Reporting

There are times when you need to collect information about a clients database for diagnosing problems or during the development cycle. The Report method will collect and dump a range of information dependent on the level of detail required:

  1. Connection parameters
  2. Connection information
  3. Connection Flags
  4. Database Pragma settings
  5. Connection Pragma settings
  6. Table Information: configuration, DDL, statistics (counts of Indices, Triggers, Records)
  7. Index Information
  8. Trigger Information
  9. View Information

The level of information reported is selectable via parameters passed to the Report method of the DbContext class.

Below is a full report call:

using (var dbCtx = new DbContext(builder))
{
    Console.WriteLine(dbCtx.Report());
}
Using dbCtx = New DbContext(connectionString)
    Console.WriteLine(dbCtx.Report())
End Using

If only the Pragma Settings are required:

using (var dbCtx = new DbContext(builder))
{
    Console.WriteLine(dbCtx.Report(SQLiteSchemaParamaterType.Pragma));
}
Using dbCtx = New DbContext(connectionString)
    Console.WriteLine(dbCtx.Report(SQLiteSchemaParamaterType.Pragma))
End Using

Here is an extract of the report with the first 5 sections:

== CONNECTIONSTRINGS ===========================================================

BaseSchemaName            =
BinaryGUID                = [uninitialized]
BrowsableConnectionString = True
BusyTimeout               = 0
CacheSize                 = -2000
ConnectionString          = version=3;data source=northwind.db
Count                     = 2
DataSource                = northwind.db
DateTimeFormat            = ISO8601
DateTimeFormatString      =
DateTimeKind              = Unspecified
DefaultDbType             = -1
DefaultIsolationLevel     = [uninitialized]
DefaultTimeout            = 30
DefaultTypeName           =
Enlist                    = True
FailIfMissing             = [uninitialized]
Flags                     = Default
ForeignKeys               = False
FullUri                   =
HexPassword               =
IsFixedSize               = False
IsReadOnly                = False
JournalMode               = Default
LegacyFormat              = False
MaxPageCount              = 0
NoDefaultFlags            = [uninitialized]
NoSharedFlags             = [uninitialized]
PageSize                  = 4096
Password                  =
Pooling                   = False
PrepareRetries            = 0
ProgressOps               = 0
ReadOnly                  = False
RecursiveTriggers         = False
SetDefaults               = [uninitialized]
SyncMode                  = Normal
ToFullPath                = [uninitialized]
Uri                       =
UseUTF16Encoding          = [uninitialized]
Version                   = 3
VfsName                   =
WaitTimeout               = 0
ZipVfsVersion             =

== CONNECTIONS =================================================================

AutoCommit            = True
BusyTimeout           = 0
ConnectionPool        =
ConnectionString      = version=3;data source=northwind.db
ConnectionTimeout     = 15
data source           = northwind.db
DefaultDbType         =
DefaultFlags          = Default
DefaultTimeout        = 30
DefaultTypeName       =
DefineConstants       = INTEROP_CODEC INTEROP_EXTENSION_FUNCTIONS INTEROP_FTS5_EXTENSION INTEROP_JSON1_EXTENSION INTEROP_PERCENTILE_EXTENSION INTEROP_REGEXP_EXTENSION INTEROP_SESSION_EXTENSION INTEROP_SHA1_EXTENSION INTEROP_TOTYPE_EXTENSION INTEROP_VIRTUAL_TABLE NET_45 PRELOAD_NATIVE_LIBRARY THROW_ON_DISPOSED TRACE TRACE_PRELOAD TRACE_SHARED TRACE_WARNING USE_INTEROP_DLL USE_PREPARE_V2 WINDOWS
Flags                 = Default
InteropCompileOptions = CODEC EXTENSION_FUNCTIONS JSON1_EXTENSION PERCENTILE_EXTENSION REGEXP_EXTENSION SESSION_EXTENSION SHA1_EXTENSION TOTYPE_EXTENSION VERSION_NUMBER=3022000 VIRTUAL_TABLE
InteropSourceId       = 2814aa20f86be6e8085206338c80cbb01c3881d0 2018-02-27 17:24:44 UTC
InteropVersion        = 1.0.108.0
LastInsertRowId       = 0
MemoryHighwater       = 126751
MemoryUsed            = 126751
OwnHandle             = True
ParseViaFramework     = False
PoolCount             = 0
PrepareRetries        = 3
ProgressOps           = 0
ProviderSourceId      = 2814aa20f86be6e8085206338c80cbb01c3881d0 2018-02-27 17:24:44 UTC
ProviderVersion       = 1.0.108.0
ServerVersion         = 3.22.0
SharedFlags           = None
Site                  =
SQLiteCompileOptions  = COMPILER=msvc-1700 ENABLE_API_ARMOR ENABLE_COLUMN_METADATA ENABLE_DBSTAT_VTAB ENABLE_FTS3 ENABLE_LOAD_EXTENSION ENABLE_MEMORY_MANAGEMENT ENABLE_PREUPDATE_HOOK ENABLE_RTREE ENABLE_SESSION ENABLE_STAT4 ENABLE_STMTVTAB HAS_CODEC SOUNDEX THREADSAFE=1 USE_URI WIN32_MALLOC
SQLiteSourceId        = 2018-01-22 18:45:57 0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2171d
SQLiteVersion         = 3.22.0
version               = 3
VfsName               =
WaitTimeout           = 30000

== SQLITE CONNECTION FLAGS =====================================================

AllowNestedTransactions                = False
BindAllAsText                          = False
BindAndGetAllAsInvariantText           = False
BindAndGetAllAsText                    = False
BindDateTimeWithKind                   = False
BindDecimalAsText                      = False
BindInvariantDecimal                   = True
BindInvariantText                      = False
BindUInt32AsInt64                      = False
ConvertAndBindAndGetAllAsInvariantText = False
ConvertAndBindInvariantText            = False
ConvertInvariantText                   = False
Default                                = True
DefaultAndLogAll                       = True
DenyOnException                        = False
DetectStringType                       = False
DetectTextAffinity                     = False
GetAllAsText                           = False
GetDecimalAsText                       = False
GetInvariantDecimal                    = True
InterruptOnException                   = False
LogAll                                 = True
LogBackup                              = False
LogBind                                = False
LogCallbackException                   = True
LogDefault                             = True
LogModuleError                         = False
LogModuleException                     = True
LogPreBind                             = False
LogPrepare                             = False
MapIsolationLevels                     = False
NoBindFunctions                        = False
NoConnectionPool                       = False
NoConvertSettings                      = False
NoCreateModule                         = False
NoExtensionFunctions                   = False
NoGlobalTypes                          = False
NoLoadExtension                        = False
NoLogModule                            = False
NoVerifyTextAffinity                   = False
NoVerifyTypeAffinity                   = False
RollbackOnException                    = False
StickyHasRows                          = False
StrictEnlistment                       = False
TraceWarning                           = False
UnbindFunctionsOnClose                 = False
UseConnectionAllValueCallbacks         = False
UseConnectionBindValueCallbacks        = False
UseConnectionPool                      = False
UseConnectionReadValueCallbacks        = False
UseConnectionTypes                     = False
UseParameterAnythingForTypeName        = False
UseParameterDbTypeForTypeName          = False
UseParameterNameForTypeName            = False
WaitForEnlistmentReset                 = False

== PRAGMA: DATABASES ===========================================================

application_id            = 0
auto_vacuum               = None
cache_size                = -2000
collation_list            = [RTRIM], [NOCASE], [BINARY]
database_list             = [main]
encoding                  = UTF-8
foreign_key_check         =
foreign_keys              = Off
freelist_count            = 0
journal_mode              = wal
journal_size_limit        = -1
legacy_file_format        = Off
max_page_count            = 1073741823
mmap_size                 = 0
page_count                = 811
page_size                 = 1024
schema_version            = 28
user_version              = 0
writable_schema           = Off

== PRAGMA: CONNECTIONS =========================================================

automatic_index           = On
busy_timeout              = 0
cache_spill               = 1841
cell_size_check           = Off
checkpoint_fullfsync      = Off
compile_options           = [COMPILER=msvc-1700], [ENABLE_API_ARMOR], [ENABLE_COLUMN_METADATA], [ENABLE_DBSTAT_VTAB], [ENABLE_FTS3], [ENABLE_LOAD_EXTENSION], [ENABLE_MEMORY_MANAGEMENT], [ENABLE_PREUPDATE_HOOK], [ENABLE_RTREE], [ENABLE_SESSION], [ENABLE_STAT4], [ENABLE_STMTVTAB], [HAS_CODEC], [SOUNDEX], [THREADSAFE=1], [USE_URI], [WIN32_MALLOC]
data_version              = 3
defer_foreign_keys        = Off
fullfsync                 = Off
ignore_check_constraints  = Off
locking_mode              = normal
query_only                = Off
read_uncommitted          = Off
recursive_triggers        = Off
reverse_unordered_selects = Off
secure_delete             = Off
soft_heap_limit           = 0
synchronous               = Full
temp_store                = Default
threads                   = 0
wal_autocheckpoint        = 1000
wal_checkpoint            = [busy = 0], [log = 3], [checkpointed = 3]
[...trimmed...]

Benchmarking

Working with SQLite in C# &amp; VB

The DotNet.Core.SQLite library is optimised to give flexibility with as little impact as possible. Below are some benchmark comparisons.

My dev/test machine is a MacBook pro. Not the fastest kid on the block but a reliable workhorse as I’m always on the move. Here is how Benchmark sees my system:

BenchmarkDotNet=v0.10.14, OS=Windows 10.0.17134
Intel Core i7-4980HQ CPU 2.80GHz (Haswell), 1 CPU, 8 logical and 4 physical cores
Frequency=2728070 Hz, Resolution=366.5595 ns, Timer=TSC
  [Host]     : .NET Framework 4.6.1 (CLR 4.0.30319.42000), 32bit LegacyJIT-v4.7.3110.0
  DefaultJob : .NET Framework 4.6.1 (CLR 4.0.30319.42000), 32bit LegacyJIT-v4.7.3110.0

All benchmarks below are included in the downloads. Tests are being run against a small dataset of 77 Products and 8 Categories. The benchmarks are not about the performance of SQLite but instead the minimal overhead of the DotNet.Core.SQLite library. I have tried to keep the code as close to identical as possible for each test for unbiased comparisons.

Count Comparison

There are 5 benchmarks ranging from all standard SQLite coding, a hybrid of the DotNet.Core.SQLite library & standard SQLite coding, and lastly DotNet.Core.SQLite library only coding.

public class CountBenchmark
{
    public CountBenchmark()
    {
        // Register the Table/View Context
        dbRegistry.Register(new ProductsContext(), dbRegistry.Register(dbCtx));
    }

    private static readonly string connectionString
        = "data source=northwind.db;version=3;journal mode=Wal"

    private readonly DbContext dbCtx = new DbContext(connectionString);
    private readonly DbRegistry dbRegistry = new DbRegistry();

    [Benchmark(Baseline = true)]
    public void RawSQLite()
    {
        using (var conn = new SQLiteConnection(connectionString))
        using (var cmd = new SQLiteCommand("SELECT COUNT (*) FROM Products", conn))
        {
            conn.Open();
            using (var coreReader = cmd.ExecuteReader())
            {
                coreReader.Read();
                long productCount = coreReader.HasRows ? coreReader.GetInt64(0) : 0;
            }
        }
    }

    [Benchmark]
    public void RawDbContext()
    {
        using (var conn = dbCtx.ConnectionFactory())
        using (var cmd = new SQLiteCommand("SELECT COUNT (*) FROM Products", conn))
        {
            conn.Open();
            using (var coreReader = cmd.ExecuteReader())
            {
                coreReader.Read();
                long productCount = coreReader.HasRows ? coreReader.GetInt64(0) : 0;
            }
        }
    }

    [Benchmark]
    public void ExtensionDbContext()
    {
        long productCount = dbCtx.RecordCount("Products");
    }

    [Benchmark]
    public void ExtensionSQLiteConnection()
    {
        using (var conn = dbCtx.ConnectionFactory())
        {
            long productCount = conn.RecordCount("Products");
        }
    }

    [Benchmark]
    public void ResourceContext()
    {
        long productCount = dbRegistry.Resolve<Product>().Count();
    }
}
Public Class CountBenchmark

    Public Sub New()
        dbRegistry.Register(New ProductsContext(), dbRegistry.Register(dbCtx))
    End Sub

    Private Shared ReadOnly connectionString As String
        = "data source=northwind.db;version=3;journal mode=Wal"

    Private ReadOnly dbCtx As DbContext = New DbContext(connectionString)
    Private ReadOnly dbRegistry As DbRegistry = New DbRegistry()

    <Benchmark(Baseline:=True)>
    Public Sub RawSQLite()

        Using conn = New SQLiteConnection(connectionString)
            Using cmd = New SQLiteCommand("SELECT COUNT (*) FROM Products", conn)
                conn.Open()

                Using coreReader = cmd.ExecuteReader()
                    coreReader.Read()
                    Dim productCount As Long = If(coreReader.HasRows, coreReader.GetInt64(0), 0)
                End Using
            End Using
        End Using

    End Sub

    <Benchmark>
    Public Sub RawDbContext()

        Using conn = dbCtx.ConnectionFactory()
            Using cmd = New SQLiteCommand("SELECT COUNT (*) FROM Products", conn)
                conn.Open()

                Using coreReader = cmd.ExecuteReader()
                    coreReader.Read()
                    Dim productCount As Long = If(coreReader.HasRows, coreReader.GetInt64(0), 0)
                End Using
            End Using
        End Using

    End Sub

    <Benchmark>
    Public Sub ExtensionDbContext()

        Dim productCount As Long = dbCtx.RecordCount("Products")

    End Sub

    <Benchmark>
    Public Sub ExtensionSQLiteConnection()

        Using conn = dbCtx.ConnectionFactory()
            Dim productCount As Long = conn.RecordCount("Products")
        End Using

    End Sub

    <Benchmark>
    Public Sub ResourceContext()

        Dim productCount As Long = dbRegistry.Resolve(Of Product)().Count()

    End Sub

End Class

The results are:

                    Method |    Mean |    Error |   StdDev | Scaled | ScaledSD |  Gen 0 | Allocated |
-------------------------- |--------:|---------:|---------:|-------:|---------:|-------:|----------:|
                 RawSQLite | 7.239ms | 0.1408ms | 0.1622ms |   1.00 |     0.00 | 7.8125 |  62.19 KB |
              RawDbContext | 7.336ms | 0.1453ms | 0.2730ms |   1.01 |     0.04 | 7.8125 |  62.19 KB |
        ExtensionDbContext | 7.209ms | 0.1385ms | 0.1423ms |   1.00 |     0.03 | 7.8125 |  62.31 KB |
 ExtensionSQLiteConnection | 7.384ms | 0.1441ms | 0.1972ms |   1.02 |     0.03 | 7.8125 |  62.31 KB |
           ResourceContext | 7.241ms | 0.1413ms | 0.1735ms |   1.00 |     0.03 | 7.8125 |  62.44 KB |

This is a simple task and there is very little to no overhead.

Read Records Comparison

Like the Count Benchmark above, benchmarks range from raw to full DotNet.Core.SQLite library coding. There are also Async versions of raw SQLite and full DotNet.Core.SQLite library coding.

[MemoryDiagnoser]
public class ReadBenchmark
{
    public ReadBenchmark()
    {
        // Register the Table/View Context
        dbRegistry.Register(new ProductsContext(), dbRegistry.Register(dbCtx));
    }

    private static readonly string connectionString
        = "data source=northwind.db;version=3;journal mode=Wal"

    private readonly DbContext dbCtx = new DbContext(connectionString);
    private readonly DbRegistry dbRegistry = new DbRegistry();

    [Benchmark(Baseline = true)]
    public void RawSQLite()
    {
        var Products = new List<Product>();

        using (var conn = new SQLiteConnection(connectionString))
        {
            conn.Open();

            using (var cmd = conn.CommandFactory("SELECT * FROM Products;"))
            using (var coreReader = cmd.ExecuteReader())
            {
                while (coreReader.Read())
                {
                    Products.Add(new Product
                    {
                        ID = coreReader.GetLong("ProductID", 0L),
                        Name = coreReader.GetString("ProductName"),
                        SupplierID = coreReader.GetLong("SupplierID", 0L),
                        CategoryID = coreReader.GetLong("SupplierID", 0L),
                        QuantityPerUnit = coreReader.GetString("QuantityPerUnit"),
                        UnitPrice = coreReader.GetDecimal("UnitPrice", 0M),
                        UnitsInStock = coreReader.GetInt("UnitsInStock", 0),
                        UnitsOnOrder = coreReader.GetInt("UnitsOnOrder", 0),
                        ReorderLevel = coreReader.GetInt("ReorderLevel", 0),
                        Discontinued = coreReader.GetBoolean("Discontinued", false),
                        DiscontinuedDate = coreReader.GetDateTime("DiscontinuedDate",
                                                                  DateTime.MinValue)
                    });
                }
            }
        }
    }

    [Benchmark]
    public async Task RawSQLiteAsync()
    {
        var Products = new List<Product>();

        using (var conn = new SQLiteConnection(connectionString))
        {
            await conn.OpenAsync().ConfigureAwait(false);

            using (var cmd = conn.CommandFactory("SELECT * FROM Products;"))
            using (var coreReader = (SQLiteDataReader)(await cmd.ExecuteReaderAsync()
                                                                .ConfigureAwait(false)))
            {
                while (await coreReader.ReadAsync().ConfigureAwait(false))
                {
                    Products.Add(new Product
                    {
                        ID = coreReader.GetLong("ProductID", 0L),
                        Name = coreReader.GetString("ProductName"),
                        SupplierID = coreReader.GetLong("SupplierID", 0L),
                        CategoryID = coreReader.GetLong("SupplierID", 0L),
                        QuantityPerUnit = coreReader.GetString("QuantityPerUnit"),
                        UnitPrice = coreReader.GetDecimal("UnitPrice", 0M),
                        UnitsInStock = coreReader.GetInt("UnitsInStock", 0),
                        UnitsOnOrder = coreReader.GetInt("UnitsOnOrder", 0),
                        ReorderLevel = coreReader.GetInt("ReorderLevel", 0),
                        Discontinued = coreReader.GetBoolean("Discontinued", false),
                        DiscontinuedDate = coreReader.GetDateTime("DiscontinuedDate",
                                                                  DateTime.MinValue)
                    });
                }
            }
        }
    }

    [Benchmark]
    public void RawDbContext()
    {
        var Products = new List<Product>();

        using (var conn = dbCtx.ConnectionFactory())
        {
            conn.Open();

            using (var cmd = conn.CommandFactory("SELECT * FROM Products;"))
            using (var coreReader = cmd.ExecuteReader())
            {
                while (coreReader.Read())
                {
                    Products.Add(new Product
                    {
                        ID = coreReader.GetLong("ProductID", 0L),
                        Name = coreReader.GetString("ProductName"),
                        SupplierID = coreReader.GetLong("SupplierID", 0L),
                        CategoryID = coreReader.GetLong("SupplierID", 0L),
                        QuantityPerUnit = coreReader.GetString("QuantityPerUnit"),
                        UnitPrice = coreReader.GetDecimal("UnitPrice", 0M),
                        UnitsInStock = coreReader.GetInt("UnitsInStock", 0),
                        UnitsOnOrder = coreReader.GetInt("UnitsOnOrder", 0),
                        ReorderLevel = coreReader.GetInt("ReorderLevel", 0),
                        Discontinued = coreReader.GetBoolean("Discontinued", false),
                        DiscontinuedDate = coreReader.GetDateTime("DiscontinuedDate",
                                                                  DateTime.MinValue)
                    });
                }
            }
        }
    }

    [Benchmark]
    public void ResourceContext()
    {
        var Products = dbRegistry.Resolve<Product>().Get().ToList();
    }

    [Benchmark]
    public async Task ResourceContextAsync()
    {
        var Products = (await dbRegistry.Resolve<Product>()
                                        .GetAsync().ConfigureAwait(false)).ToList();
    }
}
Public Class ReadBenchmark

    Protected Sub New()
        dbRegistry.Register(New ProductsContext(), dbRegistry.Register(dbCtx))
    End Sub

    Private Shared ReadOnly connectionString As String
        = "data source=northwind.db;version=3;journal mode=Wal"

    Private ReadOnly dbCtx As DbContext = New DbContext(connectionString)
    Private ReadOnly dbRegistry As DbRegistry = New DbRegistry()

    <Benchmark(Baseline:=True)>
    Public Sub RawSQLite()

        Dim Products = New List(Of Product)()

        Using conn = New SQLiteConnection(connectionString)
            conn.Open()

            Using cmd = conn.CommandFactory("SELECT * FROM Products;")
                Using coreReader = cmd.ExecuteReader()
                    While coreReader.Read()
                        Products.Add(New Product With {
                        .Id = coreReader.GetLong("ProductID", 0L),
                        .Name = coreReader.GetString("ProductName"),
                        .SupplierID = coreReader.GetLong("SupplierID", 0L),
                        .CategoryID = coreReader.GetLong("SupplierID", 0L),
                        .QuantityPerUnit = coreReader.GetString("QuantityPerUnit"),
                        .UnitPrice = coreReader.GetDecimal("UnitPrice", 0D),
                        .UnitsInStock = coreReader.GetInt("UnitsInStock", 0),
                        .UnitsOnOrder = coreReader.GetInt("UnitsOnOrder", 0),
                        .ReorderLevel = coreReader.GetInt("ReorderLevel", 0),
                        .Discontinued = coreReader.GetBoolean("Discontinued", False),
                        .DiscontinuedDate = coreReader.GetDateTime("DiscontinuedDate",
                                                                   DateTime.MinValue)
                    })
                    End While
                End Using
            End Using
        End Using

    End Sub

    <Benchmark>
    Public Async Function RawSQLiteAsync() As Task

        Dim Products = New List(Of Product)()

        Using conn = New SQLiteConnection(connectionString)
            Await conn.OpenAsync().ConfigureAwait(False)

            Using cmd = conn.CommandFactory("SELECT * FROM Products;")
                Using coreReader = CType((Await cmd.ExecuteReaderAsync().ConfigureAwait(False)), 
                                         SQLiteDataReader)
                    While Await coreReader.ReadAsync().ConfigureAwait(False)
                        Products.Add(New Product With {
                                .Id = coreReader.GetLong("ProductID", 0L),
                                .Name = coreReader.GetString("ProductName"),
                                .SupplierID = coreReader.GetLong("SupplierID", 0L),
                                .CategoryID = coreReader.GetLong("SupplierID", 0L),
                                .QuantityPerUnit = coreReader.GetString("QuantityPerUnit"),
                                .UnitPrice = coreReader.GetDecimal("UnitPrice", 0D),
                                .UnitsInStock = coreReader.GetInt("UnitsInStock", 0),
                                .UnitsOnOrder = coreReader.GetInt("UnitsOnOrder", 0),
                                .ReorderLevel = coreReader.GetInt("ReorderLevel", 0),
                                .Discontinued = coreReader.GetBoolean("Discontinued", False),
                                .DiscontinuedDate = coreReader.GetDateTime("DiscontinuedDate",
                                                                           DateTime.MinValue)
                            })
                    End While
                End Using
            End Using
        End Using

    End Function

    <Benchmark>
    Public Sub RawDbContext()

        Dim Products = New List(Of Product)()

        Using conn = dbCtx.ConnectionFactory()
            conn.Open()

            Using cmd = conn.CommandFactory("SELECT * FROM Products;")
                Using coreReader = cmd.ExecuteReader()
                    While coreReader.Read()
                        Products.Add(New Product With {
                                .Id = coreReader.GetLong("ProductID", 0L),
                                .Name = coreReader.GetString("ProductName"),
                                .SupplierID = coreReader.GetLong("SupplierID", 0L),
                                .CategoryID = coreReader.GetLong("SupplierID", 0L),
                                .QuantityPerUnit = coreReader.GetString("QuantityPerUnit"),
                                .UnitPrice = coreReader.GetDecimal("UnitPrice", 0D),
                                .UnitsInStock = coreReader.GetInt("UnitsInStock", 0),
                                .UnitsOnOrder = coreReader.GetInt("UnitsOnOrder", 0),
                                .ReorderLevel = coreReader.GetInt("ReorderLevel", 0),
                                .Discontinued = coreReader.GetBoolean("Discontinued", False),
                                .DiscontinuedDate = coreReader.GetDateTime("DiscontinuedDate",
                                                                           DateTime.MinValue)
                            })
                    End While
                End Using
            End Using
        End Using

    End Sub

    <Benchmark>
    Public Sub ResourceContext()

        Dim Products = dbRegistry.Resolve(Of Product)().[Get]().ToList()

    End Sub

    <Benchmark>
    Public Async Function ResourceContextAsync() As Task

        Dim Products = (Await dbRegistry.Resolve(Of Product)()
                                        .GetAsync().ConfigureAwait(False)).ToList()

    End Function

End Class

The results are:

               Method |     Mean |     Error |    StdDev | Scaled | ScaledSD |  Gen 0 | Allocated |
--------------------- |---------:|----------:|----------:|-------:|---------:|-------:|----------:|
            RawSQLite | 6.379 ms | 0.0676 ms | 0.0599 ms |   1.00 |     0.00 | 7.8125 |  78.13 KB |
       RawSQLiteAsync | 6.368 ms | 0.0799 ms | 0.0709 ms |   1.00 |     0.01 | 7.8125 |  78.45 KB |
         RawDbContext | 6.382 ms | 0.0750 ms | 0.0702 ms |   1.00 |     0.01 | 7.8125 |  78.13 KB |
      ResourceContext | 6.456 ms | 0.1393 ms | 0.1303 ms |   1.01 |     0.02 | 7.8125 |  78.44 KB |
 ResourceContextAsync | 6.485 ms | 0.1001 ms | 0.0937 ms |   1.02 |     0.02 | 7.8125 |  79.19 KB |

Again, we can see that it is very little to no overhead and the DotNet.Core.SQLite library code is a single line. Async, as expected, has a very low overhead.

Add/Delete Records Comparison

This Benchmark will add and remove 10 x Categories. This set of benchmarks also demonstrates how to optimize DotNet.Core.SQLite library calls when more than one operation occurs on the same database connection. It is always best practice to group multiple commands for database access on a single connection. ResourceContextAsync and ResourceContextAsyncOptimized methods clearly demonstrate the why.

[MemoryDiagnoser]
public class AddDeleteBenchmark
{
    public AddDeleteBenchmark()
    {
        // Register the Table/View Context
        dbRegistry.Register(new CategoriesContext(), dbRegistry.Register(dbCtx));
    }

    private static readonly string connectionString
        = "data source=northwind.db;version=3;journal mode=Wal";

    private readonly DbContext dbCtx = new DbContext(connectionString);
    private readonly DbRegistry dbRegistry = new DbRegistry();

    private const string insertSql = 
        "INSERT INTO Categories (CategoryName, Description, Picture)" +
        " VALUES (@CategoryName, @Description, @Picture);";

    private const string deleteSql
        = "DELETE FROM Categories WHERE CategoryID = @CategoryID;";

    private static readonly byte[] nullPicture = new byte[0];

    private readonly Category categoryItem = new Category
    {
        Description = "this is a test category",
        Picture = nullPicture
    };

    private readonly int records = 10;

    [Benchmark(Baseline = true)]
    public async Task RawSQLiteAsync()
    {
        using (var conn = new SQLiteConnection(connectionString))
        {
            await conn.OpenAsync().ConfigureAwait(false);

            for (int i = 0; i < records; i++)
            {
                using (var addCmd = new SQLiteCommand(insertSql, conn))
                using (var delCmd = new SQLiteCommand(deleteSql, conn))
                {
                    addCmd.Parameters.AddWithValue("@CategoryName",
                                                   ("test category " + i).ToSqLiteText());
                    addCmd.Parameters.AddWithValue("@Description",
                                                   ("this is a test category").ToSqLiteText());
                    addCmd.Parameters.AddWithValue("@Picture", nullPicture);
                    await addCmd.ExecuteNonQueryAsync().ConfigureAwait(false);

                    delCmd.Parameters.AddWithValue("@CategoryID", conn.LastInsertRowId);
                    await delCmd.ExecuteNonQueryAsync().ConfigureAwait(false);
                }
            }
        }
    }

    [Benchmark]
    public async Task OptimisedRawSQLiteAsync()
    {
        using (var conn = new SQLiteConnection(connectionString))
        {
            await conn.OpenAsync().ConfigureAwait(false);

            using (var addCmd = new SQLiteCommand(insertSql, conn))
            using (var delCmd = new SQLiteCommand(deleteSql, conn))
            {
                for (int i = 0; i < records; i++)
                {
                    addCmd.Parameters.AddWithValue("@CategoryName",
                                                   ("test category " + i).ToSqLiteText());
                    addCmd.Parameters.AddWithValue("@Description",
                                                   ("this is a test category").ToSqLiteText());
                    addCmd.Parameters.AddWithValue("@Picture", nullPicture);
                    await addCmd.ExecuteNonQueryAsync().ConfigureAwait(false);

                    delCmd.Parameters.AddWithValue("@CategoryID", conn.LastInsertRowId);
                    await delCmd.ExecuteNonQueryAsync().ConfigureAwait(false);
                }
            }
        }
    }

    [Benchmark]
    public async Task OptimisedRawDbContextAsync()
    {
        using (var conn = dbCtx.ConnectionFactory())
        {
            await conn.OpenAsync().ConfigureAwait(false);
            using (var addCmd = new SQLiteCommand(insertSql, conn))
            using (var delCmd = new SQLiteCommand(deleteSql, conn))
            {
                for (int i = 0; i < records; i++)
                {
                    addCmd.Parameters.AddWithValue("@CategoryName",
                                                   ("test category " + i).ToSqLiteText());
                    addCmd.Parameters.AddWithValue("@Description",
                                                   ("this is a test category").ToSqLiteText());
                    addCmd.Parameters.AddWithValue("@Picture", nullPicture);
                    await addCmd.ExecuteNonQueryAsync().ConfigureAwait(false);

                    delCmd.Parameters.AddWithValue("@CategoryID", conn.LastInsertRowId);
                    await delCmd.ExecuteNonQueryAsync().ConfigureAwait(false);
                }
            }
        }
    }

    [Benchmark]
    public async Task ResourceContextAsync()
    {
        var repo = dbRegistry.Resolve<Category>();
        for (int i = 0; i < records; i++)
        {
            categoryItem.Name = "test category " + i;
            await repo.AddAsync(new DbQuery<Category>(categoryItem)).ConfigureAwait(false);

            categoryItem.Id = repo.LastInsertRowId;
            await repo.DeleteAsync(new DbQuery<Category>(categoryItem)).ConfigureAwait(false);
        }
    }

    [Benchmark]
    public async Task OptimizedResourceContextAsync()
    {
        var repo = dbRegistry.Resolve<Category>();
        using (var conn = repo.Context.ConnectionFactory())
        {
            for (int i = 0; i < records; i++)
            {
                categoryItem.Name = "test category " + i;
                await repo.AddAsync(conn,
                                    new DbQuery<Category>(categoryItem)).ConfigureAwait(false);

                categoryItem.Id = repo.LastInsertRowId;
                await repo.DeleteAsync(conn,
                                       new DbQuery<Category>(categoryItem)).ConfigureAwait(false);
            }
        }
    }
}
Public Class AddDeleteBenchmark

    Public Sub New()
        dbRegistry.Register(New CategoriesContext(), dbRegistry.Register(dbCtx))
    End Sub

    Private Shared ReadOnly connectionString As String
        = "data source=northwind.db;version=3;journal mode=Wal"

    Private ReadOnly dbCtx As DbContext = New DbContext(connectionString)
    Private ReadOnly dbRegistry As DbRegistry = New DbRegistry()

    Private Const insertSql As String =
        "INSERT INTO Categories (CategoryName, Description, Picture)" &
        " VALUES (@CategoryName, @Description, @Picture);"

    Private Const deleteSql As String
        = "DELETE FROM Categories WHERE CategoryID = @CategoryID;"

    Private Shared nullPicture As Byte() = New Byte(-1) {}

    Private ReadOnly categoryItem As Category = New Category With {
            .Description = "this is a test category",
            .Picture = nullPicture
        }

    Private ReadOnly records As Integer = 10

    <Benchmark(Baseline:=True)>
    Public Async Function RawSQLiteAsync() As Task

        Using conn = New SQLiteConnection(connectionString)
            Await conn.OpenAsync().ConfigureAwait(False)

            For i As Integer = 0 To records - 1
                Using addCmd = New SQLiteCommand(insertSql, conn)
                    Using delCmd = New SQLiteCommand(deleteSql, conn)
                        addCmd.Parameters.AddWithValue("@CategoryName",
                                                       ("test category " & i).ToSqLiteText())
                        addCmd.Parameters.AddWithValue("@Description",
                                                       ("this is a test category").ToSqLiteText())
                        addCmd.Parameters.AddWithValue("@Picture", nullPicture)
                        Await addCmd.ExecuteNonQueryAsync().ConfigureAwait(False)

                        delCmd.Parameters.AddWithValue("@CategoryID", conn.LastInsertRowId)
                        Await delCmd.ExecuteNonQueryAsync().ConfigureAwait(False)
                    End Using
                End Using
            Next
        End Using

    End Function

    <Benchmark>
    Public Async Function OptimisedRawSQLiteAsync() As Task

        Using conn = New SQLiteConnection(connectionString)
            Await conn.OpenAsync().ConfigureAwait(False)

            Using addCmd = New SQLiteCommand(insertSql, conn)
                Using delCmd = New SQLiteCommand(deleteSql, conn)
                    For i As Integer = 0 To records - 1
                        addCmd.Parameters.AddWithValue("@CategoryName",
                                                       ("test category " & i).ToSqLiteText())
                        addCmd.Parameters.AddWithValue("@Description",
                                                       ("this is a test category").ToSqLiteText())
                        addCmd.Parameters.AddWithValue("@Picture", nullPicture)
                        Await addCmd.ExecuteNonQueryAsync().ConfigureAwait(False)

                        delCmd.Parameters.AddWithValue("@CategoryID", conn.LastInsertRowId)
                        Await delCmd.ExecuteNonQueryAsync().ConfigureAwait(False)
                    Next
                End Using
            End Using
        End Using

    End Function

    <Benchmark>
    Public Async Function OptimisedRawDbContextAsync() As Task

        Using conn = dbCtx.ConnectionFactory()
            Await conn.OpenAsync().ConfigureAwait(False)

            Using addCmd = New SQLiteCommand(insertSql, conn)
                Using delCmd = New SQLiteCommand(deleteSql, conn)
                    For i As Integer = 0 To records - 1
                        addCmd.Parameters.AddWithValue("@CategoryName",
                                                       ("test category " & i).ToSqLiteText())
                        addCmd.Parameters.AddWithValue("@Description",
                                                       ("this is a test category").ToSqLiteText())
                        addCmd.Parameters.AddWithValue("@Picture", nullPicture)
                        Await addCmd.ExecuteNonQueryAsync().ConfigureAwait(False)
                        delCmd.Parameters.AddWithValue("@CategoryID", conn.LastInsertRowId)
                        Await delCmd.ExecuteNonQueryAsync().ConfigureAwait(False)
                    Next
                End Using
            End Using
        End Using

    End Function

    <Benchmark>
    Public Async Function ResourceContextAsync() As Task

        Dim repo = dbRegistry.Resolve(Of Category)()

        For i As Integer = 0 To records - 1
            categoryItem.Name = "test category " & i
            Await repo.AddAsync(New DbQuery(Of Category)(categoryItem)).ConfigureAwait(False)
            categoryItem.Id = repo.LastInsertRowId
            Await repo.DeleteAsync(New DbQuery(Of Category)(categoryItem)).ConfigureAwait(False)
        Next

    End Function

    <Benchmark>
    Public Async Function OptimizedResourceContextAsync() As Task

        Dim repo = dbRegistry.Resolve(Of Category)()

        Using conn = repo.Context.ConnectionFactory()

            For i As Integer = 0 To records - 1
                categoryItem.Name = "test category " & i
                Await repo.AddAsync(conn,
                                    New DbQuery(Of Category)(categoryItem)).ConfigureAwait(False)

                categoryItem.Id = repo.LastInsertRowId
                Await repo.DeleteAsync(conn,
                                       New DbQuery(Of Category)(categoryItem)).ConfigureAwait(False)
            Next
        End Using

    End Function

End Class

The key difference between RawSQLiteAsync and OptimisedRawSQLiteAsync benchmark tests is the OptimisedRawSQLiteAsync benchmark creates the SQLiteCommands outside the loop.

Also, the key difference between ResourceContextAsync and OptimizedResourceContextAsync benchmark tests is that OptimizedResourceContextAsync shares a SQLiteConnection object. ResourceContextAsync creates SQLiteConnection and SQLiteCommands objects for each AddAsync and DeleteAsync method inside the loop.

The results are:

                       Method |    Mean |    Error |   StdDev | Scaled | ScaledSD | Gen 0 | Allocated |
----------------------------- |--------:|---------:|---------:|-------:|---------:|------:|----------:|
               RawSQLiteAsync | 143.3ms |  2.830ms |  4.321ms |   1.00 |     0.00 |     - |     80 KB |
      OptimisedRawSQLiteAsync | 138.2ms |  2.733ms |  7.056ms |   0.97 |     0.06 |     - |   64.5 KB |
   OptimisedRawDbContextAsync | 132.5ms |  3.496ms | 10.307ms |   0.93 |     0.08 |     - |   64.5 KB |
         ResourceContextAsync | 778.1ms | 23.810ms | 70.204ms |   5.43 |     0.51 | 187.5 | 1000.6 KB |
OptimizedResourceContextAsync | 135.1ms |  2.885ms |  8.506ms |   0.94 |     0.07 |     - |     86 KB |

The code in the DotNet.Core.SQLite library seems to run slightly more efficiently in the asynchronous environment even though the code used in the SQLite only benchmark is the same code used in the DotNet.Core.SQLite library.

Transactional Add/Delete Records Comparison

This Benchmark is almost identical to the previous, however, as we are now working with Transactions, I have increased the number of records from 10 to 1000.

[MemoryDiagnoser]
public class TransactionBenchmark
{
    public TransactionBenchmark()
    {
        // Register the Table/View Context
        dbRegistry.Register(new CategoriesContext(), dbRegistry.Register(dbCtx));
    }

    private static readonly string connectionString
        = "data source=northwind.db;version=3;journal mode=Wal";

    private readonly DbContext dbCtx = new DbContext(connectionString);
    private readonly DbRegistry dbRegistry = new DbRegistry();

    private const string insertSql = "INSERT INTO Categories (CategoryName, Description, Picture)"
                                        + " VALUES (@CategoryName, @Description, @Picture);";

    private const string deleteSql = "DELETE FROM Categories WHERE CategoryID = @CategoryID;";

    private static readonly byte[] nullPicture = new byte[0];

    private readonly Category categoryItem = new Category
    {
        Description = "this is a test category",
        Picture = nullPicture
    };

    private readonly int records = 1000;

    [Benchmark(Baseline = true)]
    public async Task BulkRawSQLiteTransAsync()
    {
        using (var conn = new SQLiteConnection(connectionString))
        {
            await conn.OpenAsync().ConfigureAwait(false);
            using (var trans = conn.BeginTransaction())
            {
                try
                {
                    for (int i = 0; i < records; i++)
                    {
                        using (var addCmd = new SQLiteCommand(insertSql, conn))
                        using (var delCmd = new SQLiteCommand(deleteSql, conn))
                        {
                            addCmd.Parameters.AddWithValue("@CategoryName",
                                                     ("test category " + i).ToSqLiteText());
                            addCmd.Parameters.AddWithValue("@Description",
                                                     ("this is a test category").ToSqLiteText());
                            addCmd.Parameters.AddWithValue("@Picture", nullPicture);
                            await addCmd.ExecuteNonQueryAsync().ConfigureAwait(false);

                            delCmd.Parameters.AddWithValue("@CategoryID", conn.LastInsertRowId);
                            await delCmd.ExecuteNonQueryAsync().ConfigureAwait(false);
                        }
                    }

                    trans.Commit();
                }
                catch (Exception ex)
                {
                    Console.WriteLine("!! ERROR: " + ex.Message + " ... "
                        + ex.InnerException.Message.Replace("/r/n", " >> "));
                    // fail, so log here
                    trans.Rollback();
                }
            }
        }
    }

    [Benchmark]
    public async Task OptimisedBulkRawSQLiteTransAsync()
    {
        using (var conn = new SQLiteConnection(connectionString))
        {
            await conn.OpenAsync().ConfigureAwait(false);
            using (var trans = conn.BeginTransaction())
            {
                try
                {
                    using (var addCmd = new SQLiteCommand(insertSql, conn))
                    using (var delCmd = new SQLiteCommand(deleteSql, conn))
                    {
                        for (int i = 0; i < records; i++)
                        {
                            addCmd.Parameters.AddWithValue("@CategoryName",
                                                     ("test category " + i).ToSqLiteText());
                            addCmd.Parameters.AddWithValue("@Description",
                                                     ("this is a test category").ToSqLiteText());
                            addCmd.Parameters.AddWithValue("@Picture", nullPicture);
                            await addCmd.ExecuteNonQueryAsync().ConfigureAwait(false);

                            delCmd.Parameters.AddWithValue("@CategoryID", conn.LastInsertRowId);
                            await delCmd.ExecuteNonQueryAsync().ConfigureAwait(false);
                        }
                    }

                    trans.Commit();
                }
                catch (Exception ex)
                {
                    Console.WriteLine("!! ERROR: " + ex.Message + " ... "
                        + ex.InnerException.Message.Replace("/r/n", " >> "));
                    // fail, so log here
                    trans.Rollback();
                }
            }
        }
    }

    [Benchmark]
    public async Task RawDbContextUOWAsync()
    {
        using (var conn = dbCtx.ConnectionFactory())
        {
            await conn.OpenAsync().ConfigureAwait(false);
            using (var uow = new UnitOfWork(conn))
            {
                try
                {
                    using (var addCmd = new SQLiteCommand(insertSql, conn))
                    using (var delCmd = new SQLiteCommand(deleteSql, conn))
                    {
                        for (int i = 0; i < records; i++)
                        {
                            addCmd.Parameters.AddWithValue("@CategoryName",
                                                     ("test category " + i).ToSqLiteText());
                            addCmd.Parameters.AddWithValue("@Description",
                                                     ("this is a test category").ToSqLiteText());
                            addCmd.Parameters.AddWithValue("@Picture", nullPicture);
                            await addCmd.ExecuteNonQueryAsync().ConfigureAwait(false);

                            delCmd.Parameters.AddWithValue("@CategoryID", conn.LastInsertRowId);
                            await delCmd.ExecuteNonQueryAsync().ConfigureAwait(false);
                        }
                    }

                    uow.SaveChanges();
                }
                catch (Exception ex)
                {
                    Console.WriteLine("!! ERROR: " + ex.Message + " ... "
                        + ex.InnerException.Message.Replace("/r/n", " >> "));
                    // fail, so log here and the transaction
                    // will auto rollback (see UnitOfWork class)
                }
            }
        }
    }

    [Benchmark]
    public async Task ResourceContextUOWAsync()
    {
        var repo = dbRegistry.Resolve<Category>();
        using (var uow = repo.UnitOfWorkFactory())
        {
            try
            {
                for (int i = 0; i < records; i++)
                {
                    categoryItem.Name = "test category " + i;
                    await repo.AddAsync(uow, new DbQuery<Category>(categoryItem))
                              .ConfigureAwait(false);

                    categoryItem.Id = repo.LastInsertRowId;
                    await repo.DeleteAsync(uow, new DbQuery<Category>(categoryItem))
                              .ConfigureAwait(false);
                }
                uow.SaveChanges();
            }
            catch (Exception ex)
            {
                Console.WriteLine("!! ERROR: " + ex.Message + " ... "
                    + ex.InnerException.Message.Replace("/r/n", " >> "));
                // fail, so log here and the transaction
                // will auto rollback (see UnitOfWork class)
            }
        }
    }
}
Public Class TransactionBenchmark

    Public Sub New()
        dbRegistry.Register(New CategoriesContext(), dbRegistry.Register(dbCtx))
    End Sub

    Private Shared ReadOnly connectionString As String =
        "data source=northwind.db;version=3;journal mode=Wal"

    Private ReadOnly dbCtx As DbContext = New DbContext(connectionString)
    Private ReadOnly dbRegistry As DbRegistry = New DbRegistry()

    Private Const insertSql As String =
        "INSERT INTO Categories (CategoryName, Description, Picture)" &
        " VALUES (@CategoryName, @Description, @Picture);"

    Private Const deleteSql As String =
        "DELETE FROM Categories WHERE CategoryID = @CategoryID;"

    Private Shared ReadOnly nullPicture As Byte() = New Byte(-1) {}

    Private ReadOnly categoryItem As Category = New Category With {
            .Description = "this is a test category",
            .Picture = nullPicture
        }

    Private ReadOnly records As Integer = 1000

    <Benchmark(Baseline:=True)>
    Public Async Function BulkRawSQLiteTransAsync() As Task

        Using conn = New SQLiteConnection(connectionString)
            Await conn.OpenAsync().ConfigureAwait(False)

            Using trans = conn.BeginTransaction()
                Try
                    For i As Integer = 0 To records - 1
                        Using addCmd = New SQLiteCommand(insertSql, conn)
                            Using delCmd = New SQLiteCommand(deleteSql, conn)
                                addCmd.Parameters.AddWithValue("@CategoryName",
                                                         ("test category " & i).ToSqLiteText())
                                addCmd.Parameters.AddWithValue("@Description",
                                                         ("this is a test category").ToSqLiteText())
                                addCmd.Parameters.AddWithValue("@Picture", nullPicture)
                                Await addCmd.ExecuteNonQueryAsync().ConfigureAwait(False)

                                delCmd.Parameters.AddWithValue("@CategoryID", conn.LastInsertRowId)
                                Await delCmd.ExecuteNonQueryAsync().ConfigureAwait(False)
                            End Using
                        End Using
                    Next

                    trans.Commit()

                Catch ex As Exception
                    Console.WriteLine("!! ERROR: " & ex.Message & " ... " &
                        ex.InnerException.Message.Replace(vbCrLf, " >> "))
                    // fail, so log here
                    trans.Rollback()
                End Try
            End Using
        End Using

    End Function

    <Benchmark>
    Public Async Function OptimisedBulkRawSQLiteTransAsync() As Task

        Using conn = New SQLiteConnection(connectionString)
            Await conn.OpenAsync().ConfigureAwait(False)

            Using trans = conn.BeginTransaction()
                Try
                    Using addCmd = New SQLiteCommand(insertSql, conn)
                        Using delCmd = New SQLiteCommand(deleteSql, conn)
                            For i As Integer = 0 To records - 1
                                addCmd.Parameters.AddWithValue("@CategoryName",
                                                         ("test category " & i).ToSqLiteText())
                                addCmd.Parameters.AddWithValue("@Description",
                                                         ("this is a test category").ToSqLiteText())
                                addCmd.Parameters.AddWithValue("@Picture", nullPicture)
                                Await addCmd.ExecuteNonQueryAsync().ConfigureAwait(False)

                                delCmd.Parameters.AddWithValue("@CategoryID", conn.LastInsertRowId)
                                Await delCmd.ExecuteNonQueryAsync().ConfigureAwait(False)
                            Next
                        End Using
                    End Using

                    trans.Commit()

                Catch ex As Exception
                    Console.WriteLine("!! ERROR: " & ex.Message & " ... " &
                        ex.InnerException.Message.Replace(vbCrLf, " >> "))
                    // fail, so log here
                    trans.Rollback()
                End Try
            End Using
        End Using

    End Function

    <Benchmark>
    Public Async Function RawDbContextUOWAsync() As Task

        Using conn = dbCtx.ConnectionFactory()
            Await conn.OpenAsync().ConfigureAwait(False)

            Using uow = New UnitOfWork(conn)
                Try
                    Using addCmd = New SQLiteCommand(insertSql, conn)
                        Using delCmd = New SQLiteCommand(deleteSql, conn)
                            For i As Integer = 0 To records - 1
                                addCmd.Parameters.AddWithValue("@CategoryName",
                                                         ("test category " & i).ToSqLiteText())
                                addCmd.Parameters.AddWithValue("@Description",
                                                         ("this is a test category").ToSqLiteText())
                                addCmd.Parameters.AddWithValue("@Picture", nullPicture)
                                Await addCmd.ExecuteNonQueryAsync().ConfigureAwait(False)

                                delCmd.Parameters.AddWithValue("@CategoryID", conn.LastInsertRowId)
                                Await delCmd.ExecuteNonQueryAsync().ConfigureAwait(False)
                            Next
                        End Using
                    End Using

                    uow.SaveChanges()
                Catch ex As Exception
                    Console.WriteLine("!! ERROR: " & ex.Message & " ... " &
                        ex.InnerException.Message.Replace(vbCrLf, " >> "))
                    // fail, so log here and the transaction
                    // will auto rollback (see UnitOfWork class) 
                End Try
            End Using
        End Using

    End Function

    <Benchmark>
    Public Async Function ResourceContextUOWAsync() As Task

        Dim repo = dbRegistry.Resolve(Of Category)()

        Using uow = repo.UnitOfWorkFactory()
            Try
                For i As Integer = 0 To records - 1
                    categoryItem.Name = "test category " & i
                    Await repo.AddAsync(uow, New DbQuery(Of Category)(categoryItem))
                              .ConfigureAwait(False)
                    categoryItem.Id = repo.LastInsertRowId
                    Await repo.DeleteAsync(uow, New DbQuery(Of Category)(categoryItem))
                              .ConfigureAwait(False)
                Next

                uow.SaveChanges()

            Catch ex As Exception
                Console.WriteLine("!! ERROR: " & ex.Message & " ... " &
                    ex.InnerException.Message.Replace(vbCrLf, " >> "))
            End Try
        End Using

    End Function

End Class

The results are:

                    Method |    Mean |    Error |  StdDev | Scaled | ScaledSD |    Gen 0 |Allocated |
-------------------------- |--------:|---------:|--------:|-------:|---------:|---------:|---------:|
         BulkRawTransAsync | 54.96ms | 1.2501ms | 3.646ms |   1.00 |     0.00 | 625.0000 |   3.14MB |
OptimisedBulkRawTransAsync | 48.41ms | 2.1520ms | 6.311ms |   0.88 |     0.13 | 250.0000 |   1.46MB |
      RawDbContextUOWAsync | 45.50ms | 1.6327ms | 4.737ms |   0.83 |     0.10 | 250.0000 |   1.46MB |
   ResourceContextUOWAsync | 39.26ms | 0.7823ms | 1.017ms |   0.72 |     0.05 | 687.5000 |   3.69MB |

Like the previous set of benchmarks, the code in the DotNet.Core.SQLite library seems to run slightly more efficiently in the asynchronous environment.

How it Works

Working with SQLite in C# &amp; VB

Quote:

Pay no attention to that man behind the curtain … L. Frank Baum, The Wizard of Oz

There is a simple joy in seeing something happening and not know how it happened. Sometimes, trying to understand it takes the fun out of it. But for those who are interested, read on.

Most of the DotNet.Core.SQLite library functionality is extension methods for standard SQLite resources with only a small set of concrete classes:

  1. DbContext – wrapping a database connection, tracing, and db specific functionality like reporting
  2. DbRegistry – Associating Tables and Views with POCOs and DbContext
  3. DbResourceContext – mapping a POCO to a Table or View
  4. UnitOfWork – managing transactions

This enables the developer to choose either to use a subset of extension methods to enhance their current code or to use the full functionality of the DotNet.Core.SQLite library.

DbContext Database Context

The DotNet.Core.SQLite library is built around a DbContext. The DbContext represents a single database instance. Later in the article, we will discuss the DbRegistry class where we can link any number of different databases against individual Tables and Views.

The DbContext is used as the core for actions (methods) that can be performed against the database. It is also used to enable tracing and logging of the internal SQLite engine.

public class DbContext : IDisposable
{
    #region Constructor / Deconstructor

    public DbContext() { }

    public DbContext(ISqliteEventTrace trace) : this()
        => SetTrace(trace);

    public DbContext(SQLiteConnectionStringBuilder builder)
        => SetConnectionString(builder);

    public DbContext(string connectionString)
        => SetConnectionString(connectionString);

    public DbContext(string file, Dictionary<string, string> paramList)
        => SetConnectionString(file, paramList);

    public DbContext(SQLiteConnectionStringBuilder builder, ISqliteEventTrace trace)
    {
        SetConnectionString(builder);
        SetTrace(trace);
    }

    public DbContext(string connectionString, ISqliteEventTrace trace)
    {
        SetConnectionString(connectionString);
        SetTrace(trace);
    }

    public DbContext(string file, Dictionary<string, string> paramList, ISqliteEventTrace trace)
    {
        SetConnectionString(file, paramList);
        SetTrace(trace);
    }

    public void Dispose()
    {
        if (Trace != null)
        {
            Trace.StateChange -= OnStateSchanged;
            Trace.DbContext = null;
            Trace = null;
        }
        if (Builder != null) Builder = null;
    }

    // just in case we forget to dispose, let the GC do it for us...
    ~DbContext() => Dispose();

    #endregion

By implementing the IDisposable interface we can encapsulate the lifetime of the class in a using statement. If not encapsulated, then the Dispose() method must be called.

#region Properties

public SqliteEventTraceBase Trace { get; private set; }

public SQLiteConnectionStringBuilder Builder { get; private set; }

public string DbPath { get => Path.GetDirectoryName(DbFileName); }
public string DbFile { get => Path.GetFileName(DbFileName); }

public string DbFileName { get; private set; }

private int openConnectionCount;
public int OpenConnectionCount { get => openConnectionCount; }

#endregion

Key properties are exposed for quick access.

#region ConnectionFactory

public SQLiteConnection ConnectionFactory()
{
    if (Builder.IsNull() || Builder.ConnectionString.IsNullOrEmpty())
        throw new Exception("ConnectionString is not set.");

    var connection = new SQLiteConnection(Builder.ConnectionString);
    Trace?.StartListeningToConnection(connection);

    return connection;
}

#endregion

The ConnectionFactory() method needs to be used for creating connections to the database if tracing is required. It will return a standard SQLite SQLiteConnection object.

#region ConnectionString

public bool SetConnectionString(SQLiteConnectionStringBuilder builder)
{
    SetConnection(new SQLiteConnectionStringBuilder { ConnectionString = builder.ConnectionString });
    return File.Exists(Builder.DataSource);
}

public bool SetConnectionString(string connectionString)
{
    SetConnection(new SQLiteConnectionStringBuilder { ConnectionString = connectionString });
    return File.Exists(Builder.DataSource);
}

public bool SetConnectionString(string file, Dictionary<string, string> paramList)
{
    var @params = paramList.Count == 0
        ? ""
        : string.Concat(paramList.Select(x => $";{x.Key}={x.Value}"));

    SetConnection(new SQLiteConnectionStringBuilder($"Data Source={file}{@params}"));
    return File.Exists(Builder.DataSource);
}

private void SetConnection(SQLiteConnectionStringBuilder builder)
{
    Builder = builder;
    DbFileName = builder.DataSource;
}

#endregion

Setting the ConnectionString can either be set when the DbContext class is initialized or manually after.

    #region Tracing

    public void SetTrace(ISqliteEventTrace trace)
    {
        if (Trace != null)
        {
            Trace.StateChange -= OnStateSchanged;
            Trace.DbContext = null;
        }

        if (trace != null)
        {
            Trace = trace as SqliteEventTraceBase;
            Trace.DbContext = this;
            Trace.StateChange += OnStateSchanged; 
        }
    }

    internal void OnStateSchanged(object sender, StateChangeEventArgs e)
    {
        switch (e.CurrentState)
        {
            case ConnectionState.Closed:
                Interlocked.Decrement(ref openConnectionCount);
                break;
            case ConnectionState.Open:
                Interlocked.Increment(ref openConnectionCount);
                break;
        }
    }

    #endregion
}

Like the ConnectionString, the trace can either be set when the DbContext class is initialized or manually after.

Tracing and Logging

Understanding and optimizing your code can be tricky. However, SQLite has support for listening to actions that are performed. Here is the sample code again to open a connection and perform a query and the trace log:

var connectionString = "data source=northwind.db;version=3";

var writer = new SimpleConsoleTextWriter();
var trace = new SqliteCsvEventTrace(writer, SqliteConnectionTraceType.Full);

using (var dbCtx = new DbContext(connectionString, trace))
{
    var result = dbCtx.RecordCount("Products");
    Console.WriteLine($"/n** Total Products: {result}/n");
}

The output:

_TRACE: "northwind.db","Listening","74DB74"
_TRACE: "74DB74","Event","Opening",""
_TRACE: "74DB74","Event","Closing",""
_TRACE: "74DB74","Event","Closed",""
_TRACE: "74DB74","Event","ConnectionString","version=3;data source=northwind.db"
_TRACE: "northwind.db","Event","NewCriticalHandle","C:/SQLiteWrapper/Samples/Tracing/bin/Debug/northwind.db"
_TRACE: "74DB74","Event","NewCommand",""
_TRACE: "74DB74","Event","DisposingCommand",""
_TRACE: "74DB74","State","Open"
_TRACE: "74DB74","Event","Opened",""
_TRACE: "74DB74","Event","NewCommand",""
_TRACE: "74DB74","Event","NewDataReader",""
_TRACE: "74DB74","Auth","","Select","","","Ok"
_TRACE: "74DB74","Auth","","Function","","count","Ok"
_TRACE: "74DB74","Auth","","Read","Products","","Ok"
_TRACE: "northwind.db","Event","NewCriticalHandle","SELECT Count(*) FROM Products"
_TRACE: "74DB74","Event","DisposingDataReader",""
_TRACE: "74DB74","Event","ClosingDataReader",""
_TRACE: "74DB74","Event","DisposingCommand",""
_TRACE: "74DB74","Event","Closing",""
_TRACE: "74DB74","State","Closed"
_TRACE: "74DB74","Event","Closed",""
_TRACE: "74DB74","Disposing"

** Total Products: 77

The SqliteCsvEventTrace class inherits from the SqliteEventTraceBase, listens to chosen events and outputs the information in a CSV format to a TextWriter. In the above example, I am using the SimpleConsoleTextWriter class to dump output to the console.

SqliteEventTraceBase Class

The SqliteEventTraceBase base class encapsulates the code to listen to the events. and exposes the TextWriter for the app to write custom messages.

public interface ISqliteEventTrace : IDisposable
{
    TextWriter TextWriter { get; }
}
public abstract class SqliteEventTraceBase : ISqliteEventTrace 
{
    protected SqliteEventTraceBase(TextWriter writer,
                                   SqliteConnectionTraceType traceTypes =
                                       SqliteConnectionTraceType.Compact)
    {
        TextWriter = writer;
        TraceTypes = traceTypes;
    }

    internal DbContext DbContext { get; set; }
    public TextWriter TextWriter { get; internal set; }

    internal SqliteConnectionTraceType TraceTypes { get; set; }

    internal void StartListeningToConnection(SQLiteConnection connection)
    {
        // we should never ecounter this! But just in case...
        if (connection.IsNull()) return;

        Write(SqliteEventTraceWriterType.StartListening, connection, null);

        connection.Disposed += OnConnectionDisposed;
        connection.StateChange += OnStateChange;

        if (IsTrace(SqliteConnectionTraceType.Authorize))
            connection.Authorize += OnAuthorize;

        // ... trimmed - see downloaded code ...
    }

    internal void StopListeningToConnection(SQLiteConnection connection)
    {
        // we should never ecounter this! But just in case...
        if (connection.IsNull()) return;

        connection.Disposed -= OnConnectionDisposed;
        connection.StateChange -= OnStateChange;

        if (IsTrace(SqliteConnectionTraceType.Authorize))
            connection.Authorize -= OnAuthorize;

        // ... trimmed - see downloaded code ...
    }

    internal bool IsTrace(SqliteConnectionTraceType type) => (TraceTypes & type) != 0;

    internal event EventHandler<StateChangeEventArgs> StateChange;

    internal void RaiseStateChange(object sender, StateChangeEventArgs e)
        => StateChange?.Invoke(sender, e);

    // just in case we forget to dispose, let the GC do it for us...
    ~SqliteEventTraceBase() => Dispose();

    public virtual void Dispose()
    {
        if (TextWriter != null) TextWriter = null;
        if (DbContext != null) DbContext = null;
    }

    #region Events

    #region Event Messages

    protected internal abstract void Write(SqliteEventTraceWriterType eventType,
                                           object sender,
                                           EventArgs e);

    #endregion

    private void OnConnectionDisposed(object sender, EventArgs e)
    {
        var connection = (SQLiteConnection)sender;
        Write(SqliteEventTraceWriterType.ConnectionDisposed, sender, e);
        StopListeningToConnection(connection);
    }

        // ... trimmed - see downloaded code ...

    #endregion
}

Following is a CSV implementation of the SqliteEventTraceBase base class. You can create your own implementation for your specific needs.

public sealed class SqliteCsvEventTrace : SqliteEventTraceBase
{
    public SqliteCsvEventTrace(TextWriter writer,
                               SqliteConnectionTraceType traceTypes =
                                   SqliteConnectionTraceType.Compact) : base(writer, traceTypes)
    {
    }

    protected internal override void Write(SqliteEventTraceWriterType eventType,
                                           object sender,
                                           EventArgs e)
    {
        var id = sender?.GetHashCode().ToString("X") ?? DbContext.DbFile;

        switch (eventType)
        {
            case SqliteEventTraceWriterType.StartListening:
                TextWriter?.WriteLine($"/"{DbContext.DbFile}/",/"Listening/",/"{id}/"");
                break;
            // ... trimmed ...
        }
    }
}

The DbContext ConnectionFactory method will call the SqliteEventTraceBase StartListeningToConnection to wire up the event handlers. When an event occurs, the abstract Write event is called and the implemented class, SqliteCsvEventTrace in this case, formats the output to the TextWriter.

When the connection is disposed of, the SqliteEventTraceBase base class will automatically unhook the event handlers avoiding any memory leaks.

Writer Examples

The TextWriter implementation will simply output the string text from the SqliteEventTraceBase base class implementation. Below are two console TextWriter samples included. You can implement your own – eg: write to a local or remote logging service, to a database, etc…

public sealed class SimpleConsoleTextWriter : TextWriter
{
    public override void WriteLine(string value)
        => Console.WriteLine($"_TRACE: {value}");

    public override void Write(char value)
        => throw new NotImplementedException();

    public override Encoding Encoding
        => Encoding.UTF8;
}

And here is another example:

public sealed class ConsoleTextWriter : TextWriter
{
    public override void WriteLine(string value)
        => Console.WriteLine($"_TRACE {DateTime.Now} : {value}");

    public override void Write(char value)
        => throw new NotImplementedException();

    public override Encoding Encoding
        => Encoding.UTF8;
}

DbRegistry Service Class

The DbRegistry service is simply a class that tracks databases and the tables and views associated with which database. The DbTableContext<T> and DbViewContext<T> base classes require the DbRegistry service to set the DbContext. It also has a handy built-in lookup that associates a POCO with its’ DbResourceContext<T>.

public class DbRegistry
{
    private Dictionary<string, DbContext> DataBases { get; }
        = new Dictionary<string, DbContext>();

    private Dictionary<string, IDbResourceContext> Contexts { get; }
        = new Dictionary<string, IDbResourceContext>();

    #region Register Data Contexts

    public string Register(DbContext db, string key = "",
                           bool autoCreate = true)
    {
        if (key.IsNullOrEmpty()) key = db.GetHashCode().ToString();
        if (!DataBases.ContainsKey(key)) DataBases.Add(key, db);

        if (autoCreate && !db.Exists()) db.CreateDB();

        return key;
    }

    public void Register<T>(DbResourceContext<T> resource, string dbKey,
                            bool autoCreate = true)
    {
        if (!DataBases.ContainsKey(dbKey))
            throw new Exception($"Database not found for key {dbKey}");

        if (Contexts.ContainsKey(resource.Identifier))
            throw new Exception($"Resource {resource.Identifier} is already registered");

        ((IDbResourceContextSet)resource).SetDb(DataBases[dbKey]);
        Contexts.Add(resource.Identifier, resource);

        if (autoCreate) resource.CheckOrCreateTable();
    }

    #endregion

    #region Retrieve Data Context

    public IDbResourceContext this[string key]
        => Contexts[key];

    public IDbResourceContext this[Type type]
        => Resolve(type);

    public IDbResourceContext<T> Resolve<T>()
        => Resolve(typeof(T)) as IDbResourceContext<T>;

    private IDbResourceContext Resolve(Type type)
        => Contexts.Where(x => ((IOwnership)x.Value).IsOfType(type))
                   .Select(x => x.Value)
                   .FirstOrDefault();

    #endregion
}

DbResourceContext<t> Base Class

DbResourceContext<T> base class is the functional core of the DbTableContext<T> and DbViewContext<T> base classes. DbResourceContext<T> requires the DbRegistry service class to set the DbContext.

Earlier there were two examples of how to pull records from the DB into POCOs. To recap:

Retrieve all Records using a CategoriesContext:

// Get all categories
var categories = dbRegistry.Resolve<Category>().Get();

And to retrieve a subset of the data:

// Get the data
var query = "SELECT * FROM Categories WHERE CategoryName Like 'c%'";
var categories = dbRegistry.Resolve<Category>().Get(query);

The CategoriesContext will automatically handle all database operations. The CategoriesContext inherits from the DbTableContext<T> base class. For Views, you would use the DbViewContext<T> base class. The difference between the two DbXxxxContext<T> abstract classes is that the DbViewContext<T> is readonly.

Below is the CategoriesContext class. The CategoriesContext class inherits from the DbTableContext<T> and only implements table specific code. All key methods are wrapped in the base classes.

public class CategoriesContext : DbTableContext<Category>
{
    // trimmed...

    public override Category FromSQLiteDataReader(SQLiteDataReader coreReader)
        => new Category
    {
        Id = coreReader.GetLong("CategoryID", 0L),
        Name = coreReader.GetString("CategoryName"),
        Description = coreReader.GetString("Description"),
        Picture = coreReader.GetBinary("Picture")
    };

    // trimmed...
}

And here is the code that gets executed in the CategoriesContext > DbTableContext<T> > DbResourceContext<T> base class:

public IEnumerable<T> Get(SQLiteConnection conn, SqlQuery query)
    => conn.Get(query, FromSQLiteDataReader);

Which calls the SQLiteConnection Get Extension method:

public static IEnumerable<T> Get<T>(this SQLiteConnection @this, string query,
                                    Func<SQLiteDataReader, T> FromSQLiteDataReader)
    => @this.Get(new SqlQuery(query), FromSQLiteDataReader);

public static IEnumerable<T> Get<T>(this SQLiteConnection @this, SqlQuery query,
                                        Func<SQLiteDataReader, T> FromSQLiteDataReader)
{
    @this.OpenIfRequired();
    using (var cmd = @this.CommandFactory(query))
    using (var coreReader = cmd.ExecuteReader())
        while (coreReader.Read())
            yield return (T)Convert.ChangeType(FromSQLiteDataReader(coreReader), typeof(T));
}

The SQLiteConnection Get Extension method can be used directly without the need for DbResourceContext<T> base class:

var connectionString = "data source=northwind.db;version=3";

List<Category> categories;

using (var dbCtx = new DbContext(connectionString))
using (var conn = dbCtx.ConnectionFactory())
{
    categories = new List<Category>(
        conn.Get("SELECT * FROM Categories",
                 new Func<SQLiteDataReader, Category>((coreReader)
                 => new Category
                 {
                     Id = coreReader.GetLong("CategoryID", 0L),
                     Name = coreReader.GetString("CategoryName"),
                     Description = coreReader.GetString("Description"),
                     Picture = coreReader.GetBinary("Picture")
                 })));
}

We can also directly call the Get Extension method for the DbContext:

var connectionString = "data source=northwind.db;version=3";

List<Category> categories;

using (var dbCtx = new DbContext(connectionString))
    categories = new List<Category>(
        dbCtx.Get("SELECT * FROM Categories",
                  new Func<SQLiteDataReader, Category>((coreReader)
                  => new Category
                  {
                      Id = coreReader.GetLong("CategoryID", 0L),
                      Name = coreReader.GetString("CategoryName"),
                      Description = coreReader.GetString("Description"),
                      Picture = coreReader.GetBinary("Picture")
                  })));

Here is the DbContextCoreExtension Get extension method:

public static IEnumerable<T> Get<T>(this DbContext @this, string query,
                            Func<SQLiteDataReader, T> FromSQLiteDataReader)
    => @this.Get(new SqlQuery(query), FromSQLiteDataReader);

public static IEnumerable<T> Get<T>(this DbContext @this, SqlQuery query,
                                Func<SQLiteDataReader, T> FromSQLiteDataReader)
{
    using (var conn = @this.ConnectionFactory())
        return conn.Get(query, FromSQLiteDataReader);
}

The DbContextCoreExtension Get extension method calls the SQLiteConnection Get Extension method to retrieve the data.

UnitOfWork Class

This is a wrapper for SQLite SQLiteTransaction class’ BeginTransaction, Commit, & Rollback methods, and also holds a shared SQLiteConnection.

public interface IUnitOfWork : IDisposable
{
    SQLiteConnection Connection { get; }
    void SaveChanges();
}
public class UnitOfWork : IUnitOfWork
{
    private SQLiteTransaction trans;

    public SQLiteConnection Connection { get; }

    public UnitOfWork(SQLiteConnection conn)
    {
        Connection = conn;

        Connection.OpenIfRequired();
        trans = Connection.BeginTransaction();
    }

    public void SaveChanges()
    {
        if (trans == null)
            throw new InvalidOperationException("UnitOfWork has already been saved.");

        trans.Commit();
        trans = null;
    }

    ~UnitOfWork() => Dispose();

    public void Dispose()
    {
        if (Connection.IsOpen()) trans?.Rollback();
    }
}

When a SQLiteConnection object is passed to the UnitOfWork constructor, the connection is opened if required and the transaction is started. Calling the SaveChanges method will try to commit all pending changes. If the Commit fails, a normal SQLite exception is thrown and it is up to the developer to manage. When the UnitOfWork class is disposed of, it will check if the Commit was not successful and RollBack if it was not

Included Projects (Sample code)

Working with SQLite in C# &amp; VB

Note: The download link is at the top of this article…

The core libraries, C# only, in the download are:

  1. DotNet.Core.SQLite – This is the library used in this article
  2. DotNet.Core – A number of helper functions and handy extension methods

A number of the code samples, both in C# and VB, in this article have sample projects in the download:

  1. Connections
    • 01_Connecting_manualSQLite only code for opening a connection and executing a query
    • 02_Connecting_hybrid – Demonstrates how to use the ‘DbContext’ class and ConnectionFactory method to wrap existing code base. Handy if tracing is to be used to examine existing code
    • 03_Connecting_fullv1 – Same code as the last example with existing code converted to use SQLiteConnection class DotNet.Core.SQLite library extension methods
    • 04_Connecting_fullv2 – Same code as the last example but using the DbContext class extension methods instead of SQLiteConnection
    • 05_Connecting_fullv3 – A revised version of the above code using a DbTableContext and DbRegistry classes for cleaner and more efficient coding
  2. Tracing
    • 06_Trace_OptimizationExample – Code to retrieve counts from 2 tables with tracing output enabled
    • 07_Trace_OptimizationConn – First attempt of optimizing code with tracing output enabled
    • 08_Trace_OptimizationConnSql – Final example of optimised code with tracing output enabled
  3. Transactions
    • 09_Transactions_manualSQLite only code to add and delete 5,000 records using Transactions
    • 10_Transactions_hybrid – Replacing the Transaction code with the DotNet.Core.SQLite library’s UnitOfWork class for cleaner coding and auto-rollback of failed transactions
    • 11_Transactions_full – Same as the last however using DbTableContext and DbRegistry classes for cleaner and more efficient coding

There is a Benchmarking project, both in C# and VB, included in the download called DotNet.Core.SQLite.Benchmarking that uses the 3rd-party BenchmarkDotNet library:

  1. CountBenchmark – compares: Raw SQLite, Hybrid, DbContext class extension method, SQLiteConnection' class extension method,DbTableContextandDbRegistry` classes
  2. ReadBenchmark – table read comparison: Raw SQLite synchronous, Raw SQLite asynchronous, hybrid synchronous, synchronous DbTableContextand DbRegistry classes, asynchronous DbTableContextand DbRegistry classes
  3. TransactionBenchmark – asynchronous transactional add and delete 5,000 records comparisons: Raw SQLite, optimized Raw SQLite, hybrid optimized using UnitOfWork class, DbTableContextand DbRegistry classes using UnitOfWork class

Summary

SQLite is a great library system for single user applications. The DotNet.Core.SQLite library has been a great wrapper for the projects that I have needed it for greatly reducing the amount of coding required, the tracing function has quickly helped to identify where tweaks are needed to optimize performance, and lastly, the reporting to retrieve profile snapshots to improve support & debugging. I hope that others can benefit from the DotNet.Core.SQLite library as well.

History

v1.0 – July 1, 2018 – Initial Release