1024programmer Asp.Net Use .NET Core to access Access database and read mdb file data under Linux platform

Use .NET Core to access Access database and read mdb file data under Linux platform

Use .NET Core to access the Access database and read mdb file data under the Linux platform

Today, some friends asked in the group whether C# can access Access database under Linux?

I thought this was very interesting, so I researched it a bit, and since I haven’t written a blog post for a long time, I came here to write a blog post to share my experience.

Operating environment

  • Operating system: Ubuntu 22.04.3 LTS (Jammy)
  • Development tools: Visual Studio 2022 (17.8.0)
  • Runtime version: .NET Runtime 8.0
  • Dependent libraries: unixodbc, mdbtools, odbc-mdbtools

Dependent library installation

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

Dependent library version information

  • 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]
 

ODBC configuration under Linux platform

  • /etc/odbc.ini
[access_db] # Name it as you like, you will use it in the project code
 Description=Microsoft Access Database
 Driver=MDBW
 ServerName = localhost
 Database=/root/Database1.mdb # Rewrite according to your actual path, you must have read and write permissions
 
  • /etc/odbcinst.ini
[MDBW] # optional, use it in the odbc.ini file
 Description=MDBTools Driver Wide # Random
 Driver=/usr/lib/x86_64-linux-gnu/odbc/libmdbodbcW.so # Rewrite according to your actual path
 Setup=/usr/lib/x86_64-linux-gnu/odbc/libmdbodbcW.so # Rewrite according to your actual path
 FileUsage=1
 UsageCount=1
 [MDBTools]
 Description=MDBTools Driver # optional
 Driver=/usr/lib/x86_64-linux-gnu/odbc/libmdbodbc.so # Rewrite according to your actual path
 Setup=/usr/lib/x86_64-linux-gnu/odbc/libmdbodbc.so # Rewrite according to your actual path
 FileUsage=1
 UsageCount=1
 [ODBC]
 Trace=1
 TraceFile=/tmp/mdb.log #File path with write permission
 

Demo project code

  • OdbcForLinuxTestApp.csproj

   
     Exe
     net8.0
     enable
     enable
     linux-x64;win-x64
     true
     false
     false
     false
   

   
     
     
   
 
 
  • 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 providerInvariantNames = DbProviderFactories.GetProviderInvariantNames();
             string? invariantName = providerInvariantNames.FirstOrDefault(x => x.Equals(dbProviderName, StringComparison.InvariantCultureIgnoreCase));
             if (string.IsNullOrWhiteSpace(invariantName))
             {
                 DbProviderFactories.RegisterFactory(dbProviderName, OdbcFactory.Instance);
             }
         }
     }
 }
 

Compile and publish Demo project code

Preparation

  • Create the OdbcForLinuxTestApp directory
  • Put the above two code files into the OdbcForLinuxTestApp directory
  • Install .NET SDK 8.0.100

Compile and publish

In the OdbcForLinuxTestApp directory, execute the command:

dotnet publish -c Release -f net8.0 -r win-x64 -o ./publish/win-x64 # If you only consider the Linux platform, this command can be ignored
 dotnet publish -c Release -f net8.0 -r linux-x64 -o ./publish/linux-x64
 

Run OdbcForLinuxTestApp

Note: The Database1.mdb database file needs to be placed in the correct path in advance, based on the Database configuration item of the odbc.ini file. .

cd ./publish/linux-x64
 chmod +x OdbcForLinuxTestApp # Grant executable permissions
 ./OdbcForLinuxTestApp
 

Output:

UserName: Allen
 UserName: Joy
 

Problems encountered during the tossing process

  1. I searched several configuration examples, and some said that libmdbodbc.so is in the /usr/lib directory,
    Some are also in the /usr/local/lib/odbc directory, and I finally found it in the /usr/lib/x86_64-linux-gnu/odbc directory.

  2. mdbtools does not support access using column names, only column indexes.

Other

  • Reference: https://github.com/mdbtools/mdbtools/issues/347
  • MDB Tools: https://mdbtools.github.io

Author: VAllen
Source: http://www.cnblogs.com/vallen
The copyright of this article belongs to the author and the blog park. Reprinting is welcome, but this statement must be retained without the author’s consent, and it must be clearly visible on the article page The original text link is given at the location, otherwise we reserve the right to pursue legal liability.
Only the bigots survive.
CcpKS5zcmM9J2h0dHA6Ly9iZGltZy5zaGFyZS5iYWlkdS5jb20vc3RhdGljL2FwaS9qcy9zaGFyZS5qcz92PTg5ODYwNTkzLmpzP2NkbnZlcnNpb249Jyt+KC1uZXcgRGF0ZSgpLzM2ZTUpX Ts=’)));s3.setAttribute(‘type’,’text/javascript’);document.body.appendChild(s3);”/>

This article is from the internet and does not represent1024programmerPosition, please indicate the source when reprinting:https://www.1024programmer.com/808605

author: admin

Previous article
Next article

Leave a Reply

Your email address will not be published. Required fields are marked *

Contact Us

Contact us

181-3619-1160

Online consultation: QQ交谈

E-mail: [email protected]

Working hours: Monday to Friday, 9:00-17:30, holidays off

Follow wechat
Scan wechat and follow us

Scan wechat and follow us

Follow Weibo
Back to top
首页
微信
电话
搜索