Class DatabaseOperations

Utility class for performing database operations

Inheritance

object

DatabaseOperations

OracleOperations

PostgreSqlOperations

SqlServerOperations

Implements

IDatabaseOperations

Inherited Members

object.ToString()

object.Equals(object)

object.Equals(object, object)

object.ReferenceEquals(object, object)

object.GetHashCode()

object.GetType()

object.MemberwiseClone()

Namespace: SuperOffice.Data.Dialect

Assembly: SoDataBase.BusinessLogic.dll

Syntax

public abstract class DatabaseOperations : IDatabaseOperations

Constructors

DatabaseOperations(string, string)

Default constructor

Declaration

protected DatabaseOperations(string databaseMajor, string databaseMinor)

Parameters

Type	Name	Description
string	databaseMajor
string	databaseMinor

Fields

_allowTravelTransactionLog

Utility class for performing database operations

Declaration

protected bool _allowTravelTransactionLog

Field Value

Type	Description
bool

_currentDialect

Utility class for performing database operations

Declaration

protected readonly Dialect _currentDialect

Field Value

Type	Description
Dialect

_ttlFlags

Utility class for performing database operations

Declaration

protected TtlFlags _ttlFlags

Field Value

Type	Description
TtlFlags

Properties

NumShipoutThreads

The number of parallel threads that process the data chunks and write them to the database

Declaration

protected virtual int NumShipoutThreads { get; }

Property Value

Type	Description
int

ShipoutBatchSize

The size of the batches that are queued for shipping out to the database

Declaration

protected virtual int ShipoutBatchSize { get; }

Property Value

Type	Description
int

Methods

BulkDeleteAsync(SoTable, int[])

Efficiently delete large numbers of rows, identified by a query that returns (only!) the primary keys of the rows to be deleted

Declaration

public Task<int> BulkDeleteAsync(SoTable target, int[] iDs)

Parameters

Type	Name	Description
SoTable	target
int[]	iDs

Returns

Type	Description
Task<int>	Actual number of rows deleted, as reported by the database

Remarks

Since this method deletes data in batches, cancellation halfway would leave the database in an incomsistent state. Cancellation is therefore not supported here.

BulkDeleteAsync(Select, CancellationToken)

Efficiently delete large numbers of rows, identified by a query that returns (only!) the primary keys of the rows to be deleted

Declaration

public Task<int> BulkDeleteAsync(Select rowSelector, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
Select	rowSelector	A Select of arbitrary complexity, whose ReturnFields collection only has one field. This field must be the primary key of a table
CancellationToken	cancellationToken

Returns

Type	Description
Task<int>

ExecuteNonQueryAsync(string, CancellationToken)

Execute an SQL command that is not a query

Declaration

protected Task<int> ExecuteNonQueryAsync(string sqlCommand, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
string	sqlCommand
CancellationToken	cancellationToken

Returns

Type	Description
Task<int>	Number of rows affected, as reported by database server

GetConnectionString()

Utility class for performing database operations

Declaration

protected string GetConnectionString()

Returns

Type	Description
string

GetCurrent()

Obtain reference to DatabaseOperations for the current database.

Declaration

public static IDatabaseOperations GetCurrent()

Returns

Type	Description
IDatabaseOperations

GetDatabaseSize()

Get the total database size (including any unallocated space inside the db, etc) in megabytes

Declaration

public abstract int GetDatabaseSize()

Returns

Type	Description
int

GetDatabaseVersion()

Utility class for performing database operations

Declaration

public abstract string GetDatabaseVersion()

Returns

Type	Description
string

GetDbConnectionForUserAdmin()

Get a connection to the database connected as a User Administrator.

Declaration

[Obsolete("Database users are no longer supported by NetServer", true)]
protected SoConnection GetDbConnectionForUserAdmin()

Returns

Type	Description
SoConnection	Connection to the database connected as a User Administrator.

GetIdentityFromAutoIncrement(SoTable)

Obtain the ID of a new record by inserting a dummy record into the database (or a set of records if count > 1; the ID of the first one is returned)

Declaration

public abstract int GetIdentityFromAutoIncrement(SoTable soTable)

Parameters

Type	Name	Description
SoTable	soTable

Returns

Type	Description
int

GetKilobytes(SoTable)

Utility class for performing database operations

Declaration

public abstract int GetKilobytes(SoTable table)

Parameters

Type	Name	Description
SoTable	table

Returns

Type	Description
int

GetRowCountAsync(SoTable, CancellationToken)

Get count of rows in the table (as quickly as possible). Not guaranteed to return exact results.

Declaration

public abstract Task<int> GetRowCountAsync(SoTable table, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
SoTable	table	Table we want counts for
CancellationToken	cancellationToken

Returns

Type	Description
Task<int>	Approximate row count.

GetSpecific(string, string)

Utility class for performing database operations

Declaration

public static DatabaseOperations GetSpecific(string databaseMajor, string databaseMinor)

Parameters

Type	Name	Description
string	databaseMajor
string	databaseMinor

Returns

Type	Description
DatabaseOperations

GetTableAdmin()

Get the user credentials for the user that is permitted to modify database tables.

Declaration

[Obsolete("Database users are no longer supported by NetServer", true)]
protected DatabaseOperations.UserInfo GetTableAdmin()

Returns

Type	Description
DatabaseOperations.UserInfo	Credentials for the user that is permitted to modify database tables.

GetUserAdmin()

Get the user credentials for the user that is permitted to modify database users.

Declaration

[Obsolete("Database users are no longer supported by NetServer", true)]
protected DatabaseOperations.UserInfo GetUserAdmin()

Returns

Type	Description
DatabaseOperations.UserInfo	Credentials for the user that is permitted to modify database users.

ImportTableAsync(SoTable, IAsyncEnumerable<object[]>, List<int>, CancellationToken)

Bulk-insert rows into a table. Please read and understand the remarks before using this method.

Declaration

public virtual Task<long> ImportTableAsync(SoTable table, IAsyncEnumerable<object[]> rows, List<int> actualPrimaryKeys = null, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
SoTable	table	Definition of table to dump data into
IAsyncEnumerable<object[]>	rows	Enumeration of row objects, where each row object is an array of values. All columns have to be specified, using values that are compatible with the table columns.
List<int>	actualPrimaryKeys	Optional list that will be populated with the primary keys actually in the rows; may be null. This functionality does not work for tables that have database-allocated keys (Service y_ extratables)
CancellationToken	cancellationToken

Returns

Type	Description
Task<long>	Number of rows written to database

Remarks

Bulk insertion is a special API supported by some databases. It will insert rows in the fastest possible way, but subject to a number of limitations; generally including the following:

Normal NetServer logic (sequence numbers, traveltransctionlog (but see param), freetext etc) do not apply
NetServer Sentry logic does not apply
Database transaction logging may not apply, rendering such insert impossible to roll back
Database triggers do not fire
Database-level replication features will not work; however SQL Server change tracking still registers the changes properly
Special permissions might be required on the database

In addition, full speed advantage is generally only available if indexes are not present.

ImportTable will "pull" data from the rows parameter, and ship it in reasonably-sized batches to the database server. It may use some level of parallelization to increase throughput, but the iteration over the rows parameter will only be on the original thread. The method will return when the enumeration is exhausted.

On databases that do not have bulk-insertion API, or where we haven't implemented it, the implementation will fallback to ordinary NetServer insert's.

The classes that actually implement Bulk Copy have the same syntax, more or less, but no common inheritance.
http://docs.oracle.com/html/E10927_01/OracleBulkCopyClass.htm
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx
http://dcx.sybase.com/1201/en/dbprogramming/programming-api-sabulkcopy-cla.html

InnerTruncateTableAsync(SoTable, TruncateOptions)

Utility class for performing database operations

Declaration

public abstract Task InnerTruncateTableAsync(SoTable table, TruncateOptions options = TruncateOptions.DeallocateStorage)

Parameters

Type	Name	Description
SoTable	table
TruncateOptions	options

Returns

Type	Description
Task

NonLoggedMassUpdate(int[], Dictionary<FieldInfo, object>)

Perform an update of all rows that match the primaryKeys. This method completely bypasses all Sentry logic, all logging, webhooks, everything. A thorough review and justification are required in every case. Consider MassOperations for a mechanism that is almost as efficient, and that satisfies most logging / webhook functionality instead.

Declaration

public virtual int NonLoggedMassUpdate(int[] primaryKeys, Dictionary<FieldInfo, object> fieldsToUpdate)

Parameters

Type	Name	Description
int[]	primaryKeys	The primary keys of the rows to be updated
Dictionary<FieldInfo, object>	fieldsToUpdate	The fields to be updated, and their values. Every row will be updated with the same values!

Returns

Type	Description
int	Number of rows updated, as reported by the database

Exceptions

Type	Condition
SoIllegalOperationException

NonLoggedMassUpdateAsync(Select, Dictionary<FieldInfo, object>, CancellationToken)

Perform an update of all rows that match the rowSelector. This method completely bypasses all Sentry logic, all logging, webhooks, everything. A thorough review and justification are required in every case. Consider MassOperations for a mechanism that is almost as efficient, and that satisfies most logging / webhook functionality instead.

Declaration

public virtual Task<int> NonLoggedMassUpdateAsync(Select rowSelector, Dictionary<FieldInfo, object> fieldsToUpdate, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
Select	rowSelector	A select that returns (only) the primary key of the target table, and whose Restriction defines the rows to be updated. It will be executed with Sentry ignored
Dictionary<FieldInfo, object>	fieldsToUpdate	The fields to be updated, and their values. Every row will be updated with the same values!
CancellationToken	cancellationToken

Returns

Type	Description
Task<int>	Number of rows updated, as reported by the database

Exceptions

Type	Condition
SoIllegalOperationException

SetOptions(bool, TtlFlags)

Utility class for performing database operations

Declaration

public IDatabaseOperations SetOptions(bool allowTravelTransactionLog, TtlFlags ttlFlags)

Parameters

Type	Name	Description
bool	allowTravelTransactionLog
TtlFlags	ttlFlags

Returns

Type	Description
IDatabaseOperations

TruncateTableAsync(SoTable, TruncateOptions, bool, CancellationToken)

Throw away all the rows in a table. On most databases this is an operation that is very fast, but it is not logged in the database transaction log and so cannot be rolled back.

It is also not logged in the superoffice traveltransactionlog, so it is not replicated, and should generally not be used on tables that are subject to replication.

Declaration

public virtual Task<int> TruncateTableAsync(SoTable table, TruncateOptions options = TruncateOptions.DeallocateStorage, bool resetSequence = false, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
SoTable	table	The table to truncate - remember, there is no undo, even with transactions!
TruncateOptions	options	Storage allocation options, may or may not be supported
bool	resetSequence
CancellationToken	cancellationToken	Cancellationtoken will be checked as long as we are reading; once changing of database starts, cancellation will no longer be checked.

Returns

Type	Description
Task<int>

Remarks

On Oracle, it requires the DROP ANY TABLE privilege. On DB/2 DELETE, CONTROL and DATACCESS are required. Any DELETE triggers are generally not executed, though this might vary with database and access.

WipeAndImportTableAsync(SoTable, IAsyncEnumerable<object[]>, CancellationToken)

Utility class for performing database operations

Declaration

public virtual Task<long> WipeAndImportTableAsync(SoTable table, IAsyncEnumerable<object[]> rows, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
SoTable	table
IAsyncEnumerable<object[]>	rows
CancellationToken	cancellationToken

Returns

Type	Description
Task<long>

WipeAndImportTableAsync(SoTable, IEnumerable<object[]>, CancellationToken)

Utility class for performing database operations

Declaration

public virtual Task<long> WipeAndImportTableAsync(SoTable table, IEnumerable<object[]> rows, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
SoTable	table
IEnumerable<object[]>	rows
CancellationToken	cancellationToken

Returns

Type	Description
Task<long>

WriteBulkRowsAsync(BulkImportInfo, CancellationToken)

Actual bulk insert implementation

Declaration

protected abstract Task WriteBulkRowsAsync(DatabaseOperations.BulkImportInfo info, CancellationToken token)

Parameters

Type	Name	Description
DatabaseOperations.BulkImportInfo	info
CancellationToken	token

Returns

Type	Description
Task

Implements

IDatabaseOperations

Extension Methods

EnumUtil.MapEnums<From, To>(From)

Converters.MapEnums<From, To>(From)