ExecuteReader() в скрипте Powershell

Синтаксис правильный, но вы ничего не делаете со значением внутри цикла. Вы захотите сохранить его как-то. Вот пример запуска некоторого базового SQL внутри powershell с двумя разными типами команд (текст/SP) и двумя разными методами выполнения (DataAdapter/DataReader). Любой из них должен работать нормально.

# 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()

Во-первых, если вы просто выполняете какую-то быструю и грязную работу с SQL Server или запускаете скрипты на основе файлов, избавьте себя от массы проблем и просто используйте Invoke-Sqlcmd. Он написан и поддерживается действительно умными людьми, так что, вероятно, сослужит вам хорошую службу.

Если вам нужно выполнить много запросов за короткий период и вы можете извлечь выгоду из повторного использования вашего соединения. Или хотите безопасность/целостность параметризованных запросов, SqlConnection, SqlCommand и SqlDataReader имеют больше смысла.

Принимая во внимание, что PowerShell является конструкцией, ориентированной на конвейер, нам надлежит думать с точки зрения конвейера и эффективно использовать его. Тем не менее, вместо того, чтобы сбрасывать все записи в DataTable только для повторной итерации их ниже по течению, почему бы не использовать динамическую природу PowerShell и передать «обратный вызов» (то есть [ScriptBlock]) для выполнения некоторой операции над каждым IDataRecord при повторении IDataReader.

Для следующей функции Invoke-SqlCommand требуются: строка подключения, запрос и обратный вызов, которые можно использовать для прогнозирования/анализа строк и т. д.

Примечание. Если требуется постоянное значение SqlConnection, просто замените параметр $ConnectionString на $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()
  }
}

Пожалуйста, не используйте это в рабочей среде без указания catch {...}, опущенного здесь для краткости.

Этот формат дает вам возможность выполнять некоторые операции и проекции для каждого IDataRecord И передавать его в конвейер для последующей обработки.

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

Использование New-Object здесь просто для обеспечения согласованного порядка полей без необходимости полагаться на упорядоченную хеш-таблицу и помогает нам легче идентифицировать наши пользовательские PSObject при запуске таких вещей, как Get-Member.

У меня была точно такая же проблема, и я считаю, что причина в следующем (у меня сработало):

Соединение с базой данных не всегда корректно закрывается, например, в случае ошибки. Если он не закрыт, он пропустит цикл while. Измените код на следующий:

     $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() 
     }

Оператор finally всегда выполняется и обеспечивает правильное закрытие соединения.

Ответ ниже, но устарел. Вы должны использовать Invoke-Sqlcmd сейчас.

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

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