Using Microsoft.Data.SqlClient in Powershell

gistfile1.txt

# 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

# 
# Download the following Nuget Packages:
# https://www.nuget.org/packages/Microsoft.Data.SqlClient/2.1.0-preview2.20297.7
# https://www.nuget.org/packages/Microsoft.Identity.Client/
# https://www.nuget.org/packages/Microsoft.Data.SqlClient.SNI.runtime/

# Installation:
# 1) In an elevated Powershell console type:
#        nuget.exe install microsoft.Data.Sqlclient -outputdirectory c:\nuget
#        nuget.exe install Microsoft.Identity.Client -outputdirectory c:\nuget
#        nuget.exe install Microsoft.Data.SqlClient.SNI.runtime -outputdirectory c:\nuget

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

# Copy: 
# Microsoft.Data.SqlClient.dll
# Microsoft.Data.SqlClient.SNI.x64.dll
# Microsoft.Identity.Client.dll

# 3) Create Powershell Module Microsoft_Data_Sqlclient.psm1:
------------------
Function LoadMicrosoftDataSQLClient
{
    # Load custom NuGet Assemblies instead of the .NET Framework GAC version [system.data.sqlclient]
    try
    {
        Add-Type -Path ".\Microsoft.Identity.Client.dll" -ErrorAction Stop
        Add-Type -Path ".\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-InternalSQLServer
{   
    [CmdletBinding()]
    Param(
         [String]$SQLInstance,
         [String]$Database,
         [String]$SQLExec          
         )

    Begin
    {
       LoadMicrosoftDataSQLClient
    }

    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;"
        $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

        # 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
Import-Module -Name '.\Microsoft_Data_Sqlclient.psm1'
Connect-InternalSQLServer -SQLInstance 'myservername' -Database 'master' -SQLExec "select * from sys.certificates"