How to return specific data type from PowerShell function i.e. DataTable

Question

I've been trying to write a utility function that will return initialized [System.Data.DataTable] object data type.

I am aware of the annoying PowerShell functions return behavior where it tries to "unroll" everything into a [System.Array] return type. Previously I have always managed to get around that problem. Usually using the "comma trick" to return your own array @(,$result) aways worked - however this time this does not seem to make a difference and I've run out of options...

Another trick I normally use is the $null assignments within the Process block (see my code bellow) - this way I fool the pipeline there is nothing to "unroll" on the output...

I am not taking impossible for an answer, so far based on my experience nothing is impossible in PowerShell :)

Here is my code:

function Get-SourceDataTable 
{
    [OutputType([System.Data.DataTable])]
    [cmdletbinding()]
    param(
        [parameter(Mandatory=$true, Position=0)]
        [System.Data.SqlClient.SqlBulkCopy] $Destination,
        [parameter(Mandatory=$true, Position=1)]
        [System.Collections.Specialized.OrderedDictionary] $ColumnDefinition,
        [parameter(Mandatory=$false, Position=2)]
        [int]$ColumnStartIndex = 0
    )
    BEGIN{
        $datatable = New-Object System.Data.DataTable
        $colIndex = $ColumnStartIndex 
    }
    PROCESS{
        $ColumnDefinition.Keys | 
        foreach {
            $null = $datatable.Columns.Add($_, $ColumnDefinition[$_])   # define each column name and data type
            $null = $Destination.ColumnMappings.Add($_, $colIndex) # map column to destination table
            $colIndex++
        }
    }
    END{
        return ,$datatable
    }
}

I hope someone can get this code working...

Answer 1

Rather than return use Write-Output -NoEnumerate. For example:

function New-DataTable {
   $datatable = New-Object System.Data.DataTable
   $null = $datatable.Columns.Add("x",[int])
   $null = $datatable.Columns.Add("y",[int])
   $null = $datatable.Rows.Add(@(1,2))
   $null = $dataTable.Rows.Add(@(3,4))
   Write-Output -NoEnumerate $datatable
}

New-DataTable | Get-Member

Note however that if you just type New-DataTable, it might look like enumberated rows, but Get-Member tells you the actual type returned.

Answer 2

I got the function from the question to return DataTable type when I used LoadWithPartialName to load the assembly containing the type and pipe it out with Out-Null.

Don't ask my why, but feel free to comment if you know the reason.

The working function code is below. Note the return statement is not necessary I only use it to improve code readability:

function Get-SourceDataTable 
{
    [OutputType([System.Data.DataTable])]
    [cmdletbinding()]
    param(
        [parameter(Mandatory=$true, Position=0)]
        [System.Data.SqlClient.SqlBulkCopy] $Destination,
        [parameter(Mandatory=$true, Position=1)]
        [System.Collections.Specialized.OrderedDictionary] $ColumnDefinition,
        [parameter(Mandatory=$false, Position=2)]
        [int]$ColumnStartIndex = 0
    )
    BEGIN{
        [System.Reflection.Assembly]::LoadWithPartialName("System.Data") | Out-Null
        $datatable = New-Object System.Data.DataTable
        $colIndex = $ColumnStartIndex 
    }
    PROCESS{
        $ColumnDefinition.Keys | 
        foreach {
            $null = $datatable.Columns.Add($_, $ColumnDefinition[$_])   # define each column name and data type
            $null = $Destination.ColumnMappings.Add($_, $colIndex) # map column to destination table
            $colIndex++
        }
    }
    END{
        return ,$datatable
    }
}

To summarize all known possible solutions to the problem of forcing PowerShell function to return specific data type:

• use $null assignments
• use comma to return an array ,$variable
• use LoadWithPartialName("Assembly.Name") | Out-Null
• use Write-Output -NoEnumerate $variable to return the type - credit goes to Burt_Harris

Finally, after the imput from Burt_Harris (THANKS Burt!) the final working version of the function from this question is this:

function Get-SourceDataTable 
{
    [OutputType([System.Data.DataTable])]
    [cmdletbinding()]
    param(
        [parameter(Mandatory=$true, Position=0)]
        [System.Data.SqlClient.SqlBulkCopy] $Destination,
        [parameter(Mandatory=$true, Position=1)]
        [System.Collections.Specialized.OrderedDictionary] $ColumnDefinition,
        [parameter(Mandatory=$false, Position=2)]
        [int]$ColumnStartIndex = 0
    )
    BEGIN{
        #[System.Reflection.Assembly]::LoadWithPartialName("System.Data") | Out-Null
        $datatable = New-Object System.Data.DataTable
        $colIndex = $ColumnStartIndex 
    }
    PROCESS{
        $ColumnDefinition.Keys | 
        foreach {
            $null = $datatable.Columns.Add($_, $ColumnDefinition[$_])   # define each column name and data type
            $null = $Destination.ColumnMappings.Add($_, $colIndex) # map column to destination table
            $colIndex++
        }
    }
    END{
        #return ,$datatable
        Write-Output -NoEnumerate $datatable
    }
}