DbConnectionManager<TConnection, TTransaction, TParameter>

Class DbConnectionManager<TConnection, TTransaction, TParameter>

The generic implementation on which all connection managers are based on

Implements
IConnectionManager<TConnection, TTransaction>
Namespace: ETLBox
Assembly: ETLBox.dll
Syntax
    public abstract class DbConnectionManager<TConnection, TTransaction, TParameter> : IConnectionManager<TConnection, TTransaction>, IConnectionManager, IDisposable where TConnection : class, IDbConnection, new() where TTransaction : class, IDbTransaction where TParameter : class, IDbDataParameter, new()
Type Parameters
NameDescription
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
    public DbConnectionManager()

DbConnectionManager(IDbConnectionString)

Declaration
    public DbConnectionManager(IDbConnectionString connectionString)
Parameters
TypeNameDescription
IDbConnectionStringconnectionString

Fields

_leaveOpen

Declaration
    protected bool _leaveOpen
Field Value
TypeDescription
bool

Properties

CommandTimeout

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

Declaration
    public int CommandTimeout { get; set; }
Property Value
TypeDescription
int

Compatibility

Information about compatibility of the current connector

Declaration
    public virtual string Compatibility { get; set; }
Property Value
TypeDescription
string

ConnectionString

The connection string used to establish the connection with the database

Declaration
    public IDbConnectionString ConnectionString { get; set; }
Property Value
TypeDescription
IDbConnectionString

ConnectionType

The database type for the connection manager.

Declaration
    public abstract ConnectionType ConnectionType { get; protected set; }
Property Value
TypeDescription
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
    public TConnection DbConnection { get; protected set; }
Property Value
TypeDescription
TConnection

HasTransaction

Indicates if the current connection has a transaction assigned

Declaration
    public bool HasTransaction { get; }
Property Value
TypeDescription
bool

IsInBulkInsert

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

Declaration
    public bool IsInBulkInsert { get; set; }
Property Value
TypeDescription
bool

IsOdbcOrOleDbConnection

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

Declaration
    public virtual bool IsOdbcOrOleDbConnection { get; }
Property Value
TypeDescription
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
    public bool LeaveOpen { get; set; }
Property Value
TypeDescription
bool

MaxLoginAttempts

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

Declaration
    public int MaxLoginAttempts { get; set; }
Property Value
TypeDescription
int

MaxParameterSizeBulkCopy

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

Declaration
    public virtual int MaxParameterSizeBulkCopy { get; set; }
Property Value
TypeDescription
int

MaxParameterSizeSql

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

Declaration
    public virtual int MaxParameterSizeSql { get; set; }
Property Value
TypeDescription
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
    public virtual string PP { get; protected set; }
Property Value
TypeDescription
string

QB

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

Declaration
    public abstract string QB { get; protected set; }
Property Value
TypeDescription
string

QE

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

Declaration
    public abstract string QE { get; protected set; }
Property Value
TypeDescription
string

State

The state of the underlying ADO.NET connection

Declaration
    public ConnectionState? State { get; }
Property Value
TypeDescription
ConnectionState?

SupportDatabases

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

Declaration
    public virtual bool SupportDatabases { get; protected set; }
Property Value
TypeDescription
bool

SupportIndexes

Indicates if database server does support indexes.

Declaration
    public virtual bool SupportIndexes { get; protected set; }
Property Value
TypeDescription
bool

SupportProcedures

Indicates if the database supports procedures

Declaration
    public virtual bool SupportProcedures { get; protected set; }
Property Value
TypeDescription
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
    public virtual bool SupportSchemas { get; protected set; }
Property Value
TypeDescription
bool

Transaction

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

Declaration
    public TTransaction Transaction { get; set; }
Property Value
TypeDescription
TTransaction

UseValueToSqlConversionFunc

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

Declaration
    public bool UseValueToSqlConversionFunc { get; }
Property Value
TypeDescription
bool

ValueToSqlConversionFunc

Declaration
    public Func<ConversionContext, string> ValueToSqlConversionFunc { get; }
Property Value
TypeDescription
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
    public 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
    public void BeginTransaction(IsolationLevel isolationLevel)
Parameters
TypeNameDescription
IsolationLevelisolationLevel

The isolation level for the transaction

BulkDelete(ITableData)

Performs a bulk delete

Declaration
    public abstract void BulkDelete(ITableData data)
Parameters
TypeNameDescription
ITableDatadata

Batch of data

BulkInsert(ITableData)

Performs a bulk insert

Declaration
    public abstract void BulkInsert(ITableData data)
Parameters
TypeNameDescription
ITableDatadata

Batch of data

BulkNonQuery(string, IEnumerable<TParameter>)

Declaration
    protected int BulkNonQuery(string commandText, IEnumerable<TParameter> parameterList)
Parameters
TypeNameDescription
stringcommandText
IEnumerable<TParameter>parameterList
Returns
TypeDescription
int

BulkNonQueryAsync(string, IEnumerable<TParameter>)

Declaration
    protected Task<int> BulkNonQueryAsync(string commandText, IEnumerable<TParameter> parameterList)
Parameters
TypeNameDescription
stringcommandText
IEnumerable<TParameter>parameterList
Returns
TypeDescription
Task<int>

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

Declaration
    protected void BulkReader(string commandText, IEnumerable<TParameter> parameterList, Action beforeRowReadAction, Action afterRowReadAction, params Action<object>[] rowActions)
Parameters
TypeNameDescription
stringcommandText
IEnumerable<TParameter>parameterList
ActionbeforeRowReadAction
ActionafterRowReadAction
Action<object>[]rowActions

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

Declaration
    protected Task BulkReaderAsync(string commandText, IEnumerable<TParameter> parameterList, Action beforeRowReadAction, Action afterRowReadAction, params Action<object>[] rowActions)
Parameters
TypeNameDescription
stringcommandText
IEnumerable<TParameter>parameterList
ActionbeforeRowReadAction
ActionafterRowReadAction
Action<object>[]rowActions
Returns
TypeDescription
Task

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

Performs a bulk select

Declaration
    public virtual void BulkSelect(ITableData data, ICollection<string> selectColumnNames, Action beforeRowReadAction, Action afterRowReadAction, params Action<object>[] actions)
Parameters
TypeNameDescription
ITableDatadata

Batch of data needed for the where condition

ICollection<string>selectColumnNames

Column names included in the select

ActionbeforeRowReadAction

Action invoked before any data is read

ActionafterRowReadAction

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
    public abstract void BulkUpdate(ITableData data, ICollection<string> setColumnNames, ICollection<string> joinColumnNames)
Parameters
TypeNameDescription
ITableDatadata

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
    public abstract void CleanUpBulkInsert(string tableName)
Parameters
TypeNameDescription
stringtableName

Destination table name

Clone()

Cretes a clone of the current connection manager

Declaration
    public abstract IConnectionManager Clone()
Returns
TypeDescription
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
    public IConnectionManager CloneIfAllowed()
Returns
TypeDescription
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
    public void Close()

CloseIfAllowed()

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

Declaration
    public void CloseIfAllowed()

CommitTransaction()

Commits the current tranasction.

Declaration
    public void CommitTransaction()

ConfigureByConnectionType(ConnectionType)

Declaration
    protected void ConfigureByConnectionType(ConnectionType connectionType)
Parameters
TypeNameDescription
ConnectionTypeconnectionType

CopyBaseAttributes(DbConnectionManager<TConnection, TTransaction, TParameter>)

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

Declaration
    public void CopyBaseAttributes(DbConnectionManager<TConnection, TTransaction, TParameter> original)
Parameters
TypeNameDescription
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
    public virtual void CreateDbConnection()

CreateTempTableOnCurrentConnection(ITableData)

Declaration
    protected string CreateTempTableOnCurrentConnection(ITableData data)
Parameters
TypeNameDescription
ITableDatadata
Returns
TypeDescription
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
    public void Dispose()

Dispose(bool)

Declaration
    protected virtual void Dispose(bool disposing)
Parameters
TypeNameDescription
booldisposing

DropTableOnCurrentConnection(string)

Declaration
    protected void DropTableOnCurrentConnection(string tableName)
Parameters
TypeNameDescription
stringtableName

ExecuteNonQuery(string, IEnumerable<QueryParameter>)

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

Declaration
    public int ExecuteNonQuery(string commandText, IEnumerable<QueryParameter> parameterList = null)
Parameters
TypeNameDescription
stringcommandText
IEnumerable<QueryParameter>parameterList

The optional list of parameters

Returns
TypeDescription
int

Number of affected rows.

ExecuteNonQueryAsync(string, IEnumerable<QueryParameter>)

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

Declaration
    public Task<int> ExecuteNonQueryAsync(string commandText, IEnumerable<QueryParameter> parameterList = null)
Parameters
TypeNameDescription
stringcommandText
IEnumerable<QueryParameter>parameterList

The optional list of parameters

Returns
TypeDescription
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
    public void ExecuteReader(string commandText, IEnumerable<QueryParameter> parameterList = null, int limit = 2147483647, Action beforeRowReadAction = null, Action afterRowReadAction = null, params Action<object>[] actions)
Parameters
TypeNameDescription
stringcommandText

The sql command

IEnumerable<QueryParameter>parameterList

The optional list of query parameters

intlimit

Maximum number of rows to read

ActionbeforeRowReadAction

This action is executed before reading the next row

ActionafterRowReadAction

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
    public Task ExecuteReaderAsync(string commandText, IEnumerable<QueryParameter> parameterList = null, int limit = 2147483647, Action beforeRowReadAction = null, Action afterRowReadAction = null, params Action<object>[] actions)
Parameters
TypeNameDescription
stringcommandText

The sql command

IEnumerable<QueryParameter>parameterList

The optional list of query parameters

intlimit

Maximum number of rows to read

ActionbeforeRowReadAction

This action is executed before reading the next row

ActionafterRowReadAction

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
TypeDescription
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
    public object ExecuteScalar(string commandText, IEnumerable<QueryParameter> parameterList = null)
Parameters
TypeNameDescription
stringcommandText
IEnumerable<QueryParameter>parameterList

The optional list of parameters

Returns
TypeDescription
object

The result

ExecuteScalarAsync(string, IEnumerable<QueryParameter>)

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

Declaration
    public Task<object> ExecuteScalarAsync(string commandText, IEnumerable<QueryParameter> parameterList = null)
Parameters
TypeNameDescription
stringcommandText
IEnumerable<QueryParameter>parameterList

The optional list of parameters

Returns
TypeDescription
Task<object>

The result

GetSchema(string)

Declaration
    public virtual TableDefinition GetSchema(string tableName)
Parameters
TypeNameDescription
stringtableName
Returns
TypeDescription
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
    public 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
    public Task OpenAsync()
Returns
TypeDescription
Task

OpenCoreAsync(bool)

Declaration
    public Task OpenCoreAsync(bool sync = false)
Parameters
TypeNameDescription
boolsync
Returns
TypeDescription
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
    public void OverrideConnectionSpecifics(ConnectionType connectionManagerType, string QB, string QE, string PP = &quot;@&quot;, int maxParameterSizeBulkCopy = 2147483647, int maxParameterSizeSql = 2147483647)
Parameters
TypeNameDescription
ConnectionTypeconnectionManagerType

The new connection type for this connection manager.

stringQB

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

stringQE

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

stringPP

Parameter placeholder ("@" for most databases)

intmaxParameterSizeBulkCopy

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

intmaxParameterSizeSql

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
    public abstract void PrepareBulkInsert(string tableName)
Parameters
TypeNameDescription
stringtableName

Destination table name

ReadSqlOutputIntoTableData(ITableData, string, bool)

Declaration
    protected void ReadSqlOutputIntoTableData(ITableData data, string sql, bool hasSequenceColumn = true)
Parameters
TypeNameDescription
ITableDatadata
stringsql
boolhasSequenceColumn

RollbackTransaction()

Rolls the current transaction back.

Declaration
    public 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
    public virtual void SetValueToSqlConversionFunc(Func<ConversionContext, string> valueToSqlConversionFunc)
Parameters
TypeNameDescription
Func<ConversionContext, string>valueToSqlConversionFunc

UseExistingDbConnection(IDbConnection)

Declaration
    public void UseExistingDbConnection(IDbConnection adoDbConnection)
Parameters
TypeNameDescription
IDbConnectionadoDbConnection

Implements