HelperFunctions.psm1
|
function Save-ClearTextToEncryptedFile ($Password, $FileName) { #$secureStringPwd = $Password | ConvertTo-SecureString -AsPlainText -Force $secureStringPwd = New-Object PSCredential ("Dummy User", $Password) | Select-Object -ExpandProperty Password $secureStringText = $secureStringPwd | ConvertFrom-SecureString Set-Content $FileName $secureStringText } function Save-SecureStringToEncryptedFile ($FileName, $Prompt) { if ($Prompt -eq $null) {$Prompt = "Enter Password:"} $secureStringPwd = Read-Host -Prompt $Prompt -AsSecureString $secureStringText = $secureStringPwd | ConvertFrom-SecureString Set-Content $FileName $secureStringText } function Get-SecureStringFromEncryptedFile ($FileName) { $pwdTxt = Get-Content $FileName $securePwd = $pwdTxt | ConvertTo-SecureString Write-Output $securePwd } function Get-ClearTextFromEncryptedFile ($FileName) { $pwdTxt = Get-Content $FileName $securePwd = $pwdTxt | ConvertTo-SecureString $BSTR = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($securePwd) $clearText = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto($BSTR) Write-Output $clearText } function ConvertTo-DataTable { <# .SYNOPSIS Convert regular PowerShell objects to a DataTable object. .DESCRIPTION Convert regular PowerShell objects to a DataTable object. .EXAMPLE $myDataTable = $myObject | ConvertTo-DataTable # using the SqlServer PowerShell module to connect to SQL Server and query for and return data # returns data as an array of DataRow objects $drs=Invoke-Sqlcmd -ServerInstance "ServerName" -Database Databasename -Username UserName -Password Password -Query "SELECT * FROM [dbo].[DrawingValidation] where Owner='None' and UpToDate=1 order by stamptime desc" # use this function to Convert the DataRow array to a DataTable $dt=ConvertTo-DataTable $drs # use PWPS_DAB cmdlet to output the DataTable into a spreadsheet New-XLSXWorkbook -InputTables $dt -OutputFileName c:\temp\Output.xlsx #> [CmdletBinding()] param ( # The object to convert to a DataTable [Parameter(ValueFromPipeline = $true)] [PSObject[]] $InputObject, # Override the default type. [Parameter()] [string] $DefaultType = 'System.String' ) begin { # create an empty datatable try { $dataTable = New-Object -TypeName 'System.Data.DataTable' Write-Verbose -Message 'Empty DataTable created' } catch { Write-Warning -Message $_.Exception.Message break } # define a boolean to keep track of the first datarow $first = $true # define array of supported .NET types $types = @( 'System.String', 'System.Boolean', 'System.Byte[]', 'System.Byte', 'System.Char', 'System.DateTime', 'System.Decimal', 'System.Double', 'System.Guid', 'System.Int16', 'System.Int32', 'System.Int64', 'System.Single', 'System.UInt16', 'System.UInt32', 'System.UInt64' ) } process { # iterate through each input object foreach ($object in $InputObject) { try { # create a new datarow $dataRow = $dataTable.NewRow() Write-Verbose -Message 'New DataRow created' # iterate through each object property foreach ($property in $object.PSObject.get_properties()) { # check if we are dealing with the first row or not if ($first) { # handle data types if ($types -contains $property.TypeNameOfValue) { $dataType = $property.TypeNameOfValue Write-Verbose -Message "$($property.Name): Supported datatype <$($dataType)>" } else { $dataType = $DefaultType Write-Verbose -Message "$($property.Name): Unsupported datatype ($($property.TypeNameOfValue)), using default <$($DefaultType)>" } # create a new datacolumn $dataColumn = New-Object 'System.Data.DataColumn' $property.Name, $dataType Write-Verbose -Message 'Created new DataColumn' # add column to DataTable $dataTable.Columns.Add($dataColumn) Write-Verbose -Message 'DataColumn added to DataTable' } # add values to column if ($property.Value -ne $null) { # handle data types if ($types -contains $property.TypeNameOfValue) { $dataType = $property.TypeNameOfValue Write-Verbose -Message "$($property.Name): Supported datatype <$($dataType)>" } # if array or collection, add as XML if (($property.Value.GetType().IsArray) -or ($property.TypeNameOfValue -like '*collection*')) { $dataRow.Item($property.Name) = $property.Value | ConvertTo-Xml -As 'String' -NoTypeInformation -Depth 1 Write-Verbose -Message 'Value added to row as XML' } else{ $dataRow.Item($property.Name) = $property.Value -as $dataType Write-Verbose -Message "Value ($($property.Value)) added to row as $($dataType)" } } } # add DataRow to DataTable $dataTable.Rows.Add($dataRow) Write-Verbose -Message 'DataRow added to DataTable' } catch { Write-Warning -Message $_.Exception.Message } $first = $false } } end { #"properties" that aren't really columns when this is passed an array of or DataRows if ($dataTable.Columns.Contains("RowError")) { $dataTable.Columns.Remove("RowError")} if ($dataTable.Columns.Contains("RowState")) { $dataTable.Columns.Remove("RowState")} if ($dataTable.Columns.Contains("Table")) { $dataTable.Columns.Remove("Table")} if ($dataTable.Columns.Contains("ItemArray")) { $dataTable.Columns.Remove("ItemArray")} if ($dataTable.Columns.Contains("HasErrors")) { $dataTable.Columns.Remove("HasErrors")} Write-Output (,($dataTable)) } } |