Class NPocoDatabaseExtensions
Provides extension methods to NPoco Database class.
Inheritance
Namespace: Umbraco.Extensions
Assembly: Umbraco.Infrastructure.dll
Syntax
public static class NPocoDatabaseExtensionsMethods
View SourceEscapeAtSymbols(string)
This will escape single @ symbols for npoco values so it doesn't think it's a parameter
Declaration
public static string EscapeAtSymbols(string value)Parameters
| Type | Name | Description |
|---|---|---|
| string | value | The string containing @ symbols to escape. |
Returns
| Type | Description |
|---|---|
| string | The string with single @ symbols escaped as @@. |
FetchByGroups<TResult, TSource>(IDatabase, IEnumerable<TSource>, int, Func<IEnumerable<TSource>, Sql<ISqlContext>>)
Fetches results from the database by splitting the source collection into groups of a specified size, and executing a SQL query for each group.
Declaration
public static IEnumerable<TResult> FetchByGroups<TResult, TSource>(this IDatabase db, IEnumerable<TSource> source, int groupSize, Func<IEnumerable<TSource>, Sql<ISqlContext>> sqlFactory)Parameters
| Type | Name | Description |
|---|---|---|
| IDatabase | db | The database instance used to perform the fetch operations. |
| IEnumerable<TSource> | source | The collection of source items to be grouped and used in queries. |
| int | groupSize | The maximum number of items in each group when splitting the source collection. |
| Func<IEnumerable<TSource>, Sql<ISqlContext>> | sqlFactory | A function that generates a SQL query for each group of source items. |
Returns
| Type | Description |
|---|---|
| IEnumerable<TResult> | An enumerable containing the results fetched from the database for each group, concatenated into a single sequence. |
Type Parameters
| Name | Description |
|---|---|
| TResult | The type of the result returned from the database for each group. |
| TSource | The type of the items in the source collection. |
GetCurrentTransactionIsolationLevel(IDatabase)
Gets the current transaction isolation level of the specified database.
Declaration
public static IsolationLevel GetCurrentTransactionIsolationLevel(this IDatabase database)Parameters
| Type | Name | Description |
|---|---|---|
| IDatabase | database | The database instance to retrieve the transaction isolation level from. |
Returns
| Type | Description |
|---|---|
| IsolationLevel | The current System.Data.IsolationLevel of the active transaction, or System.Data.IsolationLevel.Unspecified if no transaction is active. |
GetTypedCommand<TCommand>(IDbCommand)
Returns the underlying command as a typed command - this is used to unwrap the profiled mini profiler stuff
Declaration
public static TCommand GetTypedCommand<TCommand>(IDbCommand command) where TCommand : class, IDbCommandParameters
| Type | Name | Description |
|---|---|---|
| IDbCommand | command | The database command to unwrap. |
Returns
| Type | Description |
|---|---|
| TCommand | The underlying typed command. |
Type Parameters
| Name | Description |
|---|---|
| TCommand |
GetTypedConnection<TConnection>(IDbConnection?)
Returns the underlying connection as a typed connection - this is used to unwrap the profiled mini profiler stuff
Declaration
public static TConnection GetTypedConnection<TConnection>(IDbConnection? connection) where TConnection : class, IDbConnectionParameters
| Type | Name | Description |
|---|---|---|
| IDbConnection | connection | The database connection to unwrap. |
Returns
| Type | Description |
|---|---|
| TConnection | The underlying typed connection. |
Type Parameters
| Name | Description |
|---|---|
| TConnection |
GetTypedTransaction<TTransaction>(IDbTransaction?)
Returns the underlying transaction as a typed transaction - this is used to unwrap the profiled mini profiler stuff
Declaration
public static TTransaction GetTypedTransaction<TTransaction>(IDbTransaction? transaction) where TTransaction : class, IDbTransactionParameters
| Type | Name | Description |
|---|---|---|
| IDbTransaction | transaction | The database transaction to unwrap. |
Returns
| Type | Description |
|---|---|
| TTransaction | The underlying typed transaction. |
Type Parameters
| Name | Description |
|---|---|
| TTransaction |
IncludeColumn(PocoData, KeyValuePair<string, PocoColumn>)
Determines whether a column should be part of a bulk-insert.
Declaration
public static bool IncludeColumn(PocoData pocoData, KeyValuePair<string, PocoColumn> column)Parameters
| Type | Name | Description |
|---|---|---|
| PocoData | pocoData | The PocoData object corresponding to the record's type. |
| KeyValuePair<string, PocoColumn> | column | The column. |
Returns
| Type | Description |
|---|---|
| bool | A value indicating whether the column should be part of the bulk-insert. |
Remarks
Columns that are primary keys and auto-incremental, or result columns, are excluded from bulk-inserts.
InsertOrUpdateAsync<T>(IUmbracoDatabase, T, string?, object?)
Safely inserts a record, or updates if it exists, based on a unique constraint.
Declaration
public static Task<RecordPersistenceType> InsertOrUpdateAsync<T>(this IUmbracoDatabase db, T poco, string? updateCommand, object? updateArgs) where T : classParameters
| Type | Name | Description |
|---|---|---|
| IUmbracoDatabase | db | |
| T | poco | |
| string | updateCommand | If the entity has a composite key they you need to specify the update command explicitly |
| object | updateArgs |
Returns
| Type | Description |
|---|---|
| Task<RecordPersistenceType> | The action that executed, either an insert or an update. If an insert occurred and a PK value got generated, the poco object passed in will contain the updated value. |
Type Parameters
| Name | Description |
|---|---|
| T |
Remarks
We cannot rely on database-specific options because SQLCE does not support any of them. Ideally this should be achieved with proper transaction isolation levels but that would mean revisiting isolation levels globally. We want to keep it simple for the time being and manage it manually.
We handle it by trying to update, then insert, etc. until something works, or we get bored.
Note that with proper transactions, if T2 begins after T1 then we are sure that the database will contain T2's value once T1 and T2 have completed. Whereas here, it could contain T1's value.
InsertOrUpdate<T>(IUmbracoDatabase, T)
Safely inserts a record, or updates if it exists, based on a unique constraint.
Declaration
[Obsolete("Use InsertOrUpdateAsync instead")]
public static RecordPersistenceType InsertOrUpdate<T>(this IUmbracoDatabase db, T poco) where T : classParameters
| Type | Name | Description |
|---|---|---|
| IUmbracoDatabase | db | |
| T | poco |
Returns
| Type | Description |
|---|---|
| RecordPersistenceType | The action that executed, either an insert or an update. If an insert occurred and a PK value got generated, the poco object passed in will contain the updated value. |
Type Parameters
| Name | Description |
|---|---|
| T |
Remarks
We cannot rely on database-specific options because SQLCE does not support any of them. Ideally this should be achieved with proper transaction isolation levels but that would mean revisiting isolation levels globally. We want to keep it simple for the time being and manage it manually.
We handle it by trying to update, then insert, etc. until something works, or we get bored.
Note that with proper transactions, if T2 begins after T1 then we are sure that the database will contain T2's value once T1 and T2 have completed. Whereas here, it could contain T1's value.
InsertOrUpdate<T>(IUmbracoDatabase, T, string?, object?)
Safely inserts a record, or updates it if it exists, based on a unique constraint.
Declaration
public static RecordPersistenceType InsertOrUpdate<T>(this IUmbracoDatabase db, T poco, string? updateCommand, object? updateArgs) where T : classParameters
| Type | Name | Description |
|---|---|---|
| IUmbracoDatabase | db | The IUmbracoDatabase instance to perform the operation on. |
| T | poco | The record to insert or update. |
| string | updateCommand | An optional custom update command to use for updating the record. If null, a default update command is used. |
| object | updateArgs | Optional arguments for the update command. |
Returns
| Type | Description |
|---|---|
| RecordPersistenceType | The action that executed, either an insert or an update. If an insert occurred and a primary key value was generated, the |
Type Parameters
| Name | Description |
|---|---|
| T |
Remarks
This method does not rely on database-specific upsert options because SQLCE does not support them. Instead, it attempts to update, then insert, until successful. Note that transaction isolation is managed manually, and in some concurrency scenarios, the final value in the database may not be deterministic.
QueryPaged<T>(IDatabase, long, Sql)
Iterates over the result of a paged data set with a db reader
Declaration
public static IEnumerable<T> QueryPaged<T>(this IDatabase database, long pageSize, Sql sql)Parameters
| Type | Name | Description |
|---|---|---|
| IDatabase | database | The database to query. |
| long | pageSize | The number of rows to load per page |
| Sql | sql | The SQL query to execute. |
Returns
| Type | Description |
|---|---|
| IEnumerable<T> | An enumerable of rows from the paged query. |
Type Parameters
| Name | Description |
|---|---|
| T |
Remarks
NPoco's normal Page returns a List{T} but sometimes we don't want all that in memory and instead want to iterate over each row with a reader using Query vs Fetch.
QueryPaged<T>(IDatabase, long, Sql, Sql?)
Iterates over the result of a paged data set with a db reader
Declaration
public static IEnumerable<T> QueryPaged<T>(this IDatabase database, long pageSize, Sql sql, Sql? sqlCount)Parameters
| Type | Name | Description |
|---|---|---|
| IDatabase | database | The database to query. |
| long | pageSize | The number of rows to load per page |
| Sql | sql | The SQL query to execute. |
| Sql | sqlCount | Specify a custom Sql command to get the total count, if null is specified than the auto-generated sql count will be used |
Returns
| Type | Description |
|---|---|
| IEnumerable<T> | An enumerable of rows from the paged query. |
Type Parameters
| Name | Description |
|---|---|
| T |
Remarks
NPoco's normal Page returns a List{T} but sometimes we don't want all that in memory and instead want to iterate over each row with a reader using Query vs Fetch.
TruncateTable(IDatabase, ISqlSyntaxProvider, string)
Removes all rows from the specified table in the database by executing a TRUNCATE TABLE command.
Declaration
public static void TruncateTable(this IDatabase db, ISqlSyntaxProvider sqlSyntax, string tableName)Parameters
| Type | Name | Description |
|---|---|---|
| IDatabase | db | The NPoco.IDatabase instance on which to execute the truncate operation. |
| ISqlSyntaxProvider | sqlSyntax | The ISqlSyntaxProvider used to generate the appropriate SQL syntax for truncating the table. |
| string | tableName | The name of the table to truncate. This should be the unquoted table name; quoting is handled internally. |
Remarks
This operation deletes all data from the table but does not remove the table structure itself.