View Source

Class NPocoDatabaseExtensions

Provides extension methods to NPoco Database class.

Inheritance
System.Object
Namespace: Umbraco.Extensions
Assembly: Umbraco.Infrastructure.dll
Syntax
public static class NPocoDatabaseExtensions

Methods

View Source

EscapeAtSymbols(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
System.String value
Returns
Type Description
System.String
View Source

FetchByGroups<TResult, TSource>(IDatabase, IEnumerable<TSource>, Int32, Func<IEnumerable<TSource>, Sql<ISqlContext>>)

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
IEnumerable<TSource> source
System.Int32 groupSize
Func<IEnumerable<TSource>, Sql<ISqlContext>> sqlFactory
Returns
Type Description
IEnumerable<TResult>
Type Parameters
Name Description
TResult
TSource
View Source

GetCurrentTransactionIsolationLevel(IDatabase)

Declaration
public static IsolationLevel GetCurrentTransactionIsolationLevel(this IDatabase database)
Parameters
Type Name Description
IDatabase database
Returns
Type Description
System.Data.IsolationLevel
View Source

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, IDbCommand
Parameters
Type Name Description
System.Data.IDbCommand command
Returns
Type Description
TCommand
Type Parameters
Name Description
TCommand
View Source

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, IDbConnection
Parameters
Type Name Description
System.Data.IDbConnection connection
Returns
Type Description
TConnection
Type Parameters
Name Description
TConnection
View Source

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, IDbTransaction
Parameters
Type Name Description
System.Data.IDbTransaction transaction
Returns
Type Description
TTransaction
Type Parameters
Name Description
TTransaction
View Source

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<System.String, PocoColumn> column

The column.
Returns
Type Description
System.Boolean

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.

View Source

InsertOrUpdate<T>(IUmbracoDatabase, T)

Safely inserts a record, or updates if it exists, based on a unique constraint.

Declaration
public static RecordPersistenceType InsertOrUpdate<T>(this IUmbracoDatabase db, T poco)
    where T : class
Parameters
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.

View Source

InsertOrUpdate<T>(IUmbracoDatabase, T, String, Object)

Declaration
public static RecordPersistenceType InsertOrUpdate<T>(this IUmbracoDatabase db, T poco, string updateCommand, object updateArgs)
    where T : class
Parameters
Type Name Description
IUmbracoDatabase db
T poco
System.String updateCommand
System.Object updateArgs
Returns
Type Description
RecordPersistenceType
Type Parameters
Name Description
T
View Source

InsertOrUpdateAsync<T>(IUmbracoDatabase, T, String, Object)

Safely inserts a record, or updates if it exists, based on a unique constraint.

Declaration
public static async Task<RecordPersistenceType> InsertOrUpdateAsync<T>(this IUmbracoDatabase db, T poco, string updateCommand, object updateArgs)
    where T : class
Parameters
Type Name Description
IUmbracoDatabase db
T poco
System.String updateCommand

If the entity has a composite key they you need to specify the update command explicitly
System.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.

View Source

QueryPaged<T>(IDatabase, Int64, 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
System.Int64 pageSize

The number of rows to load per page
Sql sql
Returns
Type Description
IEnumerable<T>
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.

View Source

QueryPaged<T>(IDatabase, Int64, Sql, Nullable<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
System.Int64 pageSize

The number of rows to load per page
Sql sql
System.Nullable<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>
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.

View Source

TruncateTable(IDatabase, ISqlSyntaxProvider, String)

Declaration
public static void TruncateTable(this IDatabase db, ISqlSyntaxProvider sqlSyntax, string tableName)
Parameters
Type Name Description
IDatabase db
ISqlSyntaxProvider sqlSyntax
System.String tableName