Powershell, DataTable, ExecuteReader, zero rows returned by a query

Question

I use the following PowerShell function to run an arbitrary SQL query against an SQL Server instance, then return whatever data is there:

Function ExecSQLReader([string] $sqlText) {
    $cn = New-Object System.Data.SqlClient.SqlConnection
    $cn.ConnectionString = "Data Source={0};Initial Catalog={1};Integrated Security=SSPI;" -f $fc.server,$fc.database
    $cn.Open()
    $cmd = $cn.CreateCommand()
    $cmd.CommandText = $sqlText
    $cmd.CommandTimeout = 60
    $resTable = New-Object("System.Data.DataTable")
    try {
        $result = $cmd.ExecuteReader()
        $resTable.Load($result)
        $result.Close()
        $cn.Close()
    } catch {
        "=======ERROR in ExecSQLReader========" | DoLog
        "Message: {0}" -f $_.Exception.Message | DoLog
        "SQL: {0}" -f $sqlText | DoLog
       "=======/ERROR in ExecSQLReader=======" | DoLog
    }
    return($resTable)
}

The function works fine when there is data returned by the query.

However, the function does not return any column-related info when there is no data returned by the query.

I need to know column names / types / order etc even if there is no data rows.

I can see that the returned value is different based on resulting record count (which makes things even more complicated):

an empty result for 0 records
a [DataRow] object for a single record
a Collection of [DataRow] objects for more than 1 record

So, the question is: how to make sure that the returned object is always of a "System.Data.DataTable" type?

Or, at least, how to make sure that columns-related information (names, types, order) is always available in function results, regardless of the number of rows returned by the query?

Any hints?

Dan Guzman · Accepted Answer · 2018-04-25 10:44:37Z

2

This is a common gotcha when returning a collection from a PowerShell function. As a workaround, add a comma before the return value:

return(,$resTable)

If no rows are returned by the query, the DataTable will contain the source query schema but with zero rows.

answered Apr 25, 2018 at 10:44

Dan Guzman

46.9k3 gold badges52 silver badges77 bronze badges

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

1 Comment

Piotr L Over a year ago

It works! Now I have a different issue: I pipe the resulting DataObject to Export-Csv and apparently Export-Csv does not export anything (not even column names) if the input rowset is empty. But for this one, I have a workaround (I know column names!) so we're all in the green now. Thank you.

Collectives™ on Stack Overflow

Powershell, DataTable, ExecuteReader, zero rows returned by a query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related