SQLite Database

SQLite is a self-contained, serverless, single-file SQL database engine — the most widely deployed database engine in the world. TinyCLR includes a port of SQLite that's well-suited to embedded use cases where you need structured queries: data loggers, configuration stores, recipe tables, lookup data for sensor calibration, and similar.

The database can live entirely in RAM (fast, lost on power-off) or on persistent storage like an SD card or USB drive — same code either way; only the connection string changes.

NuGet package: GHIElectronics.TinyCLR.Data.SQLite.

note

TinyCLR's SQLite is a trimmed subset of the full engine. Several commands and features are omitted to reduce memory footprint — see Omitted features for the full list. Most standard SQL works; just don't expect ALTER TABLE, TRIGGER, FOREIGN KEY, subqueries, etc.

Create, insert, query

The example below creates an in-memory database, builds a table, inserts a few rows, runs three queries, and prints the results.

using System.Collections;
using System.Diagnostics;
using GHIElectronics.TinyCLR.Data.SQLite;

using (var db = new SQLiteDatabase()){
    db.ExecuteNonQuery("CREATE TABLE Test (Var1 TEXT, Var2 INTEGER, Var3 DOUBLE);");

    db.ExecuteNonQuery("INSERT INTO Test (Var1, Var2, Var3) VALUES ('Hello, World!', 25, 3.14);");
    db.ExecuteNonQuery("INSERT INTO Test (Var1, Var2, Var3) VALUES ('Goodbye, World!', 15, 6.28);");
    db.ExecuteNonQuery("INSERT INTO Test (Var1) VALUES ('Red'), ('Blue'), ('Green'), ('White');");

    // Select every Var1 value.
    PrintResult(db.ExecuteQuery("SELECT Var1 FROM Test;"));

    // Filter with WHERE.
    PrintResult(db.ExecuteQuery("SELECT Var1, Var2, Var3 FROM Test WHERE Var2 > 10;"));

    // Range filter with BETWEEN.
    PrintResult(db.ExecuteQuery("SELECT Var1, Var2, Var3 FROM Test WHERE Var2 BETWEEN 24 AND 26;"));
}

void PrintResult(ResultSet result){
    Debug.WriteLine(result.RowCount + " row(s), " + result.ColumnCount + " column(s)");

    foreach (ArrayList row in result.Data){
        var line = "";
        foreach (object value in row)
            line += value.ToString() + " ";

        Debug.WriteLine(line);
    }
}

ResultSet.ColumnNames gives you the names of each column if you want to print a header row, and ResultSet.Data is a list of ArrayList rows where each ArrayList holds the column values in the same order as ColumnNames.

Omitted features

To keep the binary size manageable, TinyCLR's SQLite drops a number of commands and features. See the SQLite compile-time options for what each option does in the upstream engine.

Omitted command or feature	Behavior
ALTER TABLE	Executing causes a parse error
ANALYZE	Omitted from the build
ATTACH / DETACH	ATTACH and DETACH commands are omitted
REINDEX	Executing causes a parse error
AUTOMATIC INDEX	Feature omitted from the build
AUTHORIZATION	Authorization callback feature omitted
AUTOINCREMENT	Feature omitted from the build
AUTOVACUUM	Feature omitted from the build
BLOB LITERAL	Not possible to specify a blob in SQL using X'ABCD' syntax
CTE	Common Table Expressions omitted
DATETIME FUNCS	julianday(), date(), time(), datetime(), strftime() not available
DEPRECATED	Support for interfaces marked deprecated by SQLite is omitted
EXPLAIN	Executing causes a parse error
FLAG PRAGMAS	PRAGMA commands that query/set boolean properties omitted
FOREIGN KEY	Foreign-key constraint syntax not recognized
HEX INTEGER	Hexadecimal integer literals omitted
INCRBLOB	Incremental BLOB I/O omitted
INTEGRITY CHECK	Integrity-check pragma omitted
LIKE OPTIMIZATION	Optimization for LIKE and GLOB in WHERE clauses omitted
LOAD EXTENSION	Extension loading mechanism omitted
LOCALTIME	localtime modifier from date/time functions omitted
LOOKASIDE	Lookaside memory allocator omitted
OR OPTIMIZATION	Index optimization for terms connected by OR disabled
PAGER PRAGMAS	Pragmas related to the pager subsystem omitted
PRAGMA	PRAGMA command has been omitted
PROGRESS CALLBACK	Progress callbacks during long-running SQL statements omitted
QUICKBALANCE	Alternative faster B-Tree balancing routine omitted
SCHEMA PRAGMAS	Pragmas for querying the schema omitted
SCHEMA VERSION PRAGMAS	Pragmas for querying/modifying schema and user versions omitted
SHARED CACHE	Shared-cache mode omitted
SUBQUERY	Sub-selects and IN() operator omitted
TCL VARIABLE	$-prefix binding for TCL variables omitted
TEMPDB	TEMP / TEMPORARY tables omitted
TRACE	sqlite3_profile() / sqlite3_trace() interfaces omitted
TRIGGER	CREATE TRIGGER / DROP TRIGGER unavailable
TRUNCATE OPTIMIZATION	Speed optimization removed (functionally equivalent)
UTF16	UTF-16 text encoding omitted (UTF-8 only)
VIRTUALTABLE	Virtual Table mechanism omitted
XFER OPT	Optimization for INSERT INTO ... SELECT ... removed
WAL	Write-ahead log capability omitted

More information

The official SQLite documentation at sqlite.org covers the SQL syntax, data types, and engine behavior — all of which apply to TinyCLR's port except where noted above.

API reference

Namespace	Description
GHIElectronics.TinyCLR.Data.SQLite	SQLite database engine classes