ODBC with C#

About

Use the ODBC .NET Data Provider to access data from your C Sharp ADO.NET applications. The .NET Framework Data Provider for ODBC is available through the System.Data.Odbc namespace.

Install

The PostgreSQL ODBC driver can be used to connect to CrateDB from ODBC environments.

Install and configure the PostgreSQL ODBC driver

While Windows typically includes an ODBC driver manager, you can install the unixODBC driver manager on Linux and macOS systems. The PostgreSQL ODBC driver is called psqlODBC.

Windows

Please navigate to the psqlODBC download site to download and install the latest psqlODBC driver for Windows systems. Installing PostgreSQL ODBC drivers on Windows includes an illustrated walkthrough.

Linux

On Linux, install the unixODBC ODBC driver manager and the psqlODBC driver. Installing PostgreSQL ODBC drivers on Linux includes an illustrated walkthrough.

Arch Linux

pacman -Sy psqlodbc

Debian and derivatives

apt install --yes odbc-postgresql odbcinst unixodbc

Red Hat and derivatives

yum install -y postgresql-odbc

Verify installation.

odbcinst -q -d

[PostgreSQL ANSI]
[PostgreSQL Unicode]

macOS

On macOS, install the unixODBC ODBC driver manager and the psqlODBC driver, then register it.

# macOS
brew install psqlodbc unixodbc

odbcinst.ini

[PostgreSQL Unicode]
Description     = PostgreSQL ODBC driver (Unicode version)
Driver          = /usr/local/lib/psqlodbcw.so

odbcinst -i -d -f odbcinst.ini

Verify installation.

odbcinst -q -d

[PostgreSQL Unicode]

Synopsis

example.csproj

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net$(NETCoreAppMaximumVersion)</TargetFramework>
    <GenerateProgramFile>false</GenerateProgramFile>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="System.Data.Odbc" Version="9.*" />
  </ItemGroup>

</Project>

example.cs

using System;
using System.Data.Odbc;

// Connect to database
string connection_string = "Driver={PostgreSQL Unicode};Server=localhost;Port=5432;Uid=crate;Pwd=crate;MaxVarcharSize=1073741824;Sslmode=disable;";
using (OdbcConnection connection = new OdbcConnection(connection_string))
{
    connection.Open();

    // Invoke query
    using (OdbcCommand command = new OdbcCommand("SELECT mountain, height FROM sys.summits ORDER BY height DESC LIMIT 5", connection))
    using (OdbcDataReader reader = command.ExecuteReader())
    {
        // Display results
        while (reader.Read())
            Console.WriteLine($"{reader.GetString(0)}: {reader.GetInt32(1)}");
    }
}

SSL connection

Use the Sslmode=require parameter, and replace username, password, and hostname with values matching your environment. Also use this variant to connect to CrateDB Cloud.

string connection_string = "Driver={PostgreSQL Unicode};Server=testcluster.cratedb.net;Port=5432;Uid=admin;Pwd=password;MaxVarcharSize=1073741824;Sslmode=require;";

Example

Create the files example.csproj and example.cs including the synopsis code shared above.

Start CrateDB using Docker or Podman, then invoke the example program.

docker run --rm --publish=5432:5432 docker.io/crate '-Cdiscovery.type=single-node'

Invoke program.

dotnet run