通過 ODBC 訪問 PostgreSQL

甚麼是 ODBC,維基百科這樣說

"ODBC(Open Database Connectivity,開放資料庫互連)提供了一種標準的 API( 應用程式編程介面)方法來訪問資料庫管理系統(DBMS)。ODBC 的運用形態通常是由應用程式經過一個稱之為 ODBC 管理器的工具,建立一個 DSN,指明需要呼叫的 ODBC 驅動程式,從而訪問對應的資料庫。"

簡單來說,可以透過 ODBC 來簡化連線字串的管理,最近工作上,需要用到它,趕緊趁無風無雨的颱風天,重新學習一下。

ODBC - 維基百科,自由的百科全書 (wikipedia.org)

開發環境

  • Windows 11 Home
  • .NET 8
  • Rider 2024.2
  • PostgreSQL 12

連線字串

PostgreSQL connection strings - ConnectionStrings.com

安裝

在作業系統安裝 PostgreSQL ODBC Driver,下載位置為  psqlodbc - PostgreSQL ODBC driver

 

 

測試環境

這裡我要用 EF Core + TestContainer 建立環境以及測試資料,開一個測試專案,安裝以下套件

dotnet add package Testcontainers.PostgreSql --version 3.10.0
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL --version 8.0.8

 

在測試專案一起動的時候就建立 PostgreSqlContainer 並呼叫 InsertTestData 新增測試資料

[TestClass]
public static class Initialize
{
    static IContainer? PostgreSqlContainer;

    [AssemblyInitialize]
    public static void AssemblyInitialize(TestContext context)
    {
        Console.WriteLine("AssemblyInitialize");
        PostgreSqlContainer = CreatePostgreSQLContainer();
        PostgreSqlContainer.StartAsync().GetAwaiter().GetResult();

        InsertTestData();
    }

    private static void InsertTestData()
    {
        var connectionString = "Host=localhost;Port=5432;Database=employee;Username=postgres;Password=postgres";
        var dbContextOptions = new DbContextOptionsBuilder<EmployeeDbContext>()
            .UseNpgsql(connectionString)
            .Options;
        using var dbContext = new EmployeeDbContext(dbContextOptions);
        dbContext.Database.EnsureCreated();
        dbContext.Employees.Add(new Employee
        {
            Id = Guid.NewGuid(),
            Name = "yao",
            Age = 18,
            Remark = null,
            CreateAt = DateTime.UtcNow,
            CreateBy = "yao"
        });
        dbContext.SaveChanges();
    }

    [AssemblyCleanup]
    public static void AssemblyCleanup()
    {
        Console.WriteLine("AssemblyCleanup");
        PostgreSqlContainer.StopAsync().GetAwaiter().GetResult();
        PostgreSqlContainer.DisposeAsync().GetAwaiter().GetResult();
    }

    private static IContainer CreatePostgreSQLContainer()
    {
        var waitStrategy = Wait.ForUnixContainer().UntilCommandIsCompleted("pg_isready");
        var container = new ContainerBuilder()
            .WithImage("postgres:12-alpine")
            .WithName("postgres.12")
            .WithPortBinding(5432)
            .WithWaitStrategy(waitStrategy)
            .WithEnvironment("POSTGRES_USER", "postgres")
            .WithEnvironment("POSTGRES_PASSWORD", "postgres")
            .WithEnvironment("POSTGRES_DB", "employee")
            .Build();

        return container;
    }
}

 

使用  ADO.NET OdbcConnection 訪問資料庫

在專案安裝 ODBC  套件 

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

 

用 ADO.NET 讀取資料

[TestMethod]
public async Task ReadForAdoNet()
{
    var connectionString =
        "Driver={PostgreSQL Unicode};Server=localhost;Port=5432;Database=employee;Uid=postgres;Pwd=postgres;";
    await using var connection = new OdbcConnection(connectionString);
    await using var command = new OdbcCommand();
    await connection.OpenAsync();
    command.Connection = connection;
    command.CommandText = @"select * from ""Employee""";
    await using var reader = await command.ExecuteReaderAsync();

    while (true)
    {
        var hasData = await reader.ReadAsync();
        if (hasData == false)
        {
            break;
        }

        for (var i = 0; i < reader.FieldCount; i++)
        {
            var name = reader.GetName(i);
            var value = reader.GetValue(i);
            Console.WriteLine($"{name}: {value}");
        }
    }

    await connection.CloseAsync();
}

 

搭配 Dapper 做 Model Mapping

安裝套件

dotnet add package Dapper --version 2.1.35

 

[TestMethod]
public async Task ReadForDapper()
{
    var connectionString =
        "Driver={PostgreSQL Unicode};Server=localhost;Port=5432;Database=employee;Uid=postgres;Pwd=postgres;";
    await using var connection = new OdbcConnection(connectionString);
    var sql = @"select * from ""Employee""";
    var data = connection.Query<Employee>(sql).ToList();
    await connection.CloseAsync();
}

 

用 DSN 連接 PostgreSQL

ODBC 只需要提供 DSN 名稱,而非完整的連接字串,就能訪問資料庫,首先要先配置 ODBC,Win+S 搜尋 ODBC

 

名稱就是 DSN,等下連線字串改用這個名稱

 

範例程式如下,用 DSN 就能訪問資料庫了

[TestMethod]
public async Task ReadForDsn()
{
    var dsn = "PostgreSQL";
    var connectionString = $"DSN={dsn}";
    await using var connection = new OdbcConnection(connectionString);
    var sql = @"select * from ""Employee""";
    var data = connection.Query<Employee>(sql).ToList();
    await connection.CloseAsync();
}

 

心得

ODBC DSN 的確可以簡化管理者的維護工作,多墊了一層 ODBC Driver,唯一要注意的應該就是效能議題了吧,下圖是問  AI 得到的結果

下面這個連結則是別人寫的性能比較
SSIS 数据流性能比较 (ADO.NET vs. OLE DB vs. ODBC) - larryqian86 - 博客园 (cnblogs.com) 

另外,EF Core 預設也沒有支援 ODBC,QQ

範例位置

sample.dotblog/ODBC/Lab.ODBC at 10d46b3ddb7e3e4fa4418212ba382471574174b8 · yaochangyu/sample.dotblog (github.com)

 

若有謬誤,煩請告知,新手發帖請多包涵


Microsoft MVP Award 2010~2017 C# 第四季
Microsoft MVP Award 2018~2022 .NET

Image result for microsoft+mvp+logo