How to Get the Valid DataTable Row Count Following a SQL Query? RRS feed

  • Question

  • This issue has me stumped.  I am setting a DataTable object to the results a SQL query, however getting the correct value of the number of rows of data proves elusive.  When the SQL query executed returns > 1 row (e.g. 5 rows), the Write-Host statement displays this same value (5).  When the SQL query executed returns exactly 1 row, the Write-Host statement displays the value of 0.  When the SQL query executed returns no rows,  the Write-Host statement displays the value of 0.

    Why is the value of Rows.Count correct when the number of rows >1 and not when there is exactly 1 row? In other words this value appears to be non-deterministic for the case of 0 or 1 rows in the DataTable object.

    I have independently verified the number of rows returned by various SQL queries outsid of the PowerShell script.

    Here is a code snippet:

    function ExecuteSqlQuery {
        $Datatable = New-Object System.Data.DataTable
        $Connection = New-Object System.Data.SQLClient.SQLConnection
        $Connection.ConnectionString = "..."
        $Command = New-Object System.Data.SQLClient.SQLCommand
        $Command.Connection = "..."
        $Command.CommandText = "..."
        $Reader = $Command.ExecuteReader()
        return $Datatable

    $resultsDataTable = New-Object System.Data.DataTable
    $resultsDataTable = ExecuteSqlQuery
    Write-Host ("Result set contains: " + $resultsDataTable.Rows.Count + " rows." )

    Thursday, September 28, 2017 5:51 PM


  • This will eliminate that issue:

    $dt =@(ExecuteSqlQuery 'select * from something')


    Monday, November 27, 2017 10:40 PM

All replies

  • Functions must be defined before they are called.  Place the function at the top of the script.


    Thursday, September 28, 2017 5:56 PM
  • The function does appear in the correct order in the actual script - it is just shown this way for reference.
    Thursday, September 28, 2017 6:46 PM
  • function ExecuteSqlQuery {
    Param($sql) $Datatable = New-Object System.Data.DataTable $Connection = New-Object System.Data.SQLClient.SQLConnection $Connection.ConnectionString = "..." $Connection.Open() $Command = $Connection.CreateCommand() $Command.CommandText = $sql $Reader = $Command.ExecuteReader() $Datatable.Load($Reader) $Connection.Close() ,$Datatable } $dt = ExecuteSqlQuery 'select * from something' $dt.Rows.Count


    • Edited by jrv Thursday, September 28, 2017 7:32 PM
    Thursday, September 28, 2017 7:31 PM
  • I am running into similar issue.

    It appears that when 1 row is returned, it is returned as: System.Data.DataRow

    Where as when multiple rows are returned, it is returned as: System.Object[]

    (Object Array).

    In your case: $resultsDataTable.Count will also work since it is simple array.

    Do look at it using getType()

    So, you can handle it like this:

    if ($resultsDataTable -eq $null) {0}

    elseif ($resultsDataTable.getType().FullName -eq "System.Data.DataRow") {1}

    else {$resultsDataTable.Rows.Count}

    • Edited by hpark21 Monday, November 27, 2017 11:05 PM
    Monday, November 27, 2017 10:30 PM
  • This will eliminate that issue:

    $dt =@(ExecuteSqlQuery 'select * from something')


    Monday, November 27, 2017 10:40 PM
  • If you use my above function this is not necessary because a true table object is returned even with only one row in it.  The return causes enumeration which cause a singleton to be returned.  Adding the comma before the $dt forces the return to only enumerate the array and the first null element is ignored in the return pipeline thus returning the second object which is the table.


    Monday, November 27, 2017 10:47 PM
  • So I had this same issue, I wanted to get the number of data(MDF&NDF) files and number of log files for tempdb

    I exec 

    I created a connection and query VAR called $QDB

    $Tsql = "TSQL code I wanted to run" 

    $TempDBDS = $Qdb.ExecuteWithResults($Tsql) -Ran the TSQL

    $TempdbData=$TempDBDS.Tables[0] -Extracted the data table from the data set that was return

    I then used the measure-object in the pipe line to give me the count result
    $TempDB_DataFile = [int]($TempdbData | Where-Object {$_."File Type" -eq "Rows"} | Measure-Object).count
    $TempDB_LogFiles = [int]($TempdbData | Where-Object {$_."File Type" -eq "LOG"} | Measure-Object).count

    Hope this is of some use to someone :) There are prob better ways but it work for me.

    • Proposed as answer by Pete LakerMVP Saturday, August 3, 2019 2:13 PM
    • Unproposed as answer by jrv Saturday, August 3, 2019 2:19 PM
    Saturday, August 3, 2019 1:16 PM
  • Posting colorized code iss not allowed in this forum. It is unreadable in many browser which is why we supply a code tool on the edit bar.

    It is easiest to get the row count from any data table by just getting the 'Count"


    All Net collections have a count property and it is much faster then using "Measure-Object"


    Saturday, August 3, 2019 1:40 PM
  • You should also avoid adding comments to extremely old threads. IN many cases changes in PowerShell make the topic and answers obsolete.  New answers to old topics can cause confusion.


    Saturday, August 3, 2019 1:42 PM
  • For help for all arriving on this thread the following is the best way to return a table from ANY SQLServer instance. As written it can be executed and will return a DataTable correctly.

    function Get-SQLDatatable{
    [OutputType([System.Data.DataTable])] Param( $Instance = '.', $DataBase = 'master', $Query = 'SELECT * FROM sys.syscolumns' ) $connectionString="Server=$Instance;Database=$DataBase;Integrated Security=True;" Try{ $conn=New-Object System.Data.SqlClient.SqlConnection($connectionString) $conn.Open() $command = $conn.CreateCommand() $command.CommandText=$query $reader=$command.ExecuteReader() $dt=new-object System.Data.DataTable $dt.Load($reader) $conn.Close() return $dt } Catch{ Throw $_ } } $dt = Get-SQLDatatable $dt.Rows.Count $dt = Get-SQLDatatable -DataBase TestDB $dt.Rows.Count # ... etc


    • Proposed as answer by Pete LakerMVP Saturday, August 3, 2019 2:11 PM
    • Edited by jrv Saturday, August 3, 2019 2:12 PM
    Saturday, August 3, 2019 1:55 PM
  • That's what is so unique about these forums. A two year old thread, rekindled for the next generation with up to date goodness.

    Great thread <3

    Got any nice code? If you invest time in coding an elegant, novel or impressive answer on MSDN forums, why not copy it over to TechNet Wiki, for future generations to benefit from! You'll never get archived again, and you could win weekly awards!

    Have you got what it takes to become this month's TechNet Technical Guru? Join a long list of well known community big hitters, show your knowledge and prowess in your favoured technologies!

    Saturday, August 3, 2019 2:12 PM