SQLite Database

Docs under migration

These docs are under active migration and updates. The original docs will remain available at docs.ghielectronics.com for future reference.

According to the SQLite homepage, "SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world." SQLite lets you set up a database that resides entirely in a single file on a persistent storage device.

tip

Needed NuGets: GHIElectronics.TinyCLR.Data.SQLite

The code below is a simple example that creates a database file in RAM (SD cards and USB drives can be used as well). A table is created, a few rows are filled, and then this data is read from the database. This data is then iterated over and printed out. ColumnNames returns the name of each of the columns.

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

using (var db = new SQLiteDatabase()) {

    Debug.WriteLine("Executing 1...");
    db.ExecuteNonQuery("CREATE TABLE Test (Var1 TEXT, Var2 INTEGER, Var3 DOUBLE);");

    Debug.WriteLine("Executing 2...");
    db.ExecuteNonQuery("INSERT INTO Test(Var1, Var2, Var3) VALUES ('Hello, World!', 25, 3.14);");

    Debug.WriteLine("Executing 3...");
    db.ExecuteNonQuery("INSERT INTO Test(Var1, Var2, Var3) VALUES('Goodbye, World!', 15, 6.28); ");

    Debug.WriteLine("Executing 4...");
    db.ExecuteNonQuery("INSERT INTO Test (Var1) VALUES('Red'),('Blue'),('Green'),('White');");

    Debug.WriteLine("Executing 5...");
    var result1 = db.ExecuteQuery("SELECT Var1 FROM Test;");

    Debug.WriteLine("Executing 6...");
    var result2 = db.ExecuteQuery("SELECT Var1, Var2, Var3 FROM Test WHERE Var2 > 10;");

    Debug.WriteLine("Executing 7...");
    var result3 = db.ExecuteQuery("SELECT Var1, Var2, Var3 FROM Test WHERE Var2 BETWEEN 24 AND 26");

    Debug.WriteLine("Executing 7...");
    Debug.WriteLine(result2.ColumnCount.ToString() + " " +
        result2.RowCount.ToString());

    var str = "";

    //foreach (var j in result1.ColumnNames)
    //    str += j + " ";

    //Debug.WriteLine(str);

    foreach (ArrayList i in result1.Data) {
        str = "";

        foreach (object j in i)
            str += j.ToString() + " ";

        Debug.WriteLine(str);
                
    }

    foreach (ArrayList i in result2.Data) {
        str = "";

        foreach (object j in i)
            str += j.ToString() + " ";

        Debug.WriteLine(str);
    }

    foreach (ArrayList i in result3.Data) {
        str = "";

        foreach (object j in i)
            str += j.ToString() + " ";

        Debug.WriteLine(str);
    }
}

Omitted commands & features

In order to reduce the memory requirements, some commands and features of SQLite have been omitted. Below is a table of those omitted features. For more information regarding these omissions see the SQLite Website.

Omitted Command or Feature
ALTER TABLE	Executing the statement causes a parse error
ANALYZE	Command omitted from the build
ATTACH	ATTACH and DETACH commands are omitted
REINDEX	Executing the statement causes a parse error
AUTOMATIC INDEX	Feature omitted from the build
AUTHORIZATION	Authorization callback feature omitted
AUTOINCREMENT	Feature omitted from the build
AUTOVACCUM	Feature omitted from the build
BLOB LITERAL	Not possible to specify a blob in an SQL statement using X'ABCD' syntax
CTE	Common Table Expressions omitted from the build
DATETIME FUNCS	SQL functions julianday(), date(), time(), datetime() and strftime() are not available
DEPRECATED	Omitted support for interfaces marked deprecated by SQLite
EXPLAIN	Executing the statement causes a parse error
FLAG PRAGMAS	PRAGMA commands that query and set boolean properties omitted
FOREIGN KEY	Foreign key constraint syntax is not recognized
HEX INTEGER	Support for Hexadecimal integer literals omitted
INCRBLOB	Support for incremental BLOB I/O omitted
INTEGRITY CHECK	Support for integrity check pragma omitted
LIKE OPTIMIZATION	Feature to help resolve LIKE and GLOB operators in a WHERE clause omitted
LOAD EXTENSION	Extension loading mechanism omitted
LOCALTIME	"localtime" modifier from the date and time functions omitted
LOOKASIDE	Lookaside memory allocator omitted
OR OPTIMIZATION	Index together with terms of a WHERE clause connected by the OR operator, disabled.
PAGER PRAGMAS	Pragmas related to the pager subsystem omitted from the build
PRAGMA	P ragma command has been omitted
PROGRESS CALLBACK	"progress" callbacks capability during long-running SQL statements omitted.
QUICKBALANCE	Omitted an alternative, faster B-Tree balancing routine.
SCHEMA PRAGMAS	Pragmas for querying the database schema from the build omitted
SCHEMA VERSION PRAGMAS	Pragmas for querying & modifying the database schema and user versions omitted
SHARED CACHE	Support for shared-cache mode has been omitted
SUBQUERY	Support for sub-selects and the IN() operator are omitted.
TCL VARIABLE	"$" syntax used to automatically bind SQL variables to TCL variables is omitted.
TEMPDB	Support for TEMP or TEMPORARY tables omitted
TRACE	Sqlite3_profile() and sqlite3_trace() interfaces and their associated logic omitted
TRIGGER	TRIGGER objects omitted. CREATE TRIGGER or DROP TRIGGER commands are unavailable
TRUNCATE OPTIMIZATION	Removing this feature only affects the speed of operation
UTF16	Support for UTF16 text encoding has been omitted.
VIRTUALTABLE	Virtual Table mechanism in SQLite omitted.
XFER OPT	Removed optimization that help INSERT INTO...SELECT...run faster
WAL	Write-ahead log capability omitted.

More Info

Further details on SQLite can be found at the official SQLite website http://www.sqlite.org/