SqlDataTools.psm1

Set-StrictMode -Version Latest

#Region Global Configuration
try {
    $Script:PSData = $null
}
catch {
    throw $_
}
#EndRegion

#Region Enumerations
enum Mode {
    Export
    Import
    Merge
}

enum Encoding {
    ASCII
    BigEndianUnicode
    BigEndianUTF32
    OEM
    Unicode
    UTF7
    UTF8
    UTF8BOM
    UTF8NoBOM
    UTF32
}
#EndRegion

#Region Classes
Class ArgumentCompleterResult {
    #properties

    #Method
    static [System.Management.Automation.CompletionResult[]] GetArgumentCompleterResult([System.Array]$Arguments) {
        #[string]$CommandName = $Arguments[0]
        [string]$ParameterName = $Arguments[1]
        [string]$WordToComplete = $Arguments[2]
        #[System.Management.Automation.Language.CommandAst]$CommandAst = $Arguments[3]
        [Hashtable]$FakeBoundParameters = $Arguments[4]

        $ParameterValueList = $null

        switch ($ParameterName) {
            'DataTemplateName' {
                $TemplateList = [System.Collections.Generic.List[string]]::New()

                if ($FakeBoundParameters.ContainsKey('Mode')) {
                    switch ($FakeBoundParameters['Mode']) {
                        'Export' {
                            $TemplateList.AddRange([string[]]$Script:PSData.Export.Keys)
                        }
                        'Import' {
                            $TemplateList.AddRange([string[]]$Script:PSData.Import.Keys)

                        }
                        'Merge' {
                            $TemplateList.AddRange([string[]]$Script:PSData.Merge.Keys)
                        }
                    }
                } else {
                    $TemplateList.AddRange([string[]]$Script:PSData.Export.Keys)
                    $TemplateList.AddRange([string[]]$Script:PSData.Merge.Keys)
                }

                $ParameterValueList = $TemplateList
            }
            Default {
                throw [System.Management.Automation.ErrorRecord]::New(
                    [Exception]::New('Unknown argument completer.'),
                    '1',
                    [System.Management.Automation.ErrorCategory]::InvalidType,
                    $ParameterName
                )
            }
        }

        $SelectedParameters = $ParameterValueList.where({ $_ -like "$WordToComplete*" }) | Sort-Object

        $CompletionResultList = [System.Collections.Generic.List[System.Management.Automation.CompletionResult]]::New()

        foreach ($Parameter in $SelectedParameters) {
            if ($Parameter -match '\s|''') {
                $CompletionText = [string]::Format("'{0}'", $($Parameter.Replace("'", "''")))
            } else {
                $CompletionText = $Parameter
            }

            $ListItemText = $Parameter
            $ResultType = [System.Management.Automation.CompletionResultType]::ParameterValue
            $ToolTip = $Parameter

            $CompletionResultList.Add([System.Management.Automation.CompletionResult]::New($CompletionText, $ListItemText, $ResultType, $ToolTip))
        }

        return $CompletionResultList
    }
}

Class ExportConfigurationTemplates : System.Management.Automation.IValidateSetValuesGenerator {
    [string[]] GetValidValues() {
        if ($null -eq $Script:PSData) {
            throw [System.Management.Automation.ErrorRecord]::New(
                [Exception]::New('Template repository not loaded. Use the Use-TemplateRepository function to load template repository.'),
                '1',
                [System.Management.Automation.ErrorCategory]::ResourceUnavailable,
                $null
            )
        } elseif ($null -eq $Script:PSData.Export) {
            return @()
        } else {
            return [string[]]$Script:PSData.Export.Keys
        }
    }
}

Class ImportConfigurationTemplates : System.Management.Automation.IValidateSetValuesGenerator {
    [string[]] GetValidValues() {
        if ($null -eq $Script:PSData) {
            throw [System.Management.Automation.ErrorRecord]::New(
                [Exception]::New('Template repository not loaded. Use the Use-TemplateRepository function to load template repository.'),
                '1',
                [System.Management.Automation.ErrorCategory]::ResourceUnavailable,
                $null
            )
        } elseif ($null -eq $Script:PSData.Import) {
            return @()
        } else {
            return [string[]]$Script:PSData.Import.Keys
        }
    }
}

Class MergeConfigurationTemplates : System.Management.Automation.IValidateSetValuesGenerator {
    [string[]] GetValidValues() {
        if ($null -eq $Script:PSData) {
            throw [System.Management.Automation.ErrorRecord]::New(
                [Exception]::New('Template repository not loaded. Use the Use-TemplateRepository function to load template repository.'),
                '1',
                [System.Management.Automation.ErrorCategory]::ResourceUnavailable,
                $null
            )
        } elseif ($null -eq $Script:PSData.Merge) {
            return @()
        } else {
            return [string[]]$Script:PSData.Merge.Keys
        }
    }
}

class ValidatePathExists : System.Management.Automation.ValidateArgumentsAttribute {
    [string]$PathType = 'Any'

    ValidatePathExists([string]$PathType) {
        $this.PathType = $PathType
    }

    [void]Validate([object]$Path, [System.Management.Automation.EngineIntrinsics]$EngineIntrinsics) {
        if([string]::IsNullOrWhiteSpace($Path)) {
            throw [System.ArgumentNullException]::New()
        }

        if(-not (Test-Path -Path $Path -PathType $this.PathType)) {
            switch ($this.PathType) {
                'Container' {
                    throw [System.IO.DirectoryNotFoundException]::New()
                }
                'Leaf' {
                    throw [System.IO.FileNotFoundException]::New()
                }
                Default {
                    throw [System.InvalidOperationException]::New('An unexpected error has occurred.')
                }
            }
        }
    }
}

class ValidatePathIsValid : System.Management.Automation.ValidateArgumentsAttribute {
    [void]Validate([object]$Path, [System.Management.Automation.EngineIntrinsics]$EngineIntrinsics) {
        if([string]::IsNullOrWhiteSpace($Path)) {
            throw [System.ArgumentNullException]::New()
        }

        if ($Path -is [System.IO.DirectoryInfo]) {
            if (-not $(Test-Path -LiteralPath $Path.Parent -PathType Container)) {
                throw [System.Management.Automation.ErrorRecord]::New(
                    [System.ArgumentException]::New("Cannot find path '$Path.Directory' because it does not exist."),
                    '1',
                    [System.Management.Automation.ErrorCategory]::InvalidArgument,
                    $Path
                )
            } elseif (-not $(Test-Path -LiteralPath $Path -PathType Container -IsValid)) {
                throw [System.Management.Automation.ErrorRecord]::New(
                    [System.ArgumentException]::New("The path '$Path.Name' is not valid."),
                    '1',
                    [System.Management.Automation.ErrorCategory]::InvalidArgument,
                    $Path
                )
            }
        }

        if ($Path -is [System.IO.FileInfo]) {
            if (-not $(Test-Path -LiteralPath $Path.Directory -PathType Container)) {
                throw [System.Management.Automation.ErrorRecord]::New(
                    [System.ArgumentException]::New("Cannot find path '$Path.Directory' because it does not exist."),
                    '1',
                    [System.Management.Automation.ErrorCategory]::InvalidArgument,
                    $Path
                )
            } elseif (-not $(Test-Path -LiteralPath $Path -PathType Leaf -IsValid)) {
                throw [System.Management.Automation.ErrorRecord]::New(
                    [System.ArgumentException]::New("The path '$Path.Name' is not valid."),
                    '1',
                    [System.Management.Automation.ErrorCategory]::InvalidArgument,
                    $Path
                )
            }
        }
    }
}

class TextFieldParser {
    [System.IO.StreamReader]$StreamReader
    [Microsoft.VisualBasic.FileIO.TextFieldParser]$TextFieldParser
    [char]$Delimiter
    [switch]$IgnoreQuotes
    [string[]]$Headers
    [int]$CurrentLine = 0
    [int]$TotalLines = 0

    TextFieldParser([string]$Path, [Encoding]$Encoding, [char]$Delimiter, [switch]$IgnoreQuotes) {
        if([string]::IsNullOrWhiteSpace($Path)) {
            throw [System.ArgumentNullException]::New()
        }

        if (-not (Test-Path -Path $Path -PathType Leaf)) {
            throw [System.IO.FileNotFoundException]::New("Cannot find path '$Path' because it does not exist.")
        }

        try {
            $FileStream = [System.IO.FileStream]::New($Path, [System.IO.FileMode]::Open, [System.IO.FileAccess]::Read, [System.IO.FileShare]::Read)

            $this.StreamReader = [System.IO.StreamReader]::New($FileStream, $Encoding)
            $this.Delimiter = $Delimiter
            $this.IgnoreQuotes = $IgnoreQuotes

            $this.TotalLines = 0

            while ($null -ne $this.StreamReader.ReadLine()) {
                $this.TotalLines++
            }

            [void]$this.StreamReader.BaseStream.Seek(0, [System.IO.SeekOrigin]::Begin)
            $this.StreamReader.DiscardBufferedData()

            $TextEncoding = [System.Text.Encoding]::$Encoding

            switch ($Encoding) {
                'BigEndianUTF32' {
                    $TextEncoding = [System.Text.Encoding]::GetEncoding('utf-32BE')
                }
                'OEM' {
                    $TextEncoding = [System.Text.Encoding]::GetEncoding([System.Globalization.CultureInfo]::CurrentCulture.TextInfo.OEMCodePage)
                }
                'UTF8BOM' {
                    $TextEncoding = [System.Text.Encoding]::UTF8
                }
                'UTF8NoBOM' {
                    $TextEncoding = [System.Text.Encoding]::New([System.Text.UTF8Encoding]::New($false))
                }
                Default {
                    if ([System.Text.Encoding]::$Encoding -is [System.Text.Encoding]) {
                        $TextEncoding = [System.Text.Encoding]::$Encoding
                    } else {
                        throw [System.Management.Automation.ErrorRecord]::New(
                            [Exception]::New("Invalid encoding specified: $Encoding"),
                            '1',
                            [System.Management.Automation.ErrorCategory]::NotImplemented,
                            $Encoding
                        )
                    }
                }
            }

            $this.TextFieldParser = [Microsoft.VisualBasic.FileIO.TextFieldParser]::New($FileStream, $TextEncoding)

            $this.TextFieldParser.SetDelimiters($Delimiter)

            if ($IgnoreQuotes) {
                $this.TextFieldParser.HasFieldsEnclosedInQuotes = $false
            }

            $this.Headers = $this.TextFieldParser.ReadFields()
        }
        catch {
            throw $_
        }
    }

    TextFieldParser([string]$Path, [Encoding]$Encoding, [char]$Delimiter, [switch]$IgnoreQuotes, [string[]]$RequiredColumnNames) {
        if([string]::IsNullOrWhiteSpace($Path)) {
            throw [System.ArgumentNullException]::New()
        }

        if (-not (Test-Path -Path $Path -PathType Leaf)) {
            throw [System.IO.FileNotFoundException]::New("Cannot find path '$Path' because it does not exist.")
        }

        try {
            $FileStream = [System.IO.FileStream]::New($Path, [System.IO.FileMode]::Open, [System.IO.FileAccess]::Read, [System.IO.FileShare]::Read)

            $this.StreamReader = [System.IO.StreamReader]::New($FileStream, $Encoding)
            $this.Delimiter = $Delimiter
            $this.IgnoreQuotes = $IgnoreQuotes

            $this.TotalLines = 0

            while ($null -ne $this.StreamReader.ReadLine()) {
                $this.TotalLines++
            }

            [void]$this.StreamReader.BaseStream.Seek(0, [System.IO.SeekOrigin]::Begin)
            $this.StreamReader.DiscardBufferedData()

            $TextEncoding = [System.Text.Encoding]::$Encoding

            switch ($Encoding) {
                'BigEndianUTF32' {
                    $TextEncoding = [System.Text.Encoding]::GetEncoding('utf-32BE')
                }
                'OEM' {
                    $TextEncoding = [System.Text.Encoding]::GetEncoding([System.Globalization.CultureInfo]::CurrentCulture.TextInfo.OEMCodePage)
                }
                'UTF8BOM' {
                    $TextEncoding = [System.Text.Encoding]::UTF8
                }
                'UTF8NoBOM' {
                    $TextEncoding = [System.Text.Encoding]::New([System.Text.UTF8Encoding]::New($false))
                }
                Default {
                    if ([System.Text.Encoding]::$Encoding -is [System.Text.Encoding]) {
                        $TextEncoding = [System.Text.Encoding]::$Encoding
                    } else {
                        throw [System.Management.Automation.ErrorRecord]::New(
                            [Exception]::New("Invalid encoding specified: $Encoding"),
                            '1',
                            [System.Management.Automation.ErrorCategory]::NotImplemented,
                            $Encoding
                        )
                    }
                }
            }

            $this.TextFieldParser = [Microsoft.VisualBasic.FileIO.TextFieldParser]::New($FileStream, $TextEncoding)

            $this.TextFieldParser.SetDelimiters($Delimiter)

            if ($IgnoreQuotes) {
                $this.TextFieldParser.HasFieldsEnclosedInQuotes = $false
            }

            $this.Headers = $this.TextFieldParser.ReadFields()

            $ComparObjectParameters = @{
                'ReferenceObject' = $this.Headers
                'DifferenceObject' = $RequiredColumnNames
            }

            $MissingProperties = (Compare-Object @ComparObjectParameters).where({$_.SideIndicator -eq "=>"})

            if ($MissingProperties.Count -gt 0) {
                throw [System.Management.Automation.ErrorRecord]::New(
                    [Exception]::New("CSV missing required properties: $([string]::Join(', ', $MissingProperties.InputObject))"),
                    '1',
                    [System.Management.Automation.ErrorCategory]::InvalidData,
                    $MissingProperties
                )
            }
        }
        catch {
            throw $_
        }
    }

    [void] Close() {
        $this.TextFieldParser.Close()
        $this.StreamReader.Close()
        $this.StreamReader.BaseStream.Close()
    }

    [PSCustomObject] ReadLine() {
        $this.CurrentLine = $this.TextFieldParser.LineNumber

        $Fields = $this.TextFieldParser.ReadFields();

        if ($null -eq $Fields) {
            return $null
        }

        $Row = [PSCustomObject]::New()

        for ($i = 0; $i -lt $Fields.Count; $i++) {
            $Row.PSObject.Properties.Add(
                [PSNoteProperty]::new($this.Headers[$i], $Fields[$i])
            )
        }

        return $Row
    }

    [bool] IsValidRow([PSCustomObject]$Row, [psobject[]]$InputColumns) {
        $WarningFormatString = 'In row {0}, column {1} with the value "{2}" is not a valid value.'
        $IsValidRow = $true
        $Match = $false

        foreach ($InputColumn in $InputColumns) {
            switch ($InputColumn.Keys.where({$_ -NotIn @('DataType', 'Name')})) {
                'ValidatePattern' {
                    $Regex = [System.Text.RegularExpressions.Regex]::New($InputColumn.ValidatePattern)
                    $Match = $Regex.IsMatch($Row.$($InputColumn.Name))

                    Continue
                }
                'ValidateRange' {
                    $TypeObject = [System.Type]::GetType($InputColumn.DataType)

                    $ColumnValue = [System.Management.Automation.LanguagePrimitives]::ConvertTo($Row.$($InputColumn.Name), $TypeObject)

                    $Match = $ColumnValue -ge $InputColumn.ValidateRange[0] -and $ColumnValue -le $InputColumn.ValidateRange[1]

                    Continue
                }
                'ValidateSet' {
                    $Match = $Row.$($InputColumn.Name) -In $InputColumn.ValidateSet

                    Continue
                }
                Default {
                    throw [System.Management.Automation.ErrorRecord]::New(
                        [Exception]::New('Unknown validate method.'),
                        '1',
                        [System.Management.Automation.ErrorCategory]::InvalidOperation,
                        $InputColumn
                    )
                }
            }

            if (-not $Match) {
                Write-Warning $([string]::Format($WarningFormatString, $this.CurrentLine, $InputColumn.Name, $Row.$($InputColumn.Name)))

                $IsValidRow = $false
            }
        }

        return $IsValidRow
    }
}

class TransformPath : System.Management.Automation.ArgumentTransformationAttribute {
    [object]Transform([System.Management.Automation.EngineIntrinsics]$EngineIntrinsics, [object]$InputData) {
        if ([System.IO.Path]::IsPathRooted($InputData)) {
            return $InputData
        } else {
            return [System.IO.Path]::GetFullPath([System.IO.Path]::Combine($PWD.Path, $InputData))
        }

        throw [System.InvalidOperationException]::New('Unexpected error.')
    }
}
#EndRegion

#Region Type Definitions
$TypeDefinition = @'
using System;
namespace SqlDataTools
{
    public class SqlDataTemplate
    {
        public string Mode;
        public string Name;
        public string Description;
    }
}
'@


Add-Type -TypeDefinition $TypeDefinition

Remove-Variable -Name TypeDefinition
#EndRegion


function Export-SqlData {
    <#
    .EXTERNALHELP
    SqlDataTools-Help.xml
    #>


    [System.Diagnostics.DebuggerStepThrough()]

    [CmdletBinding(
        PositionalBinding = $false,
        SupportsShouldProcess = $true,
        ConfirmImpact = 'Low',
        DefaultParameterSetName = 'ServerInstance_Template'
    )]

    [OutputType([System.Void])]

    param (
        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'ServerInstance_Template'
        )]
        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'SqlConnection_Template'
        )]
        [ValidateSet([ExportConfigurationTemplates])]
        [string]$DataTemplateName,

        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'ServerInstance_Table'
        )]
        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'SqlConnection_Table'
        )]
        [ValidateLength(1, 128)]
        [string]$TableName,

        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [ValidatePathIsValid()]
        [System.IO.FileInfo][TransformPath()]$Path,

        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'ServerInstance_Table'
        )]
        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'ServerInstance_Template'
        )]
        [ValidateLength(1, 128)]
        [Alias('SqlServer')]
        [string]$ServerInstance,

        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'SqlConnection_Table'
        )]
        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'SqlConnection_Template'
        )]
        [Microsoft.Data.SqlClient.SqlConnection]$SqlConnection,

        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'ServerInstance_Table'
        )]
        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'ServerInstance_Template'
        )]
        [ValidateLength(1, 128)]
        [Alias('Database')]
        [string]$DatabaseName,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [Encoding]$Encoding,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [char]$Delimiter,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [Switch]$Force
    )

    begin {
        $ServerInstanceParameterSets = @('ServerInstance_Table', 'ServerInstance_Template')
        $TemplateParameterSets = @('ServerInstance_Template', 'SqlConnection_Template')

        try {
            if ($PSCmdlet.ParameterSetName -in $TemplateParameterSets) {
                $Configuration = $Script:PSData.Export."$DataTemplateName"

                $CsvObjectParameters = [System.Collections.ArrayList]::New()

                foreach ($Enumerator in $Configuration.OutputObject.Property.GetEnumerator()) {
                    $Hashtable = @{
                        N = $Enumerator.Name
                        E = [scriptblock]::Create($Enumerator.Expression.ToString().SubString(1, $Enumerator.Expression.ToString().Length - 2))
                    }

                    [void]$CsvObjectParameters.Add($Hashtable)
                }

                $SqlDataAdapterCommandSet = $Configuration.SqlDataAdapterCommandSet
            }

            if ($PSCmdlet.ParameterSetName -in $ServerInstanceParameterSets) {
                $SqlServerInstanceParameters = @{
                    'ServerInstance' = $ServerInstance
                    'DatabaseName' = 'master'
                    'Verbose' = [bool]($PSBoundParameters['Verbose'])
                }

                $SqlConnection = Connect-SqlServerInstance @SqlServerInstanceParameters
            }
        }
        catch {
            if ($PSCmdlet.ParameterSetName -in $ServerInstanceParameterSets) {
                if (Test-Path -Path variable:\SqlConnection) {
                    $SqlConnection.Dispose()
                }
            }

            throw $_
        }
    }

    process {
        try {
            $SqlClientDataSetParameters = @{
                'SqlConnection' = $SqlConnection
                'DataSetName' = 'SqlDataTools'
                'OutputAs' = 'DataTable'
            }

            if ($PSCmdlet.ParameterSetName -in $TemplateParameterSets) {
                $SqlClientDataSetParameters.Add('SqlCommand', $SqlDataAdapterCommandSet.Select.CommandText)
            } else {
                $SqlClientDataSetParameters.Add('SqlCommand', $TableName)
                $SqlClientDataSetParameters.Add('CommandType', [System.Data.CommandType]::TableDirect)
            }

            $DataTable = Get-SqlClientDataSet @SqlClientDataSetParameters

            $ExportCsvParameters = @{
                'Path' = $Path
                'NoTypeInformation' = $true
            }

            if ($PSBoundParameters.ContainsKey('Encoding')) {
                $ExportCsvParameters.Add('Encoding', [string]$Encoding)
            }

            if ($PSBoundParameters.ContainsKey('Delimiter')) {
                $ExportCsvParameters.Add('Delimiter', $Delimiter)
            }

            if ($Force) {
                $ExportCsvParameters.Add('Force', $true)
            } else {
                $ExportCsvParameters.Add('NoClobber', $true)
            }

            if ($PSCmdlet.ShouldProcess($DatabaseName, "Export $DataTemplateName")) {
                if ($PSCmdlet.ParameterSetName -in $TemplateParameterSets) {
                    $DataTable | Select-Object $CsvObjectParameters | Export-Csv @ExportCsvParameters
                } else {
                    $DataTable | Export-Csv @ExportCsvParameters
                }
            }
        }
        catch {
            throw $_
        }
        finally {
            if ($PSCmdlet.ParameterSetName -in $ServerInstanceParameterSets) {
                if (Test-Path -Path Variable:SqlConnection) {
                    Disconnect-SqlServerInstance -SqlConnection $SqlConnection
                }
            }
        }
    }

    end {
    }
}

function Get-SqlDataTemplate {
    <#
    .EXTERNALHELP
    SqlDataTools-Help.xml
    #>


    [System.Diagnostics.DebuggerStepThrough()]

    [CmdletBinding(
        PositionalBinding = $true,
        SupportsShouldProcess = $false,
        ConfirmImpact = 'Low'
    )]

    [OutputType([SqlDataTools.SqlDataTemplate])]

    param (
        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            Position = 0
        )]
        [Mode]$Mode,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            Position = 1
        )]
        [ArgumentCompleter({ [ArgumentCompleterResult]::GetArgumentCompleterResult($Args) })]
        [string]$DataTemplateName

    )

    begin {
        try {
            if ($null -eq $Script:PSData) {
                throw [System.Management.Automation.ErrorRecord]::New(
                    [Exception]::New('Module not initialized.'),
                    '1',
                    [System.Management.Automation.ErrorCategory]::ResourceUnavailable,
                    $null
                )
            }

            $Configuration = $Script:PSData
        }
        catch {
            throw
        }
    }

    process {
        $ModeNames = [System.Enum]::GetNames([Mode]) | Sort-Object

        foreach ($ModeName in $ModeNames) {
            if ($PSBoundParameters.ContainsKey('Mode')) {
                if ($ModeName -ne $Mode) {
                    Continue
                }
            }

            $TemplateKeys = $Configuration[$ModeName].Keys | Sort-Object

            foreach ($TemplateKey in $TemplateKeys) {
                if ($PSBoundParameters.ContainsKey('DataTemplateName')) {
                    if ($DataTemplateName -ne $TemplateKey) {
                        Continue
                    }
                }

                $SqlDataTemplate = [SqlDataTools.SqlDataTemplate]::New()

                $SqlDataTemplate.Mode = $ModeName
                $SqlDataTemplate.Name = $TemplateKey
                $SqlDataTemplate.Description = $Configuration[$ModeName][$TemplateKey].Description

                $SqlDataTemplate
            }
        }
    }

    end {
    }
}

function Import-SqlData {
    <#
    .EXTERNALHELP
    SqlDataTools-Help.xml
    #>


    [System.Diagnostics.DebuggerStepThrough()]

    [CmdletBinding(
        PositionalBinding = $false,
        SupportsShouldProcess = $true,
        ConfirmImpact = 'Medium',
        DefaultParameterSetName = 'ServerInstance_DataTemplate'
    )]

    [OutputType([SqlClient.DataSetResult])]

    param (
        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'ServerInstance_DataTemplate'
        )]
        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'SqlConnection_DataTemplate'
        )]
        [ValidateSet([ImportConfigurationTemplates])]
        [string]$DataTemplateName,

        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [ValidatePathExists('Leaf')]
        [System.IO.FileInfo][TransformPath()]$Path,

        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'ServerInstance_DataTemplate'
        )]
        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'ServerInstance_Table'
        )]
        [ValidateLength(1, 128)]
        [Alias('SqlServer')]
        [string]$ServerInstance,

        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'ServerInstance_DataTemplate'
        )]
        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'ServerInstance_Table'
        )]
        [ValidateLength(1, 128)]
        [Alias('Database')]
        [string]$DatabaseName,

        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'SqlConnection_DataTemplate'
        )]
        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'SqlConnection_Table'
        )]
        [Microsoft.Data.SqlClient.SqlConnection]$SqlConnection,

        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'ServerInstance_Table'
        )]
        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'SqlConnection_Table'
        )]
        [ValidateLength(1, 128)]
        [string]$TableName,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [Encoding]$Encoding,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [char]$Delimiter,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [switch]$IgnoreQuotes,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'ServerInstance_DataTemplate'
        )]
        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'SqlConnection_DataTemplate'
        )]
        [switch]$SkipValidation,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'ServerInstance_Table'
        )]
        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'SqlConnection_Table'
        )]
        [int]$BatchSize = 1000
    )

    begin {
        $ServerInstanceParameterSets = @('ServerInstance_Table', 'ServerInstance_DataTemplate')

        try {
            if ($PSBoundParameters.ContainsKey('DataTemplateName')) {
                $Configuration = $Script:PSData.Import."$DataTemplateName"

                $DataSetTableName = $Configuration.DataTable.TableName

                $PropertyArray = [System.Collections.ArrayList]::New()

                foreach ($Enumerator in $Configuration.InputObject.Property.GetEnumerator()) {
                    $Hashtable = @{
                        N = $Enumerator.Name
                        E = [scriptblock]::Create($Enumerator.Expression.ToString().SubString(1, $Enumerator.Expression.ToString().Length - 2))
                    }

                    [void]$PropertyArray.Add($Hashtable)
                }

                $CsvObjectParameters = @{
                    'Property' = $PropertyArray
                    'Unique' = $Configuration.InputObject.Unique
                }
            } else {
                $DataSetTableName = [System.IO.Path]::GetFileNameWithoutExtension($Path.Name)

                $CsvObjectParameters = @{
                    'Unique' = $false
                }
            }

            if ($PSCmdlet.ParameterSetName -in $ServerInstanceParameterSets) {
                $SqlServerInstanceParameters = @{
                    'ServerInstance' = $ServerInstance
                    'DatabaseName' = 'master'
                    'Verbose' = [bool]($PSBoundParameters['Verbose'])
                }

                $SqlConnection = Connect-SqlServerInstance @SqlServerInstanceParameters
            }

            $ProgressParameters = @{
                'Id' = 0
                'Activity' = 'Import SQL Data.'
                'Status' = [string]::Format('Step {0} of {1}', 0, 3)
                'CurrentOperation' = ''
                'PercentComplete' = 0
            }

            $ChildProgressParameters = @{
                'Id' = 1
                'ParentId' = 0
                'Activity' = 'Reading delimited file.'
                'Status' = [string]::Format('Line {0} of {1}', 0, 1)
                'CurrentOperation' = ''
                'PercentComplete' = 0
            }
        }
        catch {
            if ($PSCmdlet.ParameterSetName -in $ServerInstanceParameterSets) {
                if (Test-Path -Path variable:\SqlConnection) {
                    $SqlConnection.Dispose()
                }
            }

            throw $_
        }
    }

    process {
        try {
            #Region Create Dataset
            $ProgressParameters.Activity = 'Import SQL Data.'
            $ProgressParameters.Status = [string]::Format('Step {0} of {1}', 1, 2)
            $ProgressParameters.CurrentOperation = 'Create dataset.'
            $ProgressParameters.PercentComplete = 0 / 2 * 100

            Write-Verbose $ProgressParameters.CurrentOperation
            Write-Progress @ProgressParameters

            $DataSet = [System.Data.DataSet]::New('SqlDataTools')

            $DataTable = [System.Data.DataTable]::New($DataSetTableName)

            if ($PSBoundParameters.ContainsKey('DataTemplateName')) {
                $TableName = $Configuration.DataTable.TableName

                foreach ($Column in $Configuration.DataTable.Columns) {
                    $DataColumn = [System.Data.DataColumn]::New($Column.Name, [System.Type]::GetType($Column.DataType))

                    $DataColumn.AllowDBNull = $Column.AllowDBNull

                    if ($Column.Unique -eq 'True') {
                        $DataColumn.Unique = $true
                    }

                    if ([System.Type]::GetType($Column.DataType) -is [System.String]) {
                        $DataColumn.MaxLength = $Column.MaxLength
                    }

                    [void]$DataTable.Columns.Add($DataColumn)
                }

                $KeyColumns = [System.Collections.Generic.List[System.Data.DataColumn]]::New()

                foreach ($PrimaryKey in $Configuration.DataTable.PrimaryKeys) {
                    $KeyColumns.Add($DataTable.Columns[$PrimaryKey])
                }

                try {
                    $DataTable.PrimaryKey = $KeyColumns
                }
                catch {
                    $ErrorMessage = [string]::Format("These columns do not have unique values in table {0}: {1}", $DataSetTableName, [string]::Join(', ', $KeyColumns.ColumnName))

                    throw [System.Management.Automation.ErrorRecord]::New(
                        [Exception]::New($ErrorMessage),
                        '1',
                        [System.Management.Automation.ErrorCategory]::InvalidData,
                        $DataSetTableName
                    )
                }
            }

            $DataSet.Tables.Add($DataTable)
            #Endregion

            #Region Setup Bulk Copy Base Parameters
            $SqlClientBulkCopyBaseParameters = @{
                'SqlConnection' = $SqlConnection
                'TableName' = $TableName
                'QueryTimeout' = 0
                'Verbose' = [bool]($PSBoundParameters['Verbose'])
            }

            if ($PSBoundParameters.ContainsKey('BatchSize')) {
                $SqlClientBulkCopyBaseParameters.Add('BatchSize', $BatchSize)
            } elseif ($Configuration.keys -contains 'BatchSize') {
                $SqlClientBulkCopyBaseParameters.Add('BatchSize', $Configuration.BatchSize)
            }

            if ($Configuration.keys -contains 'EnableStreaming') {
                $SqlClientBulkCopyBaseParameters.Add('EnableStreaming', $Configuration.EnableStreaming)
            }

            if ($Configuration.keys -contains 'NotifyAfter') {
                $SqlClientBulkCopyBaseParameters.Add('NotifyAfter', $Configuration.NotifyAfter)
            }

            if ($Configuration.keys -contains 'SqlBulkCopyOptions') {
                $SqlClientBulkCopyBaseParameters.Add('SqlBulkCopyOptions', $Configuration.SqlBulkCopyOptions)
            }

            $ColumnMappingCollection = [System.Collections.Generic.List[Microsoft.Data.SqlClient.SqlBulkCopyColumnMapping]]::New()

            if ($Configuration.keys -contains 'ColumnMappings') {
                foreach ($ColumnMapping in $Configuration.ColumnMappings) {
                    $SqlClientBulkCopyColumnMapping = [Microsoft.Data.SqlClient.SqlBulkCopyColumnMapping]::New($ColumnMapping.SourceColumn, $ColumnMapping.DestinationColumn)

                    $ColumnMappingCollection.Add($SqlClientBulkCopyColumnMapping)
                }

                if ($ColumnMappingCollection.Count -gt 0) {
                    $SqlClientBulkCopyBaseParameters.Add('ColumnMappingCollection', $ColumnMappingCollection)
                }
            }
            #Endregion

            #Region Add CSV data to dataset
            $ProgressParameters.Activity = 'Import SQL Data.'
            $ProgressParameters.Status = [string]::Format('Step {0} of {1}', 2, 2)
            $ProgressParameters.CurrentOperation = 'Adding CSV data to dataset.'
            $ProgressParameters.PercentComplete = 1 / 2 * 100

            Write-Verbose $ProgressParameters.CurrentOperation
            Write-Progress @ProgressParameters

            if ($PSBoundParameters.ContainsKey('Delimiter')) {
                $TextFieldParser = [TextFieldParser]::New($Path, $Encoding, $Delimiter, $IgnoreQuotes)
            } else {
                $TextFieldParser = [TextFieldParser]::New($Path, $Encoding, ',', $IgnoreQuotes)
            }

            if (-not $PSBoundParameters.ContainsKey('DataTemplateName')) {
                foreach ($Column in $TextFieldParser.Headers) {
                    [void]$DataTable.Columns.Add($Column.Trim())
                }
            }

            try {
                $LinesPerGB = [Math]::Ceiling(1gb / ($Path.length / $TextFieldParser.TotalLines))

                $ChildProgressParameters.Activity = [string]::Format('Line Number: {0}', $TextFieldParser.CurrentLine)
                $ChildProgressParameters.Status = [string]::Format('Line {0} of {1}', $TextFieldParser.CurrentLine, $TextFieldParser.TotalLines)
                $ChildProgressParameters.CurrentOperation = [string]::Format('Reading line: {0}', $TextFieldParser.CurrentLine)
                $ChildProgressParameters.PercentComplete = $TextFieldParser.CurrentLine / $TextFieldParser.TotalLines * 100

                Write-Verbose $ChildProgressParameters.CurrentOperation
                Write-Progress @ChildProgressParameters

                while (-not $TextFieldParser.TextFieldParser.EndOfData) {
                    $Row = $TextFieldParser.ReadLine()

                    if ($TextFieldParser.TotalLines -lt 10000 -or $TextFieldParser.CurrentLine % [System.Math]::Floor($TextFieldParser.TotalLines / 10000) -eq 0) {
                        $ChildProgressParameters.Activity = [string]::Format('Line Number: {0}', $TextFieldParser.CurrentLine)
                        $ChildProgressParameters.Status = [string]::Format('Line {0} of {1}', $TextFieldParser.CurrentLine, $TextFieldParser.TotalLines)
                        $ChildProgressParameters.CurrentOperation = [string]::Format('Reading line: {0}', $TextFieldParser.CurrentLine)
                        $ChildProgressParameters.PercentComplete = $TextFieldParser.CurrentLine / $TextFieldParser.TotalLines * 100

                        Write-Verbose $ChildProgressParameters.CurrentOperation
                        Write-Progress @ChildProgressParameters
                    }

                    if ($SkipValidation -or $TextFieldParser.IsValidRow($Row, $Configuration.InputColumns)) {
                        $TransformedRow = $Row | Select-Object @CsvObjectParameters

                        $DataRow = $DataSet.Tables[$DataSetTableName].NewRow()

                        foreach ($Property in $TransformedRow.PsObject.Properties) {
                            if ($null -eq $Property.Value) {
                                $DataRow.Item($Property.Name) = [DBNull]::Value
                            } else {
                                $DataRow.Item($Property.Name) = $Property.Value
                            }
                        }

                        [void]$DataSet.Tables[$DataSetTableName].Rows.Add($DataRow)

                        if ($TextFieldParser.CurrentLine % $LinesPerGB -eq 0 -or $TextFieldParser.TextFieldParser.EndOfData) {
                            $SqlClientBulkCopyParameters = $SqlClientBulkCopyBaseParameters.PSObject.Copy()

                            $SqlClientBulkCopyParameters.Add('DataTable', $DataSet.Tables[$DataSetTableName])

                            if ($PSCmdlet.ShouldProcess($DatabaseName, "Bulk copy")) {
                                Invoke-SqlClientBulkCopy @SqlClientBulkCopyParameters
                            }

                            $DataSet.Tables[$DataSetTableName].Clear()
                        }
                    }
                }
            }
            catch {
                throw $_
            }
            finally {
                Write-Progress -Id 1 -Activity 'Reading delimited file.' -Completed
            }
            #Endregion
        }
        catch {
            throw $_
        }
        finally{
            Write-Progress -Id 1 -Activity 'Copy SQL Data.' -Completed
            Write-Progress -Id 0 -Activity 'Copy SQL Data.' -Completed

            if (Test-Path -Path Variable:TextFieldParser) {
                $TextFieldParser.Close()
            }

            if (Test-Path -Path Variable:SqlDataAdapter) {
                $SqlDataAdapter.Dispose()
            }

            if ($PSCmdlet.ParameterSetName -in $ServerInstanceParameterSets) {
                if (Test-Path -Path Variable:SqlConnection) {
                    Disconnect-SqlServerInstance -SqlConnection $SqlConnection
                }
            }
        }
    }

    end {
    }
}

function Merge-SqlData {
    <#
    .EXTERNALHELP
    SqlDataTools-Help.xml
    #>


    [System.Diagnostics.DebuggerStepThrough()]

    [CmdletBinding(
        PositionalBinding = $false,
        SupportsShouldProcess = $true,
        ConfirmImpact = 'High',
        DefaultParameterSetName = 'ServerInstance'
    )]

    [OutputType([SqlClient.DataSetResult])]

    param (
        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [ValidateSet([MergeConfigurationTemplates])]
        [string]$DataTemplateName,

        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [ValidatePathExists('Leaf')]
        [System.IO.FileInfo][TransformPath()]$Path,

        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'ServerInstance'
        )]
        [ValidateLength(1, 128)]
        [Alias('SqlServer')]
        [string]$ServerInstance,

        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'ServerInstance'
        )]
        [ValidateLength(1, 128)]
        [Alias('Database')]
        [string]$DatabaseName,

        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'SqlConnection'
        )]
        [Microsoft.Data.SqlClient.SqlConnection]$SqlConnection,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [Encoding]$Encoding,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [char]$Delimiter,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [switch]$IgnoreQuotes,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [switch]$SkipValidation,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [int]$BatchSize = 1000
    )

    begin {
        $ServerInstanceParameterSets = @('ServerInstance')

        try {
            $Configuration = $Script:PSData.Merge."$DataTemplateName"

            $DataSetTableName = $Configuration.DataTable.TableName

            $PropertyArray = [System.Collections.ArrayList]::New()

            foreach ($Enumerator in $Configuration.InputObject.Property.GetEnumerator()) {
                $Hashtable = @{
                    N = $Enumerator.Name
                    E = [scriptblock]::Create($Enumerator.Expression.ToString().SubString(1, $Enumerator.Expression.ToString().Length - 2))
                }

                [void]$PropertyArray.Add($Hashtable)
            }

            $CsvObjectParameters = @{
                'Property' = $PropertyArray
                'Unique' = $Configuration.InputObject.Unique
            }

            $SqlDataAdapterCommandSet = $Configuration.SqlDataAdapterCommandSet

            if ($SqlDataAdapterCommandSet.Keys -NotContains 'Insert' -and $SqlDataAdapterCommandSet.Keys -NotContains 'Update') {
                throw [System.Management.Automation.ErrorRecord]::New(
                    [Exception]::New("Configuration $DataTemplateName not implemented for import."),
                    '1',
                    [System.Management.Automation.ErrorCategory]::NotImplemented,
                    $DataTemplateName
                )
            }

            if ($PSCmdlet.ParameterSetName -in $ServerInstanceParameterSets) {
                $SqlServerInstanceParameters = @{
                    'ServerInstance' = $ServerInstance
                    'DatabaseName' = 'master'
                    'Verbose' = [bool]($PSBoundParameters['Verbose'])
                }

                $SqlConnection = Connect-SqlServerInstance @SqlServerInstanceParameters
            }

            $ProgressParameters = @{
                'Id' = 0
                'Activity' = 'Import SQL Data.'
                'Status' = [string]::Format('Step {0} of {1}', 0, 3)
                'CurrentOperation' = ''
                'PercentComplete' = 0
            }

            $ChildProgressParameters = @{
                'Id' = 1
                'ParentId' = 0
                'Activity' = 'Reading delimited file.'
                'Status' = [string]::Format('Line {0} of {1}', 0, 1)
                'CurrentOperation' = ''
                'PercentComplete' = 0
            }
        }
        catch {
            if ($PSCmdlet.ParameterSetName -in $ServerInstanceParameterSets) {
                if (Test-Path -Path variable:\SqlConnection) {
                    $SqlConnection.Dispose()
                }
            }

            throw $_
        }
    }

    process {
        try {
            #Region Reading Destination Dataset
            $ProgressParameters.Activity = 'Import SQL Data.'
            $ProgressParameters.Status = [string]::Format('Step {0} of {1}', 1, 3)
            $ProgressParameters.CurrentOperation = 'Reading destination dataset.'
            $ProgressParameters.PercentComplete = 0 / 3 * 100

            Write-Verbose $ProgressParameters.CurrentOperation
            Write-Progress @ProgressParameters

            $SqlClientDataSetParameters = @{
                'SqlConnection' = $SqlConnection
                'SqlCommand' = $SqlDataAdapterCommandSet.Select.CommandText
                'DataSetName' = 'SqlDataTools'
                'DataTableName' = $DataSetTableName
                'OutputAs' = 'DataSet'
            }

            $DataSet = Get-SqlClientDataSet @SqlClientDataSetParameters

            $KeyColumns = [System.Collections.Generic.List[System.Data.DataColumn]]::New()

            foreach ($PrimaryKey in $Configuration.DataTable.PrimaryKeys) {
                $KeyColumns.Add($DataSet.Tables[$DataSetTableName].Columns[$PrimaryKey])
            }

            try {
                $DataSet.Tables[$DataSetTableName].PrimaryKey = $KeyColumns
            }
            catch {
                $ErrorMessage = [string]::Format("These columns do not have unique values in table {0}: {1}", $DataSetTableName, [string]::Join(', ', $KeyColumns.ColumnName))

                throw [System.Management.Automation.ErrorRecord]::New(
                    [Exception]::New($ErrorMessage),
                    '1',
                    [System.Management.Automation.ErrorCategory]::InvalidData,
                    $DataSetTableName
                )
            }
            #Endregion

            #Region Add CSV data to dataset
            $ProgressParameters.Activity = 'Import SQL Data.'
            $ProgressParameters.Status = [string]::Format('Step {0} of {1}', 2, 3)
            $ProgressParameters.CurrentOperation = 'Adding CSV data to dataset.'
            $ProgressParameters.PercentComplete = 1 / 3 * 100

            Write-Verbose $ProgressParameters.CurrentOperation
            Write-Progress @ProgressParameters

            if ($PSBoundParameters.ContainsKey('Delimiter')) {
                $TextFieldParser = [TextFieldParser]::New($Path, $Encoding, $Delimiter, $IgnoreQuotes, $Configuration.InputColumns.Name)
            } else {
                $TextFieldParser = [TextFieldParser]::New($Path, $Encoding, ',', $IgnoreQuotes, $Configuration.InputColumns.Name)
            }

            $Results = [SqlClient.DataSetResult]::New()

            if ($SqlDataAdapterCommandSet.Keys -Contains 'Delete') {
                $UnmodifiedList = [System.Collections.Generic.List[psobject]]::New()
            }

            try {
                $ChildProgressParameters.Activity = [string]::Format('Line Number: {0}', $TextFieldParser.CurrentLine)
                $ChildProgressParameters.Status = [string]::Format('Line {0} of {1}', $TextFieldParser.CurrentLine, $TextFieldParser.TotalLines)
                $ChildProgressParameters.CurrentOperation = [string]::Format('Reading line: {0}', $TextFieldParser.CurrentLine)
                $ChildProgressParameters.PercentComplete = $TextFieldParser.CurrentLine / $TextFieldParser.TotalLines * 100

                Write-Verbose $ChildProgressParameters.CurrentOperation
                Write-Progress @ChildProgressParameters

                while ($Row = $TextFieldParser.ReadLine()) {
                    if ($TextFieldParser.TotalLines -lt 10000 -or $TextFieldParser.CurrentLine % [System.Math]::Floor($TextFieldParser.TotalLines / 10000) -eq 0) {
                        $ChildProgressParameters.Activity = [string]::Format('Line Number: {0}', $TextFieldParser.CurrentLine)
                        $ChildProgressParameters.Status = [string]::Format('Line {0} of {1}', $TextFieldParser.CurrentLine, $TextFieldParser.TotalLines)
                        $ChildProgressParameters.CurrentOperation = [string]::Format('Reading line: {0}', $TextFieldParser.CurrentLine)
                        $ChildProgressParameters.PercentComplete = $TextFieldParser.CurrentLine / $TextFieldParser.TotalLines * 100

                        Write-Verbose $ChildProgressParameters.CurrentOperation
                        Write-Progress @ChildProgressParameters
                    }

                    if ($SkipValidation -or $TextFieldParser.IsValidRow($Row, $Configuration.InputColumns)) {
                        $TransformedRow = $Row | Select-Object @CsvObjectParameters

                        $PrimaryKeyValuesList = [System.Collections.Generic.List[String]]::New()

                        foreach ($PrimaryKey in $Configuration.DataTable.PrimaryKeys) {
                            $PrimaryKeyValuesList.Add($TransformedRow.$PrimaryKey)
                        }

                        $PrimaryKeyValues = $($PrimaryKeyValuesList -as [string[]])

                        if ($DataSet.Tables[$DataSetTableName].Rows.Contains($PrimaryKeyValues)) {
                            $DataRow = $DataSet.Tables[$DataSetTableName].Rows.Find($PrimaryKeyValues)

                            foreach ($Property in $TransformedRow.PsObject.Properties) {
                                if ($Property.Name -NotIn $Configuration.DataTable.PrimaryKeys) {
                                    if ($null -eq $Property.Value) {
                                        if ($DataRow.Item($Property.Name) -ne [DBNull]::Value) {
                                            $DataRow.Item($Property.Name) = [DBNull]::Value
                                        }
                                    } else {
                                        if ($DataRow.Item($Property.Name) -ne $Property.Value) {
                                            $DataRow.Item($Property.Name) = $Property.Value
                                        }
                                    }
                                }
                            }

                            if ($DataRow.RowState -eq 'Unchanged') {
                                if ($SqlDataAdapterCommandSet.Keys -Contains 'Delete') {
                                    $UnmodifiedList.Add($($DataRow | Select-Object -Property $Configuration.DataTable.PrimaryKeys))
                                }

                                $Results.UnchangedRows++
                            } else {
                                $Results.UpdatedRows++
                            }
                        } else {
                            $DataRow = $DataSet.Tables[$DataSetTableName].NewRow()

                            foreach ($Property in $TransformedRow.PsObject.Properties) {
                                if ($null -eq $Property.Value) {
                                    $DataRow.Item($Property.Name) = [DBNull]::Value
                                } else {
                                    $DataRow.Item($Property.Name) = $Property.Value
                                }
                            }

                            [void]$DataSet.Tables[$DataSetTableName].Rows.Add($DataRow)

                            $Results.InsertedRows++
                        }
                    }
                }
            }
            catch {
                throw $_
            }
            finally {
                Write-Progress -Id 1 -Activity 'Reading delimited file.' -Completed
            }
            #Endregion

            #Region Delete Rows from dataset
            if ($SqlDataAdapterCommandSet.Keys -Contains 'Delete') {
                $UnchangedRows = $DataSet.Tables[$DataSetTableName].Rows.where({$_.RowState -eq 'Unchanged'})

                if ($UnchangedRows.Count -gt $UnmodifiedList.Count) {
                    $Difference = Compare-Object -ReferenceObject $($UnchangedRows | Select-Object $Configuration.DataTable.PrimaryKeys) -DifferenceObject $UnmodifiedList

                    foreach ($Item in $Difference) {
                        if ($Item.SideIndicator -eq '<=') {
                            $PrimaryKeyValuesList = [System.Collections.Generic.List[String]]::New()

                            foreach ($PrimaryKey in $Configuration.DataTable.PrimaryKeys) {
                                $PrimaryKeyValuesList.Add($Item.InputObject.$PrimaryKey)
                            }

                            $DataSet.Tables[$DataSetTableName].Rows.Find($($PrimaryKeyValuesList -as [string[]])).Delete()

                            $Results.UnchangedRows--
                            $Results.DeletedRows++
                        }
                    }
                }

                Remove-Variable -Name UnmodifiedList
            }
            #EndRegion

            #Region Set Sql Data Adapter
            $ProgressParameters.Activity = 'Import SQL Data.'
            $ProgressParameters.Status = [string]::Format('Step {0} of {1}', 3, 3)
            $ProgressParameters.CurrentOperation = 'Adding CSV data to dataset.'
            $ProgressParameters.PercentComplete = 2 / 3 * 100

            Write-Verbose $ProgressParameters.CurrentOperation
            Write-Progress @ProgressParameters

            $TotalChangedRows = $Results.InsertedRows + $Results.UpdatedRows + $Results.DeletedRows

            if ($TotalChangedRows -gt 0) {
                $SqlDataAdapter = [Microsoft.Data.SqlClient.SqlDataAdapter]::New()

                $SqlDataAdapter.UpdateBatchSize = $BatchSize

                foreach ($Key in $SqlDataAdapterCommandSet.Keys) {
                    $SqlCommand = $SqlDataAdapterCommandSet.$Key

                    $SqlCommandObject = [Microsoft.Data.SqlClient.SqlCommand]::New($SqlCommand.CommandText, $SqlConnection)

                    if ($Key -ne 'Select') {
                        $SqlCommandObject.UpdatedRowSource = [System.Data.UpdateRowSource]::None
                    }

                    if ($SqlCommand.Keys -contains 'Parameters') {
                        foreach ($Parameter in $SqlCommand.Parameters) {
                            [void]$SqlCommandObject.Parameters.Add($Parameter.ParameterName, [System.Data.SqlDbType]$Parameter.SqlDbType)

                            switch ($Parameter.keys) {
                                {$_ -contains 'Precision'} {
                                    $SqlCommandObject.Parameters[$Parameter.ParameterName].Precision = $Parameter.Precision
                                }
                                {$_ -contains 'Scale'} {
                                    $SqlCommandObject.Parameters[$Parameter.ParameterName].Scale = $Parameter.Scale
                                }
                                {$_ -contains 'Size'} {
                                    $SqlCommandObject.Parameters[$Parameter.ParameterName].Size = $Parameter.Size
                                }
                                {$_ -contains 'SourceColumn'} {
                                    $SqlCommandObject.Parameters[$Parameter.ParameterName].SourceColumn = $Parameter.SourceColumn
                                }
                                {$_ -contains 'SourceVersion'} {
                                    $SqlCommandObject.Parameters[$Parameter.ParameterName].SourceVersion = [System.Data.DataRowVersion]$Parameter.SourceVersion
                                }
                                Default {}
                            }
                        }
                    }

                    switch ($Key) {
                        'Select' {
                            $SqlDataAdapter.SelectCommand = $SqlCommandObject
                        }
                        'Insert' {
                            $SqlDataAdapter.InsertCommand = $SqlCommandObject
                        }
                        'Update' {
                            $SqlDataAdapter.UpdateCommand = $SqlCommandObject
                        }
                        'Delete' {
                            $SqlDataAdapter.DeleteCommand = $SqlCommandObject
                        }
                    }
                }

                if ($PSCmdlet.ShouldProcess($DatabaseName, "Update $DataTemplateName")) {
                    $SqlClientDataSetParameters = @{
                        'DataSet' = $DataSet
                        'DataSetTableName' = $DataSetTableName
                        'SqlDataAdapter' = $SqlDataAdapter
                        'Confirm' = $false
                    }

                    $Results = Save-SqlClientDataSet @SqlClientDataSetParameters
                }
            }
            #Endregion

            $Results
        }
        catch {
            throw $_
        }
        finally{
            Write-Progress -Id 1 -Activity 'Import SQL Data.' -Completed
            Write-Progress -Id 0 -Activity 'Import SQL Data.' -Completed

            if (Test-Path -Path Variable:TextFieldParser) {
                $TextFieldParser.Close()
            }

            if (Test-Path -Path Variable:SqlDataAdapter) {
                $SqlDataAdapter.Dispose()
            }

            if (Test-Path -Path Variable:SqlCommandObject) {
                $SqlCommandObject.Dispose()
            }

            if ($PSCmdlet.ParameterSetName -in $ServerInstanceParameterSets) {
                if (Test-Path -Path Variable:SqlConnection) {
                    Disconnect-SqlServerInstance -SqlConnection $SqlConnection
                }
            }
        }
    }

    end {
    }
}

function Test-SqlData {
    <#
    .EXTERNALHELP
    SqlDataTools-Help.xml
    #>


    [System.Diagnostics.DebuggerStepThrough()]

    [CmdletBinding(
        PositionalBinding = $false,
        SupportsShouldProcess = $false,
        ConfirmImpact = 'Low'
    )]

    [OutputType([System.Boolean])]

    param (
        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            Position = 0
        )]
        [Mode]$Mode,

        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            Position = 1
        )]
        [ArgumentCompleter({ [ArgumentCompleterResult]::GetArgumentCompleterResult($Args) })]
        [string]$DataTemplateName,

        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            Position = 2
        )]
        [ValidatePathExists('Leaf')]
        [System.IO.FileInfo][TransformPath()]$Path,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [Encoding]$Encoding,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [char]$Delimiter,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [switch]$IgnoreQuotes
    )

    begin {
        try {
            if ($Mode -eq 'Export') {
                throw [System.Management.Automation.ErrorRecord]::New(
                    [Exception]::New('Export mode not valid for test.'),
                    '1',
                    [System.Management.Automation.ErrorCategory]::InvalidArgument,
                    $Mode
                )
            }

            $Configuration = $Script:PSData."$Mode"."$DataTemplateName"

            $ProgressParameters = @{
                'Id' = 0
                'Activity' = 'Reading delimited file.'
                'Status' = [string]::Format('Line {0} of {1}', 0, 1)
                'CurrentOperation' = ''
                'PercentComplete' = 0
            }
        }
        catch {
            throw
        }
    }

    process {
        try {
            if ($PSBoundParameters.ContainsKey('Delimiter')) {
                $TextFieldParser = [TextFieldParser]::New($Path, $Encoding, $Delimiter, $IgnoreQuotes, $Configuration.InputColumns.Name)
            } else {
                $TextFieldParser = [TextFieldParser]::New($Path, $Encoding, ',', $IgnoreQuotes, $Configuration.InputColumns.Name)
            }

            $ReturnValue = $true

            $ProgressParameters.Activity = [string]::Format('Line Number: {0}', $TextFieldParser.CurrentLine)
            $ProgressParameters.Status = [string]::Format('Line {0} of {1}', $TextFieldParser.CurrentLine, $TextFieldParser.TotalLines)
            $ProgressParameters.CurrentOperation = [string]::Format('Reading line: {0}', $TextFieldParser.CurrentLine)
            $ProgressParameters.PercentComplete = $TextFieldParser.CurrentLine / $TextFieldParser.TotalLines * 100

            Write-Verbose $ProgressParameters.CurrentOperation
            Write-Progress @ProgressParameters

            while ($Row = $TextFieldParser.ReadLine()) {
                if ($TextFieldParser.TotalLines -lt 10000 -or $TextFieldParser.CurrentLine % [System.Math]::Floor($TextFieldParser.TotalLines / 10000) -eq 0) {
                    $ProgressParameters.Activity = [string]::Format('Line Number: {0}', $TextFieldParser.CurrentLine)
                    $ProgressParameters.Status = [string]::Format('Line {0} of {1}', $TextFieldParser.CurrentLine, $TextFieldParser.TotalLines)
                    $ProgressParameters.CurrentOperation = [string]::Format('Reading line: {0}', $TextFieldParser.CurrentLine)
                    $ProgressParameters.PercentComplete = $TextFieldParser.CurrentLine / $TextFieldParser.TotalLines * 100

                    Write-Verbose $ProgressParameters.CurrentOperation
                    Write-Progress @ProgressParameters
                }

                if (-not $TextFieldParser.IsValidRow($Row, $Configuration.InputColumns)) {
                    $ReturnValue = $false
                }
            }
        }
        catch {
            $ReturnValue = $false

            $PSCmdlet.WriteError($_)
        }
        finally {
            Write-Progress -Id 0 -Activity 'Reading delimited file.' -Completed

            if (Test-Path -Path Variable:TextFieldParser) {
                $TextFieldParser.Close()
            }

            $ReturnValue
        }
    }

    end {
    }
}

function Use-TemplateRepository {
    <#
    .EXTERNALHELP
    SqlDataTools-Help.xml
    #>


    [System.Diagnostics.DebuggerStepThrough()]

    [CmdletBinding(
        PositionalBinding = $false,
        SupportsShouldProcess = $false,
        ConfirmImpact = 'Low'
    )]

    [OutputType([System.Void])]

    param (
        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [ValidatePathExists('Leaf')]
        [System.IO.FileInfo][TransformPath()]$DataTemplatePath,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [switch]$SkipSignatureValidation
    )

    begin {

    }

    process {
        try {
            if (-not $SkipSignatureValidation) {
                $AuthenticodeSignature = Get-AuthenticodeSignature -FilePath $DataTemplatePath

                if ($AuthenticodeSignature.Status -ne 'Valid') {
                    throw [System.Management.Automation.ErrorRecord]::New(
                        [Exception]::New('Invalid signature file on data file.'),
                        '1',
                        [System.Management.Automation.ErrorCategory]::ResourceExists,
                        $DataTemplatePath
                    )
                }
            }

            $PowerShellDataFileParameters = @{
                'LiteralPath' = $DataTemplatePath
            }

            if ($PSVersionTable.PSVersion -ge [version]'7.2') {
                $PowerShellDataFileParameters.Add('SkipLimitCheck', $true)
            }

            # $Script:PSData.Add($(Import-PowerShellDataFile @PowerShellDataFileParameters))
            $Script:PSData = Import-PowerShellDataFile @PowerShellDataFileParameters
        }
        catch {
            throw $_
        }
    }

    end {
    }
}