ETLBox
Title here
Summary here
IConnectionManager
Interface IConnectionManager
Common properties and methods for all database connection managers
Inherited Members
Namespace: ETLBox
Assembly: ETLBox.dll
Syntax
public interface IConnectionManager : IDisposableProperties
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>, CancellationToken?)
Executes a query asynchronously against the database that doesn't return any data.
Declaration
Task<int> ExecuteNonQueryAsync(string command, IEnumerable<QueryParameter> parameterList = null, CancellationToken? cancellationToken = null)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
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>, 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
Task ExecuteReaderAsync(string commandText, IEnumerable<QueryParameter> parameterList = null, CancellationToken? cancellationToken = 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 |
| 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
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>, CancellationToken?)
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, CancellationToken? cancellationToken = null)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 |
GetSchema(string)
Declaration
TableDefinition GetSchema(string tableName)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
void Open()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
Task OpenAsync(CancellationToken? cancellationToken)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
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 |