Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Using Microsoft.Data.SqlClient in Powershell
# 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"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment