Please add documentation for PowerShell developers

[potatoqualitee]

Is your feature request related to a problem? Please describe.

I am a PowerShell developer and do not know how to use this SQL Client. I cannot find any docs that help.

Describe the solution you'd like

• A code block in the official docs, starting with Add-Type -Path that at least includes a query to a remote server.
• Let us know which of the DLLs we have to use - the one in lib or the one in runtime (a buddy just told me lib for our purposes).
• Highlight some differentiations or gotchas that we have to consider as a community that doesn't entirely understand DLLs.
• Can both System and Microsoft be used? If so, please add that to the code block with a comment like # Pre-SQL Server 2008 or # For Azure

I would like PowerShell to be given as much love as C# in the official docs. Our community uses the heck out of SqlClient.

Describe alternatives you've considered

I tried it myself to no avail.

Add-Type -Path C:\temp\sqlclient\lib\netstandard2.0\System.Configuration.ConfigurationManager.dll
Add-Type -Path C:\temp\sqlclient\lib\netstandard2.0\Microsoft.Data.SqlClient.dll

$azureconnstring = 'Data Source=TCP:psdbatools.database.windows.net,1433;Initial Catalog=dbatools;User [email protected];Password=XYZ;MultipleActiveResultSets=False;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False;Application Name="dbatools PowerShell module - dbatools.io";Authentication="Active Directory Password"'
New-Object Microsoft.Data.SqlClient.SqlConnection $azureconnstring

System.Management.Automation.MethodInvocationException: Exception calling ".ctor" with "1" argument(s): "Microsoft.Data.SqlClient is not supported on this platform." --->
                        System.PlatformNotSupportedException: Microsoft.Data.SqlClient is not supported on this platform.
                           at Microsoft.Data.SqlClient.SqlConnection..ctor(String connectionString)
                           --- End of inner exception stack trace ---
                           at System.Management.Automation.DotNetAdapter.AuxiliaryConstructorInvoke(MethodInformation methodInformation, Object[] arguments, Object[] originalArguments)
                           at System.Management.Automation.DotNetAdapter.ConstructorInvokeDotNet(Type type, ConstructorInfo[] constructors, Object[] arguments)
                           at Microsoft.PowerShell.Commands.NewObjectCommand.CallConstructor(Type type, ConstructorInfo[] constructors, Object[] args)

I also tried in Core with the same error

Add-Type -Path C:\temp\sqlclient\lib\netcoreapp2.1\Microsoft.Data.SqlClient.dll
$azureconnstring = 'Data Source=TCP:psdbatools.database.windows.net,1433;Initial Catalog=dbatools;User [email protected];Password=XYZ;MultipleActiveResultSets=False;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False;Application Name="dbatools PowerShell module - dbatools.io";Authentication="Active Directory Password"'
New-Object Microsoft.Data.SqlClient.SqlConnection $azureconnstring

Additional context

Please also keep PowerShell documentation in mind for other new, cool SQL-related projects, not just cmdlets that are made (which are great for end-users), but the actual classes (which are appropriate for PS Devs).

[Wraith2]

The api you use through powershell is just the standard api and the documentation for other .net langauges will apply equally. It may not have the specific powershell syntax but just like finding a vb.net sample when you're using c# you should be able to translate it.

The problem you're having is that you're trying to use the netstandard assembly and that won't work because netstandard isn't a platform. You need to use the correct binary for the runtime you're on, so netfx or netcore. I'm also not sure how you're going to deal with the dependency chain.

[cheenamalhotra]

Hi @potatoqualitee

Although this would be good to have, I agree on that, but currently we are occupied with lot of high priority work including .NET API Browser Documentation onboarding for M.D.S. Once we get some bandwidth, we will consider adding this for Powershell users.

[sharpjs]

I also was unable to use this package with PowerShell — specifically, in a PowerShell binary module. This package worked with PowerShell 5.1 (targeting net461), but not PowerShell Core 6.2.3 (targeting either netstandard2.0 or netcoreapp2.1). In that environment, any use of types from this package throws a PlatformNotSupportedException.

The Microsoft.Data.SqlClient.dll file in my bin\Debug\netcoreapp2.1 folder is 323KB. ILSpy shows every method and property implemented with:

throw new PlatformNotSupportedException(System.SR.PlatformNotSupported_DataSqlClient);

The same DLL in my bin\Debug\net461 folder is 1835KB and is accompanied by a number of satellite assemblies.

The relevant parts of my csproj:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <TargetFrameworks>net461;netcoreapp2.1</TargetFrameworks>
  </PropertyGroup>

  <PropertyGroup>
    <!-- This causes all DLLs from NuGet dependencies to be copied to the output folder. -->
    <CopyLocalLockFileAssemblies>true</CopyLocalLockFileAssemblies>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="PowerShellStandard.Library" Version="5.1.0">
      <PrivateAssets>All</PrivateAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.Data.SqlClient" Version="1.0.19249.1" />
  </ItemGroup>

</Project>

For now I'm just going back to System.Data.SqlClient.

[David-Engel]

When targeting netcore and picking your DLL manually, you need to pick the appropriate one from the runtime folder. The DLL in the lib folder just throws the PlatformNotSupported exception everywhere. The reason for this boils down to cross-platform support. SqlClient needs different DLLs depending on whether (at runtime) the environment is Windows versus Linux. But as Wraith2 mentioned, you are also going to have a problem with the dependency chain.

[sharpjs]

I was able to get M.D.S working in my case via @David-Engel 's advice. It was quite painful, though. I had to traverse the M.D.S dependency graph, loading each DLL into ILSpy to see which references were unresolved, adding .csproj elements to resolve them, and recursing until all DLLs in the graph could resolve their dependencies. This was made more difficult by dependencies' assembly versions and nupkg versions often not matching, and by the dependency nupkgs rarely having release notes to explain what their versions mean. Then I needed a .ps1 script to load the appropriate M.D.S DLL constellation at module load time. I'm not confident that my solution is a correct one, and I'm not looking forward to doing this work again when M.D.S updates.

The desired developer experience here, IMO, is to have a simple set of instructions to follow (e.g. "go add this NuGet package"), resulting in a small .csproj that just works when you hit F5. I'm not an expert in the intricacies of packaging for various target frameworks/platforms/runtimes, nor should I need to be. That stuff should be handled automatically by the tooling.

MyProject.csproj

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <TargetFramework>netstandard2.0</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="PowerShellStandard.Library" Version="5.1.0">
      <PrivateAssets>All</PrivateAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.Data.SqlClient" Version="1.0.19249.1" />
  </ItemGroup>

  <ItemGroup>
    <None Update="*.ps1">
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>
  </ItemGroup>

  <ItemGroup>
    <!-- Microsoft.Data.SqlClient.dll -->
    <None Include="$(NuGetPackageRoot)microsoft.data.sqlclient\1.0.19249.1\lib\net46\Microsoft.Data.SqlClient.dll">
      <Link>deps\win-net461\Microsoft.Data.SqlClient.dll</Link>
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>
    <None Include="$(NuGetPackageRoot)microsoft.data.sqlclient\1.0.19249.1\runtimes\win\lib\netcoreapp2.1\Microsoft.Data.SqlClient.dll">
      <Link>deps\win-netcoreapp2.1\Microsoft.Data.SqlClient.dll</Link>
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>
    <None Include="$(NuGetPackageRoot)microsoft.data.sqlclient\1.0.19249.1\runtimes\unix\lib\netcoreapp2.1\Microsoft.Data.SqlClient.dll">
      <Link>deps\unix-netcoreapp2.1\Microsoft.Data.SqlClient.dll</Link>
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>

    <!-- SNI.dll -->
    <None Include="$(NuGetPackageRoot)microsoft.data.sqlclient.sni\1.0.19235.1\buildTransitive\net46\x64\SNI.dll">
      <Link>deps\win-net461\x64\SNI.dll</Link>
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>
    <None Include="$(NuGetPackageRoot)microsoft.data.sqlclient.sni\1.0.19235.1\buildTransitive\net46\x86\SNI.dll">
      <Link>deps\win-net461\x86\SNI.dll</Link>
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>
    <None Include="$(NuGetPackageRoot)runtime.win7-x64.runtime.native.system.data.sqlclient.sni\4.3.0\runtimes\win7-x64\native\sni.dll">
      <Link>deps\win-netcoreapp2.1\x64\sni.dll</Link>
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>
    <None Include="$(NuGetPackageRoot)runtime.win7-x86.runtime.native.system.data.sqlclient.sni\4.3.0\runtimes\win7-x86\native\sni.dll">
      <Link>deps\win-netcoreapp2.1\x86\sni.dll</Link>
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>

    <!-- Microsoft.Identity.Client.dll -->
    <None Include="$(NuGetPackageRoot)microsoft.identity.client\3.0.8\lib\net45\Microsoft.Identity.Client.dll">
      <Link>deps\win-net461\Microsoft.Identity.Client.dll</Link>
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>
    <None Include="$(NuGetPackageRoot)microsoft.identity.client\3.0.8\lib\netcoreapp2.1\Microsoft.Identity.Client.dll">
      <Link>deps\win-netcoreapp2.1\Microsoft.Identity.Client.dll</Link>
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>
    <None Include="$(NuGetPackageRoot)microsoft.identity.client\3.0.8\lib\netcoreapp2.1\Microsoft.Identity.Client.dll">
      <Link>deps\unix-netcoreapp2.1\Microsoft.Identity.Client.dll</Link>
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>

    <!-- System.Configuration.ConfigurationManager.dll -->
    <None Include="$(NuGetPackageRoot)system.configuration.configurationmanager\4.5.0\lib\netstandard2.0\System.Configuration.ConfigurationManager.dll">
      <Link>deps\win-netcoreapp2.1\System.Configuration.ConfigurationManager.dll</Link>
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>
    <None Include="$(NuGetPackageRoot)system.configuration.configurationmanager\4.5.0\lib\netstandard2.0\System.Configuration.ConfigurationManager.dll">
      <Link>deps\unix-netcoreapp2.1\System.Configuration.ConfigurationManager.dll</Link>
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>

    <!-- System.Security.Permissions.dll -->
    <None Include="$(_NugetFallbackFolder)\system.security.permissions\4.5.0\lib\netstandard2.0\System.Security.Permissions.dll">
      <Link>deps\win-netcoreapp2.1\System.Security.Permissions.dll</Link>
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>

    <!-- System.Text.Encoding.CodePages.dll -->
    <None Include="$(_NugetFallbackFolder)\system.text.encoding.codepages\4.5.0\lib\netstandard2.0\System.Text.Encoding.CodePages.dll">
      <Link>deps\win-netcoreapp2.1\System.Text.Encoding.CodePages.dll</Link>
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>
    <None Include="$(_NugetFallbackFolder)\system.text.encoding.codepages\4.5.0\lib\netstandard2.0\System.Text.Encoding.CodePages.dll">
      <Link>deps\unix-netcoreapp2.1\System.Text.Encoding.CodePages.dll</Link>
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>

  </ItemGroup>

</Project>

Resolve-SqlClient.ps1

if ($PSEdition -eq 'Desktop')
{
    # PowerShell 5.1 on Windows
    Add-Type -Path (Join-Path $PSScriptRoot deps\win-net461\Microsoft.Data.SqlClient.dll)
}
elseif ($IsWindows)
{
    # PowerShell 6.x+ on Windows
    Add-Type -Path (Join-Path $PSScriptRoot deps\win-netcoreapp2.1\Microsoft.Data.SqlClient.dll)
}
else
{
    # PowerShell 6.x+ on *nix
    Add-Type -Path (Join-Path $PSScriptRoot deps\unix-netcoreapp2.1\Microsoft.Data.SqlClient.dll)
}

[MartinHBA]

could someone just give us PowerShell guys that are used to use just PowerShell Gallery modules clear step by step working connection example using Microsoft.Data.Sqlclient? I am having .NET 4.6 and I was able to add nuget as package provider to PowerShell, I was able to Install-Package Microsoft.Data.Sqlclient with PowerShell. But when I would like to create object in PowerShell code I need to load custom dll. When using Add-Type it is giving me errors with dependencies. However I can see all these additional required in same packages folder. When I was trying to load them one by one before calling Microsoft.Data.Sqlclient I was able to do it with Add-Type for Microsoft.Identity.Client and System.Data.Common, but no luck with Microsoft.Data.SqlClient.sni (missing manifest error). Can someone pls provide step by step PowerShell guide how can I use this new Microsoft.Data.Sqlclient namespace? I can do it pretty easily with System.Data.SqlClient but I'd really love to use new namespace now.

[David-Engel]

Keep in mind that the developers of SqlClient are not PowerShell experts/developers.

After a bit of searching, it sounds like the PowerShell infrastructure might just not quite be up to easily handling NuGet packages yet. I found Loading Assemblies from NuGet Packages and Make using NuGet packages installed with Install-Package easier to use - make Add-Type support NuGet packages

It might be better to ask in the PowerShell repository how to correctly load and use a Nuget package.

It was much easier to use System.Data.SqlClient in .NET Framework because it and all dependencies were already installed for you and it only had to support one platform, Windows.

[MartinHBA]

hi @David-Engel , I can load with Add-Type any nuget package without any problem if package has no dependency, Problem is not just loading nuget packages into PowerShell, it can be done, problem is loading nuget packages with all dependencies into PowerShell. And all dependencies should have manifests, etc. When I want to abstract this problem is:
There is not single "Hello World!" step by step example for PowerShell using Microsoft.Data.SqlClient. Not a single one as far as I know. I think this is far fatched said outrageous give that System.Data.Client won't get any more feature updates. @jpsnover promised us all with @theJasonHelmick that with PowerShell we can do all on Windows and here we are, having new .NET namespace that can't be easily loaded into PowerShell and used, yet System.Data.Client silently deprecated.

[David-Engel]

I can load with Add-Type any nuget package without any problem if package has no dependency,

You just described the problem with PowerShell. Dependencies are an integral part of NuGet packages. Once you leave .NET Framework, which was a very monolithic install of a lot of core dependencies, it's a rare package that has zero dependencies.

Until PowerShell fully supports NuGet packages, what new features are you blocked from using in Microsoft.Data.SqlClient that are not available in System.Data.SqlClient on .NET Framework?

[MartinHBA]

@David-Engel thank you for your feedback, I am new to GitHub, can you tell me where we need to push so "Powershell fully supports NuGet packages" ?
when it comes to new features there are now just few, what is important is that they will no longer add more features to System.Data.SqlClient, meaning all new SQL 2019 features won't be available and so on. Which makes System.Data.SqlClient obsolete soon.

[David-Engel]

@MartinHBA

can you tell me where we need to push so "Powershell fully supports NuGet packages" ?

I assume the PowerShell repository. Specifically, the issue I mentioned above looks relevant: Make using NuGet packages installed with Install-Package easier to use - make Add-Type support NuGet packages

[MartinHBA]

Thank you all for your patience with me and guidance, I finally get it done. Key was to switch to PowerShell Core. I will test my scenario on fresh VM next week and let you know step by step if you are interested in. Have a great weekend all

[MartinHBA]

I've documented it here

[MartinHBA]

I also played around with package dependencies, looks brutal, I hope I have it wrong , check it here

[MartinHBA]

Ok, I reworked dependency tree for package from post above. I was using Get-Package command which is not able to filter dependencies by target platform (I've opened issue for PowerShell here) . Alternatively I used Nuget API to determine full dependency tree for current major builds and .NET core 2.1

[potatoqualitee]

Awesome work, @MartinHBA ! thank you so much

[MartinHBA]

@potatoqualitee , after more research on this topic here is conclusion:

1. Install-Package cmdlet in OneGet is now just stupid, grabbing all dependencies regardless of target platform, putting all in one list and then looking for other dependencies and 90% of time ending up with "loop dependency error"
   Let's assume that someone at some point fixes Install-Package that would be able install packages from nuget and not ending up with error, then we have problem number 2:
2. Add-Type cannot load assembly with all dependencies,you need to load using dll in your PowerShell script, you need to manually specify which dll to use, for all of them in correct order, and sometimes you can't load assembly like runtime.native.System.Data.SqlClient.sni with missing manifest.
   PowerShell script is independent on platform so it's up to you to decide which target .NET will you load DLLs for.

Current situation is:

• you can use Nuget package in your PowerShell script easily if package has no dependency
• You can't use any Nuget package in your PowerShell script if there are package dependencies

Whole nuget thing with packages with dependencies can be easily used from Visual Studio with C# project where you simply put nuget package to your project and it's automatically loaded with dependencies for your project.
Nuget with C# = Tesla model S
Nuget with PowerShell = Potato Car

Edit: I've also updated my gist article to compare work with this nuget package in .NET console (piece of cake)

[cheenamalhotra]

Closing issue due to existing limitations in NuGet with PowerShell.

[potatoqualitee]

@SteveL-MSFT - please see #161 (comment) - is there an appropriate repo that we can report this to correct?

[SteveL-MSFT]

@potatoqualitee in the new PSGetv3 spec, we have a section to help PowerShell users use arbitrary nuget packages with intent to understand runtime ids appropriate for your system. I believe this should help resolve some of these issues.

[MartinHBA]

@SteveL-MSFT NuGet and PowerShellGet are separate package providers:

Get-PackageProvider

do I understand you correctly that new PowerShellGet will allow installation of NuGet Packages?

[SteveL-MSFT]

Intent is to have PSGetv3 replace the need for PackageManagement with regards to installing arbitrary NuGet pkgs

[potatoqualitee]

Thank you, Steve!

[MartinHBA]

@SteveL-MSFT can you pls share url to follow for PSgetv3 updates?

[MartinHBA]

@potatoqualitee easiest way forward is:
STEP 1: install Powershell 7
STEP 2: run Powershell 7 as administrator
STEP 3: Install-Package Microsoft.Data.SqlClient
DONE

edit: it worked before, now again showing dependency loop

[sharpjs]

easiest way forward is:
STEP 1: install Powershell 7
STEP 2: run Powershell 7 as administrator
STEP 3: Install-Package Microsoft.Data.SqlClient
DONE

Does not work for me.

> Install-Package Microsoft.Data.SqlClient -Source nuget.org -RequiredVersion 2.0.0 -Scope AllUsers -Verbose
VERBOSE: Using the provider 'NuGet' for searching packages.
VERBOSE: Retry downloading 'https://api.nuget.org/v3/registration5-gz-semver2/microsoft.data.sqlclient/2.0.json' for '0' more times
VERBOSE: Retry downloading 'https://api.nuget.org/v3/registration5-gz-semver2/microsoft.data.sqlclient/2.0.json' for '0' more times
VERBOSE: Total package yield:'1' for the specified package 'Microsoft.Data.SqlClient'.
VERBOSE: Performing the operation "Install Package" on target "Package 'Microsoft.Data.SqlClient' version '2.0.0' from 'nuget.org'.".
VERBOSE: Retry downloading 'https://api.nuget.org/v3/registration5-gz-semver2/microsoft.data.sqlclient/2.0.json' for '0' more times
VERBOSE: Retry downloading 'https://api.nuget.org/v3/registration5-gz-semver2/microsoft.data.sqlclient.sni/1.1.json' for '0' more times
VERBOSE: Retry downloading 'https://api.nuget.org/v3/registration5-gz-semver2/microsoft.data.sqlclient.sni/2.0.json' for '0' more times
VERBOSE: Retry downloading 'https://api.nuget.org/v3/registration5-gz-semver2/microsoft.data.sqlclient.sni/1.1.json' for '0' more times
...about 420 lines skipped...
VERBOSE: Retry downloading 'https://api.nuget.org/v3/registration5-gz-semver2/netstandard.library/1.6.json' for '0' more times
VERBOSE: Retry downloading 'https://api.nuget.org/v3/registration5-gz-semver2/netstandard.library/2.0.json' for '0' more times
VERBOSE: Retry downloading 'https://api.nuget.org/v3/registration5-gz-semver2/netstandard.library/1.6.json' for '0' more times
VERBOSE: Retry downloading 'https://api.nuget.org/v3/registration5-gz-semver2/netstandard.library/2.0.json' for '0' more times
Install-Package: Dependency loop detected for package 'Microsoft.Data.SqlClient'. 

If this is the replacement for such a basic package as System.Data.SqlClient, why is it so difficult to use with PowerShell? So far, the only thing I've found to work is a hack at best.

[MartinHBA]

@sharpjs , can you pls paste here your $psversiontable?
and btw for me my nuget location is https://www.nuget.org/api/v2/ when checked with Get-PackageSource

[sharpjs]

> $PSVersionTable

Name                           Value
----                           -----
PSVersion                      7.0.3
PSEdition                      Core
GitCommitId                    7.0.3
OS                             Microsoft Windows 10.0.19041
Platform                       Win32NT
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

I'll try changing my package source to the v2 URL. Edit: No luck.

> Install-Package Microsoft.Data.SqlClient -Source nuget.org -RequiredVersion 2.0.0 -Scope AllUsers -Verbose VERBOSE: Using the provider 'NuGet' for searching packages.
VERBOSE: Searching repository 'https://www.nuget.org/api/v2/FindPackagesById()?id='Microsoft.Data.SqlClient'' for ''.
VERBOSE: Total package yield:'1' for the specified package 'Microsoft.Data.SqlClient'.
VERBOSE: Performing the operation "Install Package" on target "Package 'Microsoft.Data.SqlClient' version '2.0.0' from 'nuget.org'.".
VERBOSE: Searching repository 'https://www.nuget.org/api/v2/FindPackagesById()?id='Microsoft.Data.SqlClient'' for ''.
VERBOSE: Searching repository 'https://www.nuget.org/api/v2/FindPackagesById()?id='Microsoft.Data.SqlClient.SNI'' for ''.
VERBOSE: Searching repository 'https://www.nuget.org/api/v2/FindPackagesById()?id='Microsoft.Identity.Client'' for ''.
...bunch skipped...
VERBOSE: Searching repository 'https://www.nuget.org/api/v2/FindPackagesById()?id='System.Buffers'' for ''.
VERBOSE: Searching repository 'https://www.nuget.org/api/v2/FindPackagesById()?id='System.Runtime.CompilerServices.Unsafe'' for ''.
VERBOSE: Searching repository 'https://www.nuget.org/api/v2/FindPackagesById()?id='System.Numerics.Vectors'' for ''.
VERBOSE: Searching repository 'https://www.nuget.org/api/v2/FindPackagesById()?id='NETStandard.Library'' for ''.
Install-Package: Dependency loop detected for package 'Microsoft.Data.SqlClient'.

[sharpjs]

FWIW, the hack I currently use for PowerShell binary modules is:

In project file:

<Project Sdk="Microsoft.NET.Sdk">
  ...other stuff...
  <PropertyGroup>
    <TargetFramework>netcoreapp3.1</TargetFramework>
    <RuntimeIdentifiers>win-arm;win-arm64;win-x64;win-x86;unix</RuntimeIdentifiers>
    <CopyLocalLockFileAssemblies>true</CopyLocalLockFileAssemblies>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.Data.SqlClient" Version="2.0.0" />
    <PackageReference Include="System.Management.Automation" Version="7.0.3">
      <PrivateAssets>all</PrivateAssets>
    </PackageReference>
  </ItemGroup>
  ...other stuff...
</Project>

In a .ps1 script executed at module load time:

if ($IsWindows)
{
    Add-Type -Path (Join-Path $PSScriptRoot runtimes\win\lib\netcoreapp3.1\Microsoft.Data.SqlClient.dll)
}
else
{
    Add-Type -Path (Join-Path $PSScriptRoot runtimes\unix\lib\netcoreapp3.1\Microsoft.Data.SqlClient.dll)
}

And SniLoader.cs:

using System.IO;
using System.Runtime.InteropServices;

namespace SomeModule
{
    // Microsoft.Data.SqlClient 2.0.0 and later, at least when used within this
    // PowerShell module, has trouble locating the appropriate SNI DLL.  The
    // workaround is to load it manually.

    internal static class SniLoader
    {
        internal static void Load()
        {
            // Does platform need SNI?
            if (!RuntimeInformation.IsOSPlatform(OSPlatform.Windows))
                return; // no

            // Get runtime identifier
            var rid = RuntimeInformation.ProcessArchitecture switch
            {
                Architecture.X86   => "win-x86",
                Architecture.X64   => "win-x64",
                Architecture.Arm   => "win-arm",
                Architecture.Arm64 => "win-arm64",
                _                  => null
            };

            // Does runtime need SNI?
            if (rid == null)
                return; // no

            // Get path to SNI DLL
            var sniDllPath = Path.Combine(
                Path.GetDirectoryName(typeof(SniLoader).Assembly.Location),
                "runtimes",
                rid,
                "native",
                "Microsoft.Data.SqlClient.SNI.dll"
            );

            // Load SNI DLL
            NativeLibrary.Load(sniDllPath);
        }
    }
}

...and make sure you execute SniLoader.Load() somewhere before you use any SqlClient stuff.

[simonsabin]

Sounds like whats really needed is a better powershell module that wraps sqlclient. Invoke-sqlcmd isn't it.

[sharpjs]

@simonsabin I'm working on it.

[MartinHBA]

@sharpjs @cheenamalhotra @potatoqualitee @simonsabin
Based on previous discussion here I was looking into progress with PowerShellGet v3 which is supposed to be new go-to tool for installing packages, even from Nuget or other providers. It's preview version only atm.
I found nice article about it here
Having high hopes I started to follow article above:

Install-Module PowerShellGet -Force -AllowPrerelease

and now seeing PowerShellGet v3 commands suite:

Then I tried and I've got some result

Find-PSResource Microsoft.Data.SqlClient -Version *

So I've tried final promising command:

Install-PSResource Microsoft.Data.SqlClient -Version 2.0.0 -Verbose

However it's stuck for me as below:

If you would have more luck with this approach please let me know, I found similar issue opened for this.

[cheenamalhotra]

@MartinHBA

Could you try making an entry for NuGet as trusted source? Wondering if this is related: https://github.com/PowerShell/PowerShellGet/issues/154#issuecomment-647027689 ?

[MartinHBA]

@cheenamalhotra it worked fine to install Pester (without "force" parameter, that is unavailable), trying same for Microsoft.Data.SqlClient it didn't work and is stuck forever

Nuget is trusted for me

[ErikEJ]

Should it not be: https://api.nuget.org/v3/index.json ?

[MartinHBA]

@ErikEJ Pester installation from Nuget works, would you kindly test this from your side? I tested all http and https and v2 and v3, no luck for Microsoft.Data.SqlClient, works smoothly for Pester package.

[cheenamalhotra]

@MartinHBA

Is there any other .NET NuGet package that worked for you?
Pester seems to be designed for Powershell, and also has no dependencies, it's not surprising it works.

[MartinHBA]

well, is there? who knows...

[MartinHBA]

I can't get it work at current, I just wait for next Powershell 7 preview version

We hope to ship the the latest preview of PowerShellGet 3.0 in the next preview of PowerShell 7.1 (preview 6). The goal for this version of PowerShellGet, which will ship in PowerShell 7.1 preview 6, is to contain a compatibility module that will enable scripts with PowerShell 2.x cmdlets (ex. Install-Module) to be run using the PowerShellGet 3.0 module. This means that users will likely not need to update their scripts to use PowerShellGet 2.x cmdlets with PowerShell 7.1. It is important to note, as well, that on systems which contain any other version of PowerShell, the PowerShellGet 2.x module will still be available and used. We hope to ship PowerShellGet 3.0 with a compatibility layer into PowerShell 7.1 as the sole version of PowerShellGet in the package. However, we will only do this if we reach GA, with a high bar for release quality, in time for the PowerShell 7.1 release candidate.