Schema Collections

The Schema collections return metadata for the Advanced SQL Engine objects. The notable metadata columns for the Period(Time) data type are:

Schema Table

The TdDataReader.SchemaTable returns result set metadata. The notable metadata columns for the Period(Time) data type are:

Configuring Parameters

The following example shows how to configure a Period(Time) parameter using DbType type declaration and a Base Class Library (BCL) value. 

Note
The Data Provider sends a VarChar data type to the SQL Engine when TdParameter.DbType is set to DbType.String. The SQL Engine will perform an implicit conversion to the Period(Time) data type when required. The "SQL Functions, Operators, Expressions and Predicates" manual documents all implicit and explicit Data Type conversions.

TdParameter deliveryPeriod = new TdParameter();

deliveryPeriod.DbType = DbType.String;

deliveryPeriod.Value = "(00:00:10.111, 00:00:11.820)";

The following example shows how to configure a Period(Time) parameter using TdType type declaration and a Provider Specific value. The TdParameter.Scale property reflects the maximum number of digits for the Fractional Second component and it must be equal to or less than the target SQL Engine object (i.e. Column, Expression, Stored Procedure Parameters and etc.) declaration. The range of values are from Zero to Six. It is recommend to always set TdParameter.Scale property, however the Data Provider will utilize TdPeriodTime.Scale property value when the TdParameter.Scale, TdParameter.Precision and TdParameter.Size  properties are set to zero. .NET Applications can retrieve metadata from the Schema Collections or the Schema Table and apply the metadata to the TdParameter object.

TdParameter deliveryPeriod = new TdParameter();

deliveryPeriod.TdType = TdType.PeriodTime;

// Maximum number of digits for the Fractional Second component of Time.
deliveryPeriod.Scale = 6;

deliveryPeriod.ProviderSpecificValue = new TdPeriodTime(new TdTime(10, 15, 25, 123), new TdTime(15, 0, 0, 0));

Specifying Period(Time) as Literal

The syntax for the Period(Time) Literal is PERIOD'(hh:mi:ss.ffffff, hh:mi:ss.ffffff)'. Period(Time) literals consist of the word PERIOD followed by character string literal representation of a beginning and ending bounds. The fractional seconds can vary from zero to six characters.

Note
We recommend to always use Parameters in order to take advantage of the SQL Engine's Request Cache.

The following example shows a Period(Time) Literal embedded in the command Text.

using (TdConnection cn = new TdConnection("data source=x;UserId=y;Password=z;"))
{
    cn.Open();
    TdCommand cmd = cn.CreateCommand();

    cmd.CommandText = "INSERT INTO Policy (PolicyId, Validity) VALUES (2001, PERIOD'(08:00:00.000, 17:00:00.000)')";

    cmd.ExecuteNonQuery();
}

Retrieving Period(Time) Data

The following methods and properties return the column or parameter value as a System.String structure.

1. TdDataReader.GetString
2. TdDataReader.GetValue
3. TdDataReader.GetFieldValue<String>
4. TdParameter.Value
5. TdParameter.GetValue<String>

The following methods and properties return the column or parameter value as a TdPeriodTime structure.

1. TdDataReader.GetTdPeriodTime
2. TdDataReader.GetProviderSpecificValue
3. TdDataReader.GetFieldValue<TdPeriodTime>
4. TdParameter.ProviderSpecificValue
5. TdParameter.GetValue<TdPeriodTime>

using (TdConnection cn = new TdConnection("data source=DS1;UserId=Joe;Password=XY;"))
{
    cn.Open();

    TdCommand cmd = cn.CreateCommand();
    cmd.CommandText = "SELECT PolicyId, Validity from Policy where Validity > ?";
    cmd.Parameters.Add("Validity", TdType.PeriodTime);
    cmd.Parameters[0].Scale = 6;
    cmd.Parameters[0].Value = new TdPeriodTime(new TdTime(8, 0, 0), new TdTime(17, 0, 0) );

    using (TdDataReader reader = cmd.ExecuteReader())
    {
        if (reader.HasRows)
        {
            while (reader.Read())
            {
                Console.WriteLine("[String] Policy Validity = {0}", reader.GetString(1));
                Console.WriteLine("[TdPeriodTime] Policy Validity = {0}", reader.GetTdPeriodTime(1).ToString());
            }
        }
    }
}

/* Output:
    [String] Policy Validity =  (09:30:00.000, 16:30:00.000)
    [TdPeriodTime] Policy Validity =  (09:30:00.000, 16:30:00.000) 
*/

See Also

Data Type Mappings

Accessor Methods for Retrieving Data

Configuring Parameters and Parameter Data Types