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?

Answer 1

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;
}