Working with SQLite in C# & VB

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[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

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

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

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

// 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)

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

' Get all categories 
Dim categories = dbRegistry.Resolve(Of Category)().Get()

// 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)

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

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

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

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

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

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

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

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

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

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))

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

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

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

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

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

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

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

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

using (var dbCtx = new DbContext(connectionString))
{
    dbCtx.Reindex("Categories");
}

Using dbCtx = New DbContext(connectionString)
    dbCtx.Reindex("Categories")
End Using

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

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

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

Using dbCtx = New DbContext(connectionString)
    dbCtx.ManualReindex(SQLiteResourceType.Table, "Categories")
End Using

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

using (var dbCtx = new DbContext(connectionString))
{
    // Get the Application Identification 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 Identification 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

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

    // Set the User version
    dbCtx.UserVersion(5678);
}

Using dbCtx = New DbContext(connectionString)

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

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

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

Using dbCtx = New DbContext(connectionString)
    Console.WriteLine(dbCtx.Report())
End Using

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

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

[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

[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

[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