(Sponsor) Need Help to create Working Packed .Net Core AddIn

[Glayson Patricio]

Hi Govert,

First I would like to congratulate you for the great Excel DNA Product.
I Have been sponsoring your project in github since the beginning.

But I am having a really hard time migrating my .NetFramework stuff to .NetCore, and some help would be really appreciated.

I am using the nuget Microsoft.Data.Sql.Client to connect to an SqlServer Database.

As you have told me in a previous Post (https://groups.google.com/g/exceldna/c/4PQLjPj76N4/m/gBtxY5M8DAAJ), after building, I need to copy two files into my output directory
  - Microsoft.Data.SqlClient.dll from bin\debug\net6.0-windows\runtimes\win\lib\newcoreapp3.1\
  - Microsoft.Data.SqlClient.SNI.dll from bin\debug\net6.0-windows\runtimes\win-x64\native\

And the project Works when I run it on Visual Studio..

I could even automate the copy process using the below directives:
<Target Name="PostBuild" AfterTargets="PostBuildEvent">
   <Exec Command="copy $(OutDir)\runtimes\win\lib\netcoreapp3.1\Microsoft.Data.SqlClient.dll $(OutDir)" />
   <Exec Command="copy $(OutDir)\runtimes\win-x64\native\Microsoft.Data.SqlClient.SNI.dll $(OutDir)" />
   <Exec Command="ren $(OutDir)\publish\AddIn64-packed.xll QuoteAddIn.xll" />
</Target>

But, I could not create an XLL Addin that Works Alone and Packed.

I have tried adding the below packing instructions to the .dna file,
   <Reference Path="runtimes\win\lib\netcoreapp3.1\Microsoft.Data.SqlClient.dll" Pack="true"/>
   <Reference Path="runtimes\win-x64\native\Microsoft.Data.SqlClient.SNI.dll" Pack="true"/>

but I still have the exception below when I try to use de AddIn:
   "UNABLE TO LOAD MICROSOFT.DATA.SQLCLIENT.SNI.DLL OR ONE OF ITS DEPENDENCIES"
 
Do you have any clue of how to pack those files in a way that the AddIn can read then ?
 
Thanks

Glayson

[Govert van Drimmelen]

Hi Glayson,

 

Thank you very much for sponsoring the project through the GitHub Sponsors program over the last year.

Every sponsorship helps us move forward toward the next release

 

In the packing mechanism we support now for the .xll files, we only support managed libraries.

This is because these can be loaded from memory, so we don’t have to extract to a real file somewhere, and deal with cleanup etc.

The file Microsoft.Data.SqlClient.SNI.dll is a native code library, so cannot be packed (and unpacked and used) with our current mechanism.

So this file would need to be shipped external to your add-in .xll file(s).

 

What you could do as a workaround might be the following:

• Add the …SNI.dll file to your C# project as a binary resource.
• When the add-in is loaded, extract and save the .dll to the add-in directory (if it is not there yet).

 

This discussion suggests an alternative tool that also follows this approach, but I have no experience using that: Can we use ILRepack for our dependencies instead of ExcelDnaPack? · Issue #429 · Excel-DNA/ExcelDna (github.com)

 

We’re learning that this kind of thing has become harder with .NET 6, but I think over time we can improve Excel-DNA to add native library packing, and also we’ll get to better examples for this common problem with the .NET 6 database libraries.

 

Regards,

Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/821f2b70-5797-41cf-a861-170311108dacn%40googlegroups.com.

[Glayson Patricio]

Govert,

Thank you very much for your tips..... I was able to make it work.

Here is the code, in case someone else need it.

1-> add the native client as an embedded resource in your .csproj file

    <ItemGroup>
        <EmbeddedResource Include="$(MSBuildProjectDirectory)\Resource\Microsoft.Data.SqlClient.SNI.dll" />
    </ItemGroup>

2 ->  Extract the file, save it to "temp" folder (if neede), load the the file. (Do it on AutoOpen() method)

            var assembly = Assembly.GetExecutingAssembly();
            var sqlClientFileName = "Microsoft.Data.SqlClient.SNI.dll";
            var sqlClientPath = @$"{Path.GetTempPath()}{sqlClientFileName}";
            if (!File.Exists(sqlClientPath))
            {
                using var sqlEmbebedStream = assembly.GetManifestResourceStream($"AddIn.Resource.{sqlClientFileName}");
                var sqlFileStream = File.Create(sqlClientPath);
                sqlEmbebedStream.CopyTo(sqlFileStream);
                sqlFileStream.Close();
            }
            NativeLibrary.Load(sqlClientPath);

Govert, i do not know if this is the right channel, and if i could give you my opinion, but those topics below could be interesting to be used in ExcelDNA

• Support for ValueTask<T> return on ExcelFunction. (Need to convert ValueTask to Task using the "AsTask()" method)
• Include  DateOnly (native struct on .net 6.0) in the marshalling. (There would be no need to convert a DateOnly to other types)
• Suport for Native Dll on the Pack Mechanism . (No work arrounds like the above would be needed)
• Suport for other types of files in the Pack Mechanism with some kind of retrieve methods (Could embedded appsettings.json, etc...)

Thank you very much again for the excelent work.

Glayson,

[Govert van Drimmelen]

Hi Glayson,

 

I’m glad you have it working, and thank you for sending the code snippets that worked for you.

 

Your suggestions for expanding the type conversions that we support is very welcome.

Because we no longer support .NET 3.x, it might be possible and convenient to incorporate the Registration extensions which do this kind of conversion, into the main ExcelDna library. Otherwise we can at least add the ones you show as built-in conversions in the extension library, like the Task<T> support currently.

 

I’ve added these two issues for tracking the suggestions:

Add support for new .NET 6 types · Issue #37 · Excel-DNA/Registration (github.com)

Support packing and loading of native and mixed assemblies and other files · Issue #510 · Excel-DNA/ExcelDna (github.com)

 

-Govert

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/d8b2ccff-8707-431f-9acb-72add046e5ffn%40googlegroups.com.