Class NPocoDatabaseExtensions

Provides extension methods to NPoco Database class.

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

Methods

ConfigureNPocoBulkExtensions()

Configures NPoco's SqlBulkCopyHelper to use the correct SqlConnection and SqlTransaction instances from the underlying RetryDbConnection and ProfiledDbTransaction

Declaration
public static void ConfigureNPocoBulkExtensions()
Remarks

This is required to use NPoco's own method because we use wrapped DbConnection and DbTransaction instances. NPoco's InsertBulk method only caters for efficient bulk inserting records for Sql Server, it does not cater for bulk inserting of records for any other database type and in which case will just insert records one at a time. NPoco's InsertBulk method also deals with updating the passed in entity's PK/ID once it's inserted whereas our own BulkInsertRecords methods do not handle this scenario.

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

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

GetCurrentTransactionIsolationLevel(IDatabase)

Declaration
public static IsolationLevel GetCurrentTransactionIsolationLevel(this IDatabase database)
Parameters
Type Name Description
IDatabase database
Returns
Type Description
IsolationLevel

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
IDbCommand command
Returns
Type Description
TCommand
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, IDbConnection
Parameters
Type Name Description
IDbConnection connection
Returns
Type Description
TConnection
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, IDbTransaction
Parameters
Type Name Description
IDbTransaction transaction
Returns
Type Description
TTransaction
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.
System.Collections.Generic.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.

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.

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

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, 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
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.

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
System.Collections.Generic.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.

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

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

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