Advantages to Using The .NET Data Provider for Teradata Provider Specific Types

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Teradata Employee

The Teradata Database has support for several data types where there is no equivalent Framework Class Library (FCL) type.  For these types, the Teradata Data Provider has a corresonding Provider Specific Type.  All the provider types support:

  • Comparison operators
  • Conversions to different types using implicit, explicit, and conversion methods contained in the System.Convert class.
  • Arithmetic operations
  • Serialization
  • String conversion with support for format specifiers and culture
  • String parsing with support for culture
  • Types are nullable

 

Most of the provider types are Value Types.  The only two provider types that are not Value Types are TdBlob and TdClob.

 

Here are a few examples of using Provider Specific Types:

// Example of parsing and conversion
TdDecimal decimalExample = TdDecimal.Parse(“32421.21342”);

// When converting, explicit casting or the Convert methods can be used
Double doubleConversion = (Double)decimalExample;
Int32 intConversion = Convert.ToInt32(decimalExample);

// Arithmetic operation and converting to a string
TdNumber numberOp1 = 32132.421m;
TdNumber numberOp2 = TdNumber.Parse(“294824.213e50”);
TdNumber numberResult = numberOp1 + numberOp2;
String numberString = numberResult.ToString(“g”);

// Comparision operators
TdPeriodDate periodDateOp1 =
    new TdPeriodDate(new TdDate(2012, 4, 15), new TdDate(2012, 4, 30));
TdPeriodDate periodDateOp2 =
    new TdPeriodDate(new TdDate(2012, 4, 15), new TdDate(2012, 5, 30));
If (periodDateOp1 < periodDateOp2)
{
    Console.Writeline("{0} is less than {1}",
        periodDateOp1.ToString(), periodDateOp2.ToString());
}

There are several advantages in using the Provider Specific Types.  Here is a list of some of the advantages:

  1. Provider Specific Types have similar behavior, features, and functionality as the Teradata Types. For example, TdDecimal can support a precision of up to 38 digits.  System.Decimal can only support a precision of up to 29. TdNumber can also support a mantissa that contains up to 38 digits.  System.Double only supports a mantissa that can contain up to 15 digits.    Here is an example of using a TdNumber and corresponding TdType.Number to send data to Teradata:

    public void ParameterExample(TdCommand cmd)
    {
        // Using DbType enumeration, DbType.String must be used if a number
        // that contains more than 15 digits in the mantissa is sent to Teradata.
        //
        // By using the TdType.Number enumeration with a TdNumber instance,
        // the Teradata Database will not have to perform a string conversion.
        cmd.CommandText = "SELECT stringCol1 FROM TableExample WHERE numberCol1 = ?";

        // setting up parameter using TdType.Number
        // num1 = 3.2108324569094781290389e-30
        TdNumber num1 = new TdNumber(0xb1b24795, 0x9883e665, 0x000006cc, 0, 0, -52);
        TdParameter p1 = new TdParameter() ;
        p1.Value = num1;
        p1.TdType = TdType.Number;

        cmd.Parameters.Add(p1);
        TdDataReader dr = cmd.ExecuteReader();

        //
        // Retrieve Data
        //
    }

    The closest FCL type that matches a Teradata NUMBER type is a System.Double.  Unfortunately, System.Double cannot represent a number that contains a 23 digit mantissa.

  2. The Provider Specific Types have been designed so that minimal operations have to be performed to convert the data into the corresponding Teradata native format that is used to send data to Teradata.  Using the corresponding Provider Specific Type and TdType enumeration when sending data can improve performance.

  3. All provider types support the comparison operators.  This is important when using Interval and Period types.   Most of the FCL types cannot support these Teradata types.  The exception to this are some of the Interval types that can be represented by a System.TimeSpan.  If an FCL type is used to store these types of data, additional coding must be performed if comparisions are required. 

  4. Most Provider Specific Types contain support for a Format Specifier ("L")  that can convert the type to a string in the format of the corresponding Teradata Type's string literal representation.  The exception to this is TdNumber.  The Teradata NUMBER type does not contain support for a string literal representation. 

    Here is an example of converting a TdTimestamp instance into its equivalent Teradata string literal:

    TdTimestamp ts1 = new TdTimestamp(2012, 04, 15, 23, 59, 59);
    Console.WriteLine("{0}", ts1.ToString("L"));

    The output that will be written to the console is: TIMESTAMP'2012-04-15 23:59:59'

  5. Provider Specific Types can be loaded into a System.Data.DataTable using the TdDataAdapter. The property TdDataAdapter.ReturnProviderSpecificTypes must be set to true in order for Provider Specific Types to be loaded into the DataTable. Here is an example:

    public void AdapterExample(TdConnection cn)
    {
    TdCommand selectCmd = cn.CreateCommand();

    System.Data.DataTable dt = new System.Data.DataTable("TableExample");

    TdDataAdapter da = new TdDataAdapter(selectCmd);

    selectCmd.CommandText =
    "SELECT intCol1, numberCol2, numberCol3, decimalCol4 FROM TableExample";

    da.ReturnProviderSpecificTypes = true;
    da.Fill(dt);

    // The columns numberCol2 and numberCol3 will be filled with TdNumber instances.
    // The column decimalCol4 will be filled with instances of TdDecimal.
    }

  6. TdBlob or TdClob must be used if a Teradata BLOB or CLOB are retrieved using Deferred Mode.  There is an example of using a TdClob to retrieve data of a CLOB towards the end of this blog.

Here are all the Provider Specific Types that are supported by the data provider as of the 14.0 release:

Provider Specific Type

Teradata Database Type

Closest FCL Match

TdDecimal

Decimal(m, n)

Decimal

TdNumber

Number(*),  Number(*, n), Number(m,n)

Double

TdPeriodDate

Period Date

String

TdPeriodTime

Period Time

String

TdPeriodTimeWithTimeZone

Period Time With Time Zone

String

TdPeriodTimestamp

Period Timestamp

String

TdPeriodTimestampWithTimeZone

Period Timestamp With Time Zone

String

TdDate

Date

DateTime

TdTime

Time

TimeSpan

TdTimeWithTimeZone

Time With Time Zone

String

TdTimestamp

Timestamp

DateTime

TdTimestampWithTimeZone

Timestamp With Time Zone

String

TdIntervalYear

Interval Year

String

TdIntervalMonth

Interval Month

String

TdIntervalYearToMonth

Interval Year To Month

String

TdIntervalDay

Interval Day

TimeSpan

TdIntervalDayToHour

Interval Day To Hour

TimeSpan

TdIntervalDayToMinute

Interval Day To Minute

TimeSpan

TdIntervalDayToSecond

Interval Day To Second

TimeSpan

TdIntervalHour

Interval Hour

TimeSpan

TdIntervalHourToMinute

Interval Hour To Minute

TimeSpan

TdIntervalHourToSecond

Interval Hour To Second

TimeSpan

TdIntervalMinute

Interval Minute

TimeSpan

TdIntervalMinuteToSecond

Interval Minute To Second

TimeSpan

TdIntervalSecond

Interval Second

TimeSpan

TdBlob *

Blob

 

TdClob *

Clob

 
 

* TdBlob and TdClob are used to retrieve LOBs when using Deferred mode.  LOBs can also be retrieved using Inline mode.  Refer to the Teradata Database documentation for more information on these modes.  For more information on TdBlob and TdClob refer to the .Net Data Provider for Teradata Developer's Guide and Reference.  After a TdClob or TdBlob has been retrieved (by calling TdDataReader.GetTdClob or TdDataReader.GetTdBlob) the data can be read into an application buffer.  The following example shows how to use TdClob to retrieve data:

 

static public void GetEmployeeResume(Int32 employeeId, String fileName,
TdConnection cn)
{
// The column Resume is a CLOB.
TdCommand cmd =
new TdCommand("Select EmpResume from Employee Where ID = ?", cn);

// Initialize the parameter with employee ID.
TdParameter id = cmd.CreateParameter( );
id.Direction = System.Data.ParameterDirection.Input;
id.Value = employeeId;
id.TdType = TdType.Integer;

cmd.Parameters.Add(id);

//Execute the Query
//The CLOB will be retrieved using Deferred Mode.
//If the data of the CLOB is to be retrieved inline, Inline Mode must be used.
//This is done by passing CommandBehavior.SequentialAccess to ExecuteReader.
using (TdDataReader rd = cmd.ExecuteReader( ))
{
// checking if any rows have been returned
if (rd.Read() == false)
{
return;
}

Char [] buffer = new Char[1024];

// Going to write the resume to a file
using (StreamWriter sw = new StreamWriter(fileName, false,
System.Text.Encoding.Default))
{
using (TdClob resumeClob = rd.GetTdClob(0))
{
// checking if the clob is null
if (resumeClob.IsNull == true)
{
return;
}

while (true)
{
Int64 charsRead = resumeClob.Read(buffer, 0, buffer.Length);

// checking if any data has been read
if (0 == charsRead)
{
break;
}

sw.Write(buffer, 0, (Int32)charsRead);
}
}
}
}
cmd.Dispose( );
}

Documentation for all the Provider Specific Types are located in the .NET Data Provider for Teradata Developer's Reference.