SQL Powershell 脚本中的 ExecuteReader()

ExecuteReader() in Powershell script

提问by Geeth

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 脚本中的 SQL 表中读取数据。我可以在 reader 对象中看到数据，但是在使用 读取它时While (readerobject.read()){}，它不会进入循环。

Powershell:

电源外壳：

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

回答by KyleMit

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.

语法是正确的，但是一旦进入循环，您就不会对值执行任何操作。你会想以某种方式坚持下去。这是在 powershell 中运行一些基本 SQL 的示例，使用两种不同类型的命令 (Text/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()

回答by Naha

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:

数据库连接并不总是正确关闭，例如在错误情况下。如果它没有关闭，它将跳过 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() 
     }

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

finally 语句始终被执行，并确保连接正确关闭。

回答by pimbrouwers

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.

首先，如果您只是在使用 SQL Server 做一些快速而肮脏的工作或运行基于文件的脚本，请为自己省去很多麻烦，只需使用Invoke-Sqlcmd. 它是由非常聪明的人编写和维护的，因此可能会很好地为您服务。

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, SqlCommandand SqlDataReadermake more sense.

如果您需要在短时间内运行大量查询并且可以从重用连接中受益。或者想要参数化查询的安全性/完整性SqlConnection，SqlCommand并且SqlDataReader更有意义。

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 DataTableonly 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 IDataRecordas you iterate the IDataReader.

请记住，PowerShell 是一个面向管道的构造，我们应该从管道的角度思考并有效利用它。这就是说，而不是转储的所有记录到一个DataTable只迭代他们再次下行，为什么不利用的动态性质PowerShell和传递一个“回调”（即[ScriptBlock]）在每个执行一些操作IDataRecord，你反复调整IDataReader。

The following function Invoke-SqlCommandrequires a: Connection String, Query and Callbackwhich can be used for row projection/analysis etc.

以下函数Invoke-SqlCommand需要：连接字符串、查询和回调，可用于行投影/分析等。

Note: If a persisted SqlConnectionis required, simply replace the $ConnectionStringparameter with $Connection.

注意：如果需要持久化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()
  }
}

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

请不要在没有指定 a 的情况下在生产中使用它，catch {...}为简洁起见，此处省略。

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

这种格式使您有机会对每个执行一些操作和投影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
}

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

New-Object这里的使用只是为我们提供一致的字段排序，而不必依赖有序的哈希表，并帮助我们PSObject在运行诸如Get-Member.

回答by Rubanov

I tried your code and it worked. Perhaps you can try with a SqlDataAdapter. I made this Powershell module to fetch records with SQL. It has never failed me

我试过你的代码，它奏效了。也许您可以尝试使用SqlDataAdapter. 我制作了这个 Powershell 模块来使用 SQL 获取记录。它从来没有让我失望

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