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.
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 |
|
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 (such as Microsoft.Data.SqlClient) 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.
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 (such as Microsoft.Data.SqlClient) 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.
Declaration
public DataTable GetSchema(string collectionName)
Parameters
Type | Name | Description |
---|---|---|
String | collectionName | Specifies the name of the schema to return. Must not be |
Returns
Type | Description |
---|---|
DataTable | A DataTable that contains schema information. |
Exceptions
Type | Condition |
---|---|
ArgumentException |
|
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 (such as Microsoft.Data.SqlClient) 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.
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 |
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 |
|
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 |
|
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 |
|
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 |
|
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 |
|
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
Events
StateChange
Occurs when the state of the connection changes.
Declaration
public event StateChangeEventHandler StateChange
Event Type
Type | Description |
---|---|
StateChangeEventHandler |