PowerShell function will not return DataTable

Question

I've got a PowerShell script on PowerShell v4.0 (Windows 7 x64 SP1) that creates a pretty complex DataTable. I wanted to be able to place that DataTable code anywhere pretty easily, so I decided to wrap it in a simple function, like so:

function Get-UserDataTable
{
    $DataTable = New-Object -TypeName System.Data.DataTable -ArgumentList 'User';

    $NewColumn = $DataTable.Columns.Add('Id',[System.Int32]);
    $NewColumn.AllowDBNull = $false;
    $NewColumn.Unique = $true;

    $NewColumn = $DataTable.Columns.Add('FirstName',[System.String]);
    $NewColumn.MaxLength = 64;

    $NewColumn = $DataTable.Columns.Add('MiddleName',[System.String]);
    $NewColumn.MaxLength = 64;

    $NewColumn = $DataTable.Columns.Add('LastName',[System.String]);
    $NewColumn.MaxLength = 64;

    return $DataTable;
}

However, that code always returns a null object. I tried Write-Output $DataTable, return $DataTable.Copy(), and $DataTable, but the function is still always null.

So, I thought I might try adding some rows. I can always clear the DataTable, and it'd still be less coding this way:

function Get-UserDataTable2
{
    $DataTable = New-Object -TypeName System.Data.DataTable -ArgumentList 'User';

    $NewColumn = $DataTable.Columns.Add('Id',[System.Int32]);
    $NewColumn.AllowDBNull = $false;
    $NewColumn.Unique = $true;

    $NewColumn = $DataTable.Columns.Add('FirstName',[System.String]);
    $NewColumn.MaxLength = 64;

    $NewColumn = $DataTable.Columns.Add('MiddleName',[System.String]);
    $NewColumn.MaxLength = 64;

    $NewColumn = $DataTable.Columns.Add('LastName',[System.String]);
    $NewColumn.MaxLength = 64;

    $NewRow = $DataTable.NewRow();
    $NewRow.Id = 1;
    $NewRow.FirstName = 'Test';
    $NewRow.MiddleName = '';
    $NewRow.LastName = 'User';

    $DataTable.Rows.Add($NewRow);

    $NewRow = $DataTable.NewRow();
    $NewRow.Id = 2;
    $NewRow.FirstName = 'Other';
    $NewRow.MiddleName = 'Test';
    $NewRow.LastName = 'User';

    $DataTable.Rows.Add($NewRow);

    return $DataTable;
}

Nope. This function returns a [System.Object[]] containing individual [System.Data.DataRow]. An object array of DataRows.

How can I return a DataTable from a function in PowerShell?

return ,$DataTable;. DataTable considered as collection, although it does not implement IEnumerable. — user4003407
– user4003407, Commented Feb 18, 2016 at 22:19
Possible duplicate of HashSet in Powershell: Collection was of a fixed size — user4003407
– user4003407, Commented Feb 18, 2016 at 22:23
@PetSerAl return ,$DataTable; appears to work. Totally forgot about that. Enter as answer and I'll accept. — Bacon Bits
– Bacon Bits, Commented Feb 18, 2016 at 22:58
Ok, so, thinking about it some, the answer is the same in the duplicate, but it's not the same question. "Duplicate" means "Duplicate question", not duplicate answer. — Bacon Bits
– Bacon Bits, Commented Feb 19, 2016 at 15:03
From my point of view, the only practical difference between the questions is the collection type HashSet vs DataTable. Rest is the same: you return collection from function and do not see that collection, but individual collection elements packed into array. If you disagree, feel free to answer question by your own answer or find a better duplicate question to close. — user4003407
– user4003407, Commented Feb 19, 2016 at 22:30

Bacon Bits · Accepted Answer · 2016-02-29 14:57:35Z

13

As the link in PerSerAl's comment suggests, the issue is caused because DataTable isn't an enumerable data type. To force it to be enumerable, you can use the unary comma operator to put it into an array as a single element. Arrays are enumerable.

function Get-UserDataTable
{
    $DataTable = New-Object -TypeName System.Data.DataTable -ArgumentList 'User';

    $NewColumn = $DataTable.Columns.Add('Id',[System.Int32]);
    $NewColumn.AllowDBNull = $false;
    $NewColumn.Unique = $true;

    $NewColumn = $DataTable.Columns.Add('FirstName',[System.String]);
    $NewColumn.MaxLength = 64;

    $NewColumn = $DataTable.Columns.Add('MiddleName',[System.String]);
    $NewColumn.MaxLength = 64;

    $NewColumn = $DataTable.Columns.Add('LastName',[System.String]);
    $NewColumn.MaxLength = 64;

    return ,$DataTable;
}

edited Feb 29, 2016 at 14:57

answered Feb 18, 2016 at 20:13

Bacon Bits

32.5k6 gold badges63 silver badges78 bronze badges

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

1 Comment

mklement0 Over a year ago

The solution is effective, but the explanation isn't quite correct: you're not forcing it to be enumerable, you're preventing its enumeration, by using the unary form of , to create a transient single-element wrapper array. The background is that - unfortunately - PowerShell has a hard-coded exception that treats [System.Data.DataTable] as if it implemented the IEnumerable interface, even though it doesn't. Specifically, using such an instance in the PowerShell pipeline is equivalent to using its .Rows property (which returns a collection type that does implement IEnumerable).

Emil · Accepted Answer · 2019-09-03 14:04:02Z

0

This question seem like a duplicate for this one, but you may want to look at the more elaborative info on that answer & below.

answered Sep 3, 2019 at 14:04

Emil

2,3583 gold badges27 silver badges30 bronze badges

Collectives™ on Stack Overflow

PowerShell function will not return DataTable

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related