ExecuteReader() in Powershell script

Question

I'm trying to read data from a SQL table in a powershell script. I can see the data in reader object, but when reading it using While (readerobject.read()){}, it's not going inside the loop.

Powershell:

 $cmd = $sqlConn.CreateCommand()
 $cmd.CommandText ="SELECT * from user"
 $movedUserDetails = $cmd.ExecuteReader()
 while ($movedUserDetails.Read())
   {
      "[0] : " + $movedUserDetails.GetValue(0)
   }
 $movedUserDetails.Close() 

Answer 1

The syntax is correct, but you're not doing anything with the value once inside the loop. You'll want to persist it somehow. Here's an example of running some basic SQL inside of powershell, with two different types of commands (Text/SP) and two different methods of execution (DataAdapter/DataReader). Either one of each should work fine.

# config
$svr = "serverName"
$db = "databaseName"

# connection
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Server=$svr;Database=$db;Integrated Security=True"
$sqlConnection.Open()

# command A - text
$sqlCmd = New-Object System.Data.SqlClient.SqlCommand
$sqlCmd.Connection = $sqlConnection
$sqlCmd.CommandText = "SELECT name AS TABLE_NAME FROM sys.tables"

# command B - stored procedure
$sqlCmd = New-Object System.Data.SqlClient.SqlCommand
$sqlCmd.Connection = $sqlConnection
$sqlCmd.CommandText = "sys.sp_tables"
$sqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure
$sqlCmd.Parameters.Add("@table_owner", "dbo")

# execute A - data reader
$reader = $sqlCmd.ExecuteReader()
$tables = @()
while ($reader.Read()) {
    $tables += $reader["TABLE_NAME"]
}
$reader.Close()

# execute B - data adapter
$dataTable = New-Object System.Data.DataTable
$sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$sqlAdapter.SelectCommand = $sqlCmd
$sqlAdapter.Fill($dataTable)
$sqlConnection.Close()

Answer 2

First off, if you're just doing some quick & dirty work with SQL Server or running file-based scripts, save yourself a ton of trouble and just use Invoke-Sqlcmd. It's written and maintained by really smart people, so will likely serve you well.

If you're needing to run a lot of queries in a short period and could benefit from reusing your connection. Or want the safety/integrity of parameterized queries, SqlConnection, SqlCommand and SqlDataReader make more sense.

Bearing in mind that PowerShell is a pipeline oriented construct, it behooves us to think in terms of the pipeline and leverage it effectively. That said, rather than dump all of the records into a DataTable only to iterate them again downstream, why not leverage the dynamic nature PowerShell and pass a "callback" (i.e. [ScriptBlock]) to perform some operation on each IDataRecord as you iterate the IDataReader.

The following function Invoke-SqlCommand requires a: Connection String, Query and Callback which can be used for row projection/analysis etc.

Note: If a persisted SqlConnection is required, simply replace the $ConnectionString parameter with $Connection.

function Invoke-SqlCommand {
  param(
    [Parameter(Mandatory=$True,
               ValueFromPipeline=$True,
               ValueFromPipelineByPropertyName=$True,
               HelpMessage="The connection string.")]
    [string] $ConnectionString,

    [Parameter(Mandatory=$True,
               HelpMessage="The query to run.")]
    [string] $Query,

    [Parameter(Mandatory=$True,
               HelpMessage="The work to perform against each IDataRecord.")]
    [scriptblock] $ScriptBlock
  )

  $conn = New-Object System.Data.SqlClient.SqlConnection
  $conn.ConnectionString = $ConnectionString

  $cmd = $conn.CreateCommand()
  $cmd.CommandText = $Query

  try {
    $conn.Open()
    $rd = $cmd.ExecuteReader()

    while($rd.Read()){
        Write-Output (Invoke-Command $ScriptBlock -ArgumentList $rd)
    }  
  } 
  finally {
    $conn.Close()
  }
}

Please do not use this in production without specifying a catch {...}, omitted here for brevity.

This format affords you the opportunity to perform some operation and projection against each IDataRecord AND yield it into the pipeline for downstream processing.

$connectionString = "your connection string"
$query = "SELECT * FROM users"
Invoke-SqlCommand $connectionString $query {
    param(
        [Parameter(Mandatory=$True)]
        [System.Data.SqlClient.SqlDataReader]$rd)

    $obj = New-Object -TypeName PSObject -Property @{ user_id = $rd.GetValue($rd.GetOrdinal("geoname_id"))}
    $obj.psobject.typenames.insert(0,'MyAwesome.Object')

    Write-Output $obj
}

The use of New-Object here is simply to provide us with consistent field ordering without having to rely on an ordered hash table and helps us identify our custom PSObject more easily when running things like Get-Member.

Answer 3

The answer below is a but outdated. You should use Invoke-Sqlcmd now.

Invoke-Sqlcmd -Query $sqlStatement -ConnectionString $ConnectionString;

You might have to do a Install-Module -Name SqlServer to install it.

---

If you don't want to install the SqlServer module or your Powershell version is 2 or lower, you can try with a SqlDataAdapter. I made this Powershell module to fetch records with SQL. It has never failed me

function Invoke-SqlSelect
{
    [CmdletBinding()]
    Param
    ( 
        [ValidateNotNullOrEmpty()] 
        [Parameter(ValueFromPipeline=$True,Mandatory=$True)] 
        [string] $SqlServer,
        [Parameter(ValueFromPipeline=$True,Mandatory=$False)] 
        [string] $Database = "master",
        [ValidateNotNullOrEmpty()] 
        [Parameter(ValueFromPipeline=$True,Mandatory=$True)] 
        [string] $SqlStatement
    )
    $ErrorActionPreference = "Stop"
    
    $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $sqlConnection.ConnectionString = "Server=$SqlServer;Database=$Database;Integrated Security=True"
    
    $sqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $sqlCmd.CommandText = $SqlStatement
    $sqlCmd.Connection = $sqlConnection
    
    $sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $sqlAdapter.SelectCommand = $sqlCmd
    $dataTable = New-Object System.Data.DataTable
    try
    {
        $sqlConnection.Open()
        $sqlOutput = $sqlAdapter.Fill($dataTable)
        Write-Output -Verbose $sqlOutput
        $sqlConnection.Close()
        $sqlConnection.Dispose()
    }
    catch
    {
        Write-Output -Verbose "Error executing SQL on database [$Database] on server [$SqlServer]. Statement: `r`n$SqlStatement"
        return $null
    }
    

    if ($dataTable) { return ,$dataTable } else { return $null }
}

Answer 4

I had exactly the same issue and I believe the reason is the following(worked for me):

The database connection is not always closed correct e.g in an error case. If it is not closed it will skip the while loop. Change your code to the following:

     $sqlConn.Open()
     $cmd = $sqlConn.CreateCommand()
     $cmd.CommandText ="SELECT * from user"
     $movedUserDetails = $cmd.ExecuteReader()
     try
     {
         while ($movedUserDetails.Read())
         {
           "[0] : " + $movedUserDetails.GetValue(0)
         }
     }
     catch
     {
       #log error
     }
     finally
     {
       $sqlConn.Close() 
     }

The finally statement is always executed and will secure that the connection is properly closed.