functions/ConvertTo-DbaDataTable.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
#ValidationTags#Messaging,FlowControl,Pipeline,CodeStyle#
function ConvertTo-DbaDataTable {
    <#
        .SYNOPSIS
            Creates a DataTable for an object.
 
        .DESCRIPTION
            Creates a DataTable based on an object's 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
            Specifies the type to convert TimeSpan objects into. Default is 'TotalMilliseconds'. Valid options are: 'Ticks', 'TotalDays', 'TotalHours', 'TotalMinutes', 'TotalSeconds', 'TotalMilliseconds', and 'String'.
 
        .PARAMETER SizeType
            Specifies the type to convert DbaSize objects to. Default is 'Int64'. Valid options are 'Int32', 'Int64', and 'String'.
 
        .PARAMETER IgnoreNull
            If this switch is enabled, objects with null values will be ignored (empty rows will be added by default).
 
        .PARAMETER Raw
            If this switch is enabled, the DataTable will be created with strings. No attempt will be made to parse/determine data types.
 
        .PARAMETER EnableException
            By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
            This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
            Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.
 
        .NOTES
            Tags: DataTable, Table, Data
            Website: https://dbatools.io/
            Copyright: (C) 2016 Chrissy LeMaire
            License: MIT https://opensource.org/licenses/MIT
 
        .LINK
            https://dbatools.io/ConvertTo-DbaDataTable
 
        .OUTPUTS
            System.Object[]
 
        .EXAMPLE
            Get-Service | ConvertTo-DbaDataTable
 
            Creates a DataTable from the output of Get-Service.
 
        .EXAMPLE
            ConvertTo-DbaDataTable -InputObject $csv.cheesetypes
 
            Creates a DataTable from the CSV object $csv.cheesetypes.
 
        .EXAMPLE
            $dblist | ConvertTo-DbaDataTable
 
            Creates a DataTable from the $dblist object passed in via pipeline.
 
        .EXAMPLE
            Get-Process | ConvertTo-DbaDataTable -TimeSpanType TotalSeconds
 
            Creates a DataTable with the running processes and converts any TimeSpan property to TotalSeconds.
    #>

    [CmdletBinding()]
    [OutputType([System.Object[]])]
    param (
        [Parameter(Position = 0,
                   Mandatory,
                   ValueFromPipeline)]
        [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,
        [Alias('Silent')]
        [switch]$EnableException
    )

    begin {
        Write-Message -Level Debug -Message "Bound parameters: $($PSBoundParameters.Keys -join ", ")"
        Write-Message -Level Debug -Message "TimeSpanType = $TimeSpanType | SizeType = $SizeType"
        Test-DbaDeprecation -DeprecatedOn 1.0.0 -Alias Out-DbaDataTable

        function Convert-Type {
            # 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 stored in a database further down the line.
            [CmdletBinding()]
            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
            $specialType = ""

            # 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 {
                    # Let's 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'
                }
                $specialType = 'Timespan'
            }
            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'
                    }
                }
                $specialType = 'Size'
            }
            elseif (-not ($type -in $types)) {
                # 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; SpecialType = $specialType }
        }

        function Convert-SpecialType {
            <#
            .SYNOPSIS
                Converts a value for a known column.
 
            .DESCRIPTION
                Converts a value for a known column.
 
            .PARAMETER Value
                The value to convert
 
            .PARAMETER Type
                The special type for which to convert
 
            .PARAMETER SizeType
                The size type defined by the user
 
            .PARAMETER TimeSpanType
                The timespan type defined by the user
        #>

            [CmdletBinding()]
            Param (
                $Value,
                [ValidateSet('Timespan', 'Size')]
                [string]$Type,
                [string]$SizeType,
                [string]$TimeSpanType
            )

            switch ($Type) {
                'Size' {
                    if ($SizeType -eq 'String') { return $Value.ToString() }
                    else { return $Value.Byte }
                }
                'Timespan' {
                    if ($TimeSpanType -eq 'String') {
                        $Value.ToString()
                    }
                    else {
                        $Value.$TimeSpanType
                    }
                }
            }
        }

        function Add-Column {
            <#
            .SYNOPSIS
                Adds a column to the datatable in progress.
 
            .DESCRIPTION
                Adds a column to the datatable in progress.
 
            .PARAMETER Property
                The property for which to add a column.
 
            .PARAMETER DataTable
                Autofilled. The table for which to add a column.
 
            .PARAMETER TimeSpanType
                Autofilled. How should timespans be handled?
 
            .PARAMETER SizeType
                Autofilled. How should sizes be handled?
 
            .PARAMETER Raw
                Autofilled. Whether the column should be string, no matter the input.
        #>

            [CmdletBinding()]
            Param (
                [System.Management.Automation.PSPropertyInfo]$Property,
                [System.Data.DataTable]$DataTable = $datatable,
                [string]$TimeSpanType = $TimeSpanType,
                [string]$SizeType = $SizeType,
                [bool]$Raw = $Raw
            )

            $type = $property.TypeNameOfValue
            try {
                if ($Property.MemberType -like 'ScriptProperty') {
                    $type = $Property.GetType().FullName
                }
            }
            catch { $type = 'System.String' }

            $converted = Convert-Type -type $type -value $property.Value -timespantype $TimeSpanType -sizetype $SizeType

            $column = New-Object System.Data.DataColumn
            $column.ColumnName = $property.Name.ToString()
            if (-not $Raw) {
                $column.DataType = [System.Type]::GetType($converted.type)
            }
            $null = $DataTable.Columns.Add($column)
            $converted
        }

        $datatable = New-Object System.Data.DataTable

        # Accelerate subsequent lookups of columns and special type columns
        $columns = @()
        $specialColumns = @()
        $specialColumnsType = @{ }

        $ShouldCreateColumns = $true
    }

    process {
        #region Handle null objects
        if ($null -eq $InputObject) {
            if (-not $IgnoreNull) {
                $datarow = $datatable.NewRow()
                $datatable.Rows.Add($datarow)
            }

            # Only ends the current process block
            return
        }
        #endregion Handle null objects


        foreach ($object in $InputObject) {
            #region Handle null objects
            if ($null -eq $object) {
                if (-not $IgnoreNull) {
                    $datarow = $datatable.NewRow()
                    $datatable.Rows.Add($datarow)
                }
                continue
            }
            #endregion Handle null objects

            #Handle rows already being System.Data.DataRow
            if ($object.GetType().FullName -eq 'System.Data.DataRow') {
                if ($ShouldCreateColumns) {
                    $datatable = $object.Table.Copy()
                    $ShouldCreateColumns = $false
                }
                continue
            }

            # The new row to insert
            $datarow = $datatable.NewRow()

            #region Process Properties
            $objectProperties = $object.PSObject.Properties
            foreach ($property in $objectProperties) {
                #region Create Columns as needed
                if ($ShouldCreateColumns) {
                    $newColumn = Add-Column -Property $property
                    $columns += $property.Name
                    if ($newColumn.Special) {
                        $specialColumns += $property.Name
                        $specialColumnsType[$property.Name] = $newColumn.SpecialType
                    }
                }
                #endregion Create Columns as needed

                # Handle null properties, as well as properties with access errors
                try {
                    $propValueLength = $property.value.length
                }
                catch {
                    $propValueLength = 0
                }

                #region Insert value into column of row
                if ($propValueLength -gt 0) {
                    # If the typename was a special typename we want to use the value returned from Convert-Type 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 ($property.Name -in $specialColumns) {
                        $datarow.Item($property.Name) = Convert-SpecialType -Value $property.value -Type $specialColumnsType[$property.Name] -SizeType $SizeType -TimeSpanType $TimeSpanType
                    }
                    else {
                        if ($property.value.ToString().length -eq 15) {
                            if ($property.value.ToString() -eq 'System.Object[]') {
                                $value = $property.value -join ", "
                            }
                            elseif ($property.value.ToString() -eq 'System.String[]') {
                                $value = $property.value -join ", "
                            }
                            else {
                                $value = $property.value
                            }
                        }
                        else {
                            $value = $property.value
                        }

                        try {
                            $datarow.Item($property.Name) = $value
                        }
                        catch {
                            if ($property.Name -notin $columns) {
                                try {
                                    $newColumn = Add-Column -Property $property
                                    $columns += $property.Name
                                    if ($newColumn.Special) {
                                        $specialColumns += $property.Name
                                        $specialColumnsType[$property.Name] = $newColumn.SpecialType
                                    }

                                    $datarow.Item($property.Name) = $newColumn.Value
                                }
                                catch {
                                    Write-Message -Level Warning -Message "Failed to add property $($property.Name) from $object" -ErrorRecord $_ -Target $object
                                }
                            }
                            else {
                                Write-Message -Level Warning -Message "Failed to add property $($property.Name) from $object" -ErrorRecord $_ -Target $object
                            }
                        }
                    }
                }
                #endregion Insert value into column of row
            }

            $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 ($ShouldCreateColumns) {
                $ShouldCreateColumns = $false
            }
            #endregion Process Properties
        }
    }
    end {
        Write-Message -Level InternalComment -Message "Finished."
         , $datatable
    }
}