通過 ODBC 訪問 PostgreSQL

2024-10-03

448
0
ODBC
2024-10-06

甚麼是 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();
}