技術文章 » 在Linux平臺下使用.NET Core存取Access資料庫讀取mdb檔案資料

在Linux平臺下使用.NET Core存取Access資料庫讀取mdb檔案資料

2023-11-22 18:00:15

今天有群友在群裡問 C# 能不能在 Linux 下存取 Access資料庫？

我覺得這很有趣，因此研究折騰了一下，也因為很久沒有寫博文了，所以特意上來寫博文分享經驗。

執行環境

  • 作業系統：Ubuntu 22.04.3 LTS (Jammy)
  • 開發工具：Visual Studio 2022 (17.8.0)
  • 執行時版本：.NET Runtime 8.0
  • 依賴庫：unixodbc、mdbtools、odbc-mdbtools

依賴庫安裝

apt-get update
sudo apt-get install unixodbc mdbtools odbc-mdbtools

依賴庫版本資訊

  • apt list --installed | grep odbc
libodbc1/jammy,now 2.3.9-5 amd64 [installed,automatic]
libodbc2/jammy,now 2.3.9-5 amd64 [installed,automatic]
libodbccr2/jammy,now 2.3.9-5 amd64 [installed,automatic]
libodbcinst2/jammy,now 2.3.9-5 amd64 [installed,automatic]
odbc-mdbtools/jammy,now 1.0.0+dfsg-1 amd64 [installed]
odbcinst1debian2/jammy,now 2.3.9-5 amd64 [installed,automatic]
odbcinst/jammy,now 2.3.9-5 amd64 [installed,automatic]
unixodbc-common/jammy,now 2.3.9-5 all [installed,automatic]
unixodbc/jammy,now 2.3.9-5 amd64 [installed]
  • apt list --installed | grep mdb
liblmdb0/jammy,now 0.9.24-1build2 amd64 [installed,automatic]
libmdb3/jammy,now 1.0.0+dfsg-1 amd64 [installed,automatic]
libmdbsql3/jammy,now 1.0.0+dfsg-1 amd64 [installed,automatic]
mdbtools/jammy,now 1.0.0+dfsg-1 amd64 [installed]
odbc-mdbtools/jammy,now 1.0.0+dfsg-1 amd64 [installed]

Linux平臺 下的 ODBC 設定

  • /etc/odbc.ini
[access_db] # 隨意命名，會在專案程式碼裡用到它
Description=Microsoft Access Database
Driver=MDBW
ServerName = localhost
Database=/root/Database1.mdb # 按你的實際路徑改寫，要有讀寫許可權
  • /etc/odbcinst.ini
[MDBW] # 隨意，在odbc.ini檔案用到它
Description=MDBTools Driver Wide # 隨意
Driver=/usr/lib/x86_64-linux-gnu/odbc/libmdbodbcW.so # 按你的實際路徑改寫
Setup=/usr/lib/x86_64-linux-gnu/odbc/libmdbodbcW.so # 按你的實際路徑改寫
FileUsage=1
UsageCount=1
[MDBTools]
Description=MDBTools Driver # 隨意
Driver=/usr/lib/x86_64-linux-gnu/odbc/libmdbodbc.so # 按你的實際路徑改寫
Setup=/usr/lib/x86_64-linux-gnu/odbc/libmdbodbc.so # 按你的實際路徑改寫
FileUsage=1
UsageCount=1
[ODBC]
Trace=1
TraceFile=/tmp/mdb.log # 有寫入許可權的檔案路徑

Demo 專案程式碼

  • OdbcForLinuxTestApp.csproj
<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net8.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
    <RuntimeIdentifiers>linux-x64;win-x64</RuntimeIdentifiers>
    <SelfContained>true</SelfContained>
    <ProduceReferenceAssembly>false</ProduceReferenceAssembly>
    <AppendRuntimeIdentifierToOutputPath>false</AppendRuntimeIdentifierToOutputPath>
    <AppendTargetFrameworkToOutputPath>false</AppendTargetFrameworkToOutputPath>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="System.Data.Odbc" Version="8.0.0" />
    <PackageReference Include="System.Data.OleDb" Version="8.0.0" />
  </ItemGroup>
</Project>
  • Program.cs
using System.Data;
using System.Data.Common;
using System.Data.Odbc;
using System.Data.OleDb;

namespace OdbcForLinuxTestApp;

internal sealed class Program
{
    static async Task Main(string[] args)
    {
        string connectionStrings;
        if (OperatingSystem.IsWindows())
        {
            string mdbFile = Path.Combine(AppContext.BaseDirectory, "Database1.mdb");
            connectionStrings = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={mdbFile}";
        }
        else
        {
            //root/Database1.mdb
            connectionStrings = "DSN=access_db;";
        }

        await using (DbConnection conn = GetDbConnection(connectionStrings))
        {
            await conn.OpenAsync();
            DbCommand cmd = conn.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select [ID],[UserName] from Users";

            DbDataReader reader = await cmd.ExecuteReaderAsync();
            while (await reader.ReadAsync())
            {
                //The MDBTools does not support the use of column names
                string userName = reader.GetString(1);
                Console.WriteLine("UserName: " + userName);
            }
        }
    }

    private static string DbProviderName => OperatingSystem.IsWindows() ? "System.Data.OleDb" : "System.Data.Odbc";

    private static DbConnection GetDbConnection(string connectionStrings)
    {
        RegisterOdbcOrOleDbFactory();
        DbProviderFactory dbFactory = DbProviderFactories.GetFactory(DbProviderName);
        DbConnection? conn = dbFactory.CreateConnection();
        if (conn == null)
        {
            return OperatingSystem.IsWindows() ? new OleDbConnection(connectionStrings) : new OdbcConnection(connectionStrings);
        }

        conn.ConnectionString = connectionStrings;
        return conn;
    }

    private static int _isRegisteredDbFactory;
    private static void RegisterOdbcOrOleDbFactory()
    {
        if (Interlocked.CompareExchange(ref _isRegisteredDbFactory, 1, 0) == 0)
        {
            string dbProviderName = DbProviderName;
            IEnumerable<string> providerInvariantNames = DbProviderFactories.GetProviderInvariantNames();
            string? invariantName = providerInvariantNames.FirstOrDefault(x => x.Equals(dbProviderName, StringComparison.InvariantCultureIgnoreCase));
            if (string.IsNullOrWhiteSpace(invariantName))
            {
                DbProviderFactories.RegisterFactory(dbProviderName, OdbcFactory.Instance);
            }
        }
    }
}

編譯和釋出 Demo 專案程式碼

準備工作

  • 建立 OdbcForLinuxTestApp 目錄
  • 將上述兩個程式碼檔案放入 OdbcForLinuxTestApp 目錄
  • 安裝 .NET SDK 8.0.100

編譯和釋出

在 OdbcForLinuxTestApp 目錄下，執行命令：

dotnet publish -c Release -f net8.0 -r win-x64 -o ./publish/win-x64 # 如果只考慮 Linux平臺，該命令可忽略
dotnet publish -c Release -f net8.0 -r linux-x64 -o ./publish/linux-x64

執行 OdbcForLinuxTestApp

注意：Database1.mdb 資料庫檔案需要提前放到正確的路徑，以 odbc.ini檔案 的 Database 設定項為準。

cd ./publish/linux-x64
chmod +x OdbcForLinuxTestApp # 授予可執行許可權
./OdbcForLinuxTestApp

輸出：

UserName: Allen
UserName: Joy

折騰過程中遇到的問題

  1. 搜了幾個設定例子，有的說 libmdbodbc.so 在 /usr/lib 目錄下，
    也有的在 /usr/local/lib/odbc 目錄下，而我最終是在 /usr/lib/x86_64-linux-gnu/odbc 目錄下找到。

  2. mdbtools 不支援使用列名存取，只能用列索引。

其它