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 { } } |