# IConnectionManager {{< rawhtml >}}

Interface IConnectionManager

Common properties and methods for all database connection managers

Inherited Members
IDisposable.Dispose()
Namespace: ETLBox
Assembly: ETLBox.dll
Syntax
{{< /rawhtml >}} ```C# public interface IConnectionManager : IDisposable ``` {{< rawhtml >}}

Properties

CommandTimeout

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

Declaration
{{< /rawhtml >}} ```C# int CommandTimeout { get; set; } ``` {{< rawhtml >}}
Property Value
Type Description
int

Compatibility

Information about compatibility of the current connector

Declaration
{{< /rawhtml >}} ```C# string Compatibility { get; } ``` {{< rawhtml >}}
Property Value
Type Description
string

ConnectionString

The connection string used to establish the connection with the database

Declaration
{{< /rawhtml >}} ```C# IDbConnectionString ConnectionString { get; set; } ``` {{< rawhtml >}}
Property Value
Type Description
IDbConnectionString

ConnectionType

The database type for the connection manager.

Declaration
{{< /rawhtml >}} ```C# ConnectionType ConnectionType { get; } ``` {{< rawhtml >}}
Property Value
Type Description
ConnectionType

HasTransaction

Indicates if the current connection has a transaction assigned

Declaration
{{< /rawhtml >}} ```C# bool HasTransaction { get; } ``` {{< rawhtml >}}
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
{{< /rawhtml >}} ```C# bool IsInBulkInsert { get; set; } ``` {{< rawhtml >}}
Property Value
Type Description
bool

IsOdbcOrOleDbConnection

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

Declaration
{{< /rawhtml >}} ```C# bool IsOdbcOrOleDbConnection { get; } ``` {{< rawhtml >}}
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
{{< /rawhtml >}} ```C# bool LeaveOpen { get; set; } ``` {{< rawhtml >}}
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
{{< /rawhtml >}} ```C# int MaxLoginAttempts { get; set; } ``` {{< rawhtml >}}
Property Value
Type Description
int

MaxParameterSizeBulkCopy

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

Declaration
{{< /rawhtml >}} ```C# int MaxParameterSizeBulkCopy { get; } ``` {{< rawhtml >}}
Property Value
Type Description
int

MaxParameterSizeSql

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

Declaration
{{< /rawhtml >}} ```C# int MaxParameterSizeSql { get; } ``` {{< rawhtml >}}
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
{{< /rawhtml >}} ```C# string PP { get; } ``` {{< rawhtml >}}
Property Value
Type Description
string

QB

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

Declaration
{{< /rawhtml >}} ```C# string QB { get; } ``` {{< rawhtml >}}
Property Value
Type Description
string

QE

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

Declaration
{{< /rawhtml >}} ```C# string QE { get; } ``` {{< rawhtml >}}
Property Value
Type Description
string

State

The state of the underlying ADO.NET connection

Declaration
{{< /rawhtml >}} ```C# ConnectionState? State { get; } ``` {{< rawhtml >}}
Property Value
Type Description
ConnectionState?

SupportDatabases

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

Declaration
{{< /rawhtml >}} ```C# bool SupportDatabases { get; } ``` {{< rawhtml >}}
Property Value
Type Description
bool

SupportIndexes

Indicates if database server does support indexes.

Declaration
{{< /rawhtml >}} ```C# bool SupportIndexes { get; } ``` {{< rawhtml >}}
Property Value
Type Description
bool

SupportProcedures

Indicates if the database supports procedures

Declaration
{{< /rawhtml >}} ```C# bool SupportProcedures { get; } ``` {{< rawhtml >}}
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
{{< /rawhtml >}} ```C# bool SupportSchemas { get; } ``` {{< rawhtml >}}
Property Value
Type Description
bool

UseValueToSqlConversionFunc

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

Declaration
{{< /rawhtml >}} ```C# bool UseValueToSqlConversionFunc { get; } ``` {{< rawhtml >}}
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
{{< /rawhtml >}} ```C# void BeginTransaction() ``` {{< rawhtml >}}

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
{{< /rawhtml >}} ```C# void BeginTransaction(IsolationLevel isolationLevel) ``` {{< rawhtml >}}
Parameters
Type Name Description
IsolationLevel isolationLevel

The isolation level for the transaction

BulkDelete(ITableData)

Performs a bulk delete

Declaration
{{< /rawhtml >}} ```C# void BulkDelete(ITableData data) ``` {{< rawhtml >}}
Parameters
Type Name Description
ITableData data

Batch of data

BulkInsert(ITableData)

Performs a bulk insert

Declaration
{{< /rawhtml >}} ```C# void BulkInsert(ITableData data) ``` {{< rawhtml >}}
Parameters
Type Name Description
ITableData data

Batch of data

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

Performs a bulk select

Declaration
{{< /rawhtml >}} ```C# void BulkSelect(ITableData data, ICollection selectColumnNames, Action beforeRowReadAction, Action afterRowReadAction, params Action[] actions) ``` {{< rawhtml >}}
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
{{< /rawhtml >}} ```C# void BulkUpdate(ITableData data, ICollection setColumnNames, ICollection joinColumnNames) ``` {{< rawhtml >}}
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
{{< /rawhtml >}} ```C# void CleanUpBulkInsert(string tableName) ``` {{< rawhtml >}}
Parameters
Type Name Description
string tableName

Destination table name

Clone()

Cretes a clone of the current connection manager

Declaration
{{< /rawhtml >}} ```C# IConnectionManager Clone() ``` {{< rawhtml >}}
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
{{< /rawhtml >}} ```C# IConnectionManager CloneIfAllowed() ``` {{< rawhtml >}}
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
{{< /rawhtml >}} ```C# void Close() ``` {{< rawhtml >}}

CloseIfAllowed()

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

Declaration
{{< /rawhtml >}} ```C# void CloseIfAllowed() ``` {{< rawhtml >}}

CommitTransaction()

Commits the current tranasction.

Declaration
{{< /rawhtml >}} ```C# void CommitTransaction() ``` {{< rawhtml >}}

ExecuteNonQuery(string, IEnumerable<QueryParameter>)

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

Declaration
{{< /rawhtml >}} ```C# int ExecuteNonQuery(string command, IEnumerable parameterList = null) ``` {{< rawhtml >}}
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>, CancellationToken?)

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

Declaration
{{< /rawhtml >}} ```C# Task ExecuteNonQueryAsync(string command, IEnumerable parameterList = null, CancellationToken? cancellationToken = null) ``` {{< rawhtml >}}
Parameters
Type Name Description
string command

The sql command
IEnumerable<QueryParameter> parameterList

The optional list of parameters
CancellationToken? cancellationToken

An optional CancellationToken
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
{{< /rawhtml >}} ```C# void ExecuteReader(string commandText, IEnumerable parameterList = null, int limit = 2147483647, Action beforeRowReadAction = null, Action afterRowReadAction = null, params Action[] actions) ``` {{< rawhtml >}}
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>, CancellationToken?, 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
{{< /rawhtml >}} ```C# Task ExecuteReaderAsync(string commandText, IEnumerable parameterList = null, CancellationToken? cancellationToken = null, int limit = 2147483647, Action beforeRowReadAction = null, Action afterRowReadAction = null, params Action[] actions) ``` {{< rawhtml >}}
Parameters
Type Name Description
string commandText

The sql command
IEnumerable<QueryParameter> parameterList

The optional list of query parameters
CancellationToken? cancellationToken

An optional CancellationToken
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
{{< /rawhtml >}} ```C# object ExecuteScalar(string command, IEnumerable parameterList = null) ``` {{< rawhtml >}}
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>, CancellationToken?)

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

Declaration
{{< /rawhtml >}} ```C# Task ExecuteScalarAsync(string command, IEnumerable parameterList = null, CancellationToken? cancellationToken = null) ``` {{< rawhtml >}}
Parameters
Type Name Description
string command

The sql command
IEnumerable<QueryParameter> parameterList

The optional list of parameters
CancellationToken? cancellationToken

An optional CancellationToken
Returns
Type Description
Task<object>

The result

GetSchemaFromSql(string)

Declaration
{{< /rawhtml >}} ```C# TableDefinition GetSchemaFromSql(string sql) ``` {{< rawhtml >}}
Parameters
Type Name Description
string sql
Returns
Type Description
TableDefinition

GetSchemaFromTableName(string)

Declaration
{{< /rawhtml >}} ```C# TableDefinition GetSchemaFromTableName(string tableName) ``` {{< rawhtml >}}
Parameters
Type Name Description
string tableName
Returns
Type Description
TableDefinition

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
{{< /rawhtml >}} ```C# void Open() ``` {{< rawhtml >}}

OpenAsync(CancellationToken?)

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
{{< /rawhtml >}} ```C# Task OpenAsync(CancellationToken? cancellationToken) ``` {{< rawhtml >}}
Parameters
Type Name Description
CancellationToken? cancellationToken

An optional CancellationToken
Returns
Type Description
Task

PrepareBulkInsert(string)

Performs preparations needed to improved performance of a bulk insert operation

Declaration
{{< /rawhtml >}} ```C# void PrepareBulkInsert(string tableName) ``` {{< rawhtml >}}
Parameters
Type Name Description
string tableName

Destination table name

RollbackTransaction()

Rolls the current transaction back.

Declaration
{{< /rawhtml >}} ```C# void RollbackTransaction() ``` {{< rawhtml >}}

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
{{< /rawhtml >}} ```C# void SetValueToSqlConversionFunc(Func valueToSqlConversionFunc) ``` {{< rawhtml >}}
Parameters
Type Name Description
Func<ConversionContext, string> valueToSqlConversionFunc
{{< /rawhtml >}}