Using Microsoft.Data.SqlClient in Powershell

gistfile1.txt

# ATTENTION - This was Updated November 25, 2024

# The new [microsoft.data.sqlclient] provider allows new features such as
#     New Azure AD Authentication Methods
#     BulkCopy Speedups
#     DNS Resiliency
#     Data Classification
#     UTF-8 Support
#     Connection Statistics - 
#         https://docs.microsoft.com/en-us/sql/connect/ado-net/sql/provider-statistics-sql-server?view=sql-server-ver15

# MS Docs are here
# https://devblogs.microsoft.com/dotnet/introducing-the-new-microsoftdatasqlclient/
# https://devblogs.microsoft.com/azure-sql/microsoft-data-sqlclient-2-0-0-is-now-available/

# Project
# https://github.com/dotnet/SqlClient

# Installation:
# 1) In an elevated Powershell console type:
#        nuget.exe install Microsoft.Data.Sqlclient -outputdirectory c:\nuget
# This (should) resolve all the dependencies
# One Issue with (Nuget) is a broken version reference for [system.runtime.compilerservices.unsafe] 
# as Nuget gets latest, but the dependency chain wants Version 4.5.3 - which is actually 4.0.4.1 per this Stack Article:
# https://stackoverflow.com/questions/62764744/could-not-load-file-or-assembly-system-runtime-compilerservices-unsafe

# 2) Copy the following DLLs to the same folder as the Powershell psm1 file below
# copy all DLLs from the extracted NUGet sub folders (c:\nuget) to your work folder
# for the desired platform (Framework, Standard ,Core) and version
# of course, you need those runtimes installed on your box (Framework, Standard, Core)

# As of December 2024, these versions now work in Powershell 5.1 and in ISE:
# Copy to a shared folder this minimum DLL set along with the PSM1 that follows below
# Azure.Core.dll - 1.3.8
# Microsoft.Bcl.AsyncInterfaces.dll - 9.0.24
# Microsoft.Data.SqlClient.dll - 5.2.2
# Microsoft.Data.SqlClient.SNI.x64.dll - 5.2.0
# Microsoft.Identity.Client.dll - 4.61.3
# Microsoft.IdentityModel.Abstractions.dll - 6.35
# System.Buffers.dll - 4.6
# System.Memory.dll - 4.6
# System.Runtime.CompilerServices.Unsafe.dll - 4.6
# System.Threading.Tasks.Extensions.dll - 4.6


# 3) Create a Powershell Module Microsoft_Data_Sqlclient.psm1 as:
------------------
Function LoadMicrosoftDataSQLClient
{
    # Load custom NuGet Assemblies instead of the .NET Framework GAC version [system.data.sqlclient]
    try
    {   
        Add-Type -Path "C:\psscripts\MSSqlClient\System.Buffers.dll" -ErrorAction Stop
        Add-Type -Path "C:\psscripts\MSSqlClient\System.Runtime.CompilerServices.Unsafe.dll" -ErrorAction Stop
        Add-Type -Path "C:\psscripts\MSSqlClient\System.Threading.Tasks.Extensions.dll" -ErrorAction Stop
        Add-Type -Path "C:\psscripts\MSSqlClient\Microsoft.Bcl.AsyncInterfaces.dll" -ErrorAction Stop
        Add-Type -Path "C:\psscripts\MSSqlClient\Azure.Core.dll" -ErrorAction Stop
        Add-Type -Path "C:\psscripts\MSSqlClient\Microsoft.IdentityModel.Abstractions.dll" -ErrorAction Stop        
        Add-Type -Path "C:\psscripts\MSSqlClient\Microsoft.Identity.Client.dll" -ErrorAction Stop    
        Add-Type -Path "C:\psscripts\MSSqlClient\Microsoft.Data.SqlClient.dll" -ErrorAction Stop
    }
    catch [System.Reflection.ReflectionTypeLoadException]
    {
        Write-Host "Message: $($_.Exception.Message)"
        Write-Host "StackTrace: $($_.Exception.StackTrace)"
        Write-Host "LoaderExceptions: $($_.Exception.LoaderExceptions)"
    } 
}

Function Connect-MSInternalSQLServer
{   
    [CmdletBinding()]
    Param(
         [String]$SQLInstance,
         [String]$Database,
         [String]$SQLExec          
         )

    Process
    {
        $Connection = [Microsoft.Data.SqlClient.SqlConnection]::new()
        $SqlCmd     = [Microsoft.Data.SqlClient.SqlCommand]::new()
        $SqlAdapter = [Microsoft.Data.SqlClient.SqlDataAdapter]::new()
        $DataSet    = [System.Data.DataSet]::new()

        $SQLConnectionString = "Data Source=$SQLInstance;Initial Catalog=$Database;Integrated Security=SSPI;TrustServerCertificate=yes"
        $Connection.ConnectionString = $SQLConnectionString
        $Connection.Open()
               
        $SqlCmd.CommandText       = $SQLExec
        $SqlCmd.CommandTimeout    = 0
        $SqlCmd.Connection        = $Connection        
        $SqlAdapter.SelectCommand = $SqlCmd
   
        # Insert results into Dataset table
        $SqlAdapter.Fill($DataSet) | Out-Null
        
        # New MS.Data.SqlClient Statistics - returns System.Collections.Generic.Dictionary
        $Connection_Statistics = $Connection.RetrieveStatistics()

        # Eval Return Set
        if ($DataSet.Tables.Count -ne 0) 
        {
            $sqlresults = $DataSet.Tables[0]
        }
        else
        {
            $sqlresults =$null
        }

        # Close connection to sql server
        $Connection.Close()

        Write-Output $sqlresults
    }
}

# 4) Test in Powershell 5.1 ISE a Console or in VisualStudio Code with Posh 5.1 runtime selected
Import-Module -Name '.\Microsoft_Data_Sqlclient.psm1'
Connect-MSInternalSQLServer -SQLInstance 'myservername' -Database 'master' -SQLExec "select * from sys.certificates"