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

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.

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)

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