functions/Out-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
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 script is all him. https://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd
 
.PARAMETER InputObject
The object to transform into a DataTable
  
.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
 
.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
  
#>
    
    [CmdletBinding()]
    param (
        [Parameter(Position = 0, Mandatory = $true, ValueFromPipeline = $true)]
        [PSObject[]]$InputObject
    )
    
    BEGIN
    {
        function Get-Type
        {
            param ($type)
            
            $types = @(
                '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')
            
            if ($types -contains $type)
            {
                return $type
            }
            else
            {
                return 'System.String'
            }
        }
        
        $datatable = New-Object System.Data.DataTable
    }
    
    PROCESS
    
    {
        foreach ($object in $InputObject)
        {
            $datarow = $datatable.NewRow()
            foreach ($property in $object.PsObject.get_properties())
            {
                if ($datatable.Rows.Count -eq 0)
                {
                    $column = New-Object System.Data.DataColumn
                    $column.ColumnName = $property.Name.ToString()
                    
                    if ($property.value)
                    {
                        if ($property.value -isnot [System.DBNull])
                        {
                            $type = Get-Type $property.TypeNameOfValue
                            $column.DataType = [System.Type]::GetType($type)
                        }
                    }
                    $datatable.Columns.Add($column)
                }
                if ($property.Gettype().IsArray)
                {
                    $datarow.Item($property.Name) = $property.value | ConvertTo-XML -AS String -NoTypeInformation -Depth 1
                }
                else
                {
                    if($property.value.length -gt 0)
                        {
                            $datarow.Item($property.Name) = $property.value
                        }
                }
            }
            $datatable.Rows.Add($datarow)
        }
    }
    
    End
    {
        return @( ,($datatable))
    }
    
}