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

Piotr L

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

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.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

Zero rows being returned, despite SQL Query

return zero for no rows returned (in a complex query)

query using sqlalchemy returned zero rows

C# - Check if the RESULT returned by a linq query on a DataTable has rows

Query to return zero rows with COUNT(*)

SQL Include zero rows in query

Unable to count the number of rows returned from query

how to get returned rows from a sub query

How to check if WMI Query returned 0 rows?

My SQL query is showing no rows returned error

DataTable.Rows.Find(MultiplePrimaryKey) In Powershell

How to handle zero rows returned when binding data to DataGridView

Why does this SQL query return zero rows?

MySQL simple query returns zero rows

add rows in datatable on runtime with sql query

Return an error when no rows are returned by a query that is expected to return multiple rows

ExecuteReader query with inside of it two ExecuteNonQuery

Limit returned by MS Access query, but calculate all of the rows in query or table

identify rows returned by the first "select" and rows returned by the second (in "union" query) inserting an additional field

Limit the number of rows returned from a query in both oracle and postgres

JPA : Restrict query result number | Number of rows returned

Sum all rows returned in query and use it in each row

Incorrect number of rows returned by JOIN query in Python script

How to eliminate user-defined duplicate rows returned in an SQL query?

How to find the rows count returned from db.Query postgres

Add new rows to a table using id's returned in query

How can I count the numbers of rows that a phalcon query returned?

Get the total number of rows returned of a query using mysql

How to limit returned rows using max characters in sqlite query