PSDataSet.psm1
|
#Region '.\prefix.ps1' -1 # The content of this file will be prepended to the top of the psm1 module file. This is useful for custom module setup is needed on import. #EndRegion '.\prefix.ps1' 2 #Region '.\Private\Assert-FolderExist.ps1' -1 function Assert-FolderExist { <# .SYNOPSIS Verify and create folder .DESCRIPTION Verifies that a folder path exists, if not it will create it .PARAMETER Path Defines the path to be validated .EXAMPLE 'C:\Temp' | Assert-FolderExist This will verify that the path exists and if it does not the folder will be created #> [CmdletBinding()] param( [Parameter(Mandatory, ValueFromPipeline)] [string] $Path ) process { $exists = Test-Path -Path $Path -PathType Container if (!$exists) { $null = New-Item -Path $Path -ItemType Directory } } } #EndRegion '.\Private\Assert-FolderExist.ps1' 31 #Region '.\Private\Invoke-GarbageCollect.ps1' -1 function Invoke-GarbageCollect { <# .SYNOPSIS Calls system.gc collect method. Purpose is mainly for readability. .DESCRIPTION Calls system.gc collect method. Purpose is mainly for readability. .EXAMPLE Invoke-GarbageCollect #> [system.gc]::Collect() } #EndRegion '.\Private\Invoke-GarbageCollect.ps1' 13 #Region '.\Private\pslog.ps1' -1 function pslog { <# .SYNOPSIS This is simple logging function that automatically log to file. Logging to console is maintained. .DESCRIPTION This is simple logging function that automatically log to file. Logging to console is maintained. .PARAMETER Severity Defines the type of log, valid vales are, Success,Info,Warning,Error,Verbose,Debug .PARAMETER Message Defines the message for the log entry .PARAMETER Source Defines a source, this is useful to separate log entries in categories for different stages of a process or for each function, defaults to default .PARAMETER Throw Specifies that when using severity error pslog will throw. This is useful in catch statements so that the terminating error is propagated upwards in the stack. .PARAMETER LogDirectoryOverride Defines a hardcoded log directory to write the log file to. This defaults to %appdatalocal%\<modulename\logs. .PARAMETER DoNotLogToConsole Specifies that logs should only be written to the log file and not to the console. .EXAMPLE pslog Verbose 'Successfully wrote to logfile' Explanation of the function or its result. You can include multiple examples with additional .EXAMPLE lines #> [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSAvoidUsingWriteHost', '', Justification = 'Sole purpose of function is logging, including console')] [cmdletbinding()] param( [parameter(Position = 0)] [ValidateSet('Success', 'Info', 'Warning', 'Error', 'Verbose', 'Debug')] [Alias('Type')] [string] $Severity, [parameter(Mandatory, Position = 1)] [string] $Message, [parameter(position = 2)] [string] $source = 'default', [parameter(Position = 3)] [switch] $Throw, [parameter(Position = 4)] [string] $LogDirectoryOverride, [parameter(Position = 5)] [switch] $DoNotLogToConsole ) begin { if (-not $LogDirectoryOverride) { $localappdatapath = [Environment]::GetFolderPath('localapplicationdata') # ie C:\Users\<username>\AppData\Local $modulename = $MyInvocation.MyCommand.Module $logdir = "$localappdatapath\$modulename\logs" } else { $logdir = $LogDirectoryOverride } $logdir | Assert-FolderExist -Verbose:$VerbosePreference $timestamp = (Get-Date) $logfilename = ('{0}.log' -f $timestamp.ToString('yyy-MM-dd')) $timestampstring = $timestamp.ToString('yyyy-MM-ddThh:mm:ss.ffffzzz') } process { switch ($Severity) { 'Success' { "$timestampstring`t$psitem`t$source`t$message" | Add-Content -Path "$logdir\$logfilename" -Encoding utf8 -WhatIf:$false if (-not $DoNotLogToConsole) { Write-Host -Object "SUCCESS: $timestampstring`t$source`t$message" -ForegroundColor Green } } 'Info' { "$timestampstring`t$psitem`t$source`t$message" | Add-Content -Path "$logdir\$logfilename" -Encoding utf8 -WhatIf:$false if (-not $DoNotLogToConsole) { Write-Information -MessageData "$timestampstring`t$source`t$message" } } 'Warning' { "$timestampstring`t$psitem`t$source`t$message" | Add-Content -Path "$logdir\$logfilename" -Encoding utf8 -WhatIf:$false if (-not $DoNotLogToConsole) { Write-Warning -Message "$timestampstring`t$source`t$message" } } 'Error' { "$timestampstring`t$psitem`t$source`t$message" | Add-Content -Path "$logdir\$logfilename" -Encoding utf8 -WhatIf:$false if (-not $DoNotLogToConsole) { Write-Error -Message "$timestampstring`t$source`t$message" } if ($throw) { throw } } 'Verbose' { if ($VerbosePreference -ne 'SilentlyContinue') { "$timestampstring`t$psitem`t$source`t$message" | Add-Content -Path "$logdir\$logfilename" -Encoding utf8 -WhatIf:$false } if (-not $DoNotLogToConsole) { Write-Verbose -Message "$timestampstring`t$source`t$message" } } 'Debug' { if ($DebugPreference -ne 'SilentlyContinue') { "$timestampstring`t$psitem`t$source`t$message" | Add-Content -Path "$logdir\$logfilename" -Encoding utf8 -WhatIf:$false } if (-not $DoNotLogToConsole) { Write-Debug -Message "$timestampstring`t$source`t$message" } } } } } #EndRegion '.\Private\pslog.ps1' 137 #Region '.\Private\Write-PSProgress.ps1' -1 function Write-PSProgress { <# .SYNOPSIS Wrapper for PSProgress .DESCRIPTION This function will automatically calculate items/sec, eta, time remaining as well as set the update frequency in case the there are a lot of items processing fast. .PARAMETER Activity Defines the activity name for the progressbar .PARAMETER Id Defines a unique ID for this progressbar, this is used when nesting progressbars .PARAMETER Target Defines a arbitrary text for the currently processed item .PARAMETER ParentId Defines the ID of a parent progress bar .PARAMETER Completed Explicitly tells powershell to set the progress bar as completed removing it from view. In some cases the progress bar will linger if this is not done. .PARAMETER Counter The currently processed items counter .PARAMETER Total The total number of items to process .PARAMETER StartTime Sets the start datetime for the progressbar, this is required to calculate items/sec, eta and time remaining .PARAMETER DisableDynamicUpdateFrquency Disables the dynamic update frequency function and every item will update the status of the progressbar .PARAMETER NoTimeStats Disables calculation of items/sec, eta and time remaining .EXAMPLE 1..10000 | foreach-object -begin {$StartTime = Get-Date} -process { Write-PSProgress -Activity 'Looping' -Target $PSItem -Counter $PSItem -Total 10000 -StartTime $StartTime } Explanation of the function or its result. You can include multiple examples with additional .EXAMPLE lines #> [CmdletBinding()] param( [Parameter(Mandatory = $true, Position = 0, ParameterSetName = 'Standard')] [Parameter(Mandatory = $true, Position = 0, ParameterSetName = 'Completed')] [string] $Activity, [Parameter(Position = 1, ParameterSetName = 'Standard')] [Parameter(Position = 1, ParameterSetName = 'Completed')] [ValidateRange(0, 2147483647)] [int] $Id, [Parameter(Position = 2, ParameterSetName = 'Standard')] [string] $Target, [Parameter(Position = 3, ParameterSetName = 'Standard')] [Parameter(Position = 3, ParameterSetName = 'Completed')] [ValidateRange(-1, 2147483647)] [int] $ParentId, [Parameter(Position = 4, ParameterSetname = 'Completed')] [switch] $Completed, [Parameter(Mandatory = $true, Position = 5, ParameterSetName = 'Standard')] [long] $Counter, [Parameter(Mandatory = $true, Position = 6, ParameterSetName = 'Standard')] [long] $Total, [Parameter(Position = 7, ParameterSetName = 'Standard')] [datetime] $StartTime, [Parameter(Position = 8, ParameterSetName = 'Standard')] [switch] $DisableDynamicUpdateFrquency, [Parameter(Position = 9, ParameterSetName = 'Standard')] [switch] $NoTimeStats ) # Define current timestamp $TimeStamp = (Get-Date) # Define a dynamic variable name for the global starttime variable $StartTimeVariableName = ('ProgressStartTime_{0}' -f $Activity.Replace(' ', '')) # Manage global start time variable if ($PSBoundParameters.ContainsKey('Completed') -and (Get-Variable -Name $StartTimeVariableName -Scope Global -ErrorAction SilentlyContinue)) { # Remove the global starttime variable if the Completed switch parameter is users try { Remove-Variable -Name $StartTimeVariableName -ErrorAction Stop -Scope Global } catch { throw $_ } } elseif (-not (Get-Variable -Name $StartTimeVariableName -Scope Global -ErrorAction SilentlyContinue)) { # Global variable do not exist, create global variable if ($null -eq $StartTime) { # No start time defined with parameter, use current timestamp as starttime Set-Variable -Name $StartTimeVariableName -Value $TimeStamp -Scope Global $StartTime = $TimeStamp } else { # Start time defined with parameter, use that value as starttime Set-Variable -Name $StartTimeVariableName -Value $StartTime -Scope Global } } else { # Global start time variable is defined, collect and use it $StartTime = Get-Variable -Name $StartTimeVariableName -Scope Global -ErrorAction Stop -ValueOnly } # Define frequency threshold $Frequency = [Math]::Ceiling($Total / 100) switch ($PSCmdlet.ParameterSetName) { 'Standard' { # Only update progress is any of the following is true # - DynamicUpdateFrequency is disabled # - Counter matches a mod of defined frequecy # - Counter is 0 # - Counter is equal to Total (completed) if (($DisableDynamicUpdateFrquency) -or ($Counter % $Frequency -eq 0) -or ($Counter -eq 1) -or ($Counter -eq $Total)) { # Calculations for both timestats and without $Percent = [Math]::Round(($Counter / $Total * 100), 0) # Define count progress string status $CountProgress = ('{0}/{1}' -f $Counter, $Total) # If percent would turn out to be more than 100 due to incorrect total assignment revert back to 100% to avoid that write-progress throws if ($Percent -gt 100) { $Percent = 100 } # Define write-progress splat hash $WriteProgressSplat = @{ Activity = $Activity PercentComplete = $Percent CurrentOperation = $Target } # Add ID if specified if ($Id) { $WriteProgressSplat.Id = $Id } # Add ParentID if specified if ($ParentId) { $WriteProgressSplat.ParentId = $ParentId } # Calculations for either timestats and without if ($NoTimeStats) { $WriteProgressSplat.Status = ('{0} - {1}%' -f $CountProgress, $Percent) } else { # Total seconds elapsed since start $TotalSeconds = ($TimeStamp - $StartTime).TotalSeconds # Calculate items per sec processed (IpS) $ItemsPerSecond = ([Math]::Round(($Counter / $TotalSeconds), 2)) # Calculate seconds spent per processed item (for ETA) $SecondsPerItem = if ($Counter -eq 0) { 0 } else { ($TotalSeconds / $Counter) } # Calculate seconds remainging $SecondsRemaing = ($Total - $Counter) * $SecondsPerItem $WriteProgressSplat.SecondsRemaining = $SecondsRemaing # Calculate ETA $ETA = $(($Timestamp).AddSeconds($SecondsRemaing).ToShortTimeString()) # Add findings to write-progress splat hash $WriteProgressSplat.Status = ('{0} - {1}% - ETA: {2} - IpS {3}' -f $CountProgress, $Percent, $ETA, $ItemsPerSecond) } # Call writeprogress Write-Progress @WriteProgressSplat } } 'Completed' { Write-Progress -Activity $Activity -Id $Id -Completed } } } #EndRegion '.\Private\Write-PSProgress.ps1' 214 #Region '.\Public\Add-DataSetRelation.ps1' -1 function Add-DataSetRelation { <# .DESCRIPTION Creates a relationship between to DataTable columns .PARAMETER DataSet Provide the DataSet object .PARAMETER ParentDataTableColumn Provide the DataTable parent column object .PARAMETER ChildDataTableColumn Provide the DataTable child column object .PARAMETER RelationName Provide a name for the relation object .PARAMETER Passtrough If the passthrough parameter is specified this function will return the created relation object .EXAMPLE $CustOrderRel = Add-DataSetRelation -DataSet $DataSet -ParentDataTableColumn $CustomerTable.Columns['CustomerID'] -ChildDataTableColumn $OrderTable.Columns['OrderID'] -RelationName "Rel_CustomerID_OrderID" -PassThrough This command will define a relationship between the customerID column of table customertable and the orderID column of table ordertable #> [CmdletBinding()] # Enabled advanced function support param( [Parameter(Mandatory)] [system.data.Dataset] $DataSet, [Parameter(Mandatory)] [System.Data.DataColumn] $ParentDataTableColumn, [Parameter(Mandatory)] [System.Data.DataColumn] $ChildDataTableColumn, [Parameter(Mandatory)] [string] $RelationName, [switch] $Passtrough ) PROCESS { try { # Support for constrains is currently missing there for it is default disabled. $null = $DataSet.Relations.Add($RelationName, $ParentDataTableColumn, $ChildDataTableColumn, $false) if ($Passtrough) { $DataSet.Relations[$RelationName] } } catch { Write-Error -Message 'Failed to create data relation' -ErrorRecord $_ } } } #EndRegion '.\Public\Add-DataSetRelation.ps1' 61 #Region '.\Public\Add-DataSetTable.ps1' -1 function Add-DataSetTable { <# .DESCRIPTION Add DataTable to a DataSet .PARAMETER DataSet Define the dataset object that the table should be added to .PARAMETER DataTable Supply the datatable that should be added to the dataset .EXAMPLE Add-DataSetTable -DataSet $DataSet -DataTable $DataTable This example shows how to add a datatable object to a dataset #> [CmdletBinding()] # Enabled advanced function support param( [Parameter(Mandatory)] [system.data.Dataset] $DataSet, [Parameter(Mandatory)] [System.Data.DataTable] $DataTable ) PROCESS { $DataSet.Tables.Add($DataTable) } } #EndRegion '.\Public\Add-DataSetTable.ps1' 32 #Region '.\Public\Add-DataTableColumn.ps1' -1 function Add-DataTableColumn { <# .DESCRIPTION Add columns to data tables. Columns can be added before or after rows have been added. The command operates in two modes, single column or multiple columns mode. Multiple mode In multiple mode it is possible to provide a string array of column names. This is a fast and simple way to quickly populate a table with empty columns. Single mode In single mode each call adds a single column to the table. The difference is that when single mode is used it is possible to add caption, defaultvalue and expressions. .PARAMETER DataTable Defined the DataTable that columns should be added to .PARAMETER Names Defines the names of the columns. This is used to reference the column in the datatable. .PARAMETER Caption Sets the caption of the column. This is used when creating table views. .PARAMETER DefaultValue Sets the default value of a column. When set the cell of each row will get initialized with this default value. .PARAMETER Expression Sets a expression to calculate the cell value. For more information about expression variables and syntax see microsoft docs https://docs.microsoft.com/en-us/dotnet/api/system.data.datacolumn.expression?view=net-5.0 .PARAMETER AllowDBNull Defaults to true, if set to false column will not allow null values .EXAMPLE Add-DataTableColumn -DataTable $DataTable -Names 'Firstname','Lastname','Address','Email' This example demostrates the use of multiple mode to create four columns in the DataTable object. .EXAMPLE Add-DataTableColumn -DataTable $DataTable -Names 'PreferredColor' -Caption 'Preferred color' -DefaultValue 'Blue' This example demostrates the use of single mode to create one column with a caption and a default value. .EXAMPLE Add-DataTableColumn -DataTable $DataTable -Names 'Fee' -Expression '[Price] * [Amount]' This example demostrates the use of single mode to create one column with an expression #> [CmdletBinding(DefaultParameterSetName = 'Multiple')] # Enabled advanced function support param( [Parameter(Mandatory)] [System.Data.DataTable] $DataTable, [Parameter(Mandatory, ParameterSetName = 'Single')] [Parameter(Mandatory, ParameterSetName = 'Multiple')] [string[]] $Names, [Parameter(ParameterSetName = 'Single')] [string] $Caption, [Parameter(ParameterSetName = 'Single')] [string] $DefaultValue = '', [Parameter(ParameterSetName = 'Single')] [string] $Expression, [Parameter(ParameterSetName = 'Single')] [boolean] $AllowDBNull = $true ) PROCESS { switch ($PSCmdlet.ParameterSetName) { 'Single' { $DataColumn = New-Object System.Data.DataColumn -ArgumentList @($Names)[0] $DataColumn.Caption = $Caption $DataColumn.DefaultValue = $DefaultValue $DataColumn.DataType = [Object] if ($Expression) { $DataColumn.Expression = $Expression } $DataColumn.AllowDBNull = $AllowDBNull [void]$DataTable.Columns.Add($DataColumn) } 'Multiple' { foreach ($Name in $Names) { $DataColumn = New-Object System.Data.DataColumn -ArgumentList $Name $DataColumn.DataType = [Object] [void]$DataTable.Columns.Add($DataColumn) } } } } } #endregion #EndRegion '.\Public\Add-DataTableColumn.ps1' 111 #Region '.\Public\Add-DataTableRow.ps1' -1 function Add-DataTableRow { <# .DESCRIPTION This function adds a row to a table .PARAMETER DataTable Pass the data table object to add rows to .PARAMETER InputObject Provides an object array with objects thats has properties corresponding to the table columns. Note that the object can contain more properties than columns, they will simply be ignored during matching. On the other side, if the object is missing properties for all columns, the addition will fail. .EXAMPLE Get-Service | Add-DataTableRow -DataTable $DataTable This example demonstrates how a preexisting object array can be passed to add rows. .EXAMPLE $Object = [pscustomobject]@{ Property1 = 'Value' Property2 = 'Value' Property3 = 'Value' } Add-DataTableRow -DataTable $DateTable -InputObject $Object This example demonstrates how a single object can be passed to add a new row. #> [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSReviewUnusedParameter', '', Justification = 'False positive.')] [CmdletBinding()] # Enabled advanced function support param( [System.Data.DataTable] $DataTable, [Parameter(ValueFromPipeline)] [Object[]] $InputObject ) PROCESS { $InputObject | ForEach-Object { $CurrentObject = $PSItem $NewRow = $DataTable.NewRow() $CurrentObject.PSObject.Properties.GetEnumerator().ForEach( { $NewRow.($PSItem.Name) = $PSItem.Value }) [void]$DataTable.Rows.Add($NewRow) } } } #EndRegion '.\Public\Add-DataTableRow.ps1' 51 #Region '.\Public\Get-DataTableRow.ps1' -1 function Get-DataTableRow { <# .DESCRIPTION Retreive rows from DataTable .PARAMETER DataTable Pass the data table object to get rows from .PARAMETER Query Define a query. -like : like -gt : > -lt : < -eq : = Values should be enclosed with single qoutation marks Columns should not be enclosed with quotation marks .EXAMPLE Get-DataTableRow -DataTable $DataTable -Query '[OrderID] = 123456789' This example demonstrates how to retreive rows from data table. #> [CmdletBinding()] param( [System.Data.DataTable] $DataTable, [string] [Alias('SQLQuery')] $Query ) PROCESS { try { $DataTable.Select($Query) } catch { Throw $_ } } } #EndRegion '.\Public\Get-DataTableRow.ps1' 45 #Region '.\Public\New-DataSet.ps1' -1 function New-DataSet { <# .DESCRIPTION This function creates a new dataset .EXAMPLE $DataSet = New-DataSet This example demonstrates how to initialize a new data set object. #> [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseShouldProcessForStateChangingFunctions', '', Justification = 'No need to confirm creation of creation of in-memory empty data structure')] [CmdletBinding()] # Enabled advanced function support param( ) PROCESS { try { New-Object -TypeName System.Data.DataSet -ErrorAction Stop Write-Verbose -Message 'Created DataSet' } catch { throw $_ } } } #EndRegion '.\Public\New-DataSet.ps1' 29 #Region '.\Public\New-DataTable.ps1' -1 function New-DataTable { <# .DESCRIPTION Creating a DataTable object .PARAMETER TableName Defines the data table name. This property is used when referencing the table in a dataset for instance. .EXAMPLE $OrderTable = New-DataTable -TableName 'Orders' This example will create a datatable and return the datatable object. #> [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseShouldProcessForStateChangingFunctions', '', Justification = 'No need to confirm creation of creation of in-memory empty data structure')] [CmdletBinding()] # Enabled advanced function support [OutputType([system.object[]])] param( [Parameter(Mandatory)] [string] $TableName ) PROCESS { $DataTable = New-Object System.Data.Datatable $DataTable.TableName = $TableName # As the type DataTable is not enumerable, use the unary operator to return the DataTable as an one element array which is enumerable. return , $DataTable } } #EndRegion '.\Public\New-DataTable.ps1' 31 #Region '.\suffix.ps1' -1 # The content of this file will be appended to the top of the psm1 module file. This is useful for custom procesedures after all module functions are loaded. #EndRegion '.\suffix.ps1' 2 |