CODEWITHSUNDEEP
powershelldatatable
Bacon Bits
Bacon Bits

Reputation: 32170

PowerShell function will not return DataTable

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?

Upvotes: 10

Views: 6565

Answers (2)

Emil
Emil

Reputation: 2346

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

Upvotes: 0

Bacon Bits
Bacon Bits

Reputation: 32170

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

Upvotes: 13

Related Questions