Class SQLiteConnection
Represents an open connection to a SQLite database.
Namespace: Unity.VisualScripting.Dependencies.Sqlite
Syntax
public class SQLiteConnection : IDisposable
Constructors
SQLiteConnection(String, Boolean)
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. |
Boolean | 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, Boolean)
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 | |
Boolean | 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 |
---|---|
Boolean |
StoreDateTimeAsTicks
Declaration
public bool StoreDateTimeAsTicks { get; }
Property Value
Type | Description |
---|---|
Boolean |
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 |
---|---|
Boolean |
Trace
Declaration
public bool Trace { get; set; }
Property Value
Type | Description |
---|---|
Boolean |
Methods
BeginTransaction()
Begins a new transaction. Call Commit() to end the transaction.
Declaration
public void BeginTransaction()
Examples
Throws 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, 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 |
Returns
Type | Description |
---|---|
SQLiteCommand |
CreateIndex(String, String, Boolean)
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 |
Boolean | unique | Whether the index should be unique |
Returns
Type | Description |
---|---|
Int32 |
CreateIndex(String, String, String, Boolean)
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 |
Boolean | unique | Whether the index should be unique |
Returns
Type | Description |
---|---|
Int32 |
CreateIndex(String, String, String[], Boolean)
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 |
Boolean | unique | Whether the index should be unique |
Returns
Type | Description |
---|---|
Int32 |
CreateIndex(String, String[], Boolean)
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 |
Boolean | unique | Whether the index should be unique |
Returns
Type | Description |
---|---|
Int32 |
CreateIndex<T>(Expression<Func<T, Object>>, Boolean)
Declaration
public void CreateIndex<T>(Expression<Func<T, object>> property, bool unique = false)
Parameters
Type | Name | Description |
---|---|---|
Expression<Func<T, Object>> | property | |
Boolean | unique |
Type Parameters
Name | Description |
---|---|
T |
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 = CreateFlags.None)
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 |
---|---|
Int32 | 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 = CreateFlags.None)
Parameters
Type | Name | Description |
---|---|---|
CreateFlags | createFlags |
Returns
Type | Description |
---|---|
Int32 | The number of entries added to the database schema. |
Type Parameters
Name | Description |
---|---|
T |
DeferredQuery(TableMapping, String, 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, 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 |
---|---|
Int32 | The number of rows deleted. |
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 |
---|---|
Int32 | The number of objects deleted. |
Type Parameters
Name | Description |
---|---|
T | The type of object. |
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 |
---|---|
Int32 | The number of objects deleted. |
Type Parameters
Name | Description |
---|---|
T | The type of objects to delete. |
Dispose()
Declaration
public void Dispose()
Dispose(Boolean)
Declaration
protected virtual void Dispose(bool disposing)
Parameters
Type | Name | Description |
---|---|---|
Boolean | disposing |
DropTable<T>()
Executes a "drop table" on the database. This is non-recoverable.
Declaration
public int DropTable<T>()
Returns
Type | Description |
---|---|
Int32 |
Type Parameters
Name | Description |
---|---|
T |
EnableLoadExtension(Int32)
Declaration
public void EnableLoadExtension(int onoff)
Parameters
Type | Name | Description |
---|---|---|
Int32 | onoff |
Execute(String, 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 |
---|---|
Int32 | The number of rows modified in the database as a result of this execution. |
ExecuteScalar<T>(String, 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 |
Finalize()
Declaration
protected void Finalize()
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, Boolean>>)
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, Boolean>> | 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 |
Get<T>(Expression<Func<T, Boolean>>)
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, Boolean>> | 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 |
GetMapping(Type, CreateFlags)
Retrieves the mapping that is automatically generated for the given type.
Declaration
public TableMapping GetMapping(Type type, CreateFlags createFlags = CreateFlags.None)
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<SQLiteConnection.ColumnInfo> |
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 |
---|---|
Int32 | 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 {extra} INTO ... |
Returns
Type | Description |
---|---|
Int32 | 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 {extra} INTO ... |
Type | objType | The type of object to insert. |
Returns
Type | Description |
---|---|
Int32 | 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 |
---|---|
Int32 | The number of rows added to the table. |
InsertAll(IEnumerable)
Inserts all specified objects.
Declaration
public int InsertAll(IEnumerable objects)
Parameters
Type | Name | Description |
---|---|---|
IEnumerable | objects | An IEnumerable<T> of the objects to insert. |
Returns
Type | Description |
---|---|
Int32 | The number of rows added to the table. |
InsertAll(IEnumerable, String)
Inserts all specified objects.
Declaration
public int InsertAll(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 {extra} INTO ... |
Returns
Type | Description |
---|---|
Int32 | The number of rows added to the table. |
InsertAll(IEnumerable, Type)
Inserts all specified objects.
Declaration
public int InsertAll(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 |
---|---|
Int32 | 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 |
---|---|
Int32 | 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 |
---|---|
Int32 | 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, 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, 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)
Declaration
public void RunInDatabaseLock(Action action)
Parameters
Type | Name | Description |
---|---|---|
Action | action |
RunInTransaction(Action)
Declaration
public void RunInTransaction(Action action)
Parameters
Type | Name | Description |
---|---|---|
Action | action |
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 |
---|---|
Int32 | 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 |
---|---|
Int32 | The number of rows updated. |
UpdateAll(IEnumerable)
Updates all specified objects.
Declaration
public int UpdateAll(IEnumerable objects)
Parameters
Type | Name | Description |
---|---|---|
IEnumerable | objects | An IEnumerable<T> of the objects to insert. |
Returns
Type | Description |
---|---|
Int32 | The number of rows modified. |
Events
TraceEvent
Declaration
public event SQLiteConnection.TraceHandler TraceEvent
Event Type
Type | Description |
---|---|
SQLiteConnection.TraceHandler |