Contains functions that open and close the connection to the DB, allow execution of SQL statements and creation of DB Transactions. It is designed to support connections to different kinds of databases; there needs to be an implementation of the interface IDataBaseRDBMS to support an RDBMS. More...

Inheritance diagram for Ict.Common.DB.TDataBase:

Public Member Functions
delegate bool	MultipleParamQueryProgressUpdateDelegate (Int32 ANumberOfProcessedParameterVariations)
	Delegate that can optionally be passed to Method SelectUsingDataAdapterMulti. It will get called every 'n' records (where n is specified with the "AProgressUpdateEveryNRecs" Argument of that Method) while multiple Parameterised Query executions take place. More...

	TDataBase ()
	Default Constructor. The Database type will be specified only when one of the `EstablishDBConnection` Methods gets called More...

	TDataBase (TDBType ADBType)
	Constructor that specifies which Database type will be used with this Instance of TDataBase. More...

bool	ClearTransaction (TDBTransaction ToClear)
	if we commit or rollback a transaction, make sure we drop the default transaction of the connection as well More...

void	EstablishDBConnection (String AConnectionName="")
	simple overload for establishing a database connection using the settings from the configuration file More...

void	EstablishDBConnection (TDBType ADataBaseType, String ADsnOrServer, String ADBPort, String ADatabaseName, String AUsername, String APassword, String AConnectionString, bool AMustCoordinateDBAccess=true, String AConnectionName="")
	Establishes (opens) a DB connection to a specified RDBMS. More...

void	CloseDBConnection (bool ASuppressThreadCompatibilityCheck=false)
	Closes the DB connection. More...

string	GetDBConnectionIdentifier ()
	Returns a string containing the ConnectionIdentifier. If a ConnectionName was assigned when the DB connection was opened it is included, too. More...

TSQLCache	GetCache ()
	Means of getting Cache objects. More...

DbCommand	Command (String ACommandText, TDBTransaction ATransaction, DbParameter[] AParametersArray)
	Returns a DbCommand for a given command text in the context of a DB transaction. Suitable for parameterised SQL statements. Allows the passing in of Parameters for the SQL statement More...

DataSet	Select (String ASqlStatement, String ADataTableName, TDBTransaction AReadTransaction, DbParameter[] AParametersArray=null)
	Returns a DataSet containing a DataTable with the result of a given SQL statement. The SQL statement is executed in the given transaction context (which should have the desired IsolationLevel). Suitable for parameterised SQL statements. More...

DataSet	Select (DataSet AFillDataSet, String ASqlStatement, String ADataTableName, TDBTransaction AReadTransaction, System.Int32 AStartRecord, System.Int32 AMaxRecords)
	Puts a DataTable with the result of a given SQL statement into an existing DataSet. The SQL statement is executed in the given transaction context (which should have the desired IsolationLevel). Not suitable for parameterised SQL statements. More...

DataSet	Select (DataSet AFillDataSet, String ASqlStatement, String ADataTableName, TDBTransaction AReadTransaction, DbParameter[] AParametersArray=null, System.Int32 AStartRecord=0, System.Int32 AMaxRecords=0)
	Puts a DataTable with the result of a given SQL statement into an existing DataSet. The SQL statement is executed in the given transaction context (which should have the desired IsolationLevel). Suitable for parameterised SQL statements. More...

DataSet	SelectToTempTable (DataSet AFillDataSet, String ASqlStatement, String ADataTempTableName, TDBTransaction AReadTransaction, DbParameter[] AParametersArray, System.Int32 AStartRecord, System.Int32 AMaxRecords)
	Puts a temp DataTable with the result of a given SQL statement into an existing DataSet. The SQL statement is executed in the given transaction context (which should have the desired IsolationLevel). Suitable for parameterised SQL statements. More...

delegate string	TOptionalColumnMappingDelegate (ref IDictionaryEnumerator AColumNameMappingEnumerator)
	Delegate for optional Column Mappings for use with SelectUsingDataAdapter. More...

int	SelectUsingDataAdapter (String ASqlStatement, TDBTransaction AReadTransaction, ref DataTable AFillDataTable, TOptionalColumnMappingDelegate AOptionalColumnNameMapping=null, int ASelectCommandTimeout=-1, DbParameter[] AParametersArray=null)
	Executes a SQL Select Statement (once) using a DbDataAdapter. More...

int	SelectUsingDataAdapterMulti (String ASqlStatement, TDBTransaction AReadTransaction, ref DataTable AFillDataTable, TOptionalColumnMappingDelegate AOptionalColumnNameMapping=null, int ASelectCommandTimeout=-1, DbParameter[] AParameterDefinitions=null, List< object[]>AParameterValues=null, bool APrepareSelectCommand=false, int AProgressUpdateEveryNRecs=0, MultipleParamQueryProgressUpdateDelegate AMultipleParamQueryProgressUpdateCallback=null)
	Executes a SQL Select Statement (1..n times if a Paramterised Query is used) using a DbDataAdapter. More...

DataTable	SelectDT (String ASqlStatement, String ADataTableName, TDBTransaction AReadTransaction)
	Returns a DataTable filled with the result of a given SQL statement. The SQL statement is executed in the given transaction context (which should have the desired IsolationLevel). Not suitable for parameterised SQL statements. More...

DataTable	SelectDT (String ASqlStatement, String ADataTableName, TDBTransaction AReadTransaction, DbParameter[] AParametersArray)
	Returns a DataTable filled with the result of a given SQL statement. The SQL statement is executed in the given transaction context (which should have the desired IsolationLevel). Not suitable for parameterised SQL statements. More...

DataTable	SelectDT (DataTable ATypedDataTable, String ASqlStatement, TDBTransaction AReadTransaction, DbParameter[] AParametersArray=null, int AStartRecord=0, int AMaxRecords=0)
	this loads the result into a typed datatable More...

bool	CheckEstablishedDBConnectionThreadIsCompatible ()
	Checks if the current Thread is the same Thread that the currently established DB Connection was established on. More...

bool	CheckRunningDBTransactionIsolationLevelIsCompatible (IsolationLevel ADesiredIsolationLevel, bool AMustCoordinateDBAccess=true)
	Checks if the IsolationLevel of the currently running DB Transaction is acceptable for what is asked for with the Argument. More...

DbTransaction	BeginDbTransaction (IsolationLevel AIsolationLevel)
	create a basic transaction More...

TDBTransaction	BeginTransaction (IsolationLevel AIsolationLevel, Int16 ARetryAfterXSecWhenUnsuccessful=-1, string ATransactionName="")
	Starts a Transaction with a defined IsolationLevel on the current DB connection. Allows a retry timeout to be specified. More...

IsolationLevel	GetIsolationLevel ()
	Gets the IsolationLevel of the current DB Transaction (or debugging purposes). More...

TDBTransaction	GetNewOrExistingTransaction (IsolationLevel ADesiredIsolationLevel, out Boolean ANewTransaction, string ATransactionName="")
	Either starts a new Transaction on the current DB connection or returns a existing TDBTransaction. What it does depends on two factors: whether a Transaction is currently running or not, and if so, whether it meets the specified ADesiredIsolationLevel . More...

System.Int64	GetNextSequenceValue (String ASequenceName, TDBTransaction ATransaction)
	Returns the next sequence value for the given Sequence from the DB. More...

System.Int64	GetCurrentSequenceValue (String ASequenceName, TDBTransaction ATransaction)
	Returns the current sequence value for the given Sequence from the DB. More...

void	RestartSequence (String ASequenceName, TDBTransaction ATransaction, Int64 ARestartValue)
	restart a sequence with the given value More...

int	ExecuteNonQuery (String ASqlStatement, TDBTransaction ATransaction, DbParameter[] AParametersArray=null, bool ACommitTransaction=false)
	Executes a SQL statement that does not give back any results (eg. an UPDATE SQL command). The statement is executed in a transaction. Suitable for parameterised SQL statements. More...

object	ExecuteScalar (String ASqlStatement, TDBTransaction ATransaction, DbParameter[] AParametersArray=null)
	Executes a SQL statement that returns a single result (eg. an SELECT COUNT(*) SQL command or a call to a Stored Procedure that inserts data and returns the value of a auto-numbered field). The statement is executed in a transaction. Suitable for parameterised SQL statements. More...

virtual bool	HasAccess (String ASQLStatement)
	This Method checks if the current user has enough access rights to execute the query passed in in Argument ASQLStatement . More...

void	ReadTransaction (ref TDBTransaction ATransaction, Action AEncapsulatedDBAccessCode)
	start a read transaction, and then run the code in it More...

void	WriteTransaction (ref TDBTransaction ATransaction, ref bool ASubmitOK, Action AEncapsulatedDBAccessCode)
	start a write transaction, and then run the code in it More...

Static Public Member Functions
static string	GetDBConnectionName (string AConnectionName)
	Returns a standardised string containing the name of AConnectionName . More...

static string	GetThreadAndAppDomainCallInfoForDBConnectionEstablishmentAndDisconnection ()
	Returns information about the current Thread and the current AppDomain that can be useful for logging/debugging of DB Connection Establishment and DB Disconnection. More...

static bool	CheckDBTransactionThreadIsCompatible (TDBTransaction ATransaction)
	Checks if the current Thread is the same Thread that ATransaction was started on. More...

static string	ReadSqlFile (string ASqlFilename, SortedList< string, string >ADefines=null)
	Reads a SQL statement from file and removes the comments. More...

static XmlDocument	DataTableToXml (DataTable ATable)
	for debugging, export data table to xml (which can be saved as xml, yml, csv) More...

static void	LogTable (DataTable ATable)
	For debugging purposes only. Logs the contents of a DataTable. More...

static string	FormatSQLStatement (string s)
	For debugging purposes. Formats the sql query so that it is easily readable (mainly inserting line breaks before AND). More...

static void	LogSqlStatement (String AContext, String ASqlStatement, DbParameter[] AParametersArray)
	Logs the SQL statement and the parameters; use DebugLevel to define behaviour. More...

Properties
String	DBType `[get]`
	Returns the type of the RDBMS that the current Instance of TDataBase is connect to. More...

String	DsnOrServer `[get]`
	return the filename or server name of the database More...

bool	ConnectionOK `[get]`
	Tells whether it's save to execute any SQL command on the DB. It is updated when the DB connection's State changes. More...

System.Guid	ConnectionIdentifier `[get]`
	An identifier ('Globally Unique Identifier (GUID)') that uniquely identifies a DB Connection once it got created. It is used for internal 'sanity checks'. It also gets logged and hence can aid debugging (also useful for Unit Testing). More...

string	ConnectionName `[get]`
	Name of the DB connection (optional). It gets logged and hence can aid debugging (also useful for Unit Testing). More...

DateTime	LastDBAction `[get]`
	Tells when the last Database action was carried out by the caller. More...

int	WaitingTimeForCoordinatedDBAccess `[get]`
	Waiting time for 'Co-ordinated' (=Thread-safe) DB Access (in milliseconds). More...

TDBTransaction	Transaction `[get]`
	The current Transaction, if there is any. More...

Detailed Description

Contains functions that open and close the connection to the DB, allow execution of SQL statements and creation of DB Transactions. It is designed to support connections to different kinds of databases; there needs to be an implementation of the interface IDataBaseRDBMS to support an RDBMS.

Always use ANSI SQL-92 commands that are understood by all RDBMS systems that should be supported - TDataBase does no 'translation' of the SQL commands! The TDataBase class is the only Class that a developer needs to deal with when accessing DB's! (The TDBConnection class is a 'low-level' class that is intended to be used only by the TDataBase class.) Due to the limitations of native ODBC drivers, only one DataTable is ever returned when you call DbDataAdapter.FillSchema. This is true even when executing SQL batch statements from which multiple DataTable objects would be expected! TODO: this comment needs revising, with native drivers

Constructor & Destructor Documentation

◆ TDataBase() [1/2]

Ict.Common.DB.TDataBase.TDataBase ( )

inline

Default Constructor. The Database type will be specified only when one of the EstablishDBConnection Methods gets called

◆ TDataBase() [2/2]

Ict.Common.DB.TDataBase.TDataBase ( TDBType ADBType )

inline

Constructor that specifies which Database type will be used with this Instance of TDataBase.

Parameters

ADBType Type of RDBMS (Relational Database Management System)

Member Function Documentation

◆ BeginDbTransaction()

DbTransaction Ict.Common.DB.TDataBase.BeginDbTransaction ( IsolationLevel AIsolationLevel )

inline

create a basic transaction

Returns: The transaction.

Parameters

AIsolationLevel Isolation level.

◆ BeginTransaction()

TDBTransaction Ict.Common.DB.TDataBase.BeginTransaction	(	IsolationLevel	AIsolationLevel,
		Int16	ARetryAfterXSecWhenUnsuccessful = `-1`,
		string	ATransactionName = `""`
	)

inline

Starts a Transaction with a defined IsolationLevel on the current DB connection. Allows a retry timeout to be specified.

Parameters

AIsolationLevel	Desired IsolationLevel.
ARetryAfterXSecWhenUnsuccessful	Allows a retry timeout to be specified (in seconds). This is to be able to mitigate the problem of wanting to start a DB Transaction while another one is still running (gives time for the currently running DB Transaction to be finished).
ATransactionName	Name of the DB Transaction (optional). It gets logged and hence can aid debugging (also useful for Unit Testing).

Returns: Started Transaction (null if an error occured).

◆ CheckDBTransactionThreadIsCompatible()

static bool Ict.Common.DB.TDataBase.CheckDBTransactionThreadIsCompatible ( TDBTransaction ATransaction )

inlinestatic

Checks if the current Thread is the same Thread that ATransaction was started on.

Parameters

ATransaction An instantiated Transaction.

Returns: True if the current Thread is the same Thread that ATransaction was started on; if this isn't the case false is returned.

◆ CheckEstablishedDBConnectionThreadIsCompatible()

bool Ict.Common.DB.TDataBase.CheckEstablishedDBConnectionThreadIsCompatible ( )

inline

Checks if the current Thread is the same Thread that the currently established DB Connection was established on.

Returns: True if there isn't a currently established DB Connection or if the current Thread is the same Thread that the currently running established DB Connection was established on; if this isn't the case false is returned.

◆ CheckRunningDBTransactionIsolationLevelIsCompatible()

bool Ict.Common.DB.TDataBase.CheckRunningDBTransactionIsolationLevelIsCompatible	(	IsolationLevel	ADesiredIsolationLevel,
		bool	AMustCoordinateDBAccess = `true`
	)

inline

Checks if the IsolationLevel of the currently running DB Transaction is acceptable for what is asked for with the Argument.

Parameters

ADesiredIsolationLevel	IsolationLevel that is desired.
AMustCoordinateDBAccess	Set to true if the Method needs to co-ordinate DB Access on its own, set to false if the calling Method already takes care of this.

Returns: True if the IsolationLevel of the currently running DB Transaction is acceptable for what is asked for with the Argument; if this isn't the case false is returned.

Exceptions

EDBNullTransactionException is thrown if there is no Running DB Transaction to check against.

◆ ClearTransaction()

bool Ict.Common.DB.TDataBase.ClearTransaction ( TDBTransaction ToClear )

inline

if we commit or rollback a transaction, make sure we drop the default transaction of the connection as well

◆ CloseDBConnection()

void Ict.Common.DB.TDataBase.CloseDBConnection ( bool ASuppressThreadCompatibilityCheck = false )

inline

Closes the DB connection.

Parameters

ASuppressThreadCompatibilityCheck Set to true to suppress a check whether the Thread that calls this Method is the Thread that established the DB Connection.

Exceptions

EDBConnectionNotAvailableException Thrown if an attempt is made to close an already/still closed connection.

◆ Command()

DbCommand Ict.Common.DB.TDataBase.Command	(	String	ACommandText,
		TDBTransaction	ATransaction,
		DbParameter[]	AParametersArray
	)

inline

Returns a DbCommand for a given command text in the context of a DB transaction. Suitable for parameterised SQL statements. Allows the passing in of Parameters for the SQL statement

Important: Since an object that derives from DbCommand is returned you ought to call .Dispose() on the returned object to release its resouces! (DbCommand inherits from System.ComponentModel.Component, which implements IDisposable!)

Parameters

ACommandText	Command Text
ATransaction	An instantiated TDBTransaction, or null if the command should not be enlisted in a transaction.
AParametersArray	An array holding 1..n instantiated DbParameter (including Parameter Value)

Returns: Instantiated object (derived from DbCommand) - its actual Type depends on the RDBMS that we are connected to at runtime!

◆ DataTableToXml()

static XmlDocument Ict.Common.DB.TDataBase.DataTableToXml ( DataTable ATable )

inlinestatic

for debugging, export data table to xml (which can be saved as xml, yml, csv)

Parameters

ATable

Returns: XmlDocument containing the DataTable.

◆ EstablishDBConnection() [1/2]

void Ict.Common.DB.TDataBase.EstablishDBConnection ( String AConnectionName = "" )

inline

simple overload for establishing a database connection using the settings from the configuration file

◆ EstablishDBConnection() [2/2]

void Ict.Common.DB.TDataBase.EstablishDBConnection	(	TDBType	ADataBaseType,
		String	ADsnOrServer,
		String	ADBPort,
		String	ADatabaseName,
		String	AUsername,
		String	APassword,
		String	AConnectionString,
		bool	AMustCoordinateDBAccess = `true`,
		String	AConnectionName = `""`
	)

inline

Establishes (opens) a DB connection to a specified RDBMS.

Parameters

ADataBaseType	Type of the RDBMS to connect to.
ADsnOrServer	In case of an ODBC Connection: DSN (Data Source Name). In case of a PostgreSQL connection: Server.
ADBPort	In case of a PostgreSQL connection: port that the db server is running on.
ADatabaseName	the database to connect to
AUsername	User which should be used for connecting to the DB server
APassword	Password of the User which should be used for connecting to the DB server
AConnectionString	If this is not empty, it is prefered over the Dsn and Username and Password
AMustCoordinateDBAccess	Set to true if the Method needs to co-ordinate DB Access on its own, set to false if the calling Method already takes care of this.
AConnectionName	Name of the DB Connection (optional). It gets logged and hence can aid debugging (also useful for Unit Testing).

Exceptions

EDBConnectionNotEstablishedException Thrown when a connection cannot be established

◆ ExecuteNonQuery()

int Ict.Common.DB.TDataBase.ExecuteNonQuery	(	String	ASqlStatement,
		TDBTransaction	ATransaction,
		DbParameter[]	AParametersArray = `null`,
		bool	ACommitTransaction = `false`
	)

inline

Executes a SQL statement that does not give back any results (eg. an UPDATE SQL command). The statement is executed in a transaction. Suitable for parameterised SQL statements.

Parameters

ASqlStatement	SQL statement.
ATransaction	An instantiated TDBTransaction.
AParametersArray	An array holding 1..n instantiated DbParameters (eg. OdbcParameters) (including parameter Value).
ACommitTransaction	The transaction is committed if set to true, otherwise the transaction is not committed (useful when the caller wants to do further things in the same transaction).

Returns: Number of Rows affected.

◆ ExecuteScalar()

object Ict.Common.DB.TDataBase.ExecuteScalar	(	String	ASqlStatement,
		TDBTransaction	ATransaction,
		DbParameter[]	AParametersArray = `null`
	)

inline

Executes a SQL statement that returns a single result (eg. an SELECT COUNT(*) SQL command or a call to a Stored Procedure that inserts data and returns the value of a auto-numbered field). The statement is executed in a transaction. Suitable for parameterised SQL statements.

Parameters

ASqlStatement	SQL statement.
ATransaction	An instantiated TDBTransaction.
AParametersArray	An array holding 1..n instantiated DbParameters (eg. OdbcParameters) (including parameter Value).

Returns: Single result as object.

◆ FormatSQLStatement()

static string Ict.Common.DB.TDataBase.FormatSQLStatement ( string s )

inlinestatic

For debugging purposes. Formats the sql query so that it is easily readable (mainly inserting line breaks before AND).

Parameters

s	The sql statement that should be formatted.

Returns: Formatted sql statement.

◆ GetCache()

TSQLCache Ict.Common.DB.TDataBase.GetCache ( )

inline

Means of getting Cache objects.

Returns: A new Instance of an TSQLCache Object.

◆ GetCurrentSequenceValue()

System.Int64 Ict.Common.DB.TDataBase.GetCurrentSequenceValue	(	String	ASequenceName,
		TDBTransaction	ATransaction
	)

inline

Returns the current sequence value for the given Sequence from the DB.

Parameters

ASequenceName	Name of the Sequence.
ATransaction	An instantiated Transaction in which the Query to the DB will be enlisted.

Returns: Sequence Value.

◆ GetDBConnectionIdentifier()

string Ict.Common.DB.TDataBase.GetDBConnectionIdentifier ( )

inline

Returns a string containing the ConnectionIdentifier. If a ConnectionName was assigned when the DB connection was opened it is included, too.

Returns: String containing the ConnectionIdentifier. If a ConnectionName was assigned when the DB connection was opened it is included, too.

◆ GetDBConnectionName()

static string Ict.Common.DB.TDataBase.GetDBConnectionName ( string AConnectionName )

inlinestatic

Returns a standardised string containing the name of AConnectionName .

Returns: String containing the name of AConnectionName , or String.Empty if no name was assigned.

◆ GetIsolationLevel()

IsolationLevel Ict.Common.DB.TDataBase.GetIsolationLevel ( )

inline

Gets the IsolationLevel of the current DB Transaction (or debugging purposes).

Returns: IsolationLevel.Unspecified if no DB Transaction is open.

◆ GetNewOrExistingTransaction()

TDBTransaction Ict.Common.DB.TDataBase.GetNewOrExistingTransaction	(	IsolationLevel	ADesiredIsolationLevel,
		out Boolean	ANewTransaction,
		string	ATransactionName = `""`
	)

inline

Either starts a new Transaction on the current DB connection or returns a existing TDBTransaction. What it does depends on two factors: whether a Transaction is currently running or not, and if so, whether it meets the specified ADesiredIsolationLevel .

If there is a current Transaction but it has a different IsolationLevel, the result depends on the value of the ATryToEnforceIsolationLevel parameter.

If there is no current Transaction, a new Transaction with the specified IsolationLevel is started.

Parameters

ADesiredIsolationLevel	IsolationLevel that is desired
ANewTransaction	True if a new Transaction was started and is returned, false if an already existing Transaction is returned
ATransactionName	Name of the DB Transaction (optional). It gets logged and hence can aid debugging (also useful for Unit Testing).

Returns: Either an existing or a new Transaction that exactly meets the specified IsolationLevel

Exceptions

EDBTransactionIsolationLevelWrongException Thrown if the existing Transaction's Isolation Level hasn't got at least the Isolation Level specified.

◆ GetNextSequenceValue()

System.Int64 Ict.Common.DB.TDataBase.GetNextSequenceValue	(	String	ASequenceName,
		TDBTransaction	ATransaction
	)

inline

Returns the next sequence value for the given Sequence from the DB.

Parameters

ASequenceName	Name of the Sequence.
ATransaction	An instantiated Transaction in which the Query to the DB will be enlisted.

Returns: Sequence Value.

◆ GetThreadAndAppDomainCallInfoForDBConnectionEstablishmentAndDisconnection()

static string Ict.Common.DB.TDataBase.GetThreadAndAppDomainCallInfoForDBConnectionEstablishmentAndDisconnection ( )

inlinestatic

Returns information about the current Thread and the current AppDomain that can be useful for logging/debugging of DB Connection Establishment and DB Disconnection.

Returns: Information about the current Thread and the current AppDomain in form of a formatted string.

◆ HasAccess()

virtual bool Ict.Common.DB.TDataBase.HasAccess ( String ASQLStatement )

inlinevirtual

This Method checks if the current user has enough access rights to execute the query passed in in Argument ASQLStatement .

This Method needs to be implemented by a derived Class, that knows about the users' access rights. The implementation here simply returns true...

Returns: True if the user has access, false if access is denied. The implementation here simply returns true, though!

◆ LogSqlStatement()

static void Ict.Common.DB.TDataBase.LogSqlStatement	(	String	AContext,
		String	ASqlStatement,
		DbParameter[]	AParametersArray
	)

inlinestatic

Logs the SQL statement and the parameters; use DebugLevel to define behaviour.

Parameters

AContext	Context in which the logging takes place (eg. Method name).
ASqlStatement	SQL Statement that should be logged.
AParametersArray	Parameters for the SQL Statement. Can be null.

Returns: void

◆ LogTable()

static void Ict.Common.DB.TDataBase.LogTable ( DataTable ATable )

inlinestatic

For debugging purposes only. Logs the contents of a DataTable.

Parameters

ATable The DataTable whose contents should be logged.

Returns: void

◆ MultipleParamQueryProgressUpdateDelegate()

delegate bool Ict.Common.DB.TDataBase.MultipleParamQueryProgressUpdateDelegate ( Int32 ANumberOfProcessedParameterVariations )

Delegate that can optionally be passed to Method SelectUsingDataAdapterMulti. It will get called every 'n' records (where n is specified with the "AProgressUpdateEveryNRecs" Argument of that Method) while multiple Parameterised Query executions take place.

Parameters

ANumberOfProcessedParameterVariations

◆ ReadSqlFile()

static string Ict.Common.DB.TDataBase.ReadSqlFile	(	string	ASqlFilename,
		SortedList< string, string >	ADefines = `null`
	)

inlinestatic

Reads a SQL statement from file and removes the comments.

Parameters

ASqlFilename	.
ADefines	Defines to be set in the SQL statement.

Returns: SQL statement.

◆ ReadTransaction()

void Ict.Common.DB.TDataBase.ReadTransaction	(	ref TDBTransaction	ATransaction,
		Action	AEncapsulatedDBAccessCode
	)

inline

start a read transaction, and then run the code in it

◆ RestartSequence()

void Ict.Common.DB.TDataBase.RestartSequence	(	String	ASequenceName,
		TDBTransaction	ATransaction,
		Int64	ARestartValue
	)

inline

restart a sequence with the given value

◆ Select() [1/3]

DataSet Ict.Common.DB.TDataBase.Select	(	DataSet	AFillDataSet,
		String	ASqlStatement,
		String	ADataTableName,
		TDBTransaction	AReadTransaction,
		DbParameter[]	AParametersArray = `null`,
		System.Int32	AStartRecord = `0`,
		System.Int32	AMaxRecords = `0`
	)

inline

Puts a DataTable with the result of a given SQL statement into an existing DataSet. The SQL statement is executed in the given transaction context (which should have the desired IsolationLevel). Suitable for parameterised SQL statements.

Parameters

AFillDataSet	Existing DataSet
ASqlStatement	SQL statement
ADataTableName	Name that the DataTable should get
AReadTransaction	Instantiated TDBTransaction with the desired IsolationLevel
AParametersArray	An array holding 1..n instantiated DbParameters (eg. OdbcParameters) (including parameter Value)
AStartRecord	Start record that should be returned
AMaxRecords	Maximum number of records that should be returned

Returns: Existing DataSet, additionally containing the new DataTable

◆ Select() [2/3]

DataSet Ict.Common.DB.TDataBase.Select	(	DataSet	AFillDataSet,
		String	ASqlStatement,
		String	ADataTableName,
		TDBTransaction	AReadTransaction,
		System.Int32	AStartRecord,
		System.Int32	AMaxRecords
	)

inline

Puts a DataTable with the result of a given SQL statement into an existing DataSet. The SQL statement is executed in the given transaction context (which should have the desired IsolationLevel). Not suitable for parameterised SQL statements.

Parameters

AFillDataSet	Existing DataSet
ASqlStatement	SQL statement
ADataTableName	Name that the DataTable should get
AReadTransaction	Instantiated TDBTransaction with the desired IsolationLevel
AStartRecord	Start record that should be returned
AMaxRecords	Maximum number of records that should be returned

Returns: Existing DataSet, additionally containing the new DataTable

◆ Select() [3/3]

DataSet Ict.Common.DB.TDataBase.Select	(	String	ASqlStatement,
		String	ADataTableName,
		TDBTransaction	AReadTransaction,
		DbParameter[]	AParametersArray = `null`
	)

inline

Returns a DataSet containing a DataTable with the result of a given SQL statement. The SQL statement is executed in the given transaction context (which should have the desired IsolationLevel). Suitable for parameterised SQL statements.

Parameters

ASqlStatement	SQL statement
ADataTableName	Name that the DataTable should get
AReadTransaction	Instantiated TDBTransaction with the desired IsolationLevel
AParametersArray	An array holding 1..n instantiated DbParameters (eg. OdbcParameters) (including parameter Value)

Returns: Instantiated DataSet

◆ SelectDT() [1/3]

DataTable Ict.Common.DB.TDataBase.SelectDT	(	DataTable	ATypedDataTable,
		String	ASqlStatement,
		TDBTransaction	AReadTransaction,
		DbParameter[]	AParametersArray = `null`,
		int	AStartRecord = `0`,
		int	AMaxRecords = `0`
	)

inline

this loads the result into a typed datatable

Parameters

ATypedDataTable	this needs to be an object of the typed datatable
ASqlStatement
AReadTransaction
AParametersArray
AStartRecord	does not have any effect yet
AMaxRecords	not implemented yet

Returns

◆ SelectDT() [2/3]

DataTable Ict.Common.DB.TDataBase.SelectDT	(	String	ASqlStatement,
		String	ADataTableName,
		TDBTransaction	AReadTransaction
	)

inline

Returns a DataTable filled with the result of a given SQL statement. The SQL statement is executed in the given transaction context (which should have the desired IsolationLevel). Not suitable for parameterised SQL statements.

Parameters

ASqlStatement	SQL statement
ADataTableName	Name that the DataTable should get
AReadTransaction	Instantiated TDBTransaction with the desired IsolationLevel

Returns: Instantiated DataTable

◆ SelectDT() [3/3]

DataTable Ict.Common.DB.TDataBase.SelectDT	(	String	ASqlStatement,
		String	ADataTableName,
		TDBTransaction	AReadTransaction,
		DbParameter[]	AParametersArray
	)

inline

Returns a DataTable filled with the result of a given SQL statement. The SQL statement is executed in the given transaction context (which should have the desired IsolationLevel). Not suitable for parameterised SQL statements.

Parameters

ASqlStatement	SQL statement
ADataTableName	Name that the DataTable should get
AReadTransaction	Instantiated TDBTransaction with the desired IsolationLevel
AParametersArray	An array holding 1..n instantiated DbParameters (eg. OdbcParameters) (including parameter Value)

Returns: Instantiated DataTable

◆ SelectToTempTable()

DataSet Ict.Common.DB.TDataBase.SelectToTempTable	(	DataSet	AFillDataSet,
		String	ASqlStatement,
		String	ADataTempTableName,
		TDBTransaction	AReadTransaction,
		DbParameter[]	AParametersArray,
		System.Int32	AStartRecord,
		System.Int32	AMaxRecords
	)

inline

Puts a temp DataTable with the result of a given SQL statement into an existing DataSet. The SQL statement is executed in the given transaction context (which should have the desired IsolationLevel). Suitable for parameterised SQL statements.

Parameters

AFillDataSet	Existing DataSet
ASqlStatement	SQL statement
ADataTempTableName	Name that the temp DataTable should get
AReadTransaction	Instantiated TDBTransaction with the desired IsolationLevel
AParametersArray	An array holding 1..n instantiated DbParameters (eg. OdbcParameters) (including parameter Value)
AStartRecord	Start record that should be returned
AMaxRecords	Maximum number of records that should be returned

Returns: Existing DataSet, additionally containing the new DataTable

◆ SelectUsingDataAdapter()

int Ict.Common.DB.TDataBase.SelectUsingDataAdapter	(	String	ASqlStatement,
		TDBTransaction	AReadTransaction,
		ref DataTable	AFillDataTable,
		TOptionalColumnMappingDelegate	AOptionalColumnNameMapping = `null`,
		int	ASelectCommandTimeout = `-1`,
		DbParameter[]	AParametersArray = `null`
	)

inline

Executes a SQL Select Statement (once) using a DbDataAdapter.

Parameters

ASqlStatement	SQL statement. Must contain SQL Parameters if AParametersArray is specified!
AReadTransaction	Instantiated TDBTransaction with the desired IsolationLevel.
AFillDataTable	Instance of a DataTable. Can be null; in that case a DataTable by the name of "SelectUsingDataAdapter_DataTable" is created on-the-fly.
AOptionalColumnNameMapping	Supply a Delegate to create a mapping between the names of the fields in the DB and how they should be named in the resulting DataTable. (Optional - pass null for this Argument to not do that).
ASelectCommandTimeout	Set a timeout (in seconds) for the Select Command that is different from the default timeout for a Command (eg. 20s for a NpgsqlCommand). (Optional.)
AParametersArray	An array holding 1..n instantiated DbParameters (eg. OdbcParameters) (including parameter Value) for a Parameterised Query (Optional.)

Returns: The number of Rows successfully added or refreshed in the DataTable passed in using AFillDataTable (=return value of calling DbDataAdapter.Fill) - or -1 in case the creation of the internally used DataAdapter failed (should not happen).

◆ SelectUsingDataAdapterMulti()

int Ict.Common.DB.TDataBase.SelectUsingDataAdapterMulti	(	String	ASqlStatement,
		TDBTransaction	AReadTransaction,
		ref DataTable	AFillDataTable,
		TOptionalColumnMappingDelegate	AOptionalColumnNameMapping = `null`,
		int	ASelectCommandTimeout = `-1`,
		DbParameter[]	AParameterDefinitions = `null`,
		List< object[]>	AParameterValues = `null`,
		bool	APrepareSelectCommand = `false`,
		int	AProgressUpdateEveryNRecs = `0`,
		MultipleParamQueryProgressUpdateDelegate	AMultipleParamQueryProgressUpdateCallback = `null`
	)

inline

Executes a SQL Select Statement (1..n times if a Paramterised Query is used) using a DbDataAdapter.

In case AParameterValues holds more than one entry then the same parameterised query will be executed as many times as there are entries. The resulting data of all query executions gets appended to AFillDataTable !

Parameters

ASqlStatement	SQL statement. Must contain SQL Parameters if AParameterDefinitions is specified!
AReadTransaction	Instantiated TDBTransaction with the desired IsolationLevel.
AFillDataTable	Instance of a DataTable. Can be null; in that case a DataTable by the name of "SelectUsingDataAdapter_DataTable" is created on-the-fly.
AOptionalColumnNameMapping	Supply a Delegate to create a mapping between the names of the fields in the DB and how they should be named in the resulting DataTable. (Optional - pass null for this Argument to not do that).
ASelectCommandTimeout	Set a timeout (in seconds) for the Select Command that is different from the default timeout for a Command (eg. 20s for a NpgsqlCommand). (Optional.)
AParameterDefinitions	Instantiated DbParameters (eg. OdbcParameters) for a Parameterised Query. Only the the Types of the Parameters are relevant, the Values will be ignored (these will need to be passed in via the AParameterValues Argument!!! (Optional.)
AParameterValues	A List of Type object[] that contains 1..n Parameter Values in each array for 1..n executions (=number of List entries) of the same Parameterised Query using the internal DataAdapter.
APrepareSelectCommand	Set to true to 'Prepare' the Select Command in the RDBMS (if the RDBMS supports that). Because this only makes sense if AParameterValues holds more than one entry it will be ignored if AParameterValues is null or holds only one entry! (Optional, Default=false.).
AProgressUpdateEveryNRecs	Specifies the interval in which the Delegate passed in in Argument AMultipleParamQueryProgressUpdateCallback will get called (Optional if that Delegate isn't passed in./)
AMultipleParamQueryProgressUpdateCallback	The Delegate that should be called every 'AProgressUpdateEveryNRecs ' records while multiple Parameterised Query executions take place. This makes sense only when AParameterValues holds more than one Item. (Optional.)

Returns: The number of Rows successfully added or refreshed in the DataTable passed in using AFillDataTable (=return value of calling DbDataAdapter.Fill) - or -1 in case the creation of the internally used DataAdapter failed (should not happen). In case a Parameterised Query got executed several times (with multiple Parameter Values) then the return value is the added-up number of the calls to DbDataAdapter.Fill!

◆ TOptionalColumnMappingDelegate()

delegate string Ict.Common.DB.TDataBase.TOptionalColumnMappingDelegate ( ref IDictionaryEnumerator AColumNameMappingEnumerator )

Delegate for optional Column Mappings for use with SelectUsingDataAdapter.

Parameters

AColumNameMappingEnumerator Enumerator for the Column Mappings.

Returns: Column Mappings string.

◆ WriteTransaction()

void Ict.Common.DB.TDataBase.WriteTransaction	(	ref TDBTransaction	ATransaction,
		ref bool	ASubmitOK,
		Action	AEncapsulatedDBAccessCode
	)

inline

start a write transaction, and then run the code in it

Property Documentation

◆ ConnectionIdentifier

System.Guid Ict.Common.DB.TDataBase.ConnectionIdentifier

get

An identifier ('Globally Unique Identifier (GUID)') that uniquely identifies a DB Connection once it got created. It is used for internal 'sanity checks'. It also gets logged and hence can aid debugging (also useful for Unit Testing).

◆ ConnectionName

string Ict.Common.DB.TDataBase.ConnectionName

get

Name of the DB connection (optional). It gets logged and hence can aid debugging (also useful for Unit Testing).

◆ ConnectionOK

bool Ict.Common.DB.TDataBase.ConnectionOK

get

Tells whether it's save to execute any SQL command on the DB. It is updated when the DB connection's State changes.

◆ DBType

String Ict.Common.DB.TDataBase.DBType

get

Returns the type of the RDBMS that the current Instance of TDataBase is connect to.

◆ DsnOrServer

String Ict.Common.DB.TDataBase.DsnOrServer

get

return the filename or server name of the database

◆ LastDBAction

DateTime Ict.Common.DB.TDataBase.LastDBAction

get

Tells when the last Database action was carried out by the caller.

◆ Transaction

TDBTransaction Ict.Common.DB.TDataBase.Transaction

get

The current Transaction, if there is any.

◆ WaitingTimeForCoordinatedDBAccess

int Ict.Common.DB.TDataBase.WaitingTimeForCoordinatedDBAccess

get

Waiting time for 'Co-ordinated' (=Thread-safe) DB Access (in milliseconds).

Gets set from server.config file setting 'Server.DBWaitingTimeForCoordinatedDBAccess'. If that isn't present, then '3000' is the default (=3 seconds).

The documentation for this class was generated from the following file:

csharp/ICT/Common/DB/Database.cs

Public Member Functions

Static Public Member Functions

Properties

Detailed Description

Constructor & Destructor Documentation

◆ TDataBase() [1/2]

◆ TDataBase() [2/2]

Member Function Documentation

◆ BeginDbTransaction()

◆ BeginTransaction()

◆ CheckDBTransactionThreadIsCompatible()

◆ CheckEstablishedDBConnectionThreadIsCompatible()

◆ CheckRunningDBTransactionIsolationLevelIsCompatible()

◆ ClearTransaction()

◆ CloseDBConnection()

◆ Command()

◆ DataTableToXml()

◆ EstablishDBConnection() [1/2]

◆ EstablishDBConnection() [2/2]

◆ ExecuteNonQuery()

◆ ExecuteScalar()

◆ FormatSQLStatement()

◆ GetCache()

◆ GetCurrentSequenceValue()

◆ GetDBConnectionIdentifier()

◆ GetDBConnectionName()

◆ GetIsolationLevel()

◆ GetNewOrExistingTransaction()

◆ GetNextSequenceValue()

◆ GetThreadAndAppDomainCallInfoForDBConnectionEstablishmentAndDisconnection()

◆ HasAccess()

◆ LogSqlStatement()

◆ LogTable()

◆ MultipleParamQueryProgressUpdateDelegate()

◆ ReadSqlFile()

◆ ReadTransaction()

◆ RestartSequence()

◆ Select() [1/3]

◆ Select() [2/3]

◆ Select() [3/3]

◆ SelectDT() [1/3]

◆ SelectDT() [2/3]

◆ SelectDT() [3/3]

◆ SelectToTempTable()

◆ SelectUsingDataAdapter()

◆ SelectUsingDataAdapterMulti()

◆ TOptionalColumnMappingDelegate()

◆ WriteTransaction()

Property Documentation

◆ ConnectionIdentifier

◆ ConnectionName

◆ ConnectionOK

◆ DBType

◆ DsnOrServer

◆ LastDBAction

◆ Transaction

◆ WaitingTimeForCoordinatedDBAccess