Class AdbParameter

A database parameter used by AdbCommand for passing values to and from a database. Used by both control flow (e.g. AdbExecuteNonQueryWorker) and dataflow (e.g. AdbInsertTarget<TInputError>) workers.

Parameters are often created with a AdbParameterCollection method, and optionally further modified by chaining calls to methods on this AdbParameter class.

Parameters have a database type setting, which affects how values are copied or converted between the database and .NET. It can be set to a provider-independent type such as System.Data.DbType Int32, or a provider-specific one such as System.Data.SqlDbType UniqueIdentifier, or if left unset, the provider will guess the database type (Note: on a per row basis in a dataflow!)

Wherever possible, do set the database type explicitly with one of the SetType() or SetDbValue() methods, since relying on the provider guessing the type can lead to conversion issues, and can in some cases reduce performance. Use one of the SetMappedType(Type) overloads if the .NET type is only known at run time.

Note: GetValue() and SetValue() methods convert between DBNull and .NET null values automatically, and are often the best choice. They are different from the DbValue and ProviderValue properties, which do not automatically convert between DBNull and .NET null values.

Also see the Stored Procedure Example, which demonstrates how to use this class.

Inheritance

Object

AdbParameter

Namespace: actionETL.Adb

Assembly: actionETL.dll

Syntax

public sealed class AdbParameter

Constructors

AdbParameter(AdbProvider, String)

Initializes a new instance of the AdbParameter database parameter class, with the default input parameter direction.

Declaration

public AdbParameter(AdbProvider provider, string parameterName)

Parameters

Type	Name	Description
AdbProvider	provider	The Adb provider, cannot be null.
String	parameterName	Name of the parameter, including any named parameter prefix or suffix (e.g. @CustomerId, or :CustomerId). Cannot be null, and must be unique when added to a AdbParameterCollection. Also see ParameterName.

Exceptions

Type	Condition
ArgumentNullException	provider parameterName

AdbParameter(AdbProvider, String, ParameterDirection)

Initializes a new instance of the AdbParameter database parameter class.

Declaration

public AdbParameter(AdbProvider provider, string parameterName, ParameterDirection direction)

Parameters

Type	Name	Description
AdbProvider	provider	The Adb provider, cannot be null.
String	parameterName	Name of the parameter, including any named parameter prefix or suffix (e.g. @CustomerId, or :CustomerId). Cannot be null, and must be unique when added to a AdbParameterCollection. Also see ParameterName.
ParameterDirection	direction	The parameter direction.

Exceptions

Type	Condition
ArgumentNullException	provider parameterName

Properties

DbType

Gets the provider-independent database type. The default is String.

Use one of the SetDb*() methods to set the type.

Declaration

public DbType DbType { get; }

Property Value

Type	Description
DbType

Exceptions

Type	Condition
ArgumentException	The property was not set to a valid DbType.

DbValue

Gets or sets the provider-independent parameter value. The default value is null.

By specifying the database type before setting the value, the bound value is converted to that type when the provider sends the data to the server. The provider tries to convert any type of value if it supports the IConvertible interface. Conversion errors may result if the specified type is not compatible with the value.

Note: When receiving or to send a database NULL parameter value to the server, DBNull.Value must be used, not the .NET null (which instead means no database value has been set). This is different from the GetValue(), SetValue(), and SetDbValue() methods, which convert to and from DBNull.Value automatically.

Declaration

public object DbValue { get; set; }

Property Value

Type	Description
Object	The provider-independent value.

Direction

Gets the parameter direction, i.e. input-only, output-only, bidirectional, or a stored procedure return value parameter.

If the Direction is output, and execution of the associated command does not return a value, the parameter contains a null value.

Output, InputOut, and ReturnValue parameters returned by calling ExecuteReader and ExecuteReaderAsync cannot be accessed until the DbDataReader has been closed or disposed.

Declaration

public ParameterDirection Direction { get; }

Property Value

Type	Description
ParameterDirection	The direction. The default is Input.

IsNullable

Gets or sets a value indicating whether the parameter accepts DBNull values.

Database NULL values are received and must be set using DBNull.Value. A .NET null in DbValue or ProviderValue means no value has been set (including no database NULL).

Note that GetValue() and Set*() methods automatically convert between database DBNull.Value and .NET null.

Declaration

public bool IsNullable { get; set; }

Property Value

Type	Description
Boolean	true if this parameter accepts DBNull values; otherwise, false (the default.)

IsProviderType

true if this instance uses a provider-specific database type (e.g. SqlDateTime); otherwise, false (the default).

Setting DbValue or calling any of the SetDb methods will set this property to false. Setting ProviderValue or calling any of the SetProvider methods will set this property to true.

Declaration

public bool IsProviderType { get; }

Property Value

Type	Description
Boolean

ParameterCollection

Gets the parameter collection this parameter is added to, or null if it's not added to any. Note that a parameter can only be added to a single parameter collection, and only once.

Declaration

public AdbParameterCollection ParameterCollection { get; }

Property Value

Type	Description
AdbParameterCollection

ParameterName

Gets or sets the name of the parameter. Cannot be null.

This is used for matching with named parameters in the query, stored procedure parameter names, and dataflow column names.

This is e.g. used by some dataflow Adb workers to have ParameterName match the database parameter name, while SourceColumn match a different .NET dataflow column name.

Column name matching is ordinal case insensitive, but a case sensitive match takes precedence over a case insensitive match.

It is also useful to use meaningful names for positional parameters, since this improves logging information. Also see SourceColumn.

Note that any named parameter prefix or suffix (e.g. @ or :) must be included in the name (which will be stripped with StripParameterMarker(String) when used for column mapping).

Also note that parameter names in a AdbParameterCollection must at a minimum be (case sensitive) unique. Some providers have additional requirements, e.g. case insensitive unique.

Declaration

public string ParameterName { get; set; }

Property Value

Type	Description
String	The name of the parameter, including any named parameter prefix or suffix, e.g. @ or :.

Exceptions

Type	Condition
ArgumentNullException	Setting ParameterName

Precision

Gets the maximum number of digits used to represent the parameter value.

Use one of the Set*Type() or SetDbValue() methods to set this property.

Declaration

public byte Precision { get; }

Property Value

Type	Description
Byte

ProviderType

Gets the provider-specific parameter type enumeration value (e.g SqlDbType.SqlDateTime2), as an object.

Declaration

public object ProviderType { get; }

Property Value

Type	Description
Object

ProviderValue

Gets or sets a provider-specific parameter value. The default value is null.

By specifying the database type before setting the value, the bound value is converted to that type when the provider sends the data to the server. The provider tries to convert any type of value if it supports the IConvertible interface. Conversion errors may result if the specified type is not compatible with the value.

Note: When receiving or to send a database NULL parameter value to the server, DBNull.Value must be used, not the .NET null (which instead means no database value has been set). This is different from the GetValue(), SetValue(), and SetDbValue() methods, which convert to and from DBNull.Value automatically.

Declaration

public object ProviderValue { get; set; }

Property Value

Type	Description
Object	The provider-specific value.

Scale

Gets the number of decimal places to which the parameter value is resolved.

Use one of the Set*Type() or SetDbValue() methods to set this property.

Declaration

public byte Scale { get; }

Property Value

Type	Description
Byte

Size

Gets the maximum size of the data, in bytes or characters.

Use one of the Set*Type() or SetDbValue() methods to set this property.

The Size property is mainly used for binary and string types, although some providers also use it for additional specific types.

For non-string data types and ANSI string data, the Size property refers to the number of bytes. For Unicode string data, Size refers to the number of characters. The count for strings does not include the terminating character. For parameters of type DbType.Xml, Size is ignored.

For variable-length data types, Size describes the maximum amount of data to transmit to the server. For example, for a Unicode string value, Size could be used to limit the amount of data sent to the server to the first one hundred characters.

For bidirectional and output parameters, and return values, you must set the value of Size. This is not required for input parameters, and if not explicitly set, the value is inferred from the actual size of the specified parameter when a parameterized statement is executed.

The database type and Size properties of a parameter can be inferred when setting DbValue or ProviderValue. The resulting inferred type and size are however not exposed in the AdbParameter property settings. For example, if the size of the parameter has been inferred, Size does not contain inferred value after statement execution. Furthermore, inferring the type and size is error prone and can lead to conversion issues, and should be avoided if possible.

For fixed length data types, the value of Size is ignored. It can be retrieved for informational purposes, and returns the maximum amount of bytes the provider uses when transmitting the value of the parameter to the server.

If the size of the value supplied for a parameter exceeds the specified Size, the Value of the parameter will contain the specified value, truncated to the Size of the parameter.

Declaration

public int Size { get; }

Property Value

Type	Description
Int32	The maximum size of the data, in bytes or characters.

SourceColumn

Gets or sets the parameter mapping name to use instead of the default ParameterName.

This is e.g. used by some dataflow Adb workers to have ParameterName match the database parameter name, while SourceColumn match a different .NET dataflow column name.

Column name matching is ordinal case insensitive, but a case sensitive match takes precedence over a case insensitive match.

Declaration

public string SourceColumn { get; set; }

Property Value

Type	Description
String	The parameter mapping name. Cannot be null. The default is an empty string, in which case ParameterName is used, although stripped of any parameter markers using StripParameterMarker(String).

Remarks

Note to implementers using this property when mapping columns: If this property is not an empty string, use it as is for one or both sides of the mapping as appropriate. If it is an empty string, instead use ParameterName for the mapping, after stripping any parameter markers using StripParameterMarker(String).

Also ensure that the column mapping follows the standard casing rules, e.g. by looking up column names with GetSchemaNode(String).

Exceptions

Type	Condition
ArgumentNullException	Setting SourceColumn

UnderlyingParameter

Gets the underlying .NET DbParameter.

Note: Always use the members on AdbParameter, where available. In rare cases however, the underlying .NET DbParameter is needed, e.g. when accessing provider-specific functionality not covered by AdbParameter.

Declaration

public DbParameter UnderlyingParameter { get; }

Property Value

Type	Description
DbParameter

Methods

GetValue()

Gets the parameter value from either ProviderValue or DbValue, depending on IsProviderType.

Note: Converts any DBNull values to .NET null. This is different from the DbValue and ProviderValue properties, which do not convert DBNull.Value to .NET null.

Declaration

public object GetValue()

Returns

Type	Description
Object	The value as an object.

SetDbType(DbType)

Sets the provider-independent database type to use.

Declaration

public AdbParameter SetDbType(DbType dbType)

Parameters

Type	Name	Description
DbType	dbType	The provider-independent database type.

Returns

Type	Description
AdbParameter	The parameter itself, allowing making further changes to the parameter by chaining method calls, or to save the parameter reference in a variable for later use.

Exceptions

Type	Condition
ArgumentException	The property is not set to a valid DbType.

SetDbType(DbType, Byte, Byte)

Sets the provider-independent database type to use.

Declaration

public AdbParameter SetDbType(DbType dbType, byte precision, byte scale)

Parameters

Type	Name	Description
DbType	dbType	The provider-independent database type.
Byte	precision	The maximum number of digits used to represent the parameter value.
Byte	scale	The number of decimal places to which the parameter value is resolved.

Returns

Type	Description
AdbParameter	The parameter itself, allowing making further changes to the parameter by chaining method calls, or to save the parameter reference in a variable for later use.

Exceptions

Type	Condition
ArgumentException	The property is not set to a valid DbType.

SetDbType(DbType, Int32)

Sets the provider-independent database type to use.

Declaration

public AdbParameter SetDbType(DbType dbType, int size)

Parameters

Type	Name	Description
DbType	dbType	The provider-independent database type.
Int32	size	The maximum size of the data, in bytes or characters.

Returns

Type	Description
AdbParameter	The parameter itself, allowing making further changes to the parameter by chaining method calls, or to save the parameter reference in a variable for later use.

Exceptions

Type	Condition
ArgumentException	The property is not set to a valid DbType.

SetDbValue(Object, DbType)

Sets the provider-independent database type to use, and its value.

Note: If dbValue is null, it will be converted to DBNull.Value. This is different from the DbValue and ProviderValue properties, which do not convert .NET null to DBNull.Value.

Declaration

public AdbParameter SetDbValue(object dbValue, DbType dbType)

Parameters

Type	Name	Description
Object	dbValue	The parameter value. A null value will be converted to DBNull.Value.
DbType	dbType	The provider-independent database type.

Returns

Type	Description
AdbParameter	The parameter itself, allowing making further changes to the parameter by chaining method calls, or to save the parameter reference in a variable for later use.

Exceptions

Type	Condition
ArgumentException	dbType is not set to a valid DbType.

SetDbValue(Object, DbType, Byte, Byte)

Sets the provider-independent database type to use, and its value.

Note: If dbValue is null, it will be converted to DBNull.Value. This is different from the DbValue and ProviderValue properties, which do not convert .NET null to DBNull.Value.

Declaration

public AdbParameter SetDbValue(object dbValue, DbType dbType, byte precision, byte scale)

Parameters

Type	Name	Description
Object	dbValue	The parameter value. A null value will be converted to DBNull.Value.
DbType	dbType	The provider-independent database type.
Byte	precision	The maximum number of digits used to represent the parameter value.
Byte	scale	The number of decimal places to which the parameter value is resolved.

Returns

Type	Description
AdbParameter	The parameter itself, allowing making further changes to the parameter by chaining method calls, or to save the parameter reference in a variable for later use.

Exceptions

Type	Condition
ArgumentException	dbType is not set to a valid DbType.

SetDbValue(Object, DbType, Int32)

Sets the provider-independent database type to use, and its value.

Note: If dbValue is null, it will be converted to DBNull.Value. This is different from the DbValue and ProviderValue properties, which do not convert .NET null to DBNull.Value.

Declaration

public AdbParameter SetDbValue(object dbValue, DbType dbType, int size)

Parameters

Type	Name	Description
Object	dbValue	The parameter value. A null value will be converted to DBNull.Value.
DbType	dbType	The provider-independent database type.
Int32	size	The maximum size of the data, in bytes or characters.

Returns

Type	Description
AdbParameter	The parameter itself, allowing making further changes to the parameter by chaining method calls, or to save the parameter reference in a variable for later use.

Exceptions

Type	Condition
ArgumentException	dbType is not set to a valid DbType.

SetDbValue<T>(Nullable<T>, DbType)

Sets the provider-independent database type to use, and its value.

Note: If the Nullable{T} dbValue has no value, it will be converted to DBNull.Value. This is different from the DbValue and ProviderValue properties, which do not convert .NET null to DBNull.Value.

Declaration

public AdbParameter SetDbValue<T>(T? dbValue, DbType dbType)
    where T : struct

Parameters

Type	Name	Description
Nullable<T>	dbValue	The parameter value. If HasValue is false, the value will be converted to DBNull.Value.
DbType	dbType	The provider-independent database type.

Returns

Type	Description
AdbParameter	The parameter itself, allowing making further changes to the parameter by chaining method calls, or to save the parameter reference in a variable for later use.

Type Parameters

Name	Description
T

Exceptions

Type	Condition
ArgumentException	dbType is not set to a valid DbType.

SetDbValue<T>(Nullable<T>, DbType, Byte, Byte)

Sets the provider-independent database type to use, and its value.

Note: If the Nullable{T} dbValue has no value, it will be converted to DBNull.Value. This is different from the DbValue and ProviderValue properties, which do not convert .NET null to DBNull.Value.

Declaration

public AdbParameter SetDbValue<T>(T? dbValue, DbType dbType, byte precision, byte scale)
    where T : struct

Parameters

Type	Name	Description
Nullable<T>	dbValue	The parameter value. If HasValue is false, the value will be converted to DBNull.Value.
DbType	dbType	The provider-independent database type.
Byte	precision	The maximum number of digits used to represent the parameter value.
Byte	scale	The number of decimal places to which the parameter value is resolved.

Returns

Type	Description
AdbParameter	The parameter itself, allowing making further changes to the parameter by chaining method calls, or to save the parameter reference in a variable for later use.

Type Parameters

Name	Description
T

Exceptions

Type	Condition
ArgumentException	dbType is not set to a valid DbType.

SetDbValue<T>(Nullable<T>, DbType, Int32)

Sets the provider-independent database type to use, and its value.

Note: If the Nullable{T} dbValue has no value, it will be converted to DBNull.Value. This is different from the DbValue and ProviderValue properties, which do not convert .NET null to DBNull.Value.

Declaration

public AdbParameter SetDbValue<T>(T? dbValue, DbType dbType, int size)
    where T : struct

Parameters

Type	Name	Description
Nullable<T>	dbValue	The parameter value. If HasValue is false, the value will be converted to DBNull.Value.
DbType	dbType	The provider-independent database type.
Int32	size	The maximum size of the data, in bytes or characters.

Returns

Type	Description
AdbParameter	The parameter itself, allowing making further changes to the parameter by chaining method calls, or to save the parameter reference in a variable for later use.

Type Parameters

Name	Description
T

Exceptions

Type	Condition
ArgumentException	dbType is not set to a valid DbType.

SetMappedType(Type)

Sets the database type to use from predefined type mappings, which can result in either a provider-independent type, or a provider-specific type, or in Untyped. This is most useful when the .NET type is only known at run time.

Some mappings also uses the parameter Direction, so ensure that property is set correctly before calling this method.

Declaration

public bool SetMappedType(Type dotNetType)

Parameters

Type	Name	Description
Type	dotNetType	The .NET CLR type.

Returns

Type	Description
Boolean	true if a mapping was found (including Untyped), otherwise false.

Exceptions

Type	Condition
ArgumentNullException	dotNetType

SetMappedType(Type, AdbColumnSchema)

Sets the database type to use from predefined type mappings, which can result in either a provider-independent type, or a provider-specific type, or in Untyped. This is most useful when the .NET type is only known at run time.

Some mappings also uses the parameter Direction, so ensure that property is set correctly before calling this method.

Declaration

public bool SetMappedType(Type dotNetType, AdbColumnSchema adbColumnSchema)

Parameters

Type	Name	Description
Type	dotNetType	The .NET CLR type.
AdbColumnSchema	adbColumnSchema	A database column schema to map the dotNetType to or from. Also see GetTableColumnsAsync(IAdbTableIdentifier) and associated overloads.

Returns

Type	Description
Boolean	true if a mapping was found (including Untyped), otherwise false.

Exceptions

Type	Condition
ArgumentNullException	dotNetType

SetProviderType(Object)

Sets the provider-specific database type to use.

Declaration

public AdbParameter SetProviderType(object providerTypeEnum)

Parameters

Type	Name	Description
Object	providerTypeEnum	The provider-specific database type enum value, e.g. SqlDbType.DateTime2.

Returns

Type	Description
AdbParameter	The parameter itself, allowing making further changes to the parameter by chaining method calls, or to save the parameter reference in a variable for later use.

Exceptions

Type	Condition
ArgumentException	dbParameter is not of the correct provider type. providerTypeEnum is not of the correct provider type.
ArgumentNullException	dbParameter

SetProviderType(Object, Byte, Byte)

Sets the provider-specific database type to use.

Declaration

public AdbParameter SetProviderType(object providerTypeEnum, byte precision, byte scale)

Parameters

Type	Name	Description
Object	providerTypeEnum	The provider-specific database type enum value, e.g. SqlDbType.DateTime2.
Byte	precision	The maximum number of digits used to represent the parameter value.
Byte	scale	The number of decimal places to which the parameter value is resolved.

Returns

Type	Description
AdbParameter	The parameter itself, allowing making further changes to the parameter by chaining method calls, or to save the parameter reference in a variable for later use.

Exceptions

Type	Condition
ArgumentException	providerTypeEnum is not of the correct provider type.

SetProviderType(Object, Int32)

Sets the provider-specific database type to use.

Declaration

public AdbParameter SetProviderType(object providerTypeEnum, int size)

Parameters

Type	Name	Description
Object	providerTypeEnum	The provider-specific database type enum value, e.g. SqlDbType.DateTime2.
Int32	size	The maximum size of the data, in bytes or characters.

Returns

Type	Description
AdbParameter	The parameter itself, allowing making further changes to the parameter by chaining method calls, or to save the parameter reference in a variable for later use.

Exceptions

Type	Condition
ArgumentException	providerTypeEnum is not of the correct provider type.

SetValue(Object)

Sets the parameter value via either ProviderValue or DbValue, depending on IsProviderType.

Note: If parameterValue is null, it will be converted to DBNull.Value. This is different from the DbValue and ProviderValue properties, which do not convert .NET null to DBNull.Value.

Declaration

public AdbParameter SetValue(object parameterValue)

Parameters

Type	Name	Description
Object	parameterValue	The parameter value. A null value will be converted to DBNull.Value.

Returns

Type	Description
AdbParameter	The parameter itself, allowing making further changes to the parameter by chaining method calls, or to save the parameter reference in a variable for later use.

SetValue<T>(Nullable<T>)

Sets the parameter value via either ProviderValue or DbValue, depending on IsProviderType.

Note: If the Nullable{T} parameterValue has no value, it will be converted to DBNull.Value. This is different from the DbValue and ProviderValue properties, which do not convert .NET null to DBNull.Value.

Declaration

public AdbParameter SetValue<T>(T? parameterValue)
    where T : struct

Parameters

Type	Name	Description
Nullable<T>	parameterValue	The parameter value. If HasValue is false, the value will be converted to DBNull.Value.

Returns

Type	Description
AdbParameter	The parameter itself, allowing making further changes to the parameter by chaining method calls, or to save the parameter reference in a variable for later use.

Type Parameters

Name	Description
T	The nullable type of the value. Does normally not need to be specified.

ToString()

Returns a string with "ParameterName:SourceColumn".

Declaration

public override string ToString()

Returns

Type	Description
String

Overrides

Object.ToString()

See Also

AdbCommand

AdbParameterCollection

AdbDataSourceInformation