Class AdbConnection

An Adb database connection, similar to a DbConnection, including creating database commands, with extra information and functionality. It is created by an AdbConnectionBuilder instance.

This class opens and closes connections in the same way as the underlying .NET DbConnection, and is the normal type of connection to use for database access. AdbKeepOpenConnection is available for the (rare) cases when a connection should stay open across multiple workers.

Note: The AdbConnection instance is not thread-safe, and must only be used in a single place (in a single worker) at any one time.

Note: Database specific drivers, e.g. SqlClient and MySqlClient, sets properties like DatabaseName from the ConnectionString, before the connection is open, and sets them again with values from the database server after the connection is open. Universal drivers like ODBC do not set these properties from the connection string, and only sets them after the connection is open.

The connection can be opened and closed multiple times as needed, and it must be disposed after use. Disposal should almost always be done by whoever created the connection, typically via DisposeOnFinished<TDisposable>(TDisposable) or UsingActionWorker<TDisposable> if used across multiple method calls, or by a using statement if created and disposed in a single method and thread, without being passed to any other workers. See Disposing Disposables for details.

Inheritance

Object

AdbConnection

AdbKeepOpenConnection

Implements

IAdbConnection

IDisposable

Namespace: actionETL.Adb

Assembly: actionETL.dll

Syntax

public class AdbConnection : IAdbConnection, IDisposable

Remarks

Do design database workers to take AdbConnectionString, IAdbConnectionBuilder, and AdbCommandBuilder instances as parameters, which allows them to support connections with different AdbConnectionMode. Conversely, avoid passing AdbConnection, AdbKeepOpenConnection, AdbCommand, or connection (text) strings to workers.

Properties

ConnectionString

Gets the string used to open the connection.

Note that setting the connection string is done via IAdbConnectionBuilder or AdbConnectionString.

Note: Database specific drivers, e.g. SqlClient and MySqlClient, sets properties like DatabaseName from the ConnectionString, before the connection is open, and sets them again with values from the database server after the connection is open. Universal drivers like ODBC do not set these properties from the connection string, and only sets them after the connection is open.

Declaration

public string ConnectionString { get; }

Property Value

Type	Description
String

ConnectionTimeout

Gets the time (in seconds) to wait while trying to establish a connection before terminating the attempt and generating an error.

The default value is data source dependent; a common value is 15 seconds.

Declaration

public int ConnectionTimeout { get; }

Property Value

Type	Description
Int32

DatabaseName

Gets the unquoted name of the current database after a connection is opened.

Before the connection is open, depending on the driver, either gets the database name specified in the connection string (with whatever quoting it had there), or an empty string.

Declaration

public string DatabaseName { get; }

Property Value

Type	Description
String

DataSourceName

Gets the name of the database server to which to connect.

Before the connection is open, depending on the driver, either gets the database server name specified in the connection string (with whatever quoting it had there), or an empty string.

Declaration

public string DataSourceName { get; }

Property Value

Type	Description
String

Information

Provides and helps process the information about a data source behind a connection, such as what character to use for quoting, how to specify parameter markers etc. This is used to write provider-independent database code.

Note that when accessing this property the first time, the connection will if needed be opened before information is retrieved, and closed again if it was not open before the property was accessed, leaving it in the original state on return. After the first time, a cached instance will be returned.

Also see the TableInformation property.

Declaration

public AdbDataSourceInformation Information { get; }

Property Value

Type	Description
AdbDataSourceInformation	The data source information.

Exceptions

Type	Condition
Exception	See the provider documentation for which specific exceptions Open() can throw, it typically includes DbException and InvalidOperationException.
InvalidOperationException	May throw if the connection is associated with a transaction.
NotImplementedException	Not implemented for provider.

NumberOfColumnIdentifierParts

Gets the number of column identifier parts. E.g., if column name, table name, schema name, and catalog name are all available, this property would return 4.

Declaration

public int NumberOfColumnIdentifierParts { get; }

Property Value

Type	Description
Int32

NumberOfColumnRestrictions

Gets the number of column restrictions available when using GetSchema(String, String[]), see https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/retrieving-database-schema-information for details.

Declaration

public int NumberOfColumnRestrictions { get; }

Property Value

Type	Description
Int32

Provider

Gets the Adb provider that created this connection.

Declaration

public AdbProvider Provider { get; }

Property Value

Type	Description
AdbProvider

SchemaInformationCollection

Gets the database provider meta-data schema collections, which can be used to retrieve a schema collection with GetSchema(String) or its overloads.

Note that when accessing this property the first time, the connection will if needed be opened before information is retrieved, and closed again if it was closed before the property was accessed. After the first time, a cached instance will be returned.

Declaration

public IReadOnlyList<AdbSchemaInformation> SchemaInformationCollection { get; }

Property Value

Type	Description
IReadOnlyList<AdbSchemaInformation>

Exceptions

Type	Condition
Exception	See the provider documentation for which specific exceptions Open() can throw, it typically includes DbException and InvalidOperationException.
InvalidOperationException	May throw if the connection is associated with a transaction (this is provider-specific).

ServerVersion

On an open connection, gets a string that represents the version of the server to which the connection is connected to.

Declaration

public string ServerVersion { get; }

Property Value

Type	Description
String

Exceptions

Type	Condition
InvalidOperationException	Connection was closed.

State

Gets an enum that describes the state of the connection.

Declaration

public ConnectionState State { get; }

Property Value

Type	Description
ConnectionState

TableCommand

An optional service that executes predefined database table commands (truncate, drop, delete all rows). Will be null for providers that don't support it; always check that the property is not null before using it (if you have a connection) or that HasTableCommandService is true (if checking before you have a connection).

Also see the TableInformation and Information properties.

Declaration

public AdbTableCommand TableCommand { get; }

Property Value

Type	Description
AdbTableCommand

TableInformation

An optional service that manipulates table identifiers and provides information about tables, such as what tables and columns are present. Will be null for providers that don't support it; always check that the property is not null before using it (if you have a connection) or that HasTableInformationService is true (if checking before you have a connection).

Also see the TableCommand and Information properties.

Declaration

public AdbTableInformation TableInformation { get; }

Property Value

Type	Description
AdbTableInformation

Transaction

The currently active AdbTransaction returned from the last BeginTransaction() call, or null if no transaction is currently active.

Any active transaction is removed when either of its Commit(), Rollback(), or Dispose() methods are called.

Declaration

public AdbTransaction Transaction { get; }

Property Value

Type	Description
AdbTransaction

UnderlyingConnection

Gets the underlying .NET DbConnection.

Note: Always use the members on AdbConnection instead of this UnderlyingConnection wherever possible. In rare cases however, the underlying .NET DbConnection is needed, e.g. when accessing provider-specific functionality not covered by AdbConnection.

Declaration

public DbConnection UnderlyingConnection { get; }

Property Value

Type	Description
DbConnection

Methods

BeginTransaction()

Starts a database transaction, using the default isolation level for the specific type of connection used. As long as it is active, any commands created from this connection will automatically use this transaction, which (when the command is created after the transaction is created) removes the need to explicitly set the AdbCommand.Transaction property.

You must ensure that the transaction is later either committed or rolled back by using the transaction Commit() or Rollback() methods, or by closing or disposing the connection, which will roll back the transaction.

Do consider if the default isolation level is appropriate, in terms of performance and correctness.

Declaration

public virtual AdbTransaction BeginTransaction()

Returns

Type	Description
AdbTransaction	An object representing the new transaction.

Exceptions

Type	Condition
Exception	(See the provider documentation for specific exceptions.)

BeginTransaction(IsolationLevel)

Starts a database transaction. As long as it is active, any commands created from this connection will automatically use this transaction, which (when the command is created after the transaction is created) removes the need to explicitly set the AdbCommand.Transaction property.

You must ensure that the transaction is later either committed or rolled back by using the transaction Commit() or Rollback() methods, or by closing or disposing the connection, which will roll back the transaction.

Declaration

public virtual AdbTransaction BeginTransaction(IsolationLevel isolationLevel)

Parameters

Type	Name	Description
IsolationLevel	isolationLevel	Specifies the isolation level for the transaction. Do consider what to set it to, since this can affect both performance and correctness.

Returns

Type	Description
AdbTransaction	An object representing the new transaction.

Exceptions

Type	Condition
Exception	(See the provider documentation for specific exceptions.)

Close()

Closes the connection to the database, which will also roll back any active transaction. This is the preferred method of closing any open connection.

An application can call this method multiple times, without generating any exception.

Declaration

public virtual void Close()

Exceptions

Type	Condition
DbException	The connection-level error that occurred while opening the connection.

CreateCommand()

Creates an Adb database command with CommandType set to Text, initialized with the connection.

Note that the command query must be set separately.

Declaration

public virtual AdbCommand CreateCommand()

Returns

Type	Description
AdbCommand	A database command, initialized with the connection. The command must be disposed after its use; the associated connection will not be automatically disposed at the same time.

CreateCommand(String)

Creates an Adb database command with CommandType set to Text, initialized with the connection.

Declaration

public virtual AdbCommand CreateCommand(string queryText)

Parameters

Type	Name	Description
String	queryText	The query text.

Returns

Type	Description
AdbCommand	A database command, initialized with the connection. The command must be disposed after its use; the associated connection will not be automatically disposed at the same time.

CreateCommand(String, CommandType)

Creates an Adb database command, initialized with the connection.

Declaration

public virtual AdbCommand CreateCommand(string queryText, CommandType commandType)

Parameters

Type	Name	Description
String	queryText	The query text.
CommandType	commandType	Type of the command.

Returns

Type	Description
AdbCommand	A database command, initialized with the connection. The command must be disposed after its use; the associated connection will not be automatically disposed at the same time.

Dispose()

Disposes the connection, which also closes it, and rolls back any active transaction.

Declaration

public void Dispose()

Dispose(Boolean)

Releases any unmanaged resources, and optionally releases managed resources.

Declaration

protected virtual void Dispose(bool disposing)

Parameters

Type	Name	Description
Boolean	disposing	true to release both managed and unmanaged resources, which includes closing the underlying .NET connection; false to release only unmanaged resources.

GetSchema()

Returns metadata schema information for the data source of this connection. Also see NumberOfColumnRestrictions and Retrieving Database Schema Information.

Note that some of these schemas are already available in an easily digestible format in this class (e.g. the meta-data schema collection SchemaInformationCollection, and columns in table-like objects via one of the GetTableColumnsAsync(IAdbTableIdentifier) overloads), and in Information.

Some providers may throw an exception if the connection is associated with a transaction. Therefore avoid calling this method on a connection that has an associated transaction, or for the MySqlConnector provider, add IgnoreCommandTransaction=true to the connection string.

Declaration

public DataTable GetSchema()

Returns

Type	Description
DataTable	A DataTable that contains schema information.

Exceptions

Type	Condition
InvalidOperationException	May throw if the connection is associated with a transaction (this is provider-specific).

GetSchema(String)

Returns metadata schema information for the data source of this connection using the specified string for the schema name. Also see NumberOfColumnRestrictions and Retrieving Database Schema Information.

Note that some of these schemas are already available in an easily digestible format in this class (e.g. the meta-data schema collection SchemaInformationCollection, and columns in table-like objects via one of the GetTableColumnsAsync(IAdbTableIdentifier) overloads), and in Information.

Note that this method does not log errors. Instead the caller should normally log any returned failure status.

Some providers may throw an exception if the connection is associated with a transaction. Therefore avoid calling this method on a connection that has an associated transaction, or for the MySqlConnector provider, add IgnoreCommandTransaction=true to the connection string.

Declaration

public DataTable GetSchema(string collectionName)

Parameters

Type	Name	Description
String	collectionName	Specifies the name of the schema to return. Must not be null.

Returns

Type	Description
DataTable	A DataTable that contains schema information.

Exceptions

Type	Condition
ArgumentException	collectionName is specified as null.
InvalidOperationException	May throw if the connection is associated with a transaction (this is provider-specific).

GetSchema(String, String[])

Returns metadata schema information for the data source of this connection using the specified string for the schema name and the specified string array for the restriction values. Also see NumberOfColumnRestrictions and Retrieving Database Schema Information.

Note that some of these schemas are already available in an easily digestible format in this class (e.g. the meta-data schema collection SchemaInformationCollection, and columns in table-like objects via one of the GetTableColumnsAsync(IAdbTableIdentifier) overloads), and in Information.

Some providers may throw an exception if the connection is associated with a transaction. Therefore avoid calling this method on a connection that has an associated transaction, or for the MySqlConnector provider, add IgnoreCommandTransaction=true to the connection string.

Note that the MySqlConnector provider does not support the this overload. Instead use the GetSchema(String) overload and filter the return values yourself.

Declaration

public DataTable GetSchema(string collectionName, string[] restrictionValues)

Parameters

Type	Name	Description
String	collectionName	Specifies the name of the schema to return. Must not be null.
String[]	restrictionValues	Specifies a set of restriction values for the requested schema.

Returns

Type	Description
DataTable	A DataTable that contains schema information.

Exceptions

Type	Condition
ArgumentException	collectionName is specified as null.
InvalidOperationException	May throw if the connection is associated with a transaction (this is provider-specific).

Open()

Opens a database connection with the settings specified by the connection string.

Declaration

public virtual void Open()

Exceptions

Type	Condition
Exception	(See the provider documentation for specific exceptions.)

OpenAsync()

Opens the connection asynchronously if the provider supports it, otherwise it calls Open() and returns a completed task. Any exceptions thrown by OpenAsync() will be communicated via the returned task Exception property. Do not invoke other methods and properties of the connection object until the returned task is complete.

Declaration

public virtual Task OpenAsync()

Returns

Type	Description
Task	A task representing the asynchronous operation. The task will contain any exceptions raised (see the provider documentation for specific exceptions).

OpenAsync(CancellationToken)

Opens the connection asynchronously if the provider supports it, otherwise it calls Open() and returns a completed task (or a canceled task if cancellationToken is canceled). Any exceptions thrown by Open() will be communicated via the returned task Exception property. Do not invoke other methods and properties of the connection object until the returned task is complete.

Declaration

public virtual Task OpenAsync(CancellationToken cancellationToken)

Parameters

Type	Name	Description
CancellationToken	cancellationToken	The cancellation token.

Returns

Type	Description
Task	A task representing the asynchronous operation. The task will contain any exceptions raised (see the provider documentation for specific exceptions).

OpenCloseIfRequired(Action<IAdbConnection>)

Synchronously opens the connection only if it's closed, then runs a synchronous action that uses the open connection, and then only closes the connection if it was initially closed, leaving the connection in the same state as before the method call. The action itself should not open or close the connection.

This allows running database commands on a connection without having to check if the connection needs to be opened beforehand, and closed afterwards.

Declaration

public void OpenCloseIfRequired(Action<IAdbConnection> action)

Parameters

Type	Name	Description
Action<IAdbConnection>	action	The synchronous action that uses the open connection.

Exceptions

Type	Condition
ArgumentNullException	action
Exception	See the provider documentation for which specific exceptions Open() can throw, it typically includes DbException and InvalidOperationException.

OpenCloseIfRequired<TResult>(Func<IAdbConnection, TResult>)

Synchronously opens the connection only if it's closed, then runs a synchronous function that uses the open connection, and then only closes the connection if it was initially closed, leaving the connection in the same state as before the method call. The function itself should not open or close the connection.

This allows running database commands on a connection without having to check if the connection needs to be opened beforehand, and closed afterwards.

Declaration

public TResult OpenCloseIfRequired<TResult>(Func<IAdbConnection, TResult> func)

Parameters

Type	Name	Description
Func<IAdbConnection, TResult>	func	The synchronous action that uses the open connection.

Returns

Type	Description
TResult

Type Parameters

Name	Description
TResult	The type of the function result.

Exceptions

Type	Condition
ArgumentNullException	func
Exception	See the provider documentation for which specific exceptions Open() can throw, it typically includes DbException and InvalidOperationException.

OpenCloseIfRequiredAsync(Func<IAdbConnection, Task>)

Asynchronously opens the connection only if it's closed, then runs an asynchronous action that uses the open connection, and then only closes the connection if it was initially closed, leaving the connection in the same state as before the method call. The action itself should not open or close the connection.

This allows running database commands on a connection without having to check if the connection needs to be opened beforehand, and closed afterwards.

Declaration

public Task OpenCloseIfRequiredAsync(Func<IAdbConnection, Task> actionAsync)

Parameters

Type	Name	Description
Func<IAdbConnection, Task>	actionAsync	The asynchronous action that uses the open connection.

Returns

Type	Description
Task

Exceptions

Type	Condition
ArgumentNullException	actionAsync
Exception	See the provider documentation for which specific exceptions Open() can throw, it typically includes DbException and InvalidOperationException.

OpenCloseIfRequiredAsync<TResult>(Func<IAdbConnection, Task<TResult>>)

Asynchronously opens the connection only if it's closed, then runs an asynchronous function that uses the open connection, and then only closes the connection if it was initially closed, leaving the connection in the same state as before the method call. The function itself should not open or close the connection.

This allows running database commands on a connection without having to check if the connection needs to be opened beforehand, and closed afterwards.

Declaration

public Task<TResult> OpenCloseIfRequiredAsync<TResult>(Func<IAdbConnection, Task<TResult>> funcAsync)

Parameters

Type	Name	Description
Func<IAdbConnection, Task<TResult>>	funcAsync	The asynchronous action that uses the open connection.

Returns

Type	Description
Task<TResult>

Type Parameters

Name	Description
TResult	The type of the function result.

Exceptions

Type	Condition
ArgumentNullException	funcAsync
Exception	See the provider documentation for which specific exceptions Open() can throw, it typically includes DbException and InvalidOperationException.

ToString()

Returns a string that describes this instance.

Declaration

public override string ToString()

Returns

Type	Description
String

Overrides

Object.ToString()

Events

StateChange

Occurs when the state of the connection changes.

Declaration

public event StateChangeEventHandler StateChange

Event Type

Type	Description
StateChangeEventHandler

Implements

IAdbConnection

System.IDisposable

See Also

IAdbConnectionBuilder

IAdbConnection

AdbConnectionBuilder

AdbKeepOpenConnection

AdbCommand

DbConnection