Class DatabaseOperations

Utility class for performing database operations

Inheritance
object
DatabaseOperations
OracleOperations
SqlServerOperations
Implements
Inherited Members
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

Extension Methods