functions/Out-DbaDataTable.ps1
|
#ValidationTags#Messaging,FlowControl,Pipeline,CodeStyle# Function Out-DbaDataTable { <# .SYNOPSIS Creates a DataTable for an object .DESCRIPTION Creates a DataTable based on an objects properties. This allows you to easily write to SQL Server tables. Thanks to Chad Miller, this is based on his script. https://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd If the attempt to convert to datatable fails, try the -Raw parameter for less accurate datatype detection. .PARAMETER InputObject The object to transform into a DataTable .PARAMETER TimeSpanType Sets what type to convert TimeSpan into before creating the datatable. Default: TotalMilliseconds Options: Ticks, TotalDays, TotalHours, TotalMinutes, TotalSeconds, TotalMilliseconds and String. .PARAMETER SizeType Sets what type to convert DbaSize to. Default: Int64 Options: Int32, Int64, String .PARAMETER IgnoreNull If this switch is used, objects with null values will be ignored (empty rows will be added by default) .PARAMETER Raw Creates a datatable with all strings - no attempt to parse out datatypes is made .PARAMETER Silent Use this switch to disable any kind of verbose messages .EXAMPLE Get-Service | Out-DbaDataTable Creates a $datatable based off of the output of Get-Service .EXAMPLE Out-DbaDataTable -InputObject $csv.cheesetypes Creates a DataTable from the CSV object, $csv.cheesetypes .EXAMPLE $dblist | Out-DbaDataTable Similar to above but $dbalist gets piped in .EXAMPLE Get-Process | Out-DbaDataTable -TimeSpanType TotalSeconds Creates a DataTable with the running processes and converts any TimeSpan property to TotalSeconds. .OUTPUTS System.Object[] .NOTES dbatools PowerShell module (https://dbatools.io) Copyright (C) 2016 Chrissy LeMaire This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program. If not, see http://www.gnu.org/licenses/. .LINK https://dbatools.io/Out-DbaDataTable #> [CmdletBinding()] [OutputType([System.Object[]])] param ( [Parameter(Position = 0, Mandatory = $true, ValueFromPipeline = $true)] [AllowNull()] [PSObject[]]$InputObject, [Parameter(Position = 1)] [ValidateSet("Ticks", "TotalDays", "TotalHours", "TotalMinutes", "TotalSeconds", "TotalMilliseconds", "String")] [ValidateNotNullOrEmpty()] [string]$TimeSpanType = "TotalMilliseconds", [ValidateSet("Int64", "Int32", "String")] [string]$SizeType = "Int64", [switch]$IgnoreNull, [switch]$Raw, [switch]$Silent ) Begin { Write-Message -Level InternalComment -Message "Starting" Write-Message -Level Debug -Message "Bound parameters: $($PSBoundParameters.Keys -join ", ")" Write-Message -Level Debug -Message "TimeSpanType = $TimeSpanType | SizeType = $SizeType" function ConvertType { # This function will check so that the type is an accepted type which could be used when inserting into a table. # If a type is accepted (included in the $type array) then it will be passed on, otherwise it will first change type before passing it on. # Special types will have both their types converted as well as the value. # TimeSpan is a special type and will be converted into the $timespantype. (default: TotalMilliseconds) # so that the timespan can be store in a database further down the line. param ( $type, $value, $timespantype = 'TotalMilliseconds', $sizetype = 'Int64' ) $types = [System.Collections.ArrayList]@( 'System.Int32', 'System.UInt32', 'System.Int16', 'System.UInt16', 'System.Int64', 'System.UInt64', 'System.Decimal', 'System.Single', 'System.Double', 'System.Byte', 'System.SByte', 'System.Boolean', 'System.DateTime', 'System.Guid', 'System.Char' ) # The $special variable is used to mark the return value if a conversion was made on the value itself. # If this is set to true the original value will later be ignored when updating the DataTable. # And the value returned from this function will be used instead. (cannot modify existing properties) $special = $false # Special types need to be converted in some way. # This attempt is to convert timespan into something that works in a table. # I couldn't decide on what to convert it to so the user can decide. # If the parameter is not used, TotalMilliseconds will be used as default. # Ticks are more accurate but I think milliseconds are more useful most of the time. if (($type -eq 'System.TimeSpan') -or ($type -eq 'Sqlcollaborative.Dbatools.Utility.DbaTimeSpan') -or ($type -eq 'Sqlcollaborative.Dbatools.Utility.DbaTimeSpanPretty')) { $special = $true if ($timespantype -eq 'String') { $value = $value.ToString() $type = 'System.String' } else { # Lets use Int64 for all other types than string. # We could match the type more closely with the timespantype but that can be added in the future if needed. $value = $value.$timespantype $type = 'System.Int64' } } elseif ($type -eq 'Sqlcollaborative.Dbatools.Utility.Size') { $special = $true switch ($sizetype) { 'Int64' { $value = $value.Byte $type = 'System.Int64' } 'Int32' { $value = $value.Byte $type = 'System.Int32' } 'String' { $value = $value.ToString() $type = 'System.String' } } } elseif (!$types.Contains($type)) { # All types which are not found in the array will be converted into strings. # In this way we dont ignore it completely and it will be clear in the end why it looks as it does. $type = 'System.String' } # return a hashtable instead of an object. I like hashtables :) return @{ type = $type; Value = $value; Special = $special } } $datatable = New-Object System.Data.DataTable $specialColumns = @{ } # will store names of properties with special data types # The shouldCreateColumns variable will be set to false as soon as the column definition has been added to the data table. # This is to avoid that the rare scenario when columns are not created because the first object is null, which can be accepted. # This means that we cannot relly on the first object to create columns, hence this variable. $ShouldCreateCollumns = $true } Process { if (!$InputObject) { if ($IgnoreNull) { # If the object coming down the pipeline is null and the IgnoreNull parameter is set, ignore it. Write-Message -Level Warning -Message "The InputObject from the pipe is null. Skipping." -Silent:$Silent } else { # If the object coming down the pipeline is null, add an empty row and then skip to next. $datarow = $datatable.NewRow() $datatable.Rows.Add($datarow) } } else { foreach ($object in $InputObject) { if (!$object) { if ($IgnoreNull) { # If the object in the array is null and the IgnoreNull parameter is set, ignore it. Write-Message -Level Warning -Message "Object in array is null. Skipping." -Silent $Silent } else { # If the object in the array is null, add an empty row and then skip to next. $datarow = $datatable.NewRow() $datatable.Rows.Add($datarow) } } else { $datarow = $datatable.NewRow() foreach ($property in $object.PsObject.get_properties()) { # The converted variable will get the result from the ConvertType function and used for type and value conversion when adding to the datatable. $converted = @{ } if ($ShouldCreateCollumns) { # this is where the table columns are generated if ($property.value -isnot [System.DBNull]) { # Check if property is a ScriptProperty, then resolve it while calling ConvertType. (otherwise we dont get the proper type) Write-Verbose "Attempting to get type from property $($property.Name)" If ($property.MemberType -eq 'ScriptProperty') { try { $converted = ConvertType -type ($object.($property.Name).GetType().ToString()) -value $property.value -timespantype $TimeSpanType -sizetype $SizeType } catch { # Ends up here when the type is not possible to get so the call to ConvertType fails. # In that case we make a string out of it. (in this scenario its often that a script property points to a null value so we can't get the type) $converted = @{ type = 'System.String' Value = $property.value Special = $false } } # We need to check if the type returned by ConvertType is a special type. # In that case we add it to the $specialColumns variable for future reference. if ($converted.special) { $specialColumns.Add($property.Name, $object.($property.Name).GetType().ToString()) } } else { $converted = ConvertType -type $property.TypeNameOfValue -value $property.value -timespantype $TimeSpanType -sizetype $SizeType # We need to check if the type returned by ConvertType is a special type. # In that case we add it to the $specialColumns variable for future reference. if ($converted.special) { $specialColumns.Add($property.Name, $property.TypeNameOfValue) } } } $column = New-Object System.Data.DataColumn $column.ColumnName = $property.Name.ToString() if (-not $Raw) { $column.DataType = [System.Type]::GetType($converted.type) } $datatable.Columns.Add($column) } else { # This is where we end up if the columns has been created in the data table. # We still need to check for special columns again, to make sure that the value is converted properly. if ($property.value -isnot [System.DBNull]) { if ($specialColumns.Keys -contains $property.Name) { $converted = ConvertType -type $specialColumns.($property.Name) -value $property.Value -timespantype $TimeSpanType -sizetype $SizeType } } } try { $propValueLength = $property.value.length } catch { $propValueLength = 0 } if ($propValueLength -gt 0) { if ($property.value.ToString() -eq 'System.Object[]' -or $property.value.ToString() -eq 'System.String[]') { $datarow.Item($property.Name) = $property.value -join ", " } else { # If the typename was a special typename we want to use the value returned from ConvertType instead. # We might get error if we try to change the value for $property.value if it is read-only. That's why we use $converted.value instead. if ($converted.special) { $datarow.Item($property.Name) = $converted.value } else { $datarow.Item($property.Name) = $property.value } } } } $datatable.Rows.Add($datarow) # If this is the first non-null object then the columns has just been created. # Set variable to false to skip creating columns from now on. if ($ShouldCreateCollumns) { $ShouldCreateCollumns = $false } } } } } End { Write-Message -Level InternalComment -Message "Finished" return @( ,($datatable)) } } |