In PowerShell, I cannot transfer System.Data.DataTable using the invoke-command to the remote host

Question

Here is a script in which I am trying to pass a variable of type System.Data.DataTable via invoke-command to the remote host:

function Test1 {
    param (
        [System.Data.DataTable]
        $Table
    )
    process {
        [PSCustomObject]$OptionsInvokeCommand = @{
            ComputerName = '192.168.0.5'
            Authentication = 'Negotiate'
            ArgumentList = [System.Data.DataTable]$Table
        }
        write-host $Table.GetType()
        write-host $OptionsInvokeCommand.ArgumentList[0].GetType()
        [System.Management.Automation.ScriptBlock]$ScriptBlock = {
            param (
                [System.Data.DataTable]$Table
            )
            process {
                write-host $Table.GetType()
            }
        }
        Invoke-Command @OptionsInvokeCommand -ScriptBlock $ScriptBlock
    }
}
$Table = New-Object System.Data.DataTable
Test1 -Table $Table

It says:

Cannot process argument transformation on parameter 'Table'. Cannot convert the "System.Collections.ArrayList" value of type "System.Collections.ArrayList" to type "System.Data.DataTable".
    + CategoryInfo          : InvalidData: (:) [], ParameterBindin...mationException
    + FullyQualifiedErrorId : ParameterArgumentTransformationError
    + PSComputerName        : 192.168.0.5

I also tried passing a variable without an ArgumentList:

function Test2 {
    param (
        [System.Data.DataTable]
        $Table
    )
    process {
        [PSCustomObject]$OptionsInvokeCommand = @{
            ComputerName = '192.168.0.5'
            Authentication = 'Negotiate'
        }
        write-host $Table.GetType()
        [System.Management.Automation.ScriptBlock]$ScriptBlock = {
            process {
                [System.Data.DataTable]$Table = $using:Table
                write-host $Table.GetType()
            }
        }
        Invoke-Command @OptionsInvokeCommand -ScriptBlock $ScriptBlock
    }
}
$Table = New-Object System.Data.DataTable
Test2 -Table $Table

But It also says:

Cannot process argument transformation on parameter 'Table'. Cannot convert the "System.Collections.ArrayList" value of type "System.Collections.ArrayList" to type "System.Data.DataTable".
    + CategoryInfo          : InvalidData: (:) [], ParameterBindin...mationException
    + FullyQualifiedErrorId : ParameterArgumentTransformationError
    + PSComputerName        : 192.168.0.5

I tried Powershell versions 5.1 and 7.2.

How do I pass a variable System.Data.DataTable to remote host without converting it to another type?

Addition (2025.05.15):

I've read the information here (thanks @SantiagoSquarzon). PowerShell cannot transfer System.Data.DataTable to the remote server. And that's why PowerShell, hidden from the user, converts System.Data.DataTable to Xml (serialization) on the local host. And then PowerShell does the reverse conversion of Xml to System.Data.DataTable (deserialization) on the remote host. When PowerShell does this, it corrupts the data and the reverse transformation becomes impossible. This is a PowerShell bug! Don't say it's not true. You're just used to it, but it's a bug! Since PowerShell cannot correctly perform this operation on its own, I tried to do it for it. And I did it.:

function Test1 {
    param (
        [System.Data.DataTable]
        $Table
    )
    process {
        [string]$TmpPath = New-TemporaryFile
        $Table.WriteXml($TmpPath, [Data.XmlWriteMode]::WriteSchema)
        [string[]]$XmlString = Get-content $TmpPath
        Remove-Item -Force $TmpPath
        [PSCustomObject]$OptionsInvokeCommand = @{
            ComputerName = '192.168.0.5'
            Authentication = 'Negotiate'
            ArgumentList = ,[string[]]$XmlString
        }
        write-host $Table.GetType()
        [System.Management.Automation.ScriptBlock]$ScriptBlock = {
            param (
                [string[]]$XmlString
            )
            process {
                [string]$TmpPath = New-TemporaryFile
                Out-File -InputObject $XmlString -FilePath $TmpPath -Encoding utf8
                $Ds = New-Object Data.DataSet
                $Ds.ReadXml($TmpPath, [Data.XmlReadMode]::ReadSchema)
                $Table = $Ds.Tables[0]
                Remove-Item -Force $TmpPath
                write-host $Table.GetType()
            }
        }
        [void](Invoke-Command @OptionsInvokeCommand -ScriptBlock $ScriptBlock)
    }
}
$Table = New-Object System.Data.DataTable NameOfTable
Test1 -Table $Table

function Test2 {
    param (
        [System.Data.DataTable]
        $Table
    )
    process {
        [string]$TmpPath = New-TemporaryFile
        $Table.WriteXml($TmpPath, [Data.XmlWriteMode]::WriteSchema)
        [string[]]$XmlString = Get-content $TmpPath
        Remove-Item -Force $TmpPath
        [PSCustomObject]$OptionsInvokeCommand = @{
            ComputerName = '192.168.0.5'
            Authentication = 'Negotiate'
        }
        write-host $Table.GetType()
        [System.Management.Automation.ScriptBlock]$ScriptBlock = {
            process {
                [string[]]$XmlString = $using:XmlString
                [string]$TmpPath = New-TemporaryFile
                Out-File -InputObject $XmlString -FilePath $TmpPath -Encoding utf8
                $Ds = New-Object Data.DataSet
                $Ds.ReadXml($TmpPath, [Data.XmlReadMode]::ReadSchema)
                $Table = $Ds.Tables[0]
                Remove-Item -Force $TmpPath
                write-host $Table.GetType()
            }
        }
        [void](Invoke-Command @OptionsInvokeCommand -ScriptBlock $ScriptBlock)
    }
}
$Table = New-Object System.Data.DataTable NameOfTable
Test2 -Table $Table

But this greatly complicates the code. Can someone simplify this?

And again, maybe it's possible to do without converting variables?

Santiago Squarzon · Accepted Answer · 2025-05-15 19:12:32Z

4

Addressing the updated question, this is the expected behavior, not a bug. Some objects can be rehydrated back to the original type and some can't. This rehydration process is handled by the built-in DeserializingTypeConverter, source code in serialization.cs#L6670.

Additionally, as explained in comments, PowerShell offers a way to cast back or rehydrate the deserialized object into its original type by defining your own PSTypeConverter among others, more details in Conversions - 6.18 .NET Conversion and Implicit type conversion. For an example of a PSTypeConverter implementation see Constructor invocation syntax in PowerShell 4.0 and lower without using New-Object, the difference if you want to automatically rehydrate the object will be in the use of Update-TypeData where you would use:

class DataTableDeserializer : System.Management.Automation.PSTypeConverter {
    # implemantation of PSTypeConverter as shown in linked answer
}

$updateTypeDataSplat = @{
    TypeName                     = 'Deserialized.System.Data.DataTable'
    TargetTypeForDeserialization = 'System.Data.DataTable'
}
Update-TypeData @updateTypeDataSplat

$updateTypeDataSplat = @{
    TypeName      = 'System.Data.DataTable'
    TypeConverter = 'DataTableDeserializer'
}
Update-TypeData @updateTypeDataSplat

What you're seeing is expected and happens due to serialization and deserialization when transporting data to a remote scope, this is covered in about_Remote_Output in the Deserialized objects section.

A simple demo to help you understand what happens:

# A sample data table with a single row
$dt = [System.Data.DataTable]::new()
$null = $dt.Columns.Add('foo')
$row = $dt.NewRow()
$row.foo = 'bar'
$dt.Rows.Add($row)

# the process that happens when objects are transported to a remote session
$deserialized = [System.Management.Automation.PSSerializer]::Deserialize(
    [System.Management.Automation.PSSerializer]::Serialize($dt))

# the result looks the same:
$deserialized
# but no longer is the same:
$deserialized.PSTypeNames
# Deserialized.System.Data.DataTable
# Deserialized.System.ComponentModel.MarshalByValueComponent
# Deserialized.System.Object

What you should do to workaround this issue is remove the conversion / cast to DataTable in your param block / $using: statement. And, only if you really need a DataTable on the remote scope, you'll need to reconstruct it, for example:

Invoke-Command ..... -ScriptBlock {
    # bring the table to this scope
    $deserializedTable = $using:table
    # recreate it
    $reconstructedDt = [System.Data.DataTable]::new()
    foreach ($column in $deserializedTable.Columns) {
        $null = $reconstructedDt.Columns.Add($column)
    }

    foreach ($row in $deserializedTable) {
        $newrow = $reconstructedDt.NewRow()
        foreach ($column in $deserializedTable.Columns) {
            $newrow[$column] = $row.$column
        }
        $reconstructedDt.Rows.Add($newrow)
    }

    # now you can work with `$reconstructedDt`
}

edited May 15 at 19:12

answered May 14 at 16:09

Santiago Squarzon

65.5k5 gold badges26 silver badges59 bronze badges

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

2 Comments

Vladimir1211 May 15 at 1:42

thank you. In theory, this is probably a good thing, but in fact this solution is type conversion. In this case, it is better to immediately use, for example, Hashtable. After all, there is no such problem with Hashtable. I am interested in whether it is possible to pass System.Data.DataTable without complicating the code, just like other types of String, Hashtable, etc. Again, in theory this is probably correct, but it looks like a PowerShell bug.

Santiago Squarzon May 15 at 1:58

@Vladimir1211 Not a bug, just that the powershell team decided that some objects can be deserialized back to the original type and other can't because of what's explained in the documentation linked in my answer. Hashtable is an example of a type that can be deserialized back without issues. As for conversion, there are other workarounds but not easy ones like PSTypeConverter as shown in: stackoverflow.com/a/79276760/15339544

Collectives™ on Stack Overflow

In PowerShell, I cannot transfer System.Data.DataTable using the invoke-command to the remote host

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related