Class DatabaseOperations

Utility class for performing database operations

Inheritance

Object

DatabaseOperations

OracleOperations

SqlServerOperations

Namespace: SuperOffice.Data.Dialect

Assembly: SoDataBase.dll

Syntax

public abstract class DatabaseOperations : Object

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
Boolean

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

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
Int32

Methods

BulkDelete(SoTable, Int32[])

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

Declaration

public virtual int BulkDelete(SoTable target, int[] iDs)

Parameters

Type	Name	Description
SoTable	target
Int32[]	iDs

Returns

Type	Description
Int32	Actual number of rows deleted, as reported by the database

BulkDelete(Select)

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

Declaration

public virtual int BulkDelete(Select rowSelector)

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

Returns

Type	Description
Int32

ExecuteNonQuery(String)

Execute an SQL command that is not a query

Declaration

protected int ExecuteNonQuery(string sqlCommand)

Parameters

Type	Name	Description
String	sqlCommand

Returns

Type	Description
Int32	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 DatabaseOperations GetCurrent()

Returns

Type	Description
DatabaseOperations

GetDatabaseSize()

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

Declaration

public abstract int GetDatabaseSize()

Returns

Type	Description
Int32

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

protected SoConnection GetDbConnectionForUserAdmin()

Returns

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

GetKilobytes(SoTable)

Utility class for performing database operations

Declaration

public abstract int GetKilobytes(SoTable table)

Parameters

Type	Name	Description
SoTable	table

Returns

Type	Description
Int32

GetRowCount(SoTable)

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

Declaration

public abstract int GetRowCount(SoTable table)

Parameters

Type	Name	Description
SoTable	table	Table we want counts for

Returns

Type	Description
Int32	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

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

protected DatabaseOperations.UserInfo GetUserAdmin()

Returns

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

ImportTable(SoTable, IEnumerable<Object[]>, List<Int32>)

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

Declaration

public virtual long ImportTable(SoTable table, IEnumerable<object[]> rows, List<int> actualPrimaryKeys = null)

Parameters

Type	Name	Description
SoTable	table	Definition of table to dump data into
IEnumerable<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<Int32>	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)

Returns

Type	Description
Int64	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

InnerTruncateTable(SoTable, DatabaseOperations.TruncateOptions)

Utility class for performing database operations

Declaration

public abstract void InnerTruncateTable(SoTable table, DatabaseOperations.TruncateOptions options)

Parameters

Type	Name	Description
SoTable	table
DatabaseOperations.TruncateOptions	options

NonLoggedMassUpdate(Select, Dictionary<FieldInfo, Object>)

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 int NonLoggedMassUpdate(Select rowSelector, Dictionary<FieldInfo, object> fieldsToUpdate)

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!

Returns

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

Exceptions

Type	Condition
SoIllegalOperationException

NonLoggedMassUpdate(Int32[], 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
Int32[]	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
Int32	Number of rows updated, as reported by the database

Exceptions

Type	Condition
SoIllegalOperationException

SetOptions(Boolean, TtlFlags)

Utility class for performing database operations

Declaration

public DatabaseOperations SetOptions(bool allowTravelTransactionLog, TtlFlags ttlFlags)

Parameters

Type	Name	Description
Boolean	allowTravelTransactionLog
TtlFlags	ttlFlags

Returns

Type	Description
DatabaseOperations

TruncateTable(SoTable, DatabaseOperations.TruncateOptions, Boolean)

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 int TruncateTable(SoTable table, DatabaseOperations.TruncateOptions options, bool resetSequence = false)

Parameters

Type	Name	Description
SoTable	table	The table to truncate - remember, there is no undo, even with transactions!
DatabaseOperations.TruncateOptions	options	Storage allocation options, may or may not be supported
Boolean	resetSequence

Returns

Type	Description
Int32

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.

WipeAndImportTable(SoTable, IEnumerable<Object[]>)

Utility class for performing database operations

Declaration

public virtual long WipeAndImportTable(SoTable table, IEnumerable<object[]> rows)

Parameters

Type	Name	Description
SoTable	table
IEnumerable<Object[]>	rows

Returns

Type	Description
Int64

WriteBulkRows(DatabaseOperations.BulkImportInfo)

Actual bulk insert implementation

Declaration

protected abstract void WriteBulkRows(DatabaseOperations.BulkImportInfo workItem)

Parameters

Type	Name	Description
DatabaseOperations.BulkImportInfo	workItem

Extension Methods

EnumUtil.MapEnums<From, To>(From)

Converters.MapEnums<From, To>(From)