Custom Adb Development
Where possible, do use the existing Adb workers to perform database operations, since that tends to be the easiest and least error prone approach.
This article however describes how to implement custom database functionality, ranging from configuring existing workers, to creating new database worker types.
Also see the Custom Workers article, which equally applies to Adb custom workers.
Guidelines
When creating custom Adb functionality, you:
- SHOULD make workers support all Adb providers where possible, to allow switching to a different database, and to avoid code duplication. This typically requires using Adb Information instead of hard-coding values when creating queries etc., and injecting (simply passing in as constructor parameters) any information that changes between providers.
SHOULD design database workers to take AdbConnectionString, IAdbConnectionBuilder, or AdbCommandBuilder instances as parameters, which allows them to support connections with different AdbConnectionMode. Also see Builders and Factories for which one(s) of these to use.
Conversely, you SHOULD NOT pass AdbConnection, AdbKeepOpenConnection, AdbCommand, or connection (text) strings to workers.
- MUST ensure any disposable types that you create are disposed, see Disposing Disposables for details
- SHOULD minimize the time database connections are kept open, to minimize database
resource usage. This normally includes:
- Only opening the connection when the worker runs (as opposed to when the worker is created)
- Consider closing the connection during inactivity, e.g. when waiting for external or upstream data
- Close the connection as soon as possible after use
- SHOULD use database parameters to pass values to and from the database (as opposed to encode values as text in the query), since this improves security, increases performance, and is more reliable. See Adb Commands and Parameters and also CreateSetParametersFromRowAction<T>(AdbParameterCollection).
- SHOULD make column name matching ordinal case insensitive, while giving a case sensitive match precedence over a case insensitive match. Where possible, use out-of-box functionality to implement this, see Custom Dataflow Column Mapping for details.
Using Connections and Commands
As per SQL Database Access, there are three main options for what to pass to the worker, so that it can create database connections and commands:
- When a single connection to the database is needed:
- An IAdbConnectionBuilder instance (created from AdbConnectionBuilder or AdbKeepOpenConnectionBuilder), which is appropriate when the caller specifies the connection information, but the worker creates the database command from the connection
- An AdbCommandBuilder instance (with an embedded connection builder), which is appropriate when the caller specifies both the connection information, and the database command
- When many or an unknown number of database connections to the database are needed:
- An AdbConnectionString instance connection builder factory, which allows the worker to create multiple connections (and their commands) using the same connection details
The above three options can of course be used together, e.g. if a worker requires a single connection to one database, as well as multiple connections to a second database.
It is also common for a worker to provide options via multiple overloads, e.g. either supplying a command builder directly (with an attached connection builder), or supplying a connection builder plus the command text, and having the worker create the command builder. This is e.g. what AdbExecuteNonQueryWorker does.
In the following example, we create a custom Adb worker AdbCustomTruncateTableWorker
that takes a connection builder and a table name, and creates and executes a
TRUNCATE TABLE command on the table. It also uses exception handling to
guarantee an opened connection is always closed, and the database command is disposed
(since the worker created it).
Note
This is useful as a customer Adb worker example. Truncate, drop, if exists etc. are however already available, and portable across databases, via AdbTableNonQueryWorker.
using actionETL;
using actionETL.Adb;
using actionETL.Adb.SqlClientExternal;
using actionETL.Logging;
using System;
using System.Data.Common;
using System.Threading.Tasks;
public class AdbCustomTruncateTableWorker
: WorkerBase<AdbCustomTruncateTableWorker>
{
private readonly AdbCommandBuilder _commandBuilder;
public AdbCustomTruncateTableWorker(
WorkerParent workerParent
, string workerName
, IAdbConnectionBuilder connectionBuilder
, string tableName
)
: base(workerParent, workerName, null)
{
if (connectionBuilder == null)
throw new ArgumentNullException(nameof(connectionBuilder));
if (string.IsNullOrWhiteSpace(tableName))
throw new ArgumentException("Cannot be empty", nameof(tableName));
_commandBuilder = connectionBuilder
.CreateCommandBuilder("TRUNCATE TABLE " + tableName);
}
protected async override Task<OutcomeStatus> RunAsync()
{
var os = OutcomeStatus.Succeeded;
try
{
// Create the actual command, and dispose it when done
using (var cmd = _commandBuilder.Create())
{
try
{
// Open the connection and execute the command asynchronously to
// reduce thread usage.
// Library code should always add ConfigureAwait(false) to awaits.
await cmd.Connection.OpenAsync().ConfigureAwait(false);
await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
}
#pragma warning disable CA1031 // Do not catch general exception types. Exception included in OutcomeStatus.
catch (Exception exception)
{
os = OutcomeStatus.Error(ALogCategory.UnexpectedException, exception);
}
#pragma warning restore CA1031 // Do not catch general exception types
finally
{
try
{
cmd.Connection.Close();
}
catch (DbException exception)
// Optional catch, logs that Close() threw an exception
{
if (os.IsSucceeded)
os = OutcomeStatus.Error(ALogCategory.DatabaseExceptionClose
, exception);
}
}
}
}
// Optional catch, logs that Create() threw an exception
catch (InvalidOperationException exception) // Catch from using(...)
{
if (os.IsSucceeded)
os = OutcomeStatus.Error(ALogCategory.DatabaseCommandFailureCreate
, exception);
}
return os;
}
}
public static partial class AdbTruncateTableWorkerExample
{
public static SystemOutcomeStatus RunExample()
{
return new WorkerSystem()
.Root(ws =>
{
var cb = AdbSqlClientProvider.Get()
.CreateConnectionBuilder(ws.Config["SqlServer"]);
var loadProduct = new AdbCustomTruncateTableWorker(ws, "Truncate ProductId"
, cb
, "ProductId"
);
})
.Start();
}
}
/* The example assumes the following table already exists:
CREATE TABLE ProductId
(
Id int
)
*/