Class sqlite

A SQLite database connection. Creates/opens/closes database file or in-memory database. Executes SQL, etc.

public class sqlite : IDisposable

Remarks

This class wraps a SQLite API object sqlite3* and related sqlite3_x functions. They are documented in the SQLite website. Uses this unmanaged dll: folders.ThisApp + @"64\sqlite3.dll". In 32-bit process - 32 instead of 64.

To correctly close the database file, at first need to dispose all child objects, such as sqliteStatement, then dispose the sqlite object. To dispose a static sqlite variable, you may want to use process.thisProcessExit event. Although this class has a finalizer that disposes the object (closes database), you should always dispose explicitly. Finalizers don't run on process exit.

Examples

//open database file
using var db = new sqlite(@"C:\test\sqlite.db");
//create table
db.Execute("CREATE TABLE IF NOT EXISTS test(id INTEGER PRIMARY KEY, name TEXT, x INT, guid BLOB, array BLOB)");

//add 2 rows of data
using(var trans = db.Transaction()) { //optional, but makes much faster when making multiple changes, and ensures that all or none of these changes are written to the database
	using(var p = db.Statement("INSERT OR REPLACE INTO test VALUES(?, ?, :x, ?, ?)")) {
		//assume we want to add values of these variables to the database table
		int id = 1; string name = "TEXT"; long x = -10; Guid guid = Guid.NewGuid(); int[] arr = new int[] { 1, 2, 3 };
		//add first row
		p.Bind(1, id);
		p.Bind(2, name).BindStruct(4, guid).Bind(5, arr);
		p.Bind(":x", x);
		p.Step();
		//add second row
		p.Reset().Bind(1, 2).Bind(":x", 123456789012345).Step(); //unbound columns are null
	}
	//update single row
	db.Execute("UPDATE test SET name=?2 WHERE id=?1", 2, "two");
	//write all this to database
	trans.Commit();
}

//get data
using(var p = db.Statement("SELECT * FROM test")) {
	while(p.Step()) { //for each row of results
		print.it(p.GetInt(0), p.GetText(1), p.GetLong(2));
		print.it(p.GetStruct<Guid>("guid"));
		print.it(p.GetArray<int>("array"));
		print.it("----");
	}
}
//get single value
if(db.Get(out string s1, "SELECT name FROM test WHERE id=?", 1)) print.it(s1); else print.it("not found");
if(db.Get(out int i1, "PRAGMA page_size")) print.it(i1);

---

Namespace: Au

Assembly: Au.dll

Inheritance

object

sqlite

Constructors

Name	Description
sqlite(string, SLFlags, string)	Opens or creates a database file.

Properties

Name	Description
Changes	Calls sqlite3_changes.
Handle	sqlite3*
IsInTransaction	Calls sqlite3_get_autocommit.
LastInsertRowid	Calls sqlite3_last_insert_rowid.

Methods

Name	Description
Any(string, params object[])	Executes single SQL statement and returns true if it returns at least one row of data.
Dispose()	Calls sqlite3_close_v2. If fails, prints a warning.
Dispose(bool)
Execute(string)	Calls sqlite3_exec to execute one or more SQL statements that don't return data.
Execute(string, Action<sqliteStatement>)	Executes single SQL statement that does not return data. To bind values calls callback function.
Execute(string, params object[])	Executes single SQL statement that does not return data. Binds values.
Get(out bool, string, params object[])	Executes single SQL statement and gets single value.
Get(out double, string, params object[])	Executes single SQL statement and gets single value.
Get(out int, string, params object[])	Executes single SQL statement and gets single value.
Get(out long, string, params object[])	Executes single SQL statement and gets single value.
Get(out string, string, params object[])	Executes single SQL statement and gets single value.
GetStruct<T>(out T, string, params object[])	Executes single SQL statement and gets single value.
Get<T>(out List<T>, string, params object[])	Executes single SQL statement and gets single value.
Get<T>(out T[], string, params object[])	Executes single SQL statement and gets single value.
Statement(string)	Returns new sqliteStatement(this, sql).
Statement(string, params object[])	Returns new sqliteStatement(this, sql).BindAll(bind).
TableExists(string)	Returns true if the table exists.
Transaction(string, string)	Returns new SLTransaction(this, sql, sqlOfDispose). See SLTransaction.SLTransaction.

Operators

Name	Description
implicit operator nint(sqlite)	sqlite3*

See Also

sqliteStatement