SQL - Extended
1.0.0
Pi-Con: Small utilities makes the difference
|
This is a normal connection except it contains a Lock method that can be used to serialize access to the database in lieu of using the sqlite's FullMutex support. More...
Public Member Functions | |
SQLiteConnectionWithLock (SQLiteConnectionString connectionString) | |
Initializes a new instance of the T:SQLite.SQLiteConnectionWithLock class. More... | |
IDisposable | Lock () |
Lock the database to serialize access to it. To unlock it, call Dispose on the returned object. More... | |
Public Member Functions inherited from SQLite.SQLiteConnection | |
SQLiteConnection (string databasePath, bool storeDateTimeAsTicks=true) | |
Constructs a new SQLiteConnection and opens a SQLite database specified by databasePath. More... | |
SQLiteConnection (string databasePath, SQLiteOpenFlags openFlags, bool storeDateTimeAsTicks=true) | |
Constructs a new SQLiteConnection and opens a SQLite database specified by databasePath. More... | |
SQLiteConnection (SQLiteConnectionString connectionString) | |
Constructs a new SQLiteConnection and opens a SQLite database specified by databasePath. More... | |
void | EnableWriteAheadLogging () |
Enables the write ahead logging. WAL is significantly faster in most scenarios by providing better concurrency and better disk IO performance than the normal jounral mode. You only need to call this function once in the lifetime of the database. More... | |
void | EnableLoadExtension (bool enabled) |
Enable or disable extension loading. More... | |
void | ResetAllValues () |
Reset all the values of the columns rows Added for the foreignKey / multiple records editing More... | |
TableMapping | GetMapping (Type type, CreateFlags createFlags=CreateFlags.None, object obj=null, SQLiteConnection conn=null) |
Retrieves the mapping that is automatically generated for the given type. Modified by Pi-Con More... | |
TableMapping | GetMapping< T > (CreateFlags createFlags=CreateFlags.None) |
Retrieves the mapping that is automatically generated for the given type. More... | |
int | DropTable< T > () |
Executes a "drop table" on the database. This is non-recoverable. More... | |
int | DropTable (TableMapping map) |
Executes a "drop table" on the database. This is non-recoverable. More... | |
CreateTableResult | CreateTable< T > (CreateFlags createFlags=CreateFlags.None) |
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. More... | |
CreateTableResult | CreateTable (Type ty, CreateFlags createFlags=CreateFlags.None) |
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. More... | |
CreateTablesResult | CreateTables< T, T2 > (CreateFlags createFlags=CreateFlags.None) |
Executes a "create table if not exists" on the database for each type. 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. More... | |
CreateTablesResult | CreateTables< T, T2, T3 > (CreateFlags createFlags=CreateFlags.None) |
Executes a "create table if not exists" on the database for each type. 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. More... | |
CreateTablesResult | CreateTables< T, T2, T3, T4 > (CreateFlags createFlags=CreateFlags.None) |
Executes a "create table if not exists" on the database for each type. 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. More... | |
CreateTablesResult | CreateTables< T, T2, T3, T4, T5 > (CreateFlags createFlags=CreateFlags.None) |
Executes a "create table if not exists" on the database for each type. 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. More... | |
CreateTablesResult | CreateTables (CreateFlags createFlags=CreateFlags.None, params Type[] types) |
Executes a "create table if not exists" on the database for each type. 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. More... | |
int | CreateIndex (string indexName, string tableName, string[] columnNames, bool unique=false) |
Creates an index for the specified table and columns. More... | |
int | CreateIndex (string indexName, string tableName, string columnName, bool unique=false) |
Creates an index for the specified table and column. More... | |
int | CreateIndex (string tableName, string columnName, bool unique=false) |
Creates an index for the specified table and column. More... | |
int | CreateIndex (string tableName, string[] columnNames, bool unique=false) |
Creates an index for the specified table and columns. More... | |
int | CreateIndex< T > (Expression< Func< T, object >> property, bool unique=false) |
Creates an index for the specified object property. e.g. CreateIndex<Client>(c => c.Name); More... | |
List< ColumnInfo > | GetTableInfo (string tableName) |
Query the built-in sqlite table_info table for a specific tables columns. More... | |
SQLiteCommand | CreateCommand (string cmdText, params object[] ps) |
Creates a new SQLiteCommand given the command text with arguments. Place a '?' in the command text for each of the arguments. More... | |
void | SetForeignKeysPragma (bool setOn=false) |
Set the PRAGAMA foreignkeys, be aware that this can lead into unrequested errors on the database especially with FK's that are null or not avalible, See the SQLite-Net doumentation for more details More... | |
List< Object > | LookForObject (Object obj, ManyToManyStructure mtm) |
Look for all objects (cloumns) we need for the Many to Many Foreign Key operations Function is "almost" indidentical to the function in TableMapping. Function is recursive to get also the related objects. More... | |
int | WriteManyToManyRefTable (object obj) |
Write - Insert into the reference table(s) of the Many to Many relation(s) in the database from one object. Can be more than one table More... | |
int | WriteManyToManyRefTable (object obj, ManyToManyStructure mtm) |
Write - Insert into the reference table of the Many to Many relation in the database from one object. Only one table, one Many To Many More... | |
int | WriteManyToManyRefTable (object obj, Type objType, ManyToManyStructure mtm) |
Write - Insert into the reference table of the Many to Many relation in the database from one object. Only one table, one Many To Many More... | |
int | CleanManyToManyRefTable (Type type) |
Remove the orphant entries in the reference table of the Many To Many realtion. That means it will loop through all entires in the left table and Compare this with the entries in de Left - Foreign Key assigned column of the reference table The entries in the reference table without a link (value) in the left table will be removed. preform the same on the Right table. More... | |
int | CleanManyToManyRefTable (ManyToManyStructure mtm) |
Remove the orphant entries in the reference table of the Many To Many realtion. That means it will loop through all entires in the left table and Compare this with the entries in de Left - Foreign Key assigned column of the reference table The entries in the reference table without a link (value) in the left table will be removed. preform the same on the Right table. More... | |
List< T > | QueryFk< T > (string queyrstring) |
A Query of a SQL command, method is needed for the QueryTableMapFk method Query as defined SQLConnction basic script wil result is a error: ambigious function. More... | |
List< T > | TableManyToMany< T > (bool isRecursive=false) |
Get ALL the Many To Many data from the database with the specified type, only the fields marked as Many To Many will be addressed The marking is done by the method CheckManyToMany CheckManyToMany More... | |
List< T > | TablesManyToMany< T > (Expression< Func< T, bool >> predicate, bool isRecursive=false) |
Get the Many To Many data from the database with the specified type, only the fields marked as Many To Many will be addressed The marking is done by the method CheckManyToMany CheckManyToMany The expression is to limit the top table (Left Table). More... | |
List< T > | ManyToMany< T > (List< T > baseRecords, bool isRecursive=false) |
Get the Many To Many data from the database with the specified type, for the records as specified More... | |
List< T > | QueryMtM< T > (string query, params object[] args) |
A Query of a SQL command, method is needed for the ManyToMany method Query as defined SQLConnction basic script wil result is a error: ambigious function. More... | |
Type | RelatedManyData< T > (Object currentObject, bool isRecursive) |
Get the related ManytoMany data for specified object, And the related data recursive if option is set. More... | |
List< ManyToManyStructure > | CheckManyToMany< T > () |
Check if there are Many to Many relations on the specified table, Left Table see the Demo for principe the structure of the Many to Many, Team (Left) - TeamMatch (Right) and TeamToTeamMatch Middle ) More... | |
List< ManyToManyStructure > | CheckManyToMany (Type typeLeftTable) |
Check if there are Many to Many relations on the specified table, Left Table see the Demo for principe the structure of the Many to Many, Team (Left) - TeamMatch (Right) and TeamToTeamMatch Middle ) More... | |
List< T > | TableOneToMany< T > (bool isRecursive=false) |
Get ALL the One To Many data and the other columns from the database with the specified type, if the option isReciursive is true it will get All data specified in table scheme for the specified type, database table More... | |
List< T > | TableOneToMany< T > (Expression< Func< T, bool >> predicate, bool isRecursive=false) |
Get ALL the One To Many data and the other columns from the database with the specified type, if the option isReciursive is true it will get All data specified in table scheme for the specified type, database table More... | |
List< T > | TableAllToMany< T > (bool isRecursive=false) |
Get ALL the All To Many data and the other columns from the database with the specified type, if the option isReciursive is true it will get All data specified in table scheme for the specified type, database table More... | |
List< T > | TableAllToMany< T > (Expression< Func< T, bool >> predicate, bool isRecursive=false) |
Get ALL the All To Many data and the other columns from the database with the specified type, if the option isReciursive is true it will get All data specified in table scheme for the specified type, database table More... | |
List< T > | TableAllToMany< T > (Expression< Func< T, bool >> predicate, bool isRecursive=true, bool fetchManyToMany=true) |
Get ALL the data and the other columns from the database with the specified type, if the option isReciursive and fetchManyToMany is true (default) it will get All data specified in table scheme for the specified type, database table More... | |
List< T > | AllToMany< T > (List< T > baseRecords, bool isRecursive, bool fetchManyToMany) |
Get ALL the data and the other columns from the database with the specified type, if the option isReciursive and fetchManyToMany is true (default) it will get All data specified in table scheme for the specified type, database table More... | |
List< T > | RelatedData< T > (Object currentObject) |
Get all teh related data from the specified type with the foreignkeys as defined in the database scheme A FkReference / Foereign Key: Table: Team type: TableType Column: ClubId string: ColumnName Attribute: [ForeignKey(Club, "Id")] type: FkTableType, string: FkColumnName To get the related data we construct a predicate, that will be used to narrow down the realted data RelatedData<T>(Expression<Func<T, bool>> pred = null) RelatedData<Team>(x => x.ClubId = 1123) More... | |
Expression< Func< T, bool > > | MakePredicate< T > (string columnName, Object parentIdObj) |
Construct a predicate to specify the fetched data Specieied type: T = Team (team => team.ClubId == 1123) – 1123 is IdC.value of the object - Club. More... | |
int | UpSertAll (IEnumerable objects, bool runInTransaction=true, bool resetValues=true, bool manyToManyClean=false) |
Inserts or Updates (UpSert) the given objects The lib version should be at least 3.24.00 We reset all the temporary values of the object. The return value is the number of rows added/updated to the tables. More... | |
int | UpSert (object obj) |
Inserts or Updates (UpSert) the given object The lib version should be at least 3.24.00 We reset all the temporary values of the object. The return value is the number of rows added/updated to the table. More... | |
int | UpSert (object obj, Type objType, bool resetValues=true, bool manyToManyClean=false) |
Inserts or Update (UpSert) the given object The lib version should be at least 3.24.00 We reset all the temporary values of the object. The return value is the number of rows added/updated to the table. More... | |
SQLiteCommand | CreateCommand (string cmdText, Dictionary< string, object > args) |
int | Execute (string query, params object[] args) |
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. More... | |
T | ExecuteScalar< T > (string query, params object[] args) |
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 when return primitive values. You can set the Trace or TimeExecution properties of the connection to profile execution. More... | |
List< T > | Query< T > (string query, params object[] args) |
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. More... | |
List< T > | QueryScalars< T > (string query, params object[] args) |
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 the first column of each row of the result. More... | |
IEnumerable< T > | DeferredQuery< T > (string query, params object[] args) |
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. More... | |
List< object > | Query (TableMapping map, string query, params object[] args) |
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. More... | |
IEnumerable< object > | DeferredQuery (TableMapping map, string query, params object[] args) |
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. More... | |
TableQuery< T > | Table< T > () |
Returns a queryable interface to the table represented by the given type. More... | |
T | Get< T > (object pk) |
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). More... | |
object | Get (object pk, TableMapping map) |
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). More... | |
T | Get< T > (Expression< Func< T, bool >> predicate) |
Attempts to retrieve the first object that matches the predicate from the table associated with the specified type. More... | |
T | Find< T > (object pk) |
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). More... | |
object | Find (object pk, TableMapping map) |
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). More... | |
T | Find< T > (Expression< Func< T, bool >> predicate) |
Attempts to retrieve the first object that matches the predicate from the table associated with the specified type. More... | |
T | FindWithQuery< T > (string query, params object[] args) |
Attempts to retrieve the first object that matches the query from the table associated with the specified type. More... | |
object | FindWithQuery (TableMapping map, string query, params object[] args) |
Attempts to retrieve the first object that matches the query from the table associated with the specified type. More... | |
void | BeginTransaction () |
Begins a new transaction. Call Commit to end the transaction. More... | |
string | 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. More... | |
void | Rollback () |
Rolls back the transaction that was begun by BeginTransaction or SaveTransactionPoint. More... | |
void | RollbackTo (string savepoint) |
Rolls back the savepoint created by BeginTransaction or SaveTransactionPoint. More... | |
void | Release (string savepoint) |
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. More... | |
void | Commit () |
Commits the transaction that was begun by BeginTransaction. More... | |
void | RunInTransaction (Action 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. More... | |
int | InsertAll (System.Collections.IEnumerable objects, bool runInTransaction=true) |
Inserts all specified objects. More... | |
int | InsertAll (System.Collections.IEnumerable objects, string extra, bool runInTransaction=true) |
Inserts all specified objects. More... | |
int | InsertAll (System.Collections.IEnumerable objects, Type objType, bool runInTransaction=true) |
Inserts all specified objects. More... | |
int | Insert (object obj) |
Inserts the given object (and updates its auto incremented primary key if it has one). The return value is the number of rows added to the table. More... | |
int | InsertOrReplace (object obj) |
Inserts the given object (and updates its auto incremented primary key if it has one). The return value is the number of rows added to the table. If a UNIQUE constraint violation occurs with some pre-existing object, this function deletes the old object. More... | |
int | Insert (object obj, Type objType) |
Inserts the given object (and updates its auto incremented primary key if it has one). The return value is the number of rows added to the table. More... | |
int | InsertOrReplace (object obj, Type objType) |
Inserts the given object (and updates its auto incremented primary key if it has one). The return value is the number of rows added to the table. If a UNIQUE constraint violation occurs with some pre-existing object, this function deletes the old object. More... | |
int | Insert (object obj, string extra) |
Inserts the given object (and updates its auto incremented primary key if it has one). The return value is the number of rows added to the table. More... | |
int | Insert (object obj, string extra, Type objType) |
Inserts the given object (and updates its auto incremented primary key if it has one). The return value is the number of rows added to the table. More... | |
int | Update (object obj) |
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. More... | |
int | Update (object obj, Type objType) |
Updates the columns of the object in a table using the specified object. Except for its primary key. The object is required to have a primary key. More... | |
int | UpdateAll (System.Collections.IEnumerable objects, bool runInTransaction=true) |
Updates all specified objects. More... | |
int | Delete (object objectToDelete) |
Deletes the given object from the database using its primary key. More... | |
int | Delete< T > (object primaryKey) |
Deletes the object with the specified primary key. More... | |
int | Delete (object primaryKey, TableMapping map) |
Deletes the object with the specified primary key. More... | |
int | Delete< T > (Expression< Func< T, bool >> predicate) |
Deletes the found objects from the specified table. according the predicate. More... | |
int | 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? More... | |
int | DeleteAll (TableMapping map) |
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? More... | |
void | Backup (string destinationDatabasePath, string databaseName="main") |
Backup the entire database to the specified path. More... | |
void | Dispose () |
Dispose the connection More... | |
void | Close () |
Close action to dispose the connection More... | |
Properties | |
bool | SkipLock [get, set] |
Gets or sets a value indicating whether this T:SQLite.SQLiteConnectionWithLock skip lock. More... | |
Properties inherited from SQLite.SQLiteConnection | |
Sqlite3DatabaseHandle | Handle [get] |
The handle to the Connection of the SQL-Net 3 More... | |
string | DatabasePath [get] |
Gets the database path used by this connection. More... | |
int | LibVersionNumber [get] |
Gets the SQLite library version number. 3007014 would be v3.7.14 More... | |
bool | TimeExecution [get, set] |
Whether Trace lines should be written that show the execution time of queries. More... | |
bool | Trace [get, set] |
Whether to writer queries to Tracer during execution. More... | |
Action< string > | Tracer [get, set] |
The delegate responsible for writing trace lines. More... | |
bool | StoreDateTimeAsTicks [get] |
Whether to store DateTime properties as ticks (true) or strings (false). More... | |
bool | StoreTimeSpanAsTicks [get] |
Whether to store TimeSpan properties as ticks (true) or strings (false). More... | |
string | DateTimeStringFormat [get] |
The format to use when storing DateTime properties as strings. Ignored if StoreDateTimeAsTicks is true. More... | |
TimeSpan | BusyTimeout [get, set] |
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. More... | |
IEnumerable< TableMapping > | TableMappings [get] |
Returns the mappings from types to tables that the connection currently understands. More... | |
bool | IsInTransaction [get] |
Whether BeginTransaction has been called and the database is waiting for a Commit. More... | |
Additional Inherited Members | |
Public Attributes inherited from SQLite.SQLiteConnection | |
Dictionary< string, object > | PksLastUsed = new Dictionary<string, object>() |
Primary key last used in the TableMapping for automatic update of the related tables – the List<T> or T[] in the objects More... | |
List< FKReference > | ForeignKeys = new List<FKReference>() |
A list of the Foreign Keys used by the current objects More... | |
bool | PragmaForeignKeys = true |
Bool value for updating / resets of the foreign key PRAGMA in the database More... | |
List< string > | TableOrder = new List<string>() |
The order of the tables we need to follow, otherwise some upserts /inserts /updates gives a error when the PRAGMA foreign keys is true, structure example: Club, Team, TeamPlayers More... | |
Protected Member Functions inherited from SQLite.SQLiteConnection | |
virtual SQLiteCommand | NewCommand () |
Creates a new SQLiteCommand. Can be overridden to provide a sub-class. More... | |
virtual void | Dispose (bool disposing) |
Close action to dispose the connection More... | |
Events inherited from SQLite.SQLiteConnection | |
EventHandler< NotifyTableChangedEventArgs > | TableChanged |
Handler for the Event - Notification More... | |
This is a normal connection except it contains a Lock method that can be used to serialize access to the database in lieu of using the sqlite's FullMutex support.
SQLite.SQLiteConnectionWithLock.SQLiteConnectionWithLock | ( | SQLiteConnectionString | connectionString | ) |
Initializes a new instance of the T:SQLite.SQLiteConnectionWithLock class.
connectionString | Connection string containing the DatabasePath. |
IDisposable SQLite.SQLiteConnectionWithLock.Lock | ( | ) |
Lock the database to serialize access to it. To unlock it, call Dispose on the returned object.
|
getset |
Gets or sets a value indicating whether this T:SQLite.SQLiteConnectionWithLock skip lock.
true
if skip lock; otherwise, false
.