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...

Have you tried just casting the output as a DataTable? [system.data.datatable]$MyVar = Get-SourceDataTable $Dest $ColDef — TheMadTechnician
– TheMadTechnician, Commented Sep 14, 2016 at 16:56
Yes, I tried a casting it does not seem to have an effect on the output type — Emil
– Emil, Commented Sep 20, 2016 at 14:40
I've had the same problem. This is ridiculous to have to jump through all these hoops to get a function to return the data type you're already telling it to return. A function should ALWAYS return the data type specified, not a different type of object. If I wanted to return an array then I would write my function to return an array. — SendETHToThisAddress
– SendETHToThisAddress, Commented Jul 1, 2023 at 21:07

Burt_Harris · Accepted Answer · 2016-09-14 17:33:03Z

23

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.

answered Sep 14, 2016 at 17:33

Burt_Harris

6,9522 gold badges35 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jyao Over a year ago

Good question and good answer. After I up-voted the question and the answer many months ago, I encountered the issue again, since I forgot the answer, my google result took me back here again. Really wish I could up-vote it again. :-)

Burt_Harris Over a year ago

Thanks, I had fun answering it too.

Emil · Accepted Answer · 2023-11-06 10:01:57Z

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 me 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 input 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
    }
}

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related