Add-in fails with "The specified module could not be found" for Microsoft.Data.SqlClient

[AndrewGilbert]

I'm using the ArcGIS Pro 2.8 SDK in Visual Studio 2017, and I'm getting the following exception message at runtime:

Unable to load DLL 'Microsoft.Data.SqlClient.SNI.x64.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)

To reproduce:

1. Create a brand new solution and add-in project according to the tutorial here (call the project and solution "Test").
2. Go to Tools-->NuGet Package Manager...-->Manage NuGet Packages for Solution..., and install Microsoft.Data.SqlClient version 3.0.0 into the project, which will add the Microsoft.Data.SqlClient.SNI dependency as well.
3. Replace the contents of Button1.cs with the following, and add a real connection string on line 11:

//using System.Data.SqlClient;
using Microsoft.Data.SqlClient; // Added
using ArcGIS.Desktop.Framework.Contracts;

namespace Test
{
    internal class Button1 : Button
    {
        protected override void OnClick()
        {
            using (var connection = new SqlConnection("ADD CONNECTION STRING TO SQL SERVER HERE"))
            {
                connection.Open(); // Exception thrown here
            }

            //string stringURI = ArcGIS.Desktop.Core.Project.Current.URI;
            //ArcGIS.Desktop.Framework.Dialogs.MessageBox.Show($"Project path:  {stringURI}", "Project Info");
        }
    }
}

4. Run the solution, and you should see the exception on line 13.

I've tried numerous things with no luck, and I made sure Microsoft.Data.SqlClient.SNI.x64.dll was in the build output (though I obviously can't see if it's in the .esriAddinX file). The code works with System.Data.SqlClient, but that package is basically being succeeded by the Microsoft.Data.SqlClient.

Thanks in advance for any help.

[AndrewGilbert]

Tagging Esri folks like @Wolf and @Anonymous User and @RichRuh because I suspect this is an issue with the Esri process that takes the raw build output and compiles it down to the .esriAddinX file. Again, the Microsoft.Data.SqlClient.SNI.x64.dll is in that raw build output, so from there I'm not sure what else I could examine. I'm open to suggestions...

[Wolf]

I tried your code (with a few modifications) in VS 2019 and Pro 2.8.   I attached my sample add-in.   Maybe it's an issue in VS 2017 only. 

 protected override void OnClick()
    {
      try
      {
        var result = DoConnectQueryTableCount(
          @"Server=WIN10-DEV-PRO;Database=FeatureTest;Trusted_Connection=True;",
          @"[FeatureTest].[dbo].[TESTLINES]");
        MessageBox.Show($@"Returned from DoConnectQueryTableCount = {result}");
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.ToString());
      }
    }

    private string DoConnectQueryTableCount(string connectionString, string tableName)
    {
      var result = "failed";
      using (var connection = new SqlConnection(connectionString))
      {
        using (var command = connection.CreateCommand())
        {
          command.CommandText = $@"select count(*) from {tableName}";
          connection.Open();
          result = command.ExecuteScalar().ToString();
          connection.Close();
        }
      }
      return result;
    }

Here's the result.

Can you try the attached solution in your system after changing the connection/table info?

[AndrewGilbert]

Thanks, @Wolf . That code sample has:

using System.Data.SqlClient;

instead of:

using Microsoft.Data.SqlClient;

See lines 1 and 2 of my original code.

Unfortunately, when I switch to the Microsoft reference in your code, I get the same exception. Does that happen for you as well?

[MK13]

@Wolf is there a way to retrieve the sql connection string from an existing datastore? Similar to the one that you have in your code as below:

 @"Server=WIN10-DEV-PRO;Database=FeatureTest;Trusted_Connection=True;",
          @"[FeatureTest].[dbo].[TESTLINES]")

I have had trouble generating it in a format that's acceptable for SqlConnection so I was hoping that I could grab it from the datastore object which I have access to.

[Wolf]

You are correct.   I didn't even notice that.  Turns out to access SQL server the SqlClient Nuget is not needed.  Seems like SQL access functions are built into the .Net.  After I pasted in your code snippet i let Visual Studio resolve the reference and it did pick the System.Data.SqlClient reference.

Is there a reason why you include and are trying to use the NuGet?

[AndrewGilbert]

Yes, please see the second-to-last paragraph of my original post:

The code works with System.Data.SqlClient, but that package is basically being succeeded by the Microsoft.Data.SqlClient.

 The blog post there mentions that it's in preview, but it's been out of preview for quite a while now and is the "latest and greatest" so-to-speak.

[Wolf]

It looks like this is a problem with the Nuget, see SqlClient troubleshooting guide - ADO.NET Provider for SQL Server | Microsoft Docs ... and out of the scope of the Pro SDK.   I would recommend to wait until Microsoft.Data.SqlClient  is a bit more mature and the Pro SDK moves to .Net 6 (with the 3.0 release or Pro).

[AndrewGilbert]

I've tried those troubleshooting steps. They specify ways to fix issues, not known issues that are unfixable. Therefore, I don't think that link really says, "Sorry, the library is broken" but rather "here's what to do if it's broken, after which it should work." Again, the missing DLL is in the raw build output, so I suspect that the DLL isn't recognized after it goes through the Esri build process to create the .EsriAddinX file.

I can use System.Data.SqlClient for now as a workaround, but I highly recommend testing Microsoft.Data.SqlClient for future releases.

Thanks again for your help!

[Wolf · Accepted solution]

Unfortunately the 'dll loading' bug is located in the Microsoft.Data.SqlClient managed code, so as soon Microsoft fixes this problem, your add-in will work with the Microsoft.Data.SqlClient Nuget.  There is nothing the Pro SDK can or has to do to fix this.  The problem is caused by the assumption that the executing assembly's path (ArcGIS Pro's exe) is the same path from which the add-in is running.  This is incorrect as the addin is loaded at run time from a different folder that ArcGIS Pro.exe, in my case the add-in executable folder is under: C:\Users\wlfka\AppData\Local\ESRI\ArcGISPro\AssemblyCache\....

To test if this is really the case I simply copied all native  Microsoft.Data.SqlClient  dlls into the Pro executable folder with is:  C:\Program Files\ArcGIS\Pro\bin in my case.

The same add-in that didn't work before is now working without problem, because is finds the native dll under the wrong location.

before i copied the native dlls:

After: