Visual Studio Server Explorer Integrated with .NET Data Provider for Teradata

Connectivity
Connectivity covers the mechanisms for connecting to the Teradata Database, including driver connectivity via JDBC or ODBC.
Teradata Employee

Visual Studio Server Explorer Integrated with .NET Data Provider for Teradata

The .NET Data Provider for Teradata is integrated with Microsoft's Visual Studio 2005 and 2008.  This article addresses the Visual Studio Server Explorer integration with a Teradata data connection and the various database objects that are supported. 

Other items that are supported, but not described in this article include the following:

  1. Microsoft Query Designer support through Server Explorer - Right clicking the Teradata data connection within Microsoft Visual Studio Server Explorer and selecting New Query supports Query Designer. Query Designer is a graphical tool that displays table and view columns utilized in building database queries.
  2. Toolbox Support - Adding the .NET Data Provider for Teradata objects is supported in Visual Studio Toolbox.  The Teradata data provider objects - TdConnection, TdCommand, TdCommandBuilder and TdDataAdapter are supported Toolbox objects.  Once these objects are added to Toolbox, they are available for dragging and dropping onto design surfaces within Visual Studio.
  3. Window Form Designer Support - Teradata data provider objects - TdConnection, TdCommand, TdCommandBuilder and TdDataAdapter may be dragged from the Visual Studio Toolbox onto a Windows Form.  Code for each of these objects is automatically generated to create an instantiation of the object.
  4. Dataset Designer Support - Datasets, in-memory copies of relational table data and their relationships from supported data sources are supported in Windows Form Projects through right clicking the data sources (Edit Dataset). Creation and population of Datasets are supported through the Teradata data provider object TdDataAdapter.
  5. Data Binding Support - ASP .NET applications and Windows Form applications may create read/write links from application controls with the Teradata database. Data binding enables controls on a Windows Form to retrieve and update Teradata relational data. 
  6. SQL Server Analysis Services Support - SQL Server Analysis Services supports Visual Studio integration through a new class of Business Intelligence projects. An Analysis Services project template supports SQL Server Analysis Services. Data connections to Teradata may be defined containing tables and views used in building metadata cubes.  Metadata cubes may be generated through the definition of fact and dimension tables and the selected measures and dimensions from those tables.
  7. Teradata Generated Data Behavior Support - Microsoft Visual Studio 2005 and 2008 support generated data retrieval through the configuration of the Teradata TdDataAdapter data object in Windows Form project. A TableAdapter connects to the database and is responsible for executing queries against the database to populate DataTables and to provide synchronization between the DataTable and the database table. One of the TdDataAdapter insert command properties GeneratedDataBehavior, is available to select either an IdentityColumn or AllColumns (entire row) GeneratedDataBehavior.
  8. Microsoft Visual Studio IntelliSense Support - IntelliSense is supported by the .NET Data Provider for Teradata to assist in programming. By typing a member selection operator ('.' or '->') or a scope resolution operator ('::') after a class or an object, auto completion is supported.  By positioning the cursor above an identifier such as a function or class, quick information is available on that identifier. Similarly parameter help for identifiers is also available by typing a parenthesis ('(') or angle bracket ('<') after a function.
  9. Help Integration - Context-sensitive help in Visual Studio is available for the .NET Data Provider for Teradata classes and other help topics. Help topics and keywords have been integrated into the Visual Help Collection through compiled help.  The Developers Reference supports context-sensitive help, and we also supply a Developers Guide, which contains items such as What's New for each release, and specific information on using the data provider with the Teradata database.
  10. Entity Data Model Generation - The .NET Data Provider for Teradata release 13.10 supports the ADO.NET Entity Framework 3.5. The Entity Data Model Wizard within Visual Studio is used to generate an Entity Data Model that may be used with the Teradata Entity Provider to write LINQ queries or Entity SQL statements.

Overview

The .NET Data Provider for Teradata is integrated with Microsoft's Visual Studio. Items that are supported include:

  1. Creation of a data connection configured to support Teradata
  2. Hierarchial display of data connections as classical view of objects organized by type or view by database
  3. Data connection node support of Teradata tables, views, stored procedures, macros, user defined functions (UDFs) and user defined types (UDTs)
  4. Expansion of tables to include columns, indexes and keys (foreign, unique and primary)
  5. Expansion of views to include columns
  6. Expansion of stored procedures, macros and UDFs to include parameters
  7. Changing password support from open data connections and in response to expired passwords
  8. Integrated Teradata show commands supporting tables, views, stored procedures, macros, UDFs, UDTs, hash indexes and join indexes

Beginning in Teradata database release 12.0, a performance degradation may be experienced accessing the Teradata schema collections through the .NET Data Provider for Teradata. There are several recommendations to reduce any performance degradation.

  • The data provider contains a couple of connection settings that may be set to optimally retrieve schema information.  Setting UseXViews = false, will use the non-X versions of the system views.  Setting Restrict to Default Database = true, will limit the schema information to the Default Database. See below image showing data connection advanced properties.

 Server Explorer Advanced Data Connection Settings 

  • Enabling the collection of statistics on the Teradata database enables the Teradata optimizer to optimize table access and join plans.  For more information on collecting statistics please see the Teradata manual SQL Reference: Data Definition Statements section on Collecting Statistics or see the many Teradata Developer Exchange articles written.  Here is a link to one of Carrie Ballinger's articles - http://developer.teradata.com/blog/carrie/2010/05/if-you-re-not-collecting-statistics-on-your-dictio....

Creating Data Connections

To enable Server Explorer (if not already enabled), select menu item View and select Server Explorer. To add a data connection, right click the Data Connections node and select Add Connection. To create a connection to Teradata, select the Change button next to Data Source. Within Teradata Database, select the .NET Framework Data Provider for Teradata.  You must have installed the data provider for the provider to show up in this list box.  A view of the Add Connection dialog box is show below.  For details of all of the options (there are many advanced options by selecting the Advanced button), please see the .NET Data Provider for Teradata help documentation in Visual Studio help (if installed this option) or the tdnetdp.chm standalone file.

   Server Explorer Add Data Connection   

Once the minimum data elements are populated - Server name, User Name and Password, depress the Test Connection button to verify the data connection elements are supplied properly.  The Save my password checkbox is useful to save for opening connections easily, but if the machine is shared, you may not wish to check this box.

Hierarchial Display Options for Data Connections

Depending upon the number of data objects (tables, views, macros, etc) the classical view or the database view may be selected.  The classical view will display a group of the same objects together.  With the data connection open, right click the data connection and select Change View to either Database or Classical View.  Classical view will contain a list containing Tables, Views, Stored Procedures, Macros, User Defined Functions and User Defined Types.  Clicking on any item (+ sign) will open the list and display all objects that the user has permission to view.  The database name will preceed the object name.

The Database view will generally be more useful, especially when the user has permissions to view many databases. The image below illustrates the Classic view on the left and the Database view on the right, where the data connection is highlighted.

      Hierarchial Data Connection Views      

Data Connection Node Support for Tables, Views, Stored Procedures, Macros, UDFs and UDTs

Once the data connection is created, it will be opened automatically.  If returning to Visual Studio after the data connection was created earlier, then click on the + sign next to the data connection to re-open it. The diagram above containing the classical view of the database objects displays tables, views, stored procedures, macros, user defined functions and user defined types. 

The top level properties of each of these database objects is described in properties below:

  1. Table and View Properties
    • Catalog - Always null
    • Date_Created - The date and time the table/view was created
    • Date_Modified - The date and time the table/view was last modified
    • Name - The name of the table/view
    • Remarks - The user supplied text or commentary on the table/view
    • Schema - The name of the database in which the table/view resides
    • Type - Either table/view or system table/view reserved for table/view objects in the DBC database
  2. Stored Procedure and Macro Properties
    • Catalog - Always null
    • Created - The date and time the procedure/macro was created
    • Creator - The name of the user who created the procedure/macro
    • LastAltered - The date and time the procedure/macro was last modified
    • Name - The name of the procedure/macro
    • Remarks - The user supplied text or commentary on the procedure/macro
    • Schema - The name of the database in which the procedure/macro resides
    •  Type - Either E (external stored procedure) or P (SQL stored procedure) only for stored procedures
  3. User Defined Functions (UDFs) and User Defined Types (UDTs) Properties
    • Catalog - Always null
    • Created - The date and time the UDF/UDT was created
    • Creator - The name of the user who created the UDF/UDT
    • LastAltered - The date and time the UDF/UDT was last modified
    • Name - The name of the UDF/UDT
    • Remarks - The user supplied text or commentary on the UDF/UDT
    • Schema - The name of the database in which the UDF/UDT resides
    •  FunctionFunctionFFFFFCCCFFunctionType - The UDF type (UDFs only)
      • Aggregate
      • Combined Aggregate and Ordered Analytic
      • Ordered Analytic
      • Standard
      • Table
    • Definition - The UDT definition statement (UDTs only)

Expansion of Tables to Include Columns, Indexes and Keys

Click on the + sign next to a selected table to display the columns, indexes and the keys of the table. To show the table DDL, right click the table and select Show Table.

  1. Columns - Each column defined for each table displays the column name and the data type including the null indicator of the column. Further details are available by right clicking the column and selecting the properties of that column.  The following attributes of the column are available:
  • Data Type - This is the Teradata data type
  • DateTime Scale - Number of fractional digits following the decimal point for date/time types
  • Default - Default column value if defined
  • Format - Format of the column
  • Length - Maximum length of the column in characters
  • Nullable - True if the column is nullable, otherwise false
  • Precision - Maximum number of digits of a numeric data type or the number of digits of the most significant field in an interval
  • Remarks - User supplied text or commentary on the column
  • Scale - Number of digits following the decimal point for decimal data types
  • Schema - The database name
  • Table - Table or view name
  • TdType - The .NET Data Provider data type
  • Temporal Type - Beginning in Teradata Database release 13.10, the temporal data type
    • V - ValidTime temporal type
    • T - TransactionTime temporal type
    • B - Bi-temporal type
    • R - temporal relationship column
    • NULL - non-temporal type

  1. Indexes - Indexes are displayed by either an internal number that is unique within the table, or an index name if defined. Indexes that are join indexes or hash indexes with names may be right clicked to display the DDL for the index. Further details are available from each index property:
  • IndexName - If supplied during creation, the index name, otherwise null
  • IndexNumber - An internally supplied index number that is unique within the table
  • IndexType - The index type as:
    • Derived Column Partition Statistics
    • Hash Index
    • Hash Ordered ALL Covering Secondary
    • Internal Use Index Type
    • Join Index
    • Multi-Column Statistics
    • Non-partitioned Primary
    • Ordering Column of a Composite Secondary Index
    • Partitioned Primary
    • Primary Key
    • Secondary
    • Unique Constraint
    • Value Ordered ALL Covering Secondary
    • Value Ordered Secondary
  • IsPrimary - True if index is a primary key, otherwise false
  • IsUnique - True if the index is unique, otherwise false
  • Name - An internally supplied index number that is unique within the table
  • TransactionTimeConstraint - A constraint type for a TransactionTime temporal constraint:
    • C - Current
    • S - Sequenced
    • N - Non-sequenced
    • NULL - No constraint or TransactionTime not supported
  • ValidTime Constraint - A constraint type for a ValidTime temporal constraint:
    • C - Current
    • S - Sequenced
    • N - Non-sequenced
    • NULL - No constraint or ValidTime not supported

Each index is composed on one or more columns.  The column names are displayed while right clicking the index.  The properties available are:

  • Catalog - Always null
  • Index - An internally supplied index number that is unique within the table
  • Name - The column name
  • Nullable - True if the column is nullable, otherwise false
  • Ordinal - The order of the column in the index
  • Schema - The database name of the table index
  • Table - The table name
  1. Keys - Contraints on the table are returned indicating unique, primary, or foreign attributes.  By selecting the + sign on the keys, a list of keys are available. The keys are identified as either the index name or by the index number, which is the internally created index number unique within the table where the index resides.

The properties of the index will differ depending upon the index type.  The following properties are displayed for primary and unique keys:

  • IndexName - If supplied during creation, the index name, otherwise null
  • IndexNumber - An internally supplied index number that is unique within the table
  • IsPrimary - True if index is a primary key, otherwise false
  • IsUnique - True if the index is unique, otherwise false
  • Name - An internally supplied index number that is unique within the table
  • Schema - The database name of the table index
  • Table - The table name

If the key is a foreign key, the following properties are available:

  • Catalog - Always null
  • PrimaryCatalog - Always null
  • PrimarySchema - The database name in which the primary key (parent table) resides
  • PrimaryTable - The table name of the primary key table
  • Schema - The database name of the foreign key (referencing) table
  • Table - The table name of the foreign key (referencing) table

Expansion of Views to Include Columns

Click on the + sign next to a selected view to display the columns of the view. To show the view DDL, right click the view and select Show View.

  1. Columns - Each column defined for each view displays the column name and the data type including the null indicator of the column. Further details are available by right clicking the column and selecting the properties of that column.  The following attributes of the column are available:
  • Data Type - This is the Teradata data type
  • DateTime Scale - Number of fractional digits following the decimal point for date/time types
  • Default - Default column value if defined
  • Format - Format of the column
  • Length - Maximum length of the column in characters
  • Nullable - True if the column is nullable, otherwise false
  • Precision - Maximum number of digits of a numeric data type or the number of digits of the most significant field in an interval
  • Remarks - User supplied text or commentary on the column
  • Scale - Number of digits following the decimal point for decimal data types
  • Schema - The database name
  • TdType - The .NET Data Provider data type
  • Temporal Type - Beginning in Teradata Database release 13.10, the temporal data type
    • V - ValidTime temporal type
    • T - TransactionTime temporal type
    • B - Bi-temporal type
    • R - temporal relationship column
    • NULL - non-temporal type
  • View - The name of the view

Expansion of Stored Procedures, Macros and User Defined Functions to Include Parameters

Click on the + sign next to a selected stored procedure, macro or UDF to display the parameters. To show the DDL for the database object, right click the object and select Show Procedure, Show Macro or Show Function.

  1. Each parameter defined for each database object type displays the object name and the data type of the parameter. Further details are available by right clicking the parameter and selecting the properties.  The following attributes of the parameter are available:
  • Data Type - This is the Teradata data type
  • DateTime Scale - Number of fractional digits following the decimal point for date/time types
  • Direction - The parameter direction as Input, Output or InputOutput (only procedures and UDFs)
  • Format - Format of the parameter
  • Length - Maximum length of the parameter in characters for CHAR, VARCHAR, CLOBs and INTERVALS.  The maximum length in bytes for BYTE, VARBYTE and BLOB data types.
  • Nullable - True for nullable parameters, otherwise false (for stored procedures only)
  • Precision - Maximum number of digits of a numeric data type or the number of digits of the most significant field in an interval
  • Procedure Name - The procedure name (only stored procedures)
  • Macro Name - The macro name (only macros)
  • Function Name - The UDF name (only UDFs)
  • Remarks - User supplied text or commentary on the parameter
  • Scale - Number of digits following the decimal point for decimal data types
  • Schema - The database name
  • TdType - The .NET Data Provider data type
  • Temporal Type - Beginning in Teradata Database release 13.10, the temporal data type
    • V - ValidTime temporal type
    • T - TransactionTime temporal type
    • B - Bi-temporal type
    • R - temporal relationship column
    • NULL - non-temporal type

Changing Password Support from Open Data Connections and in Response to Expired Passwords

To change your password from an open data connection, right click the data connection from Server Explorer and select Change Password. A dialog box will appear as below.  Supply the new password and the confirmation password, then select OK.

        

Confirmation of the changed password will result in the dialog box below:

Password support for passwords that may be expired from Teradata for a user, are supported from the advanced properties of the data connection.  New Password is the advanced property that may be used to automatically supply a new password when the database expires the password.  This property is available while defining a new data connection and from a data connection that is already defined.  To supply this property of an existing data connection, right click the data connection and select Modify Connection. Navigate to the Advanced Property page and supply a password for the property New Password in the Authentication group.

    

Integrated Teradata Show Commands Supporting Tables, Views, Stored Procedures, Macros, UDFs, UDTs, Hash Indexes and Join Indexes

To show the Data Definition Language (DDL) for tables, views, stored procedures, macros, UDFs, UDTs, hash indexes and join indexes, right click the appropriate database object, and select the appropriate Show command (Show Procedure, Show Table, ...). Each of the database objects that support the show command, have separate display elements in Server Explorer, except for indexes, which appear under tables.

 

Conclusion

The Introduction lists many other features that are available through integration with Visual Studio, other than the Server Explorer support described in this article.  There are blogs within Teradata Developer that are available that describe our .NET Entity Data Provider for Teradata.  Other articles and blogs will appear to describe some of the other integration features that are supported in Visual Studio 2005 and 2008. Visual Studio 2010 is planned to be supported in a subsequent release.