SQLite connections, commands and transactions

Own the database lifetime, apply migrations first and use parameterized commands for entered values.

Read the task-based guide before using an isolated signature. Arguments are positional unless a signature explicitly marks an optional argument.

Sqlite#

Open a writable or read-only connection; a MigrationSet does not execute until applied.

BASIC-facing contract

Async Function OpenAsync(path As String) As Database
Async Function OpenReadOnlyAsync(path As String) As Database
Async Sub CloseAsync(database As Database)
Function CreateMigrationSet() As MigrationSet

Database#

Use the opened instance. A backup is a separate destination, not a SQL dump pasted into a label.

BASIC-facing contract

IsReadOnly, IsOpen As Boolean (read only)
Function CreateCommand(sql As String) As DbCommand
Async Function BeginTransactionAsync() As DbTransaction
Async Function GetSchemaAsync() As DbSchema
Async Function ApplyMigrationsAsync(migrations As MigrationSet) As Long
Async Sub BackupAsync(destinationPath As String)
Async Sub CloseAsync()
Sub Dispose()

DbCommand parameters#

Keep values separate from SQL structure. AddTypedParameter requires the intrinsic DbType enum.

BASIC-facing contract

CommandTimeout As Long
Sub AddParameter(name As String, value As Variant)
Sub AddTypedParameter(name As String, value As Variant, type As DbType)

DbCommand execution#

Page indexes begin at zero. Choose a stable ordering with a unique tie-breaker.

BASIC-facing contract

Async Function ExecuteAsync() As LongLong
Async Function ScalarAsync() As Variant
Async Function QueryAsync() As Recordset
Async Function QueryPageAsync(orderBy As String, pageIndex As Long, pageSize As Long) As Recordset
Sub Cancel()

DbTransaction#

Create commands on the transaction when they must belong to it. Dispose releases an unfinished transaction.

BASIC-facing contract

Function CreateCommand(sql As String) As DbCommand
Async Sub CommitAsync()
Async Sub RollbackAsync()
Sub Dispose()

MigrationSet#

Keep migration IDs ordered and previously applied migrations unchanged.

BASIC-facing contract

Sub Add(id As Long, sql As String, Optional checksum As String)

DbSchema#

TableName uses a zero-based index. This is the BASIC-exposed schema surface, not every CLR implementation helper.

BASIC-facing contract

TableCount As Long (read only)
Function TableName(index As Long) As String
Function HasTable(name As String) As Boolean

Source baseline 20f4bfed6b · Build manifest