Thanks to some amazing contributions by @AlexanderTaeschner, version 2.1 of Microsoft.Data.Sqlite turned into a feature-packed release!
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:
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 can now be created by using the
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!
This feature also pairs nicely with EF Core’s
[DbFunction] attribute. See my post SQLite & EF Core: UDF all the
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.
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.
|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.
Values can now be coerced into alternative types by setting
SqliteParameter.SqliteType. The following alternatives are
|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
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
DefaultTimeoutsets the timeout used by implicilty created commands. (e.g.
BackupDatabase()copies the current database to another one.
GetTimeSpan()were added for completeness.
GetStream()work now. See issue #18 for our plans enhance them.
SqliteException.SqliteExtendedErrorCodegives you the extended result code of an error.
SqliteParameter.Sizecan now be used to truncate