Interface IConnectionManager

Common properties and methods for all database connection managers

Inherited Members

Namespace: ETLBox

Assembly: ETLBox.dll

Syntax

    public interface IConnectionManager : IDisposable

Properties

CommandTimeout

The timeout used when executing sql commands with this connection manager. Default is 0 (no timeout)

Declaration

    int CommandTimeout { get; set; }

Property Value

Type	Description
int

Compatibility

Information about compatibility of the current connector

Declaration

    string Compatibility { get; }

Property Value

Type	Description
string

ConnectionString

The connection string used to establish the connection with the database

Declaration

    IDbConnectionString ConnectionString { get; set; }

Property Value

Type	Description
IDbConnectionString

ConnectionType

The database type for the connection manager.

Declaration

    ConnectionType ConnectionType { get; }

Property Value

Type	Description
ConnectionType

HasTransaction

Indicates if the current connection has a transaction assigned

Declaration

    bool HasTransaction { get; }

Property Value

Type	Description
bool

IsInBulkInsert

Indicates if the current connection is currently used in a bulk insert operation (e.g. performed by a DbDestination)

Declaration

    bool IsInBulkInsert { get; set; }

Property Value

Type	Description
bool

IsOdbcOrOleDbConnection

Indicates if the current connection manager is used as a OleDb or Odbc Connection.

Declaration

    bool IsOdbcOrOleDbConnection { get; }

Property Value

Type	Description
bool

LeaveOpen

By default, after every sql operation the underlying ADO.NET connection is closed and retured to the ADO.NET connection pool. (This is the recommended behavior) To keep the connection open and avoid having the connection returned to the pool, set this to true. A connnection will be left open when a bulk insert operation is executed or a transaction hase been openend and not yet commited or rolled back.

Declaration

    bool LeaveOpen { get; set; }

Property Value

Type	Description
bool

MaxLoginAttempts

Number of attempts that the connection managers tries to connect before it decides that the database is not reachable.

Declaration

    int MaxLoginAttempts { get; set; }

Property Value

Type	Description
int

MaxParameterSizeBulkCopy

Returns the maximum amount of parameters that can be used for bulk inserts.

Declaration

    int MaxParameterSizeBulkCopy { get; }

Property Value

Type	Description
int

MaxParameterSizeSql

Returns the maximum amount of parameters that can be passed into a sql query.

Declaration

    int MaxParameterSizeSql { get; }

Property Value

Type	Description
int

PP

The character that is used in front of parameter names in query to identify the parameter. Most databases use the '@' character, some use ':'.

Declaration

    string PP { get; }

Property Value

Type	Description
string

QB

The quotation begin character that is used in the database. E.g. SqlServer uses: '[' and Postgres: '"'

Declaration

    string QB { get; }

Property Value

Type	Description
string

QE

The quotation end character that is used in the database. E.g. SqlServer uses: ']' and Postgres: '"'

Declaration

    string QE { get; }

Property Value

Type	Description
string

State

The state of the underlying ADO.NET connection

Declaration

    ConnectionState? State { get; }

Property Value

Type	Description
ConnectionState?

SupportDatabases

Indicates if database server does support multiple databases. A database in ETLBox means a schema in MySql.

Declaration

    bool SupportDatabases { get; }

Property Value

Type	Description
bool

SupportIndexes

Indicates if database server does support indexes.

Declaration

    bool SupportIndexes { get; }

Property Value

Type	Description
bool

SupportProcedures

Indicates if the database supports procedures

Declaration

    bool SupportProcedures { get; }

Property Value

Type	Description
bool

SupportSchemas

Indicates if the database supports schemas In MySql, this is false because the schema here is a database in ETLBox. Use SupportDatabases instead

Declaration

    bool SupportSchemas { get; }

Property Value

Type	Description
bool

UseValueToSqlConversionFunc

Indicates if a value to sql conversion function was set via SetValueToSqlConversionFunc(Func<ConversionContext, string>)

Declaration

    bool UseValueToSqlConversionFunc { get; }

Property Value

Type	Description
bool

Methods

BeginTransaction()

Will start a transaction with the default isolation level. This will leave the underlying ADO.NET connection open until the transaction is committed or rolled back.

Declaration

    void BeginTransaction()

BeginTransaction(IsolationLevel)

Will start a transaction with the given isolation level (if supported by the target database) This will leave the underlying ADO.NET connection open until the transaction is committed or rolled back.

Declaration

    void BeginTransaction(IsolationLevel isolationLevel)

Parameters

Type	Name	Description
IsolationLevel	isolationLevel	The isolation level for the transaction

BulkDelete(ITableData)

Performs a bulk delete

Declaration

    void BulkDelete(ITableData data)

Parameters

Type	Name	Description
ITableData	data	Batch of data

BulkInsert(ITableData)

Performs a bulk insert

Declaration

    void BulkInsert(ITableData data)

Parameters

Type	Name	Description
ITableData	data	Batch of data

BulkSelect(ITableData, ICollection<string>, Action, Action, params Action<object>[])

Performs a bulk select

Declaration

    void BulkSelect(ITableData data, ICollection<string> selectColumnNames, Action beforeRowReadAction, Action afterRowReadAction, params Action<object>[] actions)

Parameters

Type	Name	Description
ITableData	data	Batch of data needed for the where condition
ICollection<string>	selectColumnNames	Column names included in the select
Action	beforeRowReadAction	Action invoked before any data is read
Action	afterRowReadAction	Action invoked after all data is read
Action<object>[]	actions	Pass an action for each column

BulkUpdate(ITableData, ICollection<string>, ICollection<string>)

Performs a bulk update

Declaration

    void BulkUpdate(ITableData data, ICollection<string> setColumnNames, ICollection<string> joinColumnNames)

Parameters

Type	Name	Description
ITableData	data	Batch of data
ICollection<string>	setColumnNames	The column names used in the set part of the update statement
ICollection<string>	joinColumnNames	The column names to join for the update

CleanUpBulkInsert(string)

Called after the whole bulk insert operation to change back settings made to improve bulk insert performance

Declaration

    void CleanUpBulkInsert(string tableName)

Parameters

Type	Name	Description
string	tableName	Destination table name

Clone()

Cretes a clone of the current connection manager

Declaration

    IConnectionManager Clone()

Returns

Type	Description
IConnectionManager	A instance copy of the current connection manager

CloneIfAllowed()

Try to create a clone of the current connection - only possible if LeaveOpen is false.

Declaration

    IConnectionManager CloneIfAllowed()

Returns

Type	Description
IConnectionManager	The connection that was either cloned or the current connection

Close()

Closes the connection - this will not automatically disconnect from the database server, it will only return the connection to the ADO.NET connection pool for further reuse.

Declaration

    void Close()

CloseIfAllowed()

Closes the connection only if leave open is set to false and no transaction or bulk insert is in progress.

Declaration

    void CloseIfAllowed()

CommitTransaction()

Commits the current tranasction.

Declaration

    void CommitTransaction()

ExecuteNonQuery(string, IEnumerable<QueryParameter>)

Executes a query against the database that doesn't return any data.

Declaration

    int ExecuteNonQuery(string command, IEnumerable<QueryParameter> parameterList = null)

Parameters

Type	Name	Description
string	command	The sql command
IEnumerable<QueryParameter>	parameterList	The optional list of parameters

Returns

Type	Description
int	Number of affected rows.

ExecuteNonQueryAsync(string, IEnumerable<QueryParameter>)

Executes a query asynchronously against the database that doesn't return any data.

Declaration

    Task<int> ExecuteNonQueryAsync(string command, IEnumerable<QueryParameter> parameterList = null)

Parameters

Type	Name	Description
string	command	The sql command
IEnumerable<QueryParameter>	parameterList	The optional list of parameters

Returns

Type	Description
Task<int>	Number of affected rows.

ExecuteReader(string, IEnumerable<QueryParameter>, int, Action, Action, params Action<object>[])

Executes a query against the database that does return multiple rows in multiple columns. Define a read action for each columns of your result set.

Declaration

    void ExecuteReader(string commandText, IEnumerable<QueryParameter> parameterList = null, int limit = 2147483647, Action beforeRowReadAction = null, Action afterRowReadAction = null, params Action<object>[] actions)

Parameters

Type	Name	Description
string	commandText	The sql command
IEnumerable<QueryParameter>	parameterList	The optional list of query parameters
int	limit	Maximum number of rows to read
Action	beforeRowReadAction	This action is executed before reading the next row
Action	afterRowReadAction	This action is executed after reading a row
Action<object>[]	actions	Every column in the result set will call an action with the value of the current row. The order of the columns corresponds with the order of the passed actions.

ExecuteReaderAsync(string, IEnumerable<QueryParameter>, int, Action, Action, params Action<object>[])

Executes a query asynchronously against the database that does return multiple rows in multiple columns. Define a read action for each columns of your result set.

Declaration

    Task ExecuteReaderAsync(string commandText, IEnumerable<QueryParameter> parameterList = null, int limit = 2147483647, Action beforeRowReadAction = null, Action afterRowReadAction = null, params Action<object>[] actions)

Parameters

Type	Name	Description
string	commandText	The sql command
IEnumerable<QueryParameter>	parameterList	The optional list of query parameters
int	limit	Maximum number of rows to read
Action	beforeRowReadAction	This action is executed before reading the next row
Action	afterRowReadAction	This action is executed after reading a row
Action<object>[]	actions	Every column in the result set will call an action with the value of the current row. The order of the columns corresponds with the order of the passed actions.

Returns

Type	Description
Task	A data reader to iterate through the result set

ExecuteScalar(string, IEnumerable<QueryParameter>)

Executes a query against the database that returns a single row in a single column.

Declaration

    object ExecuteScalar(string command, IEnumerable<QueryParameter> parameterList = null)

Parameters

Type	Name	Description
string	command	The sql command
IEnumerable<QueryParameter>	parameterList	The optional list of parameters

Returns

Type	Description
object	The result

ExecuteScalarAsync(string, IEnumerable<QueryParameter>)

Executes a query asynchronously against the database that returns a single row in a single column.

Declaration

    Task<object> ExecuteScalarAsync(string command, IEnumerable<QueryParameter> parameterList = null)

Parameters

Type	Name	Description
string	command	The sql command
IEnumerable<QueryParameter>	parameterList	The optional list of parameters

Returns

Type	Description
Task<object>	The result

Open()

Opens the connection to the database. Normally you don't have to do this on your own, as all tasks and components will try to open a connection if no open connection is found.

Declaration

    void Open()

OpenAsync()

Opens the connection to the database asynchrously. Normally you don't have to do this on your own, as all tasks and components will try to open a connection if no open connection is found.

Declaration

    Task OpenAsync()

Returns

Type	Description
Task

PrepareBulkInsert(string)

Performs preparations needed to improved performance of a bulk insert operation

Declaration

    void PrepareBulkInsert(string tableName)

Parameters

Type	Name	Description
string	tableName	Destination table name

RollbackTransaction()

Rolls the current transaction back.

Declaration

    void RollbackTransaction()

SetValueToSqlConversionFunc(Func<ConversionContext, string>)

If sql is used to insert/update/delete data into the destination tables, the values are injected into the sql via parameters. If a value to sql conversion func is set, the parameters are bypassed and the sql can be influenced directly with this function.

Declaration

    void SetValueToSqlConversionFunc(Func<ConversionContext, string> valueToSqlConversionFunc)

Parameters

Type	Name	Description
Func<ConversionContext, string>	valueToSqlConversionFunc