# DbConnectionManager {{< rawhtml >}}

Class DbConnectionManager<TConnection, TTransaction, TParameter>

The generic implementation on which all connection managers are based on

Inheritance
object
DbConnectionManager<TConnection, TTransaction, TParameter>
AdomdConnectionManager
ClickHouseConnectionManager
Db2ConnectionManager
MariaDbConnectionManager
MySqlConnectionManager
OdbcConnectionManager
OleDbConnectionManager
OracleConnectionManager
PostgresConnectionManager
SQLiteConnectionManager
AseConnectionManager
HanaConnectionManager
SnowflakeConnectionManager
SqlConnectionManager
Implements
IConnectionManager<TConnection, TTransaction>
IConnectionManager
IDisposable
Inherited Members
object.Equals(object)
object.Equals(object, object)
object.GetHashCode()
object.GetType()
object.MemberwiseClone()
object.ReferenceEquals(object, object)
object.ToString()
Namespace: ETLBox
Assembly: ETLBox.dll
Syntax
{{< /rawhtml >}} ```C# public abstract class DbConnectionManager : IConnectionManager, IConnectionManager, IDisposable where TConnection : class, IDbConnection, new() where TTransaction : class, IDbTransaction where TParameter : class, IDbDataParameter, new() ``` {{< rawhtml >}}
Type Parameters
Name Description
TConnection

The underlying ADO.NET connection
TTransaction

The transaction type used in the ADO.NET connector
TParameter

The parameter type used in the ADO.NET connector

Constructors

DbConnectionManager()

Declaration
{{< /rawhtml >}} ```C# public DbConnectionManager() ``` {{< rawhtml >}}

DbConnectionManager(IDbConnectionString)

Declaration
{{< /rawhtml >}} ```C# public DbConnectionManager(IDbConnectionString connectionString) ``` {{< rawhtml >}}
Parameters
Type Name Description
IDbConnectionString connectionString

Fields

_leaveOpen

Declaration
{{< /rawhtml >}} ```C# protected bool _leaveOpen ``` {{< rawhtml >}}
Field Value
Type Description
bool

Properties

CommandTimeout

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

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

Compatibility

Information about compatibility of the current connector

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

ConnectionString

The connection string used to establish the connection with the database

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

ConnectionType

The database type for the connection manager.

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

DbConnection

The underlying ADO.NET connection. Only read from this object and it's properties - by default, connections are always acquired from the connection pool.

Declaration
{{< /rawhtml >}} ```C# public TConnection DbConnection { get; set; } ``` {{< rawhtml >}}
Property Value
Type Description
TConnection

HasTransaction

Indicates if the current connection has a transaction assigned

Declaration
{{< /rawhtml >}} ```C# public 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# public 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# public virtual 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# public 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# public 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# public virtual int MaxParameterSizeBulkCopy { get; set; } ``` {{< rawhtml >}}
Property Value
Type Description
int

MaxParameterSizeSql

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

Declaration
{{< /rawhtml >}} ```C# public virtual int MaxParameterSizeSql { get; set; } ``` {{< 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# public virtual string PP { get; protected set; } ``` {{< 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# public abstract string QB { get; protected set; } ``` {{< 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# public abstract string QE { get; protected set; } ``` {{< rawhtml >}}
Property Value
Type Description
string

State

The state of the underlying ADO.NET connection

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

SupportIndexes

Indicates if database server does support indexes.

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

SupportProcedures

Indicates if the database supports procedures

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

Transaction

The current transaction. Use BeginTransaction() to start a transaction, and CommitTransaction() or RollbackTransaction() to commit or rollback.

Declaration
{{< /rawhtml >}} ```C# public TTransaction Transaction { get; set; } ``` {{< rawhtml >}}
Property Value
Type Description
TTransaction

UseValueToSqlConversionFunc

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

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

ValueToSqlConversionFunc

Declaration
{{< /rawhtml >}} ```C# public Func ValueToSqlConversionFunc { get; } ``` {{< rawhtml >}}
Property Value
Type Description
Func<ConversionContext, string>

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# public 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# public 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# public abstract void BulkDelete(ITableData data) ``` {{< rawhtml >}}
Parameters
Type Name Description
ITableData data

Batch of data

BulkInsert(ITableData)

Performs a bulk insert

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

Batch of data

BulkNonQuery(string, IEnumerable<TParameter>)

Declaration
{{< /rawhtml >}} ```C# protected int BulkNonQuery(string commandText, IEnumerable parameterList) ``` {{< rawhtml >}}
Parameters
Type Name Description
string commandText
IEnumerable<TParameter> parameterList
Returns
Type Description
int

BulkReader(string, IEnumerable<TParameter>, Action, Action, params Action<object>[])

Declaration
{{< /rawhtml >}} ```C# protected void BulkReader(string commandText, IEnumerable parameterList, Action beforeRowReadAction, Action afterRowReadAction, params Action[] rowActions) ``` {{< rawhtml >}}
Parameters
Type Name Description
string commandText
IEnumerable<TParameter> parameterList
Action beforeRowReadAction
Action afterRowReadAction
Action<object>[] rowActions

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

Performs a bulk select

Declaration
{{< /rawhtml >}} ```C# public virtual 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# public abstract 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# public abstract 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# public abstract 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# public 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# public 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# public void CloseIfAllowed() ``` {{< rawhtml >}}

CommitTransaction()

Commits the current tranasction.

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

ConfigureByConnectionType(ConnectionType)

Declaration
{{< /rawhtml >}} ```C# protected void ConfigureByConnectionType(ConnectionType connectionType) ``` {{< rawhtml >}}
Parameters
Type Name Description
ConnectionType connectionType

CopyBaseAttributes(DbConnectionManager<TConnection, TTransaction, TParameter>)

Copies the connection manager base attribnutes from the current connection manager to the target connection manager.

Declaration
{{< /rawhtml >}} ```C# public void CopyBaseAttributes(DbConnectionManager original) ``` {{< rawhtml >}}
Parameters
Type Name Description
DbConnectionManager<TConnection, TTransaction, TParameter> original

Target of the copy operation

CreateDbConnection()

By default, a db connection is created with the given connection string value. Override this method if you want to pass additional properties to the specific Ado.NET db connection.

Declaration
{{< /rawhtml >}} ```C# public virtual void CreateDbConnection() ``` {{< rawhtml >}}

CreateTempTableOnCurrentConnection(ITableData)

Declaration
{{< /rawhtml >}} ```C# protected string CreateTempTableOnCurrentConnection(ITableData data) ``` {{< rawhtml >}}
Parameters
Type Name Description
ITableData data
Returns
Type Description
string

Dispose()

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# public void Dispose() ``` {{< rawhtml >}}

Dispose(bool)

Declaration
{{< /rawhtml >}} ```C# protected virtual void Dispose(bool disposing) ``` {{< rawhtml >}}
Parameters
Type Name Description
bool disposing

DropTableOnCurrentConnection(string)

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

ExecuteNonQuery(string, IEnumerable<QueryParameter>)

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

Declaration
{{< /rawhtml >}} ```C# public int ExecuteNonQuery(string commandText, IEnumerable parameterList = null) ``` {{< rawhtml >}}
Parameters
Type Name Description
string commandText
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# public Task ExecuteNonQueryAsync(string commandText, IEnumerable parameterList = null, CancellationToken? cancellationToken = null) ``` {{< rawhtml >}}
Parameters
Type Name Description
string commandText
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# public 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# public 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# public object ExecuteScalar(string commandText, IEnumerable parameterList = null) ``` {{< rawhtml >}}
Parameters
Type Name Description
string commandText
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# public Task ExecuteScalarAsync(string commandText, IEnumerable parameterList = null, CancellationToken? cancellationToken = null) ``` {{< rawhtml >}}
Parameters
Type Name Description
string commandText
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# public virtual TableDefinition GetSchemaFromSql(string sql) ``` {{< rawhtml >}}
Parameters
Type Name Description
string sql
Returns
Type Description
TableDefinition

GetSchemaFromTableName(string)

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

OpenAsync()

Declaration
{{< /rawhtml >}} ```C# public Task OpenAsync() ``` {{< rawhtml >}}
Returns
Type Description
Task

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

An optional CancellationToken
Returns
Type Description
Task

OverrideConnectionSpecifics(ConnectionType, string, string, string, int, int)

Changes the connection manager type for the generic connector, so that you can try to use it with not supported setups. If you are looking for supported Odbc connection managers, try to use the specific connection managers (e.g. MySqlOdbcConnectionManager for MySql or PostgresOdbcConnectionManager for Postgres)

Declaration
{{< /rawhtml >}} ```C# public void OverrideConnectionSpecifics(ConnectionType connectionManagerType, string QB, string QE, string PP = "@", int maxParameterSizeBulkCopy = 2147483647, int maxParameterSizeSql = 2147483647) ``` {{< rawhtml >}}
Parameters
Type Name Description
ConnectionType connectionManagerType

The new connection type for this connection manager.
string QB

Quotation begin (e.g. "`" for MySql or "[" for SqlServer)
string QE

Quotation end (e.g. "`" for MySql or "]" for SqlServer)
string PP

Parameter placeholder ("@" for most databases)
int maxParameterSizeBulkCopy

Number of parameters allowed when running a bulk insert (columns*row per batch = number of parameters)
int maxParameterSizeSql

Number of parameters allowed for sql or non insert bulk operations (columns*row per batch = number of parameters)

PrepareBulkInsert(string)

Performs preparations needed to improved performance of a bulk insert operation

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

Destination table name

ReadSqlOutputIntoTableData(ITableData, string, bool)

Declaration
{{< /rawhtml >}} ```C# protected void ReadSqlOutputIntoTableData(ITableData data, string sql, bool hasSequenceColumn = true) ``` {{< rawhtml >}}
Parameters
Type Name Description
ITableData data
string sql
bool hasSequenceColumn

RollbackTransaction()

Rolls the current transaction back.

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

UseExistingDbConnection(IDbConnection)

Declaration
{{< /rawhtml >}} ```C# public void UseExistingDbConnection(IDbConnection adoDbConnection) ``` {{< rawhtml >}}
Parameters
Type Name Description
IDbConnection adoDbConnection

Implements

{{< /rawhtml >}}