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

Introduction

In this tutorial, we will walk through how you to connect to any database from .NET Core using Progress DataDirect ODBC Connectors on Linux. This tutorial uses the Progress DataDirect’s ODBC Driver for Apache Hadoop Hive to demonstrate how we can 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 needed.
  2. Extract the package by running the below command.

     

    tar -xvf PROGRESS_DATADIRECT_ODBC_HIVE_LINUX_64.tgz

     

  3. Install the driver by running the below command.

    ./PROGRESS_DATADIRECT_ODBC_8.0_LINUX_64_INSTALL.bin

  4. Go to the install path. By default it should be /home/<user>/Progress/DataDirect/ODBC_80_64bit and run either odbc.csh or odbc.sh based on the type of the shell being used.
  5. This will set three environment variables. Verify if these are present before going further.

    [progress@centos7264 ODBC_80_64bit]$ echo $LD_LIBRARY_PATH && echo $ODBCINI && echo $ODBCINST
    /home/progress/Progress/DataDirect/ODBC_80_64bit/lib:/home/progress/Progress/DataDirect/ODBC_80_64bit/jre/lib/amd64/server
    /home/progress/Progress/DataDirect/ODBC_80_64bit/odbc.ini
    /home/progress/Progress/DataDirect/ODBC_80_64bit/odbcinst.ini

Install unixODBC

  1. Install the unixODBC package by running the below command.
    1. For CentOS:

      sudo yum install unixODBC-devel unixODBC

    2. For Ubuntu/Debian:

      sudo apt-get install unixODBC-dev unixODBC

  2. After installing unixODBC, copy the contents of /home/<user>/Progress/DataDirect/ODBC_80_64bit/odbcinst.ini and paste them in the file at /etc/odbcinst.ini

Install System.Data.Odbc Nuget Package

  1. Create a New .NET Core project or use an existing .NET Core project.
  2. 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 a Database

  1. Below is a simple program to connect to a 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 the terminal.
dotnet <yourproject>.dll
 

Results

We hope this tutorial explained how to connect to any database 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 Linux

View all Tutorials

Connect any application to any data source anywhere

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