functions/Out-DbaDataTable.ps1

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
     
.PARAMETER IgnoreNull
Use this switch to ignore null rows
 
.PARAMETER Silent
Use this switch to disable any kind of verbose messages
 
.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)]
        [AllowNull()][PSObject[]]$InputObject,
        [switch]$IgnoreNull,
        [switch]$Silent
    )
    
    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    
    {
        if (!$InputObject)
        {
            if ($IgnoreNull)
            {
                Stop-Function -Message "The InputObject from the pipe is null. Skipping." -Continue
            }
            else
            { 
                $datarow = $datatable.NewRow()
                $datatable.Rows.Add($datarow)
                continue
            }
        }
        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))
    }
    
}