[C#] Connect to Teradata Database via .NET Data Provider
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:
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
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.
Use the Connection Information dialog box to create, edit, and delete data sources for .Net Data Provider for Teradata. The dialog box is also used to connect to existing Teradata.Net data sources.
This file is located in \Users\<username>\AppData\Teradata\SQL Assistant
1. Open Teradata SQL Assistant.
2. Select Teradata .NET from the provider drop-down list, next to the Connect tool button.
3. Click the Connect icon or go to Tools > Connect.
4. Use the Connection Information dialog box to select a .NET data source.
5. Use the Advanced tab to make additional changes to a data source.
Defining a .NET Data Provider for Oracle Data Source
1 Open Teradata SQL Assistant.
2 Select Oracle.NET from the provider drop-down list next to the Connect tool button.
3 Click the Connect icon or select Tools > Connect.
4 Use the Connection Information dialog box to select a .NET data source.
5 Use the Advanced tab to make additional changes to a data source.