sql-server - 如何在 Powershell 中循环 Datareader 并创建 DataTable

Question

首先让我感谢那些回答我之前问题的人。你们太棒了!!!

这是我的问题:我喜欢查询我的 sql server 存储过程并返回一个数据读取器。不过我想用它创建一个表。我将使用该表通过新的 Powershell OpenXML commandlet 加载 Excel。当我尝试构建数据表时，代码失败。我认为我没有正确加载新对象“System.Object[]”。这是我到目前为止所得到的:

$sqlConnection = new-object System.Data.SqlClient.SqlConnection "server=localhost;database=Demo;Integrated Security=sspi"
$sqlConnection.Open()

#Create a command object
$sqlCommand = $sqlConnection.CreateCommand()
$sqlCommand.CommandText = "EXEC Demo.usp_GetTableValueParameter_Data"

#Execute the Command
$sqlReader = $sqlCommand.ExecuteReader()

#Parse the records

$sqlReader | &{ begin{$values = new-object "System.Object[]" $sqlReader["Name"], $sqlReader["Level_Desc"], $sqlReader["Level"]} process {$_.GetValues($values); $datatable.Rows.Add($values)}}

##$datatable | format-table -autosize

# Close the database connection
$sqlConnection.Close()

#STARTING OPENXML PROCESS
#----------------------------
$xlsFile = "C:\Temp\Data.xlsx"
$datatable | Export-OpenXmlSpreadSheet -OutputPath $xlsFile  -InitialRow 3

Answer 1

翻译 Mladen's answer进入 PowerShell 非常简单:

$sqlConnection = new-object System.Data.SqlClient.SqlConnection "server=localhost;database=Demo;Integrated Security=sspi"
$sqlConnection.Open()

#Create a command object
$sqlCommand = $sqlConnection.CreateCommand()
$sqlCommand.CommandText = "EXEC Demo.usp_GetTableValueParameter_Data"

#Execute the Command
$sqlReader = $sqlCommand.ExecuteReader()

$Datatable = New-Object System.Data.DataTable
$DataTable.Load($SqlReader)

# Close the database connection
$sqlConnection.Close()

#STARTING OPENXML PROCESS
#----------------------------
$xlsFile = "C:\Temp\Data.xlsx"
$datatable | Export-OpenXmlSpreadSheet -OutputPath $xlsFile  -InitialRow 3

但是，如果您只需要返回 DataTable，则无需在命令上调用 ExecuteReader，您可以创建一个 DataAdapter 并使用它来填充 DataTable:

$sqlConnection = new-object System.Data.SqlClient.SqlConnection "server=localhost;database=Demo;Integrated Security=sspi"
$sqlConnection.Open()

#Create a command object
$sqlCommand = $sqlConnection.CreateCommand()
$sqlCommand.CommandText = "EXEC Demo.usp_GetTableValueParameter_Data"

$adapter = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcommand
$dataset = New-Object System.Data.DataSet

$adapter.Fill($dataSet) | out-null

# Close the database connection
$sqlConnection.Close()

$datatable = $dataset.Tables[0]  

#STARTING OPENXML PROCESS
#----------------------------
$xlsFile = "C:\Temp\Data.xlsx"
$datatable | Export-OpenXmlSpreadSheet -OutputPath $xlsFile  -InitialRow 3