sql - 当我有多个结果时，为什么这个 powershell 代码只返回一个结果？

Question

我正在通过 powershell 运行查询。我知道通过 ssms 运行查询有多个结果。为什么变量在powershell中只有一个结果？

我使用了许多方法来执行此查询，我终于让它工作了，但无法获得查询的所有结果。

[string] $Server= "mochqdb01";
[string] $Database = "MoCApp.Models.Item+ItemDBContext";
[string] $SQLQuery= $("Select smEmail from Items where DateRequested >= dateadd(day,datediff(day,1,GETDATE()),0)");

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$Server;Database=$Database;Integrated Security=True"
$SqlConnection.Open()
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SQLQuery
$SqlCmd.Connection = $SqlConnection
$dbname = $SqlCmd.ExecuteScalar()
$SqlConnection.Close()
Write-output "Database is " $dbname

输出：数据库是 [e-mail]

应该有多个结果。我应该保存到数组中吗？

我实际上想将结果保存为这种格式。

Send-ToEmail -email "[e-mail]","[e-mail]";这可能吗？

标签： sqldatabasepowershell

Answer 1

ExecuteScalar() 返回第一个结果集第一行的第一列。你需要ExecuteReader()。例如

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$Server;Database=$Database;Integrated Security=True"
$SqlConnection.Open()
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SQLQuery
$SqlCmd.Connection = $SqlConnection
$rdr = $SqlCmd.ExecuteReader()
while ($rdr.Read())
{
   $smMail = $rdr[0]
   write-output "email is $smMail"
}
$rdr.Close()
$SqlConnection.Close()