Class SQLiteConnection

Represents an open connection to a SQLite database.

Inheritance
object
SQLiteConnection
Namespace: Unity.VisualScripting.Dependencies.Sqlite
Assembly: solution.dll
Syntax
public class SQLiteConnection : IDisposable

Constructors

SQLiteConnection(string, bool)

Constructs a new SQLiteConnection and opens a SQLite database specified by databasePath.

Declaration
public SQLiteConnection(string databasePath, bool storeDateTimeAsTicks = false)
Parameters
Type Name Description
string databasePath

Specifies the path to the database file.
bool storeDateTimeAsTicks

Specifies whether to store DateTime properties as ticks (true) or strings (false). You absolutely do want to store them as Ticks in all new projects. The default of false is only here for backwards compatibility. There is a significant speed advantage, with no down sides, when setting storeDateTimeAsTicks = true.

SQLiteConnection(string, SQLiteOpenFlags, bool)

Constructs a new SQLiteConnection and opens a SQLite database specified by databasePath.

Declaration
public SQLiteConnection(string databasePath, SQLiteOpenFlags openFlags, bool storeDateTimeAsTicks = false)
Parameters
Type Name Description
string databasePath

Specifies the path to the database file.
SQLiteOpenFlags openFlags
bool storeDateTimeAsTicks

Specifies whether to store DateTime properties as ticks (true) or strings (false). You absolutely do want to store them as Ticks in all new projects. The default of false is only here for backwards compatibility. There is a significant speed advantage, with no down sides, when setting storeDateTimeAsTicks = true.

Properties

BusyTimeout

Sets a busy handler to sleep the specified amount of time when a table is locked. The handler will sleep multiple times until a total time of BusyTimeout has accumulated.

Declaration
public TimeSpan BusyTimeout { get; set; }
Property Value
Type Description
TimeSpan

DatabasePath

Declaration
public string DatabasePath { get; }
Property Value
Type Description
string

Handle

Declaration
public IntPtr Handle { get; }
Property Value
Type Description
IntPtr

IsInTransaction

Whether BeginTransaction() has been called and the database is waiting for a Commit().

Declaration
public bool IsInTransaction { get; }
Property Value
Type Description
bool

StoreDateTimeAsTicks

Declaration
public bool StoreDateTimeAsTicks { get; }
Property Value
Type Description
bool

SyncObject

Gets the synchronous object, to be lock the database file for updating.

Declaration
public object SyncObject { get; }
Property Value
Type Description
object

The sync object.

TableMappings

Returns the mappings from types to tables that the connection currently understands.

Declaration
public IEnumerable<TableMapping> TableMappings { get; }
Property Value
Type Description
IEnumerable<TableMapping>

TimeExecution

Declaration
public bool TimeExecution { get; set; }
Property Value
Type Description
bool

Trace

Declaration
public bool Trace { get; set; }
Property Value
Type Description
bool

Methods

BeginTransaction()

Begins a new transaction. Call Commit() to end the transaction.

Declaration
public void BeginTransaction()
Examples

Throws System.InvalidOperationException if a transaction has already begun.

Close()

Declaration
public void Close()

Commit()

Commits the transaction that was begun by BeginTransaction().

Declaration
public void Commit()

CreateCommand(string, params object[])

Creates a new SQLiteCommand given the command text with arguments. Place a '?' in the command text for each of the arguments.

Declaration
public SQLiteCommand CreateCommand(string cmdText, params object[] ps)
Parameters
Type Name Description
string cmdText

The fully escaped SQL.
object[] ps

Arguments to substitute for the occurences of '?' in the command text.
Returns
Type Description
SQLiteCommand

A SQLiteCommand

CreateIndex(string, string, bool)

Creates an index for the specified table and column.

Declaration
public int CreateIndex(string tableName, string columnName, bool unique = false)
Parameters
Type Name Description
string tableName

Name of the database table
string columnName

Name of the column to index
bool unique

Whether the index should be unique
Returns
Type Description
int

CreateIndex(string, string, string, bool)

Creates an index for the specified table and column.

Declaration
public int CreateIndex(string indexName, string tableName, string columnName, bool unique = false)
Parameters
Type Name Description
string indexName

Name of the index to create
string tableName

Name of the database table
string columnName

Name of the column to index
bool unique

Whether the index should be unique
Returns
Type Description
int

CreateIndex(string, string, string[], bool)

Creates an index for the specified table and columns.

Declaration
public int CreateIndex(string indexName, string tableName, string[] columnNames, bool unique = false)
Parameters
Type Name Description
string indexName

Name of the index to create
string tableName

Name of the database table
string[] columnNames

An array of column names to index
bool unique

Whether the index should be unique
Returns
Type Description
int

CreateIndex(string, string[], bool)

Creates an index for the specified table and columns.

Declaration
public int CreateIndex(string tableName, string[] columnNames, bool unique = false)
Parameters
Type Name Description
string tableName

Name of the database table
string[] columnNames

An array of column names to index
bool unique

Whether the index should be unique
Returns
Type Description
int

CreateIndex<T>(Expression<Func<T, object>>, bool)

Creates an index for the specified object property.

Declaration
public void CreateIndex<T>(Expression<Func<T, object>> property, bool unique = false)
Parameters
Type Name Description
Expression<Func<T, object>> property

Property to index
bool unique

Whether the index should be unique
Type Parameters
Name Description
T

Type to reflect to a database table.
Examples
CreateIndex<Client>(c => c.Name);

CreateTable(Type, CreateFlags)

Executes a "create table if not exists" on the database. It also creates any specified indexes on the columns of the table. It uses a schema automatically generated from the specified type. You can later access this schema by calling GetMapping.

Declaration
public int CreateTable(Type ty, CreateFlags createFlags = default)
Parameters
Type Name Description
Type ty

Type to reflect to a database table.
CreateFlags createFlags

Optional flags allowing implicit PK and indexes based on naming conventions.
Returns
Type Description
int

The number of entries added to the database schema.

CreateTable<T>(CreateFlags)

Executes a "create table if not exists" on the database. It also creates any specified indexes on the columns of the table. It uses a schema automatically generated from the specified type. You can later access this schema by calling GetMapping.

Declaration
public int CreateTable<T>(CreateFlags createFlags = default)
Parameters
Type Name Description
CreateFlags createFlags
Returns
Type Description
int

The number of entries added to the database schema.
Type Parameters
Name Description
T

DeferredQuery(TableMapping, string, params object[])

Creates a SQLiteCommand given the command text (SQL) with arguments. Place a '?' in the command text for each of the arguments and then executes that command. It returns each row of the result using the specified mapping. This function is only used by libraries in order to query the database via introspection. It is normally not used.

Declaration
public IEnumerable<object> DeferredQuery(TableMapping map, string query, params object[] args)
Parameters
Type Name Description
TableMapping map

A TableMapping to use to convert the resulting rows into objects.
string query

The fully escaped SQL.
object[] args

Arguments to substitute for the occurences of '?' in the query.
Returns
Type Description
IEnumerable<object>

An enumerable with one result for each row returned by the query. The enumerator will call sqlite3_step on each call to MoveNext, so the database connection must remain open for the lifetime of the enumerator.

DeferredQuery<T>(string, params object[])

Creates a SQLiteCommand given the command text (SQL) with arguments. Place a '?' in the command text for each of the arguments and then executes that command. It returns each row of the result using the mapping automatically generated for the given type.

Declaration
public IEnumerable<T> DeferredQuery<T>(string query, params object[] args) where T : new()
Parameters
Type Name Description
string query

The fully escaped SQL.
object[] args

Arguments to substitute for the occurences of '?' in the query.
Returns
Type Description
IEnumerable<T>

An enumerable with one result for each row returned by the query. The enumerator will call sqlite3_step on each call to MoveNext, so the database connection must remain open for the lifetime of the enumerator.
Type Parameters
Name Description
T

Delete(object)

Deletes the given object from the database using its primary key.

Declaration
public int Delete(object objectToDelete)
Parameters
Type Name Description
object objectToDelete

The object to delete. It must have a primary key designated using the PrimaryKeyAttribute.
Returns
Type Description
int

The number of rows deleted.

DeleteAll<T>()

Deletes all the objects from the specified table. WARNING WARNING: Let me repeat. It deletes ALL the objects from the specified table. Do you really want to do that?

Declaration
public int DeleteAll<T>()
Returns
Type Description
int

The number of objects deleted.
Type Parameters
Name Description
T

The type of objects to delete.

Delete<T>(object)

Deletes the object with the specified primary key.

Declaration
public int Delete<T>(object primaryKey)
Parameters
Type Name Description
object primaryKey

The primary key of the object to delete.
Returns
Type Description
int

The number of objects deleted.
Type Parameters
Name Description
T

The type of object.

Dispose()

Declaration
public void Dispose()

Dispose(bool)

Declaration
protected virtual void Dispose(bool disposing)
Parameters
Type Name Description
bool disposing

DropTable<T>()

Executes a "drop table" on the database. This is non-recoverable.

Declaration
public int DropTable<T>()
Returns
Type Description
int
Type Parameters
Name Description
T

EnableLoadExtension(int)

Declaration
public void EnableLoadExtension(int onoff)
Parameters
Type Name Description
int onoff

Execute(string, params object[])

Creates a SQLiteCommand given the command text (SQL) with arguments. Place a '?' in the command text for each of the arguments and then executes that command. Use this method instead of Query when you don't expect rows back. Such cases include INSERTs, UPDATEs, and DELETEs. You can set the Trace or TimeExecution properties of the connection to profile execution.

Declaration
public int Execute(string query, params object[] args)
Parameters
Type Name Description
string query

The fully escaped SQL.
object[] args

Arguments to substitute for the occurences of '?' in the query.
Returns
Type Description
int

The number of rows modified in the database as a result of this execution.

ExecuteScalar<T>(string, params object[])

Declaration
public T ExecuteScalar<T>(string query, params object[] args)
Parameters
Type Name Description
string query
object[] args
Returns
Type Description
T
Type Parameters
Name Description
T

~SQLiteConnection()

Declaration
protected ~SQLiteConnection()

Find(object, TableMapping)

Attempts to retrieve an object with the given primary key from the table associated with the specified type. Use of this method requires that the given type have a designated PrimaryKey (using the PrimaryKeyAttribute).

Declaration
public object Find(object pk, TableMapping map)
Parameters
Type Name Description
object pk

The primary key.
TableMapping map

The TableMapping used to identify the object type.
Returns
Type Description
object

The object with the given primary key or null if the object is not found.

Find<T>(Expression<Func<T, bool>>)

Attempts to retrieve the first object that matches the predicate from the table associated with the specified type.

Declaration
public T Find<T>(Expression<Func<T, bool>> predicate) where T : new()
Parameters
Type Name Description
Expression<Func<T, bool>> predicate

A predicate for which object to find.
Returns
Type Description
T

The object that matches the given predicate or null if the object is not found.
Type Parameters
Name Description
T

Find<T>(object)

Attempts to retrieve an object with the given primary key from the table associated with the specified type. Use of this method requires that the given type have a designated PrimaryKey (using the PrimaryKeyAttribute).

Declaration
public T Find<T>(object pk) where T : new()
Parameters
Type Name Description
object pk

The primary key.
Returns
Type Description
T

The object with the given primary key or null if the object is not found.
Type Parameters
Name Description
T

GetMapping(Type, CreateFlags)

Retrieves the mapping that is automatically generated for the given type.

Declaration
public TableMapping GetMapping(Type type, CreateFlags createFlags = default)
Parameters
Type Name Description
Type type

The type whose mapping to the database is returned.
CreateFlags createFlags

Optional flags allowing implicit PK and indexes based on naming conventions
Returns
Type Description
TableMapping

The mapping represents the schema of the columns of the database and contains methods to set and get properties of objects.

GetMapping<T>()

Retrieves the mapping that is automatically generated for the given type.

Declaration
public TableMapping GetMapping<T>()
Returns
Type Description
TableMapping

The mapping represents the schema of the columns of the database and contains methods to set and get properties of objects.
Type Parameters
Name Description
T

GetTableInfo(string)

Declaration
public List<SQLiteConnection.ColumnInfo> GetTableInfo(string tableName)
Parameters
Type Name Description
string tableName
Returns
Type Description
List<ColumnInfo>

Get<T>(Expression<Func<T, bool>>)

Attempts to retrieve the first object that matches the predicate from the table associated with the specified type.

Declaration
public T Get<T>(Expression<Func<T, bool>> predicate) where T : new()
Parameters
Type Name Description
Expression<Func<T, bool>> predicate

A predicate for which object to find.
Returns
Type Description
T

The object that matches the given predicate. Throws a not found exception if the object is not found.
Type Parameters
Name Description
T

Get<T>(object)

Attempts to retrieve an object with the given primary key from the table associated with the specified type. Use of this method requires that the given type have a designated PrimaryKey (using the PrimaryKeyAttribute).

Declaration
public T Get<T>(object pk) where T : new()
Parameters
Type Name Description
object pk

The primary key.
Returns
Type Description
T

The object with the given primary key. Throws a not found exception if the object is not found.
Type Parameters
Name Description
T

Insert(object)

Inserts the given object and retrieves its auto incremented primary key if it has one.

Declaration
public int Insert(object obj)
Parameters
Type Name Description
object obj

The object to insert.
Returns
Type Description
int

The number of rows added to the table.

Insert(object, string)

Inserts the given object and retrieves its auto incremented primary key if it has one.

Declaration
public int Insert(object obj, string extra)
Parameters
Type Name Description
object obj

The object to insert.
string extra

Literal SQL code that gets placed into the command. INSERT INTO ...
Returns
Type Description
int

The number of rows added to the table.

Insert(object, string, Type)

Inserts the given object and retrieves its auto incremented primary key if it has one.

Declaration
public int Insert(object obj, string extra, Type objType)
Parameters
Type Name Description
object obj

The object to insert.
string extra

Literal SQL code that gets placed into the command. INSERT INTO ...
Type objType

The type of object to insert.
Returns
Type Description
int

The number of rows added to the table.

Insert(object, Type)

Inserts the given object and retrieves its auto incremented primary key if it has one.

Declaration
public int Insert(object obj, Type objType)
Parameters
Type Name Description
object obj

The object to insert.
Type objType

The type of object to insert.
Returns
Type Description
int

The number of rows added to the table.

InsertAll(IEnumerable)

Inserts all specified objects.

Declaration
public int InsertAll(Collections.IEnumerable objects)
Parameters
Type Name Description
IEnumerable objects

An IEnumerable<T> of the objects to insert.
Returns
Type Description
int

The number of rows added to the table.

InsertAll(IEnumerable, string)

Inserts all specified objects.

Declaration
public int InsertAll(Collections.IEnumerable objects, string extra)
Parameters
Type Name Description
IEnumerable objects

An IEnumerable<T> of the objects to insert.
string extra

Literal SQL code that gets placed into the command. INSERT INTO ...
Returns
Type Description
int

The number of rows added to the table.

InsertAll(IEnumerable, Type)

Inserts all specified objects.

Declaration
public int InsertAll(Collections.IEnumerable objects, Type objType)
Parameters
Type Name Description
IEnumerable objects

An IEnumerable<T> of the objects to insert.
Type objType

The type of object to insert.
Returns
Type Description
int

The number of rows added to the table.

InsertOrReplace(object)

Inserts the given object and retrieves its auto incremented primary key if it has one. If a UNIQUE constraint violation occurs with some pre-existing object, this function deletes the old object.

Declaration
public int InsertOrReplace(object obj)
Parameters
Type Name Description
object obj

The object to insert.
Returns
Type Description
int

The number of rows modified.

InsertOrReplace(object, Type)

Inserts the given object and retrieves its auto incremented primary key if it has one. If a UNIQUE constraint violation occurs with some pre-existing object, this function deletes the old object.

Declaration
public int InsertOrReplace(object obj, Type objType)
Parameters
Type Name Description
object obj

The object to insert.
Type objType

The type of object to insert.
Returns
Type Description
int

The number of rows modified.

NewCommand()

Creates a new SQLiteCommand. Can be overridden to provide a sub-class.

Declaration
protected virtual SQLiteCommand NewCommand()
Returns
Type Description
SQLiteCommand
See Also

Query(TableMapping, string, params object[])

Creates a SQLiteCommand given the command text (SQL) with arguments. Place a '?' in the command text for each of the arguments and then executes that command. It returns each row of the result using the specified mapping. This function is only used by libraries in order to query the database via introspection. It is normally not used.

Declaration
public List<object> Query(TableMapping map, string query, params object[] args)
Parameters
Type Name Description
TableMapping map

A TableMapping to use to convert the resulting rows into objects.
string query

The fully escaped SQL.
object[] args

Arguments to substitute for the occurences of '?' in the query.
Returns
Type Description
List<object>

An enumerable with one result for each row returned by the query.

Query<T>(string, params object[])

Creates a SQLiteCommand given the command text (SQL) with arguments. Place a '?' in the command text for each of the arguments and then executes that command. It returns each row of the result using the mapping automatically generated for the given type.

Declaration
public List<T> Query<T>(string query, params object[] args) where T : new()
Parameters
Type Name Description
string query

The fully escaped SQL.
object[] args

Arguments to substitute for the occurences of '?' in the query.
Returns
Type Description
List<T>

An enumerable with one result for each row returned by the query.
Type Parameters
Name Description
T

Release(string)

Releases a savepoint returned from SaveTransactionPoint(). Releasing a savepoint makes changes since that savepoint permanent if the savepoint began the transaction, or otherwise the changes are permanent pending a call to Commit().

The RELEASE command is like a COMMIT for a SAVEPOINT.

Declaration
public void Release(string savepoint)
Parameters
Type Name Description
string savepoint

The name of the savepoint to release. The string should be the result of a call to SaveTransactionPoint()

Rollback()

Rolls back the transaction that was begun by BeginTransaction() or SaveTransactionPoint().

Declaration
public void Rollback()

RollbackTo(string)

Rolls back the savepoint created by BeginTransaction() or SaveTransactionPoint.

Declaration
public void RollbackTo(string savepoint)
Parameters
Type Name Description
string savepoint

The name of the savepoint to roll back to, as returned by SaveTransactionPoint(). If savepoint is null or empty, this method is equivalent to a call to Rollback()

RunInDatabaseLock(Action)

Executes action while blocking other threads to access the same database.

Declaration
public void RunInDatabaseLock(Action action)
Parameters
Type Name Description
Action action

The Action to perform within a lock.

RunInTransaction(Action)

Executes action within a (possibly nested) transaction by wrapping it in a SAVEPOINT. If an exception occurs the whole transaction is rolled back, not just the current savepoint. The exception is rethrown.

Declaration
public void RunInTransaction(Action action)
Parameters
Type Name Description
Action action

The Action to perform within a transaction. action can contain any number of operations on the connection but should never call BeginTransaction() or Commit().

SaveTransactionPoint()

Creates a savepoint in the database at the current point in the transaction timeline. Begins a new transaction if one is not in progress.

Call RollbackTo(string) to undo transactions since the returned savepoint. Call Release(string) to commit transactions after the savepoint returned here. Call Commit() to end the transaction, committing all changes.

Declaration
public string SaveTransactionPoint()
Returns
Type Description
string

A string naming the savepoint.

Table<T>()

Returns a queryable interface to the table represented by the given type.

Declaration
public TableQuery<T> Table<T>() where T : new()
Returns
Type Description
TableQuery<T>

A queryable object that is able to translate Where, OrderBy, and Take queries into native SQL.
Type Parameters
Name Description
T

Update(object)

Updates all of the columns of a table using the specified object except for its primary key. The object is required to have a primary key.

Declaration
public int Update(object obj)
Parameters
Type Name Description
object obj

The object to update. It must have a primary key designated using the PrimaryKeyAttribute.
Returns
Type Description
int

The number of rows updated.

Update(object, Type)

Updates all of the columns of a table using the specified object except for its primary key. The object is required to have a primary key.

Declaration
public int Update(object obj, Type objType)
Parameters
Type Name Description
object obj

The object to update. It must have a primary key designated using the PrimaryKeyAttribute.
Type objType

The type of object to insert.
Returns
Type Description
int

The number of rows updated.

UpdateAll(IEnumerable)

Updates all specified objects.

Declaration
public int UpdateAll(Collections.IEnumerable objects)
Parameters
Type Name Description
IEnumerable objects

An IEnumerable<T> of the objects to insert.
Returns
Type Description
int

The number of rows modified.

Events

TraceEvent

Declaration
public event SQLiteConnection.TraceHandler TraceEvent
Event Type
Type Description
SQLiteConnection.TraceHandler

Extension Methods