DbConnectionManager<TConnection, TTransaction, TParameter>
Class DbConnectionManager<TConnection, TTransaction, TParameter>
The generic implementation on which all connection managers are based on
Inheritance
Inherited Members
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
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
public DbConnectionManager()
DbConnectionManager(IDbConnectionString)
Declaration
public DbConnectionManager(IDbConnectionString connectionString)
Parameters
Type | Name | Description |
---|---|---|
IDbConnectionString | connectionString |
Fields
_leaveOpen
Declaration
protected bool _leaveOpen
Field Value
Type | Description |
---|---|
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
Type | Description |
---|---|
int |
Compatibility
Information about compatibility of the current connector
Declaration
public virtual string Compatibility { get; set; }
Property Value
Type | Description |
---|---|
string |
ConnectionString
The connection string used to establish the connection with the database
Declaration
public IDbConnectionString ConnectionString { get; set; }
Property Value
Type | Description |
---|---|
IDbConnectionString |
ConnectionType
The database type for the connection manager.
Declaration
public abstract ConnectionType ConnectionType { get; protected set; }
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
public TConnection DbConnection { get; protected set; }
Property Value
Type | Description |
---|---|
TConnection |
HasTransaction
Indicates if the current connection has a transaction assigned
Declaration
public 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
public 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
public virtual 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
public 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
public int MaxLoginAttempts { get; set; }
Property Value
Type | Description |
---|---|
int |
MaxParameterSizeBulkCopy
Returns the maximum amount of parameters that can be used for bulk inserts.
Declaration
public virtual int MaxParameterSizeBulkCopy { get; set; }
Property Value
Type | Description |
---|---|
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
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
public virtual string PP { get; protected set; }
Property Value
Type | Description |
---|---|
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
Type | Description |
---|---|
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
Type | Description |
---|---|
string |
State
The state of the underlying ADO.NET connection
Declaration
public 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
public virtual bool SupportDatabases { get; protected set; }
Property Value
Type | Description |
---|---|
bool |
SupportIndexes
Indicates if database server does support indexes.
Declaration
public virtual bool SupportIndexes { get; protected set; }
Property Value
Type | Description |
---|---|
bool |
SupportProcedures
Indicates if the database supports procedures
Declaration
public virtual bool SupportProcedures { get; protected set; }
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
public virtual bool SupportSchemas { get; protected set; }
Property Value
Type | Description |
---|---|
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
Type | Description |
---|---|
TTransaction |
UseValueToSqlConversionFunc
Indicates if a value to sql conversion function was set via SetValueToSqlConversionFunc(Func<ConversionContext, string>)
Declaration
public bool UseValueToSqlConversionFunc { get; }
Property Value
Type | Description |
---|---|
bool |
ValueToSqlConversionFunc
Declaration
public Func<ConversionContext, string> ValueToSqlConversionFunc { get; }
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
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
Type | Name | Description |
---|---|---|
IsolationLevel | isolationLevel | The isolation level for the transaction |
BulkDelete(ITableData)
Performs a bulk delete
Declaration
public abstract void BulkDelete(ITableData data)
Parameters
Type | Name | Description |
---|---|---|
ITableData | data | Batch of data |
BulkInsert(ITableData)
Performs a bulk insert
Declaration
public abstract void BulkInsert(ITableData data)
Parameters
Type | Name | Description |
---|---|---|
ITableData | data | Batch of data |
BulkNonQuery(string, IEnumerable<TParameter>)
Declaration
protected int BulkNonQuery(string commandText, IEnumerable<TParameter> parameterList)
Parameters
Type | Name | Description |
---|---|---|
string | commandText | |
IEnumerable<TParameter> | parameterList |
Returns
Type | Description |
---|---|
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
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
public virtual 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
public abstract 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
public abstract void CleanUpBulkInsert(string tableName)
Parameters
Type | Name | Description |
---|---|---|
string | tableName | Destination table name |
Clone()
Cretes a clone of the current connection manager
Declaration
public abstract 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
public 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
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
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
public void CopyBaseAttributes(DbConnectionManager<TConnection, TTransaction, TParameter> original)
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
public virtual void CreateDbConnection()
CreateTempTableOnCurrentConnection(ITableData)
Declaration
protected string CreateTempTableOnCurrentConnection(ITableData data)
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
public void Dispose()
Dispose(bool)
Declaration
protected virtual void Dispose(bool disposing)
Parameters
Type | Name | Description |
---|---|---|
bool | disposing |
DropTableOnCurrentConnection(string)
Declaration
protected void DropTableOnCurrentConnection(string tableName)
Parameters
Type | Name | Description |
---|---|---|
string | tableName |
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
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
public Task<int> ExecuteNonQueryAsync(string commandText, IEnumerable<QueryParameter> parameterList = null, CancellationToken? cancellationToken = null)
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
public 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
public 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
public object ExecuteScalar(string commandText, IEnumerable<QueryParameter> parameterList = null)
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
public Task<object> ExecuteScalarAsync(string commandText, IEnumerable<QueryParameter> parameterList = null, CancellationToken? cancellationToken = null)
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 |
GetSchema(string)
Declaration
public virtual 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
public void Open()
OpenAsync()
Declaration
public Task OpenAsync()
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
public Task OpenAsync(CancellationToken? cancellationToken)
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
public void OverrideConnectionSpecifics(ConnectionType connectionManagerType, string QB, string QE, string PP = "@", int maxParameterSizeBulkCopy = 2147483647, int maxParameterSizeSql = 2147483647)
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
public abstract void PrepareBulkInsert(string tableName)
Parameters
Type | Name | Description |
---|---|---|
string | tableName | Destination table name |
ReadSqlOutputIntoTableData(ITableData, string, bool)
Declaration
protected void ReadSqlOutputIntoTableData(ITableData data, string sql, bool hasSequenceColumn = true)
Parameters
Type | Name | Description |
---|---|---|
ITableData | data | |
string | sql | |
bool | hasSequenceColumn |
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
Type | Name | Description |
---|---|---|
Func<ConversionContext, string> | valueToSqlConversionFunc |
UseExistingDbConnection(IDbConnection)
Declaration
public void UseExistingDbConnection(IDbConnection adoDbConnection)
Parameters
Type | Name | Description |
---|---|---|
IDbConnection | adoDbConnection |