Adb Commands and Parameters

`AdbCommandBuilder`

An AdbCommandBuilder instance specifies what SQL statement or stored procedure and parameters to use when later creating a single AdbCommand for executing against the database. AdbCommandBuilder instances do not need to be disposed after use.

Note

Typically, the library user creates an AdbCommandBuilder to specify what the command should do, and passes it to a worker. The worker, if it runs, uses it to create an AdbCommand with which to execute actual database queries.

Command builder instances can be created by AdbProvider factory methods, which will automatically generate an associated AdbConnectionBuilder instance. Use this approach when you don't need a direct reference to the associated AdbConnectionBuilder.

Alternatively, create an AdbConnectionBuilder (or AdbKeepOpenConnectionBuilder) instance first, and use that to in turn create AdbCommandBuilder instances.

`AdbCommand`

AdbCommand is a database command (similar to System.Data.Common.DbCommand), created by AdbCommandBuilder, with extra information and functionality. It allows executing the command against its associated data source, either synchronously or asynchronously. The command must be disposed after use.

Note

While each AdbCommandBuilder instance can only create a single AdbCommand instance, the latter can, if appropriate, be reused multiple times by changing the value of its parameters before executing the next query. This is commonly used by dataflow workers that make use of parameters, since the data type of each parameter stays the same for each row, and it avoids recreating the parameters.

`AdbParameterCollection`

The Parameters collection property on the AdbCommandBuilder is the primary way to create, add, and inspect parameters. It is also available as AdbCommand.Parameters. This AdbParameterCollection instance (similar to System.Data.Common.DbParameterCollection) is automatically created when the AdbCommandBuilder is created.

After creating a parameter with AdbCommand.Parameters methods, members on the AdbParameter instance can be used to set parameter data type, value etc.

Note

Parameters in the collection must have unique names, including with positional parameters. Do use meaningful names, since the names are visible in the logging output.

If no meaningful names exist, name them "p0", "p1", "p2" etc.

`AdbParameter`

An AdbParameter instance (similar to System.Data.Common.DbParameter) is a single database parameter for passing data to and from a data source.

Database Data Types

Parameters have a database data type setting, which affects how values are copied or converted between the database and .NET. There are multiple different ways of setting and getting the database data type and value (more details further down), and the most appropriate should be chosen on a per-column basis:

Provider-Independent - Set to a DbType data type such as System.Data.DbType.Int32
Provider-Specific - Set to a provider data type such as System.Data.SqlDbType.UniqueIdentifier
Untyped - If the data type is left unset, or if predefined mappings are used and specifies Untyped, the provider will guess the database data type
Predefined Mappings - Use predefined actionETL mappings between .NET and database data types to set the parameter data type, which results in one of the above three strategies being used. This approach is preferable to Untyped since the data type is fixed and the provider does not have to guess the data type (unless the mapping specifies Untyped).

Note

Wherever possible, do ensure that the database type is set explicitly, either via one of the Set* methods or via the predefined mappings being used. Otherwise the provider will have to guess the data type, including on a row by row basis, which can lead to conversion issues, and in some cases reduced performance.

In general:

Provider-Independent and Provider-Specific types are used when the data type is known at compile time
Predefined Mappings are used when the data type is only known at run-time
Untyped is used when none of the above are possible

Also see Adb Supported Databases for provider-specific information.

Provider-Independent `System.Data.DbType`

Members with *Db* in the name (e.g. SetDbType(DbType) overloads), as well as GetValue() and SetValue(...) with IsProviderType == false use a provider-independent enumeration shared by all .NET providers: System.Data.DbType.

Key points on using these are:

The logical data type is the same irrespective of which underlying .NET provider is used
Maps to standard .NET types (int?, string, ...), which can be directly sent to any other provider, and works as-is with non-database code
Typically does not support all database specific data types
Some database data types can be used, but have a different range, scale or precision (e.g. date, time, and decimal types), which can lead to more and sometimes incorrect data type conversions

Note

Returned standard .NET types (int?, string, ...) obviously have standard .NET behavior in terms of null-handling, comparisons, sort order etc. Use DbType when this is desirable, but use provider-specific data types (if available) when database style null-handling, comparisons, sort order etc. is desired.

Provider-Specific

Members with *Provider* in the name (e.g. SetProviderType(Object) overloads), as well as GetValue() and SetValue(...) with IsProviderType == true use a provider-specific enumeration (e.g. System.Data.SqlDbType) to set a provider specific data type (e.g. System.Data.SqlDbType.UniqueIdentifier).

Key points on using provider-specific data types:

They will typically have the same range, null-handling, comparison-handling etc. as their database counterpart data types, thereby avoiding conversion issues etc.
Give access to database data types and functionality not available via DbType
Typically only include types that have some functional difference from DbType data types

Note

Provider-specific data types are ideal when both the source and destination use the same provider. If they are different, use a (DbType or provider-specific) data type that can handle the contents in both source and destination. If no good choices are available, consider using a string as an intermediary type.

Currently the following Adb providers have provider-specific data types:

Adb Provider	Supported Databases
Microsoft.Data.SqlClient	Microsoft SQL Server® 2008 onwards
Npgsql	PostgreSQL®, current versions (5 years)

Untyped

If the database data type is left unset, or if predefined mappings are used and specifies Untyped, the provider will guess the database data type, potentially on each dataflow row. This can lead to conversion issues, and in some cases reduced performance.

Predefined Mappings

Each Adb provider creates predefined mappings between database parameter data types and .NET types. The mappings are accessed via SetMappedType(Type) overloads, which are mainly used when implementing parameter-using custom Adb workers that don't know the database types ahead of time, which e.g. is the case with AdbInsertTarget<TInputError>.

.NET types are mapped to provider-independent types, e.g. System.Int32 to DbType.Int32. Some Adb providers also map .NET types to provider-specific data types, e.g. the "Microsoft.Data.SqlClient" provider maps System.Data.SqlTypes.SqlBoolean to SqlDbType.Bit.

If no pre-defined mapping is found, and the .NET type is an enum or a nullable enum, and the database column is either an integral type or unknown, then the parameter is mapped to the enum underlying type. This allows the enum to be stored in a database integer column.

See the specific provider in Adb Supported Databases for details.

`DBNull` and Accessing Values

The underlying .NET database providers use DBNull to represent database NULL values. The parameter value can be set and retrieved via either of two sets of members: with or without automatic conversion between database DBNull and .NET null.

With Automatic `DBNull` Conversion

AdbParameter members GetValue(), SetValue(...), and SetDbValue(...) automatically convert between database DBNull.Value and .NET null.

Furthermore, GetValue() and SetValue(...) use either DbValue or ProviderValue to access the value, depending on whether the parameter database data type is provider independent (IsProviderType is false, the default) or provider-specific (IsProviderType is true), respectively.

Using these members is a good choice when the data type has been set via a SetMappedType(...) overload, and also when you want automatic DBNull conversion.

Without Automatic `DBNull` Conversion

AdbParameter members DbValue and ProviderValue always use DBNull to represent a database NULL. For these, a .NET null means that no value has been set (not even a database NULL).

Furthermore, these members 'hard-code' whether to access the value as a provider-independent or provider-specific value, making this a good choice when you know which approach is preferable.

Again, remember that any database NULL must be represented as DBNull.Value.

Parameter Mapping

Add parameters to the Parameters collection to match the requirements of the query. Each parameter has a ParameterName, which by default is used to map the parameter to:

Named parameter placeholders in the query
Stored procedure parameter names
Dataflow column names

In several scenarios, SourceColumn can be used to map to a different name.

For providers using positional ? placeholders, add the same number of parameters as there are placeholders, in the order that the placeholders appear in the query.

AdbConnection.Information has several members that assist with handling parameters, including:

Stored Procedure Example

Here we execute a stored procedure with parameters. Even building an executable program (or utility method) for executing this single query can be very worthwhile since actionETL adds configuration, error handling, and logging.

We create the command builder outside the worker system to make it easy to return a parameter value to the caller. More commonly, commands and connections are created inside the worker system, as and when they are needed.

Note

This example uses the "MySqlClient" provider; you can equally use the "MySqlConnector" provider.

using actionETL;
using actionETL.Adb;
using actionETL.Adb.MySqlClientExternal;
using System.Data;

public static partial class ExecuteStoredProcedureMySql
{
    public static DateTime GetMaxDateTime()
    {
        // Get a command builder using "actionetl.aconfig.json" connection details.
        // StoredProcedure(string) sets both CommandText and CommandType properties.
        var cb = AdbMySqlClientProvider.Get()
            .CreateCommandBuilderFromDefaultAConfig("MySqlClientTester")
            .StoredProcedure("GetMaxDateTime");

        // Many methods return an instance and allow method chaining.
        // Whenever possible, set the database type explicitly.
        cb.Parameters.Add("countryId").SetDbValue(42, DbType.Int32);

        // Database type not set, provider has to guess the type (which is less good)
        cb.Parameters.Add("category").SetValue("Batteries");

        // Save the parameter reference, since we retrieve the returned value later
        var maxDateTime = cb.Parameters.Add("maxDateTime", ParameterDirection.Output)
            .SetDbType(DbType.DateTime);

        new WorkerSystem()
            // Curly braces and semi-colon not needed in 1-line lambda
            .Root(ws => _ = new AdbExecuteNonQueryWorker(ws, "Call GetMaxDateTime", cb))
            .Start()
            .ThrowOnFailure();

        // Value is of type object, and must be cast
        return (DateTime)maxDateTime.DbValue;
    }
}

/* The example assumes the following stored procedure already exists:
    CREATE PROCEDURE GetMaxDateTime (
        countryId int, 
        category varchar(50) charset utf8mb4,
        OUT maxDateTime DateTime
        )
    BEGIN
        -- Use input parameters...

        SET maxDateTime := '2017-03-13';
    END
 */

Note

PostgreSQL stored procedures return data as a result set. You can however return values in output parameters in PostgreSQL version 11 onwards, as shown in this example.
This example uses Input (the default) and InputOutput direction parameters. Output and ReturnValue parameters are not supported.
Note that CommandType.StoredProcedure should normally not be set when calling a stored procedure, please see the Npgsql documentation for details.

using actionETL;
using actionETL.Adb;
using actionETL.Adb.NpgsqlExternal;
using System.Data;

public static partial class ExecuteStoredProcedureNpgsql
{
    public static DateTime GetMaxDateTime()
    {
        // Get a command builder using "actionetl.aconfig.json" connection details.
        var cb = AdbNpgsqlProvider.Get()
            .CreateCommandBuilderFromDefaultAConfig("NpgsqlTester");
        cb.CommandText = "CALL getmaxdatetime (@countryId, @category, @maxDateTime)";

        // Many methods return an instance and allow method chaining.
        // Whenever possible, set the database type explicitly.
        cb.Parameters.Add("countryId").SetDbValue(42, DbType.Int32);

        // Database type not set, provider has to guess the type (which is less good)
        cb.Parameters.Add("category").SetValue("Batteries");

        // Save the parameter reference, since we retrieve the returned value later.
        // Npgsql supports ParameterDirection.InputOutput but not ParameterDirection.Output.
        var maxDateTime = cb.Parameters.Add("maxDateTime", ParameterDirection.InputOutput)
            .SetDbType(DbType.DateTime).SetValue(null);

        new WorkerSystem()
            // Curly braces and semi-colon not needed in 1-line lambda
            .Root(ws => _ = new AdbExecuteNonQueryWorker(ws, "Call getmaxdatetime", cb))
            .Start()
            .ThrowOnFailure();

        // Value is of type object, and must be cast
        return (DateTime)maxDateTime.DbValue;
    }
}

/* The example assumes the following stored procedure already exists:
    CREATE PROCEDURE getmaxdatetime (
        countryId integer, 
        category text,
        INOUT maxDateTime timestamp
        )
    LANGUAGE PLPGSQL
    AS $$
    BEGIN
        -- Use input parameters...

        maxDateTime := '2017-03-13';
    END;
    $$
 */

using actionETL;
using actionETL.Adb;
using actionETL.Adb.OdbcExternal;
using System.Data;

public static partial class ExecuteStoredProcedureOdbc
{
    public static DateTime GetMaxDateTime()
    {
        // Get a command builder using "actionetl.aconfig.json" connection details.
        // StoredProcedure(string) sets both CommandText and CommandType properties.
        var cb = AdbOdbcProvider.GetSqlServer()
            .CreateCommandBuilderFromDefaultAConfig("OdbcSqlServerTester")
            .StoredProcedure("dbo.GetMaxDateTime ?, ?, ?");

        // Many methods return an instance and allow method chaining.
        // Whenever possible, set the database type explicitly.
        cb.Parameters.Add("countryId").SetDbValue(42, DbType.Int32);

        // Database type not set, provider has to guess the type (which is less good)
        cb.Parameters.Add("category").SetValue("Batteries");

        // Save the parameter reference, since we retrieve the returned value later
        var maxDateTime = cb.Parameters.Add("maxDateTime", ParameterDirection.Output)
            .SetDbType(DbType.DateTime);

        new WorkerSystem()
            // Curly braces and semi-colon not needed in 1-line lambda
            .Root(ws => _ = new AdbExecuteNonQueryWorker(ws, "Call GetMaxDateTime", cb))
            .Start()
            .ThrowOnFailure();

        // Value is of type object, and must be cast
        return (DateTime)maxDateTime.DbValue;
    }
}

/* The example assumes the following stored procedure already exists:
    CREATE PROCEDURE dbo.GetMaxDateTime
        @countryId int, 
        @category nvarchar(50),
        @maxDateTime DateTime output
    AS
    BEGIN
        SET NOCOUNT ON;

        -- Use input parameters...

        SELECT @maxDateTime = '2017-03-13';
    END
 */

using actionETL;
using actionETL.Adb;
using actionETL.Adb.SqlClientExternal;
using System.Data;
using System.Data.SqlTypes;

public static partial class ExecuteStoredProcedureSqlClient
{
    public static SqlDateTime GetMaxDateTime()
    {
        // Get a command builder using "actionetl.aconfig.json" connection details.
        // StoredProcedure(string) sets both CommandText and CommandType properties.
        var cb = AdbSqlClientProvider.Get()
            .CreateCommandBuilderFromDefaultAConfig("SqlServer")
            .StoredProcedure("dbo.GetMaxDateTime");

        // Many methods return an instance and allow method chaining.
        // Whenever possible, set the database type explicitly.
        cb.Parameters.Add("countryId").SetDbValue(42, DbType.Int32);

        // Database type not set, provider has to guess the type (which is less good)
        cb.Parameters.Add("category").SetValue("Batteries");

        // Save the parameter reference, since we retrieve the returned value later
        var maxDateTime = cb.Parameters.Add("maxDateTime", ParameterDirection.Output)
            .SetProviderType(SqlDbType.DateTime);

        new WorkerSystem()
            // Curly braces and semi-colon not needed in 1-line lambda
            .Root(ws => _ = new AdbExecuteNonQueryWorker(ws, "Call GetMaxDateTime", cb))
            .Start()
            .ThrowOnFailure();

        // Value is of type object, and must be cast
        return (SqlDateTime)maxDateTime.ProviderValue;
    }
}

/* The example assumes the following stored procedure already exists:
    CREATE PROCEDURE dbo.GetMaxDateTime
        @countryId int, 
        @category nvarchar(50),
        @maxDateTime DateTime output
    AS
    BEGIN
        SET NOCOUNT ON;

        -- Use input parameters...

        SELECT @maxDateTime = '2017-03-13';
    END
 */

Adb Commands and Parameters

AdbCommandBuilder

Note

AdbCommand

Note

AdbParameterCollection

Note

AdbParameter

Database Data Types

Note

Provider-Independent System.Data.DbType

Note

Provider-Specific

Note

Untyped

Predefined Mappings

DBNull and Accessing Values

With Automatic DBNull Conversion

Without Automatic DBNull Conversion

Parameter Mapping

Stored Procedure Example

Note

See Also

`AdbCommandBuilder`

`AdbCommand`

`AdbParameterCollection`

`AdbParameter`

Provider-Independent `System.Data.DbType`

`DBNull` and Accessing Values

With Automatic `DBNull` Conversion

Without Automatic `DBNull` Conversion