使用Microsoft.Data.SqlClient 4.1.0开发UiPath自定义活动加载DLL失败求助

使用Microsoft.Data.SqlClient 4.1.0开发UiPath批量插入SQL Server的自定义活动，通过NuGet引入依赖。项目原是.NET Framework 4.6.1类库，现迁移至.NET 6.0+环境，旧版本运行正常，新版本出现以下问题：

• 未安装UiPath.Database.Activities包时，执行自定义活动报错：Unable to load DLL 'Microsoft.Data.SqlClient.SNI.dll' or one of its dependencies: The specified module could not be found. (0x8007007E)
• 安装同版本Microsoft.Data.SqlClient的UiPath.Database.Activities包后，仍报相同错误
• 添加未配置的Connect to Database活动（选择System.Data.SqlClient并填入相同连接字符串）后，自定义活动可正常运行

已尝试的无效解决方案 #

• 验证Visual Studio编译输出目录中存在Microsoft.Data.SqlClient.SNI.x64.dll和Microsoft.Data.SqlClient.SNI.x86.dll文件
• 在NuGet包管理器中添加UiPath.Database.Activities包含的所有依赖包及对应版本
• 同时添加Microsoft.Data.SqlClient.SNI与Microsoft.Data.SqlClient依赖
• 手动将Microsoft.Data.SqlClientx64.SNI.dll放入UiPath项目根目录测试
• 对比UiPath.Database与自定义项目中Microsoft.Data.SqlClient 4.1.0的包内容

自定义活动代码 #

public class DataTableToSQLTable : CodeActivity
{
     [Category("Input")]
     [RequiredArgument]
     [Description("Datatable to push into SQL table.")]
     public InArgument<DataTable> SourceDatatble { get; set; }

     [Category("Input")]
     [RequiredArgument]
     [Description("Datatable columns name that map with SQL table columns.")]
     public InArgument<string[]> SourceColumns { get; set; }

     [Category("Input")]
     [RequiredArgument]
     [Description("The connection string used to establish a database connection.")]
     public InArgument<string> connectionString { get; set; }

     [Category("Input")]
     [RequiredArgument]
     [Description("SQL table name.")]
     public InArgument<string> SQLTableName { get; set; }

     [Category("Input")]
     [RequiredArgument]
     [Description("SQL table columns name that map wih datatble columns.")]
     public InArgument<string[]> DestinationColumns { get; set; }

     protected override void Execute(CodeActivityContext context)
     {
         DataTable table = SourceDatatble.Get(context);
         string[] sourceColumns = SourceColumns.Get(context);
         string[] destinationColumns = DestinationColumns.Get(context);
         string connectionString = this.connectionString.Get(context);
         string sqlTableName = SQLTableName.Get(context);

         // Check if the source and destination columns match in length.
         if (sourceColumns.Length != destinationColumns.Length)
         {
             throw new Exception("The number of source columns does not match the number of destination columns.");
         }

         try
         {
             // First, try to open a connection to the database to ensure that our connection string is valid.
             using (SqlConnection connection = new SqlConnection(connectionString))
             {
                 connection.Open(); // Attempt to open the connection.

                 if (connection.State == ConnectionState.Open)
                 {
                     Console.WriteLine("Successfully established a connection to the database.");
                 }
                 else
                 {
                     throw new Exception("Failed to establish a connection to the database.");
                 }
             }

             // If connection is successful, proceed with the bulk insert operation.
             using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity))
             {
                 for (int i = 0; i < sourceColumns.Length; i++)
                 {
                     sqlBulkCopy.ColumnMappings.Add("[" + sourceColumns[i] + "]", "[" + destinationColumns[i] + "]");
                 }

                 sqlBulkCopy.BulkCopyTimeout = 600; // You can adjust this value based on your requirements.
                 sqlBulkCopy.DestinationTableName = "[" + sqlTableName + "]";
                 sqlBulkCopy.WriteToServer(table);
             }
         }
         catch (Exception ex)
         {
             // It's often useful to provide more specific exception handling.
             // For example, you might want to handle SqlException differently to give more detailed DB error messages.
             throw new Exception($"An error occurred: {ex.Message}", ex);
         }
     }
}

UiPath Connect to Database活动代码 #

using System;
using System.Activities;
using System.ComponentModel;
using System.Diagnostics;
using System.Net;
using System.Security;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Markup;
using UiPath.Database;
using UiPath.Database.Activities;
using UiPath.Database.Activities.Properties;

[LocalizedDescription("Activity_DatabaseConnect_Description")]
public class DatabaseConnect : AsyncTaskCodeActivity
{
    private readonly IDBConnectionFactory _connectionFactory;

    [DefaultValue(null)]
    [LocalizedCategory("ConnectionConfiguration")]
    [RequiredArgument]
    [LocalizedDisplayName("Activity_DatabaseConnect_Property_ProviderName_Name")]
    [LocalizedDescription("Activity_DatabaseConnect_Property_ProviderName_Description")]
    public InArgument<string> ProviderName { get; set; }

    [DependsOn("ProviderName")]
    [DefaultValue(null)]
    [LocalizedCategory("ConnectionConfiguration")]
    [LocalizedDisplayName("Activity_DatabaseConnect_Property_ConnectionString_Name")]
    [LocalizedDescription("Activity_DatabaseConnect_Property_ConnectionString_Description")]
    public InArgument<string> ConnectionString { get; set; }

    [DefaultValue(null)]
    [DependsOn("ProviderName")]
    [LocalizedCategory("ConnectionConfiguration")]
    [LocalizedDisplayName("Activity_DatabaseConnect_Property_ConnectionSecureString_Name")]
    [LocalizedDescription("Activity_DatabaseConnect_Property_ConnectionSecureString_Description")]
    public InArgument<SecureString> ConnectionSecureString { get; set; }

    [LocalizedCategory("Output")]
    [DependsOn("ProviderName")]
    [LocalizedDisplayName("Activity_DatabaseConnect_Property_DatabaseConnection_Name")]
    [LocalizedDescription("Activity_DatabaseConnect_Property_DatabaseConnection_Description")]
    public OutArgument<DatabaseConnection> DatabaseConnection { get; set; }

    public DatabaseConnect()
    {
        _connectionFactory = new DBConnectionFactory();
    }

    internal DatabaseConnect(IDBConnectionFactory factory)
    {
        _connectionFactory = factory;
    }

    protected override async Task<Action<AsyncCodeActivityContext>> ExecuteAsync(AsyncCodeActivityContext context, CancellationToken cancellationToken)
    {
        string connString = ConnectionString.Get(context);
        SecureString connSecureString = ConnectionSecureString.Get(context);
        if (connString == null && connSecureString == null)
        {
            throw new ArgumentNullException(UiPath_Database_Activities.ValidationError_ConnectionStringMustNotBeNull);
        }

        if (connString != null && connSecureString != null)
        {
            throw new ArgumentException(UiPath_Database_Activities.ValidationError_ConnectionStringMustBeSet);
        }

        string provName = ProviderName.Get(context);
        DatabaseConnection dbConnection = null;

        try
        {
            dbConnection = await Task.Run(() => _connectionFactory.Create(connString ?? new NetworkCredential("", connSecureString).Password, provName));
        }
        catch (Exception arg)
        {
            Trace.TraceError($"{arg}");
            throw;
        }

        return delegate(AsyncCodeActivityContext asyncCodeActivityContext)
        {
            DatabaseConnection.Set(asyncCodeActivityContext, dbConnection);
        };
    }
}

内容的提问来源于stack exchange，提问作者Indie Dev