[C#] Connect to Teradata Database via .NET Data Provider

access_time 6 years ago visibility8430 comment 2

In this post, I will demonstrate how to connect to Teradata database via .NET Data Provider for Teradata using C#.

Prerequisites

Install the .NET Data Provider for Teradata from the following link:

Teradata Tools and Utilities Windows Installation Package

Step 1 Create a Console Project

Name it as’' ‘ConsoleApps.Teradata’.

Add reference to library ‘.NET Data Provider for Teradata’ as shown in the following screenshot:

image

The version in my PC is 14.11.0.1. You can always download the latest version from Teradata official site.

Step 2 Create a method to read data from Teradata

Remember to import the namespace ‘Teradata.Client.Provider’.

using System;
using Teradata.Client.Provider;

namespace ConsoleApps.Teradata
{
    class Program
    {
        static void Main(string[] args)
        {
            DisplayEmployeeInfoFromTeraData();
            Console.ReadLine();

        }

        private static void DisplayEmployeeInfoFromTeraData()
        {
        }
    }
}

Step 3 Implement the method

Create connection, command and adapter objects to read data in a standard ADO.NET way.

The sample code will be similar to what I’ve provided here:

private static void DisplayEmployeeInfoFromTeraData()
        {
            var employeeData = new DataSet();

            var connectionString = "Data Source=192.168.121.128;User ID=dbc;Password=dbc;";
            using (var connection = new TdConnection(connectionString))
            {
                var sql = @"SELECT Emp.EmployeeID,
    Emp.EmployeeName,
    
    CASE
        WHEN  Emp.Gendar = 'M' THEN 'Dear MR ' || Emp.EmployeeName ELSE 'Dear MS ' ||Emp.EmployeeName
    END AS ""Employee Salutation""
    FROM TD_MS_SAMPLE_DB.Employee Emp; ";
                var command = new TdCommand(sql, connection);
                var adapter = new TdDataAdapter(command);
                connection.Open();
                adapter.Fill(employeeData);
                connection.Close();

            }

            DisplayEmployeeData(employeeData);
        }

        private static void DisplayEmployeeData(DataSet employeeData)
        {
            if (employeeData.Tables != null && employeeData.Tables.Count > 0)
            {
                var employees = employeeData.Tables[0];
                var columnCount = employees.Columns.Count;
                foreach (DataRow row in employees.Rows)
                {
                    for (int i = 0; i < columnCount; i++)
                    {
                        var col = employees.Columns[i];
                        Console.WriteLine("{0} = {1}", col.ColumnName, row[i]);   
                    }
                    Console.WriteLine();
                }
                Console.WriteLine("{0} Records", employees.Rows.Count);
            }
        }

In this sample, I am connecting to a local Teradata virtual machine with address as ‘192.168.121.128’. One thing to notice is that all the class names are prefixed with ‘Td’, e.g. Connection class is named TdConnection, Command class is named TdCommand and so forth.

Step 4 The result

image

Summary

Teradata is used in many data warehousing projects. If you really wants to use it in your OLTP projects, you can connect to it via .NET Data Provider for Teradata or ODBC Teradata provider. The syntax will be similar as connecting to SQL Server, Oracle or any other databases supported by ADO.NET.

info Last modified by Raymond 2 years ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Follow Kontext

Get our latest updates on LinkedIn or Twitter.

Want to publish your article on Kontext?

Learn more

More from Kontext

visibility 568
thumb_up 0
access_time 11 months ago

From Teradata 16, LEAD and LAG OLAP functions are supported. The LAG function returns data from a row preceding the current row at a specified offset in a window group; the LEAD function accesses data from a row following the current row at a specified offset in a window group. Table  ...

visibility 3
thumb_up 0
access_time 2 days ago

In article  Connect to MySQL in .NET 5 , I showed how to use MySQL.Data ADO.NET approach to read data from MySQL database. This article shows a different approach which utilizes EntityFramework Core APIs. We will use code-first approach even there is already test table created.  There ...

visibility 14
thumb_up 0
access_time 29 days ago

Teradata Wallet (tdwallet) is a facility for storage of sensitive/secret information, such as database user password. It is included in  Teradata client utility tools .  For items stored in tdwallet, there are two parts: name of the item that uniquely identities it and the value of the ...