Providers/SQL/config.ps1

#Provider Class
Class SQLProvider : ProviderBase {
    
    SQLProvider([string]$ConnectionName
                , [int]$CommandTimeout
                , [System.Data.SqlClient.SqlConnection]$Connection) {

        $this.ConnectionName = $ConnectionName
        $this.CommandTimeout = $CommandTimeout
        $this.Connection = $Connection
        
        $messages = $this.Messages
        $handler = {Param($sender, [System.Data.SqlClient.SqlInfoMessageEventArgs]$e)
            $messages.Enqueue(([SqlMessage]@{Received=(Get-Date); Message=$e.Message}))
        }.GetNewClosure()

        $this.Connection.add_InfoMessage([System.Data.SqlClient.SqlInfoMessageEventHandler]$handler)
    }

    [string] ProviderType() { return "MSSQL" }

    [PSCustomObject] ConnectionInfo() {
        return [PSCustomObject]@{
            ConnectionName = $this.ConnectionName
            ProviderType = $this.ProviderType()
            ConnectionState = $this.Connection.State
            ConnectionString = $this.Connection.ConnectionString
            ServerVersion = $this.Connection.ServerVersion
            Server = $this.Connection.DataSource
            Database = $this.Connection.Database
            CommandTimeout = $this.CommandTimeout
            HasTransaction = $this.HasTransaction()
        }
    }

    [void] ChangeDatabase([string]$DatabaseName) { $this.Connection.ChangeDatabase($DatabaseName) }

    [System.Data.DataSet] GetDataSet([System.Data.IDbCommand]$cmd) {
        $ds = [System.Data.DataSet]::new()
        $da = [System.Data.SqlClient.SqlDataAdapter]::new($cmd)
        Try {
            $da.Fill($ds)
            return $ds 
        }
        Catch { Throw $_ }
        Finally { $da.dispose() }
    }

    [long] BulkLoad([System.Data.IDataReader]$DataReader
                    , [string]$DestinationTable
                    , [hashtable]$ColumnMap = @{}
                    , [int]$BatchSize
                    , [int]$BatchTimeout
                    , [ScriptBlock]$Notify) {
        
        [long]$RowCount = 0
        $bcp = [System.Data.SqlClient.SqlBulkCopy]::new($this.Connection, [System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity, $null)
        Try {
            $bcp.DestinationTableName = $DestinationTable
            $bcp.BatchSize = $BatchSize
            $bcp.BulkCopyTimeout = $BatchTimeout
            $bcp.EnableStreaming = $true

            If($ColumnMap -and $ColumnMap.Count -gt 0) {
                ForEach ($de in $ColumnMap.GetEnumerator()) {
                    $bcp.ColumnMappings.Add($de.Key, $de.Value)
                }
            }
            
            If($Notify) {
                $bcp.NotifyAfter = $BatchSize
                $bcp.add_SqlRowsCopied({
                    Param($sender, [System.Data.SqlClient.SqlRowsCopiedEventArgs]$e)
                    $Notify.Invoke($e.RowsCopied)
                })
            }
            
            $RowCount -= $this.GetScalar("SELECT COUNT(1) FROM $DestinationTable", 30, @{})
            $bcp.WriteToServer($DataReader)
            $RowCount += $this.GetScalar("SELECT COUNT(1) FROM $DestinationTable", 30, @{})
        }
        Finally {
            $bcp.Close()
            $bcp.Dispose()
            $DataReader.Close()
            $DataReader.Dispose()
            $this.Update("IF EXISTS(SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('$DestinationTable') AND is_identity = 1) SET IDENTITY_INSERT $DestinationTable OFF", 30, @{}) | Out-Null #make sure Identity Insert is off...
        }
        
        return $RowCount
    }
}

#Open Cmdlet
Function Open-SqlConnection {
    <#
    .Synopsis
        Open a connection to a SQL Server.
 
    .Description
        Open a connection to a SQL Server. Default authentication is Integrated Windows Authetication.
         
        System.Data.SqlClient.
 
    .Parameter ConnectionName
        The name to associate with the newly created connection.
        This connection will be used with other cmdlets when specifying
        -ConnectionName <name>. If you don't specify one, it will be
        set to the default.
 
    .Parameter ConnectionString
        Specifies a provider specific connectionstring to be used.
 
    .Parameter CommandTimeout
        The default command timeout to be used for all commands executed against this connection.
 
    .Parameter DataSource
        The datasource for the connection.
 
    .Parameter InitialCatalog
        Database catalog connecting to.
 
    .Parameter UserName
        Username to connect as.
 
    .Parameter Password
        Password for the connecting user.
 
    .Parameter AzureAD
        Use this when connecting to an Azure SQL Database and you are using Azure AD credentials.
        You can specify the credentials either by using the UserName/Password parameters or by
        passing in a credential object to the Credential parameter.
 
    .Parameter Credential
        Credential object containing the SQL user/pass.
 
    #>

    [CmdletBinding(DefaultParameterSetName="default")]
    Param([Parameter(ValueFromPipelineByPropertyName)][Alias("cn")][string]$ConnectionName = "default"
        , [Parameter(ValueFromPipelineByPropertyName)][int]$CommandTimeout = 30
        , [Parameter(ValueFromPipelineByPropertyName, ParameterSetName="default", Position=0)]
            [Parameter(ValueFromPipelineByPropertyName, ParameterSetName="user", Position=0)]
            [Parameter(ValueFromPipelineByPropertyName, ParameterSetName="cred", Position=0)]
                [Alias("SqlInstance","SqlServer","DataSource")][string]$Server = "localhost"
        , [Parameter(ValueFromPipelineByPropertyName, ParameterSetName="default", Position=1)]
            [Parameter(ValueFromPipelineByPropertyName, ParameterSetName="user", Position=1)]
            [Parameter(ValueFromPipelineByPropertyName, ParameterSetName="cred", Position=1)]
                [Alias("SqlDatabase","InitialCatalog")][string]$Database = "master"
        , [Parameter(Mandatory, ValueFromPipelineByPropertyName, ParameterSetName="user")]
            [string]$UserName
        , [Parameter(Mandatory, ValueFromPipelineByPropertyName, ParameterSetName="user")]
            [string]$Password
        , [Parameter(Mandatory, ValueFromPipelineByPropertyName, ParameterSetName="cred")]
            [Parameter(ValueFromPipelineByPropertyName, ParameterSetName="conn")]
                [pscredential]$Credential
        , [Parameter(ParameterSetName="user")]
            [Parameter(ParameterSetName="cred")][Switch]$AzureAD
        , [Parameter(Mandatory, ValueFromPipelineByPropertyName, ParameterSetName="Conn")][string]$ConnectionString)

    If($Script:Connections.ContainsKey($ConnectionName)) { Close-SqlConnection $ConnectionName }

    If($ConnectionString) { $sb = [System.Data.SqlClient.SqlConnectionStringBuilder]::new($ConnectionString) }
    Else {
        $sb = [System.Data.SqlClient.SqlConnectionStringBuilder]::new()

        If($Server) { $sb.Server = $Server }
        If($Database) { $sb.Database = $Database }
        If($UserName) { 
            Write-Warning "You are using -UserName and -Password, these options are deprecated and will be removed in the future. Please consider using -Credential."
            $sb["User Id"] = $UserName
            $sb.Password = $Password
        }
        If($AzureAD) {
            $sb.Encrypt = $true
            $sb.Authentication = "Active Directory Password"
        }
        If(-not $UserName -and -not $Credential) { $sb["Integrated Security"] = $true }
    }
    $sb["Application Name"] = "PowerShell ({0})" -f $ConnectionName

    If($Credential) {
        [securestring]$sqlCred = $Credential.Password.Copy()
        $sqlCred.MakeReadOnly()
        $conn =  [System.Data.SqlClient.SqlConnection]::new($sb.ConnectionString, [System.Data.SqlClient.SqlCredential]::new($Credential.UserName, $sqlCred))
    }
    Else { $conn = [System.Data.SqlClient.SqlConnection]::new($sb.ConnectionString) }
    
    Try { $conn.Open() }
    Catch {
        $conn.Dispose()
        Throw $_
    }
    $Script:Connections.$ConnectionName = [SQLProvider]::new($ConnectionName, $CommandTimeout, $conn)
}

Export-ModuleMember -Function Open-SqlConnection