.NET Framework 4.8下NpgsqlDataSourceBuilder兼容问题及替代方案咨询

核心结论 #

是的，你必须改用NpgsqlConnection替代NpgsqlDataSourceBuilder。原因在于NpgsqlDataSourceBuilder是Npgsql 6.0+推出的新API，主要针对.NET Core/.NET 5+环境优化，在.NET Framework 4.8环境下存在兼容性问题——你遇到的type initializer for NpgsqlDataSourceBuilder错误就是典型的版本适配问题，这类API在.NET Framework中可能依赖不存在的系统类库，导致类型初始化失败。

修改后的DB_Reading类实现 #

将原代码中的NpgsqlDataSourceBuilder逻辑替换为传统的NpgsqlConnection初始化方式，调整后的代码如下：

using Npgsql;
using System;
using System.Collections.Generic;
using System.Data;

namespace Revit_Plugin_V5
{
    public class DB_Reading
    {
        public string Server_Name { get; set; }
        public int Port { get; set; }
        public string Database_Name { get; set; }
        public string User_Name { get; set; }
        public string Password { get; set; }

        public NpgsqlConnection Connection { get; set; }
        private static DB_Reading _instance = null;

        public static DB_Reading Instance()
        {
            if (_instance == null)
            {
                try
                {
                    _instance = new DB_Reading();
                }
                catch (Exception ex)
                {
                    throw new Exception($"{ex.Message} Instance()");
                }
            }
            return _instance;
        }

        public bool IsConnect()
        {
            if (Connection == null || Connection.State != ConnectionState.Open)
            {
                if (string.IsNullOrEmpty(Database_Name))
                {
                    return false;
                }

                NpgsqlConnectionStringBuilder sb = new NpgsqlConnectionStringBuilder();
                sb.Database = Database_Name;
                sb.Host = Server_Name;
                sb.Password = Password;
                sb.Username = User_Name;
                sb.Port = Port;

                try
                {
                    Connection = new NpgsqlConnection(sb.ToString());
                    Connection.Open();
                }
                catch (Exception ex)
                {
                    throw new Exception($"{ex.Message} IsConnect() is the problem");
                }
            }
            return true;
        }

        public void Close()
        {
            if (Connection != null && Connection.State == ConnectionState.Open)
            {
                Connection.Close();
                Connection.Dispose(); // 释放连接资源
                Connection = null;
            }
        }
    }
}

修改后的调用代码 #

由于不再使用NpgsqlDataSource，需要将创建命令的方式改为通过NpgsqlConnection实例，调整后的调用代码如下：

var dbCon = DB_Reading.Instance();
dbCon.Server_Name = "localhost";
dbCon.Database_Name = "database";
dbCon.Port = 5432;
dbCon.User_Name = "user_name";
dbCon.Password = "password";

if (dbCon.IsConnect())
{
    string query = "SELECT DISTINCT job_name,job_id FROM \"Table\""; // PostgreSQL中表名若为关键字需加双引号

    using(var cmd = dbCon.Connection.CreateCommand())
    {
        cmd.CommandText = query;
        using(var reader = cmd.ExecuteReader()) // 用using自动释放reader资源
        {
            try
            {
                while(reader.Read())
                {
                    string job_name = reader.GetString(0);
                    int job_id = reader.GetInt32(1);
                    
                    job_names.Add(job_name);
                    job_ids.Add(job_id);
                    job_list.Add($"{job_name}-{job_id}");
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"{ex.Message} reader");
            }
        }
    }
}
dbCon.Close();

额外注意事项 #

• 确保安装的Npgsql版本是支持.NET Framework 4.8的稳定版本（推荐Npgsql 6.x或更低版本，高版本可能对.NET Framework支持有限）。
• PostgreSQL中如果表名或字段名是SQL关键字，必须用双引号包裹，否则会引发语法错误。
• 尽量使用using语句管理NpgsqlCommand和NpgsqlDataReader，确保资源自动释放，避免内存泄漏。

内容的提问来源于stack exchange，提问作者Jeevan Gopinath