Thanks to some amazing contributions by @AlexanderTaeschner, version 2.1 of Microsoft.Data.Sqlite turned into a feature-packed release!

SQLitePCL.raw 1.1.11

We’ve updated our dependency on SQLitePCL.raw to version 1.1.11. Some of the great features added by @ericksink since the previous version we depended on (1.1.7) are:

  • SQLite was updated from 3.18.2 to version 3.22.0
  • The FTS5 extension was enabled
  • Additional runtimes were supported:
    • linux-arm
    • linux-arm64
    • linux-armel
    • linux-musl-x64
    • win8-arm

Prepared statements

We enabled SqliteCommand.Prepare(), but you don’t actually have to call it. Once a command is executed, the compilation of its SQL statements gets reused by subsequent executions. This can result in large performence improvements. See my post about bulk inserts for an example that can take advantage of this feature.

User-defined functions

User-defined functions can now be created by using the SqliteConnection.CreateFunction() and CreateAggregate() overloads. For example, you can create a scalar function to calculate the volume of a cylinder.

connection.CreateFunction(
    "volume",
    (double radius, double height)
        => Math.PI * Math.Pow(radius, 2) * height);

And use the function in SQL to find the biggest cylinder.

SELECT id, volume(radius, height) AS volume
FROM cylinder
ORDER BY volume DESC
LIMIT 1

SQLite will evaluate the function by invoke the .NET delegate. You can even set a breakpoint to debug it!

For more examples, see the aggregate function and regular expression samples.

This feature also pairs nicely with EF Core’s [DbFunction] attribute. See my post SQLite & EF Core: UDF all the things!

Custom collations

Collating sequences are used to compare strings. SQLite has a built-in NOCASE collation you can use to perform case-insensitive comparisons.

SELECT 'Λ' = 'λ' COLLATE NOCASE;

Unfortunately, it only works with the ASCII characters A through Z. With the CreateCollation() method on SqliteConnection, you can now define your own (or redefine existing ones).

connection.CreateCollation(
    "NOCASE",
    (x, y) => string.Compare(x, y, ignoreCase: true));

This feature was actually added in version 2.0, but thought it deserved to be called out again here.

Result metadata

SqliteDataReader.GetSchemaTable() can now be used to retrieve metadata about the columns in a result including the source of the data. The API returns a table with the following columns.

Column Type Description
AllowDBNull bool If the column can be NULL
BaseCatalogName string The database name
BaseColumnName string The name of the column in the table
BaseTableName string The table name
ColumnName string The name of the column in the result
ColumnOrdinal int The rank of the column within the result
DataType Type The CLR type of the column
DataTypeName string The SQL type of the column
IsAliased bool If the column is aliased
IsAutoIncrement bool If the column is auto-increment
IsExpression bool If the column is an expression
IsKey bool If the column is part of the primary key
IsUnique bool If the column is unique

See the result metadata sample for an example of using this API.

Value coercion

Values can now be coerced into alternative types by setting SqliteParameter.SqliteType. The following alternatives are allowed.

.NET SQL Description
Char TEXT A one-character string
DateTime REAL The Julian Day value
Guid TEXT The string representation
TimeSpan REAL The total days

The values are also transparently coerced back to the original type when calling the corresponding method on SqliteDataReader.

See the date and time sample for an example of some functionality this enables.

The little things

There are also a handful of other APIs added or enabled in this release:

  • DbProviderFactories.GetFactory(DbConnection) now works when passed a SqliteConnection object.
  • SqliteConnection
    • DefaultTimeout sets the timeout used by implicilty created commands. (e.g. BeginTransaction())
    • BackupDatabase() copies the current database to another one.
  • SqliteDataReader
    • GetDateTimeOffset() and GetTimeSpan() were added for completeness.
    • GetBytes(), GetChars(), and GetStream() work now. See issue #18 for our plans enhance them.
  • SqliteException.SqliteExtendedErrorCode gives you the extended result code of an error.
  • SqliteParameter.Size can now be used to truncate string and byte[] values.