.NET Applications must utilize TdParameter class to pass values to the Advanced SQL Engine or receive parameter values from the SQL Engine. .NET Applications must configure each TdParameter object and associate the TdParameter object with a TdCommand object.

Working with Parameter Markers

The SQL Text must have a Parameter Marker (Question-Mark ?) for each TdParameter object when TdCommand.CommandType property is set to Text. The .NET Data Provider for Teradata does not support Named-Parameters. The parameters are positional and there is a one to one relationship between the Parameter-Marker in the SQL-Text and the TdParameter object in TdCommand.Parameters collection.

For example the SQL-Text shown below has three parameters and the TdCommand Object has three Parameters corresponding to the three Parameter-Markers.

TdCommand cmd = new TdCommand();

cmd.CommandText = "Select OrderId, OrderDate, ShipDate, ShipAddress FROM Order " +
                    "Where Price >= ? and Quantity >= ? and OrderDate < ? ";

cmd.Parameters.Add("Price", TdType.Decimal);
cmd.Parameters.Add("Quantity", TdType.Integer);
cmd.Parameters.Add("OrderDate", TdType.Timestamp);

Supplying the Parameter Direction Property

.NET applications must set the TdParameter.Direction property when retrieving Output parameter values or when sending and retrieving InputOutput parameters to and from the SQL Engine. The Direction property cannot be set to ReturnValue because the SQL Engine does not support Return Value from Stored Procedures. The Output and InputOutput parameter directions are only applicable to Stored Procedures.

Specifying Parameter Data Type

.NET Applications set TdParameter.TdType or TdParameter.DbType property in order to declare the SQL data type of the parameter send to or received from the SQL Engine.

The Data Provider infers the SQL Engine data type from the TdParameter.Value when application does not explicitly set the parameter data type. It is highly recommend to always set the TdType property because DbType enumeration values are a subset of the SQL data types. Also in some cases one BCL type can map to two or more SQL data types. For example DbType enumeration does not have a value corresponding to the "Interval Day To Second" and System.TimeSpan type maps to the SQL Time and "Interval Day To Second" data types. In this case application must set DbType to Object (vs. Time) to resolve the ambiguity associated with System.TimeSpan or alternatively set TdType to IntervalDayToSecond. The Data Provider maps System.TimeSpan to the SQL Time data type when TdType and DbType properties are not explicitly set.

The Data Provider infers the TdType of a parameter when an application sets the DbType property. The table below shows the mappings.

The Data Provider infers the TdType of a parameter from the type of the TdParameter.Value property when an application does not explicitly set the TdParameter.TdType and TdParameter.DbType properties. The following table shows the mappings.

Specifying Parameter Size, Precision and Scale

The SQL Data Types have additional attributes, for example VarChar data type has "maximum number of characters" and Decimal data type has Precision and Scale attributes. The TdParameter properties should reflect the SQL data type attributes as outlined in the following table:

Note
TdType Enumeration does not have a member corresponding to User Defined Types (UDT), User Defined Array Types or Geospatial Types. The SQL Engine transforms the aforementioned types to and from predefined SQL types.

Configuring TD_ANYTYPE Parameters Using Parameter SecondaryTdType

Configuring TD_ANYTYPE parameters is different than other TdType parameters, where the data type is known by the database (as defined by the stored procedure). TD_ANYTYPE is a placeholder that must be defined in the external stored procedure. By defining TdParameter.TdType to TdType.AnyType, the CALL statement by the .NET Data Provider generates an additional clause transmitted to the database to define the OUT parameter (when setting Command.CommandType to CommandType.StoredProcedure). The actual database data type is defined by TdParameter.SecondaryTdType and is included within the additional clause to the database. A short example follows to illustrate setting the SecondaryTdType.

public static void ExampleTdAnyTypeParameters(TdCommand cmd)
{
   // REPLACE PROCEDURE xspAnyTypeProcedure(IN a INTEGER, OUT result TD_ANYTYPE)
   cmd.CommandType = CommandType.StoredProcedure;
   cmd.CommandText = "xspAnyTypeProcedure";

   cmd.Parameters.Add(null, TdType.Integer);
   cmd.Parameters[0].Direction = Parameter.Direction.Input;
   cmd.Parameters[0].Value = 4;

   cmd.Parameters.Add(null, TdType.AnyType);
   cmd.Parameters[1].Direction = ParameterDirection.Output;
   cmd.Parameters[1].SecondaryTdType = TdType.VarChar;
   cmd.Parameters[1].Size = 20;

   cmd.ExecuteNonQuery();

   // Going to process data of output parameter
   String result = (String)cmd.Parameters[1].Value;

}