Sqlclient: Cannot use with F# scripts

[isaacabraham]

Scripts are a common and everyday way to work F# and forms part of the standard development lifecycle. I can't figure out how to use the SqlClient library from within an F# script:

1. Download the nuget package.
2. Try referencing the netstandard version of the library e.g. #r @"C:\Users\Isaac\.nuget\packages\microsoft.data.sqlclient\1.0.19269.1\lib\netstandard2.0\Microsoft.Data.SqlClient.dll.
3. Script will crash when you try to open a connection with a PlatformNotSupported exception.
4. After looking around for a while, read this link.
5. Try to reference the net46 version of the library: #r @"C:\Users\Isaac\.nuget\packages\microsoft.data.sqlclient\1.0.19269.1\lib\net46\Microsoft.Data.SqlClient.dll"
6. Get another exception:

System.TypeInitializationException: The type initializer for 'Microsoft.Data.SqlClient.SNINativeMethodWrapper' threw an exception. ---> System.ComponentModel.Win32Exception: Failed to load C:\Users\Isaac\.nuget\packages\microsoft.data.sqlclient\1.0.19269.1\lib\net46\x64\SNI.dll
   at Microsoft.Data.SqlClient.SNINativeMethodWrapper..cctor() in E:\agent1\_work\34\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\Interop\SNINativeMethodWrapper.cs:line 66

I can't find any SNI.dll within the nuget package. How can I get this to work?

Also - why include a netstandard2.0 TFM in the package, if netstandard2.0 is, effectively, not supported? This is misleading and very tightly couples the package to NuGet.

[Wraith2]

Netstandard is supported but it requires a target platform to be resolved in order to restore the correct windows or unix native socket implementation. I think what you're really asking is why there is no fully portable fallback.

Note that SNI.dll on windows isn't the only dependency that is required. You'll also need codepage support and a number of other things like configuration. SqlClient was in-box before and could be used easily because it only had framework dependencies. This library isn't in-box and has external dependencies, they're not really equivalent.

[isaacabraham]

@Wraith2 thanks, that helps to understand it better. Is there a list of these required dependencies anywhere - and if they are not shipped with the nuget package, where are they? Thanks again

[David-Engel]

Is there a list of these required dependencies anywhere - and if they are not shipped with the nuget package, where are they?

The NuGet package contains the details of all the dependencies and the NuGet infrastructure facilitates the downloading and referencing of them all. You can view them in the .nuspec file contained inside the package or on the Nuget website. You will have to fully resolve the dependency tree yourself if you choose not to use the NuGet infrastructure. What you are trying to do is very similar to what this PowerShell developer wanted to do in #161.

[isaacabraham]

@Wraith2 thanks, I will look at that. Note: this is not a matter of choice - NuGet has no support for F# scripts, it only works with applications (.NET exes, libraries) so I have no choice but to go alone here; in the last 5+ years of working with F# this is the first nuget package I've come across that has this issue.

[isaacabraham]

Note also : I have all the dependencies downloaded - it's simply a matter of understanding within the package what things need to be resolved and in what order. This is something I'm not sure is readily visible.

[cheenamalhotra]

Hi @isaacabraham

I can't find any SNI.dll within the nuget package. How can I get this to work?

Please verify the version of NuGet.exe in use is >5.0 when performing nuget restore. If using version >5.0, dependent DLLs (SNI.dll for x64 and x86) should be loaded transitively, look for #137 for more details.

Try referencing the netstandard version of the library e.g. #r @"C:\Users\Isaac.nuget\packages\microsoft.data.sqlclient\1.0.19269.1\libnetstandard2.0\Microsoft.Data.SqlClient.dll

why include a netstandard2.0 TFM in the package, if netstandard2.0 is, effectively, not supported? This is misleading and very tightly couples the package to NuGet.

NetStandard 2.0 is supported, apparently the correct path to supported DLL is runtimes\<os>\lib\netstandard2.0\Microsoft.Data.SqlClient.dll and not lib\netstandard2.0\Microsoft.Data.SqlClient.dll.

[isaacabraham]

@cheenamalhotra I'm trying to use an F# script, which NuGet doesn't support. We have to manually reference the individual assemblies in the packages to get things to work. Thanks for that link, I will look into it.

[cheenamalhotra]

I see. Looks like there is a workaround with msbuild /t:restore, have you tried that? (https://github.com/NuGet/Home/issues/7743#issuecomment-459655384)

[cheenamalhotra]

Either way, as it turns out known limitation from NuGet, I would like to close this issue at our end and suggest registering issues with NuGet and Visual Studio to support package reference for F# scripts.

[isaacabraham]

@cheenamalhotra This is in an F# script. There is no msbuild. We just reference dlls manually. It's nothing to do with NuGet - I just need to know which DLLs to reference :-/

[David-Engel]

It looks like nuget package support is something they recently merged over at fsharp: https://github.com/dotnet/fsharp/pull/5850
https://twitter.com/realvictorprm/status/1173639408693723136?s=19
I don't know what their release schedule looks like or what release it is or will be in, but you can probably find more news over there.

[isaacabraham]

@David-Engel that's not until net5 - forget about that, it's a red herring :-) Currently we reference individual DLLs for scripts. It works very well and there's decent tooling around that as well. In this case it's not working because of the native dlls, so I need to add them manually. I just need to know what they are - is it really not possible to get a list of them?

[isaacabraham]

@cheenamalhotra perhaps open this again?

[forki]

@David-Engel @cheenamalhotra NuGet support is not recently "merged over". NuGet was always supported in F#. What's new is that you will be able to reference packages directly out of your F# scripts. As a whole. Currently you reference the dlls indiviually. This is standard practice and works pretty well across most of the ecosystem. So I'd argue this is not "external".

/cc @cartermp

[cartermp]

Support for #r "nuget:..." will land in .NET 5. Currently, there are still some quirks with transitive references on dotnet fsi due to how assemblies can be organized on a machine, which is far more complicated than with .NET Framework. The only reasonable way is to effectively replicate what NuGet does to resolve dependencies, which is why we did #r "nuget:..." to just let it handle the resolution instead.

So, long story short - it's complicated. There _may_ be an issue on the F# side of things, but the errors that @isaacabraham mentions are not consistent with the known limitations.

Additionally @cheenamalhotra this isn't about supporting <PackageReference>. F# has supported these since its inception.

[cheenamalhotra]

@isaacabraham

Have you tried with DLLs from runtimes folder as mentioned above (https://github.com/dotnet/SqlClient/issues/292#issuecomment-555855854)?

About your ask for SNI.dll, it's referenced from a separate package. For .NET Standard targeting apps it's runtime.native.System.Data.SqlClient.sni, you can find this info on M.D.[email protected] too.

[kMutagene]

@isaacabraham did you ever manage to get this to work? I still have the same issues when trying to work with this library in an F# script environment.

[isaacabraham]

@kMutagene I gave up - the last comment above basically sent me right back to where I started. I haven't tried using newer versions of FSI which run on dotnet core though, so this might be worth your trying out.

[Wraith2]

You're going to have the same problems on netcore. The library is no longer in-box and has external dependencies which are resolved by nuget. You need either a nuget package restore or to manually walk the dependency tree and ensure all required dependencies are present.

[isaacabraham]

@Wraith2 As you're aware, it's only the native DLLs that have until now presented the problem. If there's any documentation which illustrates what these are and how these work it might be possible for F# developers to get this working for scripts (an important part of the dev cycle). Cheers.

[cheenamalhotra]

Hi @isaacabraham

Just a side note, we recently enabled use of Managed SNI on Windows optionally with App Context switches (for cases where native DLL loading causes concerns in .NET Core/Standard applications).

Enabling Managed Networking on Windows - v2.0.0-preview3

Maybe you could give v2.0.0-preview3 a try with AppContext switch enabled and let us know if that would be useful in your case?

[cartermp]

I still get this same error with the #r "nuget:..." feature with F# scripts. This feature does load native .dlls (such as those in ML.NET), so there might be something else missing? @isaacabraham can you confirm this if you run the following?

#r "nuget: Microsoft.Data.SqlClient, Version=2.0.0-preview3"

open Microsoft.Data.SqlClient

let str = "Data Source=(local);Initial Catalog=NorthwindIntegrated Security=SSPI"
let qs = "SELECT OrderID, CustomerID FROM dbo.Orders;"

use conn = new SqlConnection(str)
let cmd = SqlCommand(qs, conn)
cmd.Connection.Open()
cmd.ExecuteNonQuery()

(replacing whatever query is there with your own)

dotnet fsi --langversion:preview foo.fsx

I get

System.DllNotFoundException: Unable to load DLL 'sni.dll' or one of its dependencies: The specified module could not be found. (0x8007007E)

[Wraith2]

Try the managed appcontext switch as @cheenamalhotra suggested, that removes the native dll requirement.

[cartermp]

Awesome, that does work. Is this going to be a default in .NET 5?

Separately, it seems like failing to find sni.dll is a bug in the dependency manager component used with FSI on .NET Core. We'll need to investigate thoguh

[Wraith2]

Awesome, that does work. Is this going to be a default in .NET 5?

This library is versioned independently from the runtime so it doesn't really matter. I think it's unlikely that the System.Data.SqlClient version that the runtime has will be updated with this switch, it was disabled for good reasons in the past and it's only recently that it's been made available to users again. In the long term I'd like to have managed mode be the default and not use the native dll at all but to do that performance and reliability need to be equal or better for everyone.

it seems like failing to find sni.dll is a bug in the dependency manager component used with FSI on .NET Core

If it is attempting to restore dependencies in a way compatible with nuget behaviour and you're getting this error then I'd agree that it's a problem that the FSI owners might want to take a look at.

[isaacabraham]

Just a quick update - just trying this out and still got the same problem, but if it's working for @cartermp I must be doing something wrong now?

open System
open Microsoft.Data.SqlClient

AppContext.SetSwitch("Switch.Microsoft.Data.SqlClient.UseManagedNetworkingOnWindows", true)
let connection = new SqlConnection("")

with error

System.PlatformNotSupportedException: Microsoft.Data.SqlClient is not supported on this platform.
   at Microsoft.Data.SqlClient.SqlConnection..ctor(String connectionString) in H:\tsaagent1\_work\9\s\obj\Release.AnyCPU\Microsoft.Data.SqlClient\netcore\netcoreapp3.1\Microsoft.Data.SqlClient.notsupported.cs:line 355
   at <StartupCode$FSI_0010>.$FSI_0010.main@()
Stopped due to error

Generated references:

namespace PaketLoadScripts

#r "C:\\Users\\Isaac\\.nuget\\packages\\microsoft.identity.client\\4.14.0\\lib\\netcoreapp2.1\\Microsoft.Identity.Client.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.private.datacontractserialization\\4.3.0\\lib\\netstandard1.3\\System.Private.DataContractSerialization.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.xml.xmlserializer\\4.3.0\\lib\\netstandard1.3\\System.Xml.XmlSerializer.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.componentmodel.typeconverter\\4.3.0\\lib\\netstandard1.5\\System.ComponentModel.TypeConverter.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.dynamic.runtime\\4.3.0\\lib\\netstandard1.3\\System.Dynamic.Runtime.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.xml.xdocument\\4.3.0\\lib\\netstandard1.3\\System.Xml.XDocument.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.xml.xmldocument\\4.3.0\\lib\\netstandard1.3\\System.Xml.XmlDocument.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.collections.specialized\\4.3.0\\lib\\netstandard1.3\\System.Collections.Specialized.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.linq.expressions\\4.3.0\\lib\\netstandard1.6\\System.Linq.Expressions.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.runtime.serialization.formatters\\4.3.0\\lib\\netstandard1.4\\System.Runtime.Serialization.Formatters.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.xml.readerwriter\\4.3.1\\lib\\netstandard1.3\\System.Xml.ReaderWriter.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.collections.concurrent\\4.3.0\\lib\\netstandard1.3\\System.Collections.Concurrent.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.collections.nongeneric\\4.3.0\\lib\\netstandard1.3\\System.Collections.NonGeneric.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.componentmodel.primitives\\4.3.0\\lib\\netstandard1.0\\System.ComponentModel.Primitives.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.linq\\4.3.0\\lib\\netstandard1.6\\System.Linq.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.objectmodel\\4.3.0\\lib\\netstandard1.3\\System.ObjectModel.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.runtime.numerics\\4.3.0\\lib\\netstandard1.3\\System.Runtime.Numerics.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.runtime.serialization.primitives\\4.3.0\\lib\\netstandard1.3\\System.Runtime.Serialization.Primitives.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.security.cryptography.primitives\\4.3.0\\lib\\netstandard1.3\\System.Security.Cryptography.Primitives.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.text.regularexpressions\\4.3.1\\lib\\netstandard1.6\\System.Text.RegularExpressions.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.threading\\4.3.0\\lib\\netstandard1.3\\System.Threading.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.threading.threadpool\\4.3.0\\lib\\netstandard1.3\\System.Threading.ThreadPool.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.windows.extensions\\4.7.0\\lib\\netcoreapp3.0\\System.Windows.Extensions.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\dapper\\2.0.35\\lib\\netstandard2.0\\Dapper.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\microsoft.win32.registry\\4.7.0\\lib\\netstandard2.0\\Microsoft.Win32.Registry.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.componentmodel\\4.3.0\\lib\\netstandard1.3\\System.ComponentModel.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.drawing.common\\4.7.0\\lib\\netstandard2.0\\System.Drawing.Common.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.io.filesystem.primitives\\4.3.0\\lib\\netstandard1.3\\System.IO.FileSystem.Primitives.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.threading.thread\\4.3.0\\lib\\netstandard1.3\\System.Threading.Thread.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\microsoft.win32.systemevents\\4.7.0\\lib\\netstandard2.0\\Microsoft.Win32.SystemEvents.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.security.accesscontrol\\4.7.0\\lib\\netstandard2.0\\System.Security.AccessControl.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\microsoft.csharp\\4.7.0\\lib\\netstandard2.0\\Microsoft.CSharp.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\microsoft.identitymodel.logging\\6.5.1\\lib\\netstandard2.0\\Microsoft.IdentityModel.Logging.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.buffers\\4.5.1\\lib\\netstandard2.0\\System.Buffers.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.diagnostics.diagnosticsource\\4.7.1\\lib\\netstandard1.3\\System.Diagnostics.DiagnosticSource.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.numerics.vectors\\4.5.0\\lib\\netstandard2.0\\System.Numerics.Vectors.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.reflection.typeextensions\\4.7.0\\lib\\netstandard2.0\\System.Reflection.TypeExtensions.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.runtime.compilerservices.unsafe\\4.7.1\\lib\\netstandard2.0\\System.Runtime.CompilerServices.Unsafe.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.security.cryptography.cng\\4.7.0\\lib\\netcoreapp3.0\\System.Security.Cryptography.Cng.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.security.cryptography.openssl\\4.7.0\\lib\\netcoreapp3.0\\System.Security.Cryptography.OpenSsl.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.security.cryptography.protecteddata\\4.7.0\\lib\\netstandard2.0\\System.Security.Cryptography.ProtectedData.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.security.principal.windows\\4.7.0\\lib\\netstandard2.0\\System.Security.Principal.Windows.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\microsoft.data.sqlclient\\2.0.0-preview3.20122.2\\lib\\netcoreapp3.1\\Microsoft.Data.SqlClient.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.runtime.serialization.json\\4.3.0\\lib\\netstandard1.3\\System.Runtime.Serialization.Json.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.security.permissions\\4.7.0\\lib\\netcoreapp3.0\\System.Security.Permissions.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\microsoft.identitymodel.tokens\\6.5.1\\lib\\netstandard2.0\\Microsoft.IdentityModel.Tokens.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.text.encoding.codepages\\4.7.1\\lib\\netstandard2.0\\System.Text.Encoding.CodePages.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.configuration.configurationmanager\\4.7.0\\lib\\netstandard2.0\\System.Configuration.ConfigurationManager.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\microsoft.identitymodel.jsonwebtokens\\6.5.1\\lib\\netstandard2.0\\Microsoft.IdentityModel.JsonWebTokens.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\microsoft.identitymodel.protocols\\6.5.1\\lib\\netstandard2.0\\Microsoft.IdentityModel.Protocols.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.runtime.caching\\4.7.0\\lib\\netstandard2.0\\System.Runtime.Caching.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\system.identitymodel.tokens.jwt\\6.5.1\\lib\\netstandard2.0\\System.IdentityModel.Tokens.Jwt.dll" 
#r "C:\\Users\\Isaac\\.nuget\\packages\\microsoft.identitymodel.protocols.openidconnect\\6.5.1\\lib\\netstandard2.0\\Microsoft.IdentityModel.Protocols.OpenIdConnect.dll" 

This is on netcore3.1 running latest FSI.

[cheenamalhotra]

r "C:\Users\Isaac\.nuget\packages\microsoft.data.sqlclient\2.0.0-preview3.20122.2\lib\netcoreapp3.1\Microsoft.Data.SqlClient.dll"

Please ensure you pick up "runtime" libraries from path:

r "C:\Users\Isaac\.nuget\packages\microsoft.data.sqlclient\2.0.0-preview3.20122.2\runtimes\win\lib\netcoreapp3.1\Microsoft.Data.SqlClient.dll"

OR

r "C:\Users\Isaac\.nuget\packages\microsoft.data.sqlclient\2.0.0-preview3.20122.2\runtimes\win\lib\netstandard2.0\Microsoft.Data.SqlClient.dll"

The DLLs in libnetcoreapp2.1 and libnetstandard2.0 are "Not Supported" DLLs.

[cartermp]

@isaacabraham are you using FSI with #r "nuget"? I wouldn't recommend any other way to acquire dependencies for scripts at the moment.

[isaacabraham]

@cartermp I'm not on net5. I've had some issues with it conflicting with picking up the wrong msbuild for some projects (even with explicit dotnet core refs) so I'm back to 3.x

[isaacabraham]

@cheenamalhotra thanks, I'll try that

[isaacabraham]

@cheenamalhotra that worked! I had to still add in the dependent DLLs (system,configuration being the main one) but it then works. This is really important - being in a situation when F# scripts couldn't talk to a Microsoft SQL database using Microsoft's NuGet package was an awkward situation to be in.

Thanks for helping.

[aggieben]

@isaacabraham are you using FSI with #r "nuget"? I wouldn't recommend any other way to acquire dependencies for scripts at the moment.

I'm trying this with #r "nuget: Microsoft.Data.SqlClient, Version=2.0.0-*" on dotnet 3.1 and it results in this output:

System.PlatformNotSupportedException: Microsoft.Data.SqlClient is not supported on this platform.
at Microsoft.Data.SqlClient.SqlConnection..ctor(String connectionString) in H:\tsaagent1_work\9\s\artifacts\Project\obj\Release.AnyCPU\Microsoft.Data.SqlClientnetcorenetcoreapp3.1\Microsoft.Data.SqlClient.notsupported.cs:line 355
at FSI_0003.getConnection()
at .$FSI_0003.main@()
Stopped due to error

[aggieben]

Tried again today with 2.0.0, and failed with

System.DllNotFoundException: Unable to load DLL 'Microsoft.Data.SqlClient.SNI.dll' or one of its dependencies: The specified module could not be found. (0x8007007E)

[cheenamalhotra]

@aggieben

Could you try solution posted in https://github.com/dotnet/SqlClient/issues/645#issuecomment-659209254 ?

[aggieben]

@cheenamalhotra I'm kinda doing that, although not exactly. I'm using .NET 5 preview, and --langversion:preview for FSI, and using the package like this (minimal example):

```f#

r "nuget: Microsoft.Data.SqlClient"

open System
open System.Data
open Microsoft.Data.SqlClient

AppContext.SetSwitch("Switch.Microsoft.Data.SqlClient.UseManagedNetworkingOnWindows", true)

let getConnection() =
let connectionString = Environment.GetEnvironmentVariable("CONNECTION_STRING")
new SqlConnection(connectionString)

let ensureOpen (connection:IDbConnection) =
if connection.State <> ConnectionState.Open
then connection.Open()
connection

getConnection() |> ensureOpen
```

I'll give the other way a whirl and see if that helps.

[aggieben]

@cheenamalhotra yeah - so if I switch to Paket load scripts and manually adjust the path of the Microsoft.Data.SqlClient dll to the Windows one under ~/runtimes, it seems to work.

Hopefully an issue will remain open somewhere for this, because this should work out of the box.

[isaacabraham]

I ended up writing up a minimal sample for a blog post here. I assume that for now this is the same in .NET 5 with hash R.