Connect to Any Database from .NET Core Using DataDirect ODBC Connectors on Windows

Introduction

In this tutorial, we will walk through how to connect to any database from .NET Core using Progress DataDirect’s ODBC Connectors on Windows. This tutorial uses Progress DataDirect’s ODBC Driver for Apache Hadoop Hive to demonstrate how to connect to it from .NET Core, but the same steps can be applied to any of our ODBC Connectors. 

Install Progress DataDirect ODBC Connectors

  1. Download Progress DataDirect’s ODBC Driver for Apache Hadoop Hive or any other ODBC driver.
  2. Install the ODBC connector by running the executable.

Determining the ODBC Driver name

  1. Open ODBC Administrator and Click on Add. In this window, find the DataDirect ODBC connector we installed to connect to the database.
  2. Find the full driver name there and keep note of it.


    2

Install System.Data.Odbc Nuget Package

  1. Create a New .NET Core project or use an existing .NET Core project.
  2. If using Visual Studio, install the package in the project by running the below command in the Package Manager console in Visual Studio

    Install-Package System.Data.Odbc -Version 4.7.0

  3. If using Terminal, run the below command in the project space to install the package

    dotnet add package System.Data.Odbc --version 4.7.0

Connecting to Database

  1. Below is a simple program to connect to the Hive instance. Using Progress DataDirect’s ODBC Connector, run a SQL query and print the results.
    using System;
    using System.Data.Odbc;
     
    namespace DataDirectODBCConnect
    {
        class Program
        {
            static void Main(string[] args)
            {
                OdbcConnectionStringBuilder builder = new OdbcConnectionStringBuilder
                {
                    Driver = "DataDirect 8.0 Apache Hive Wire Protocol"
                };
                builder.Add("HostName", "192.168.1.1");
                builder.Add("PortNumber", "10000");
                builder.Add("Database", "mydb");
                builder.Add("UID", "username");
                builder.Add("PWD", "password");
     
                using (OdbcConnection connection = new OdbcConnection(builder.ConnectionString))
                {
                    string sqlQuery = "SELECT activityid, emailaddress, activitydate FROM emails limit 100";
                    OdbcCommand command = new OdbcCommand(sqlQuery, connection);
                    connection.Open();
                    OdbcDataReader reader = command.ExecuteReader();
     
                    //Print Column Names
                    for (int i=0; i< reader.FieldCount; i++)
                    {
                        Console.Write(reader.GetName(i) + "\t");
                    }
     
                    Console.Write("\n");
     
                    if(reader.HasRows)
                    {
                        while(reader.Read())
                        {
                            Console.WriteLine("{0}\t{1}\t{2}", reader.GetInt32(0), reader.GetString(1), reader.GetString(2));
                        }
                    }
     
                    reader.Close();
                    command.Dispose();
                }
     
            }
        }
    }
  2. If using a different Progress DataDirect ODBC connector, replace the Driver name, Connection string configuration as needed.
  3. Build the solution and run it. The data will print on your console.

    3


    We hope this tutorial helped you to connect to Hive from .NET Core using Progress DataDirect’s ODBC Driver for Apache Hadoop Hive. Feel free to download any Progress DataDirect ODBC connector and try it out. Please contact us with any questions and we will be happy to help.

ODBC TUTORIAL

Connect to Any Database from .NET Core Using DataDirect ODBC Connectors on Windows

View all Tutorials

Connect any application to any data source anywhere

A product specialist will be glad to get in
touch with you