如何將嵌套的回圈物件附加到Powershell中的WHERE陳述句

基本上，我試圖獲取 5 個 SQL 查詢，并針對數百個物件 ID 的串列運行它們，然后在以每個物件 ID 命名的 XLSX 內的 5 個查詢命名選項卡中匯出結果。我很接近，但我只是不知道如何將這些物件 ID 添加到每個查詢的 WHERE 子句中。如果我打破回圈并包含 $obj，我會得到資料，否則不會得到資料。

$SQLServer = "Servername"
    $SQLDBName = "Database"
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"  #; User ID = $uid; Password = $pwd;
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
 
 
   $SQL_Object_IDs = get-content "C:\PS\INPUT\Obj_IDs.txt"   
 
    $SQL_QUERY1 = 
    "Select [r_object_id] as 'Object ID'
    From [Database].[dbo].[table1]
    Where blah blah blah = ''"
    #$SQL_QUERY2 = Query2.SQL
    #$SQL_QUERY3 = Query3.SQL
    #$SQL_QUERY4 = Query4.SQL
    #$SQL_QUERY5 = Query5.SQL
 
    $SqlQueries = $SQL_QUERY1, =#,$SQL_QUERY2,$SQL_QUERY3,$SQL_QUERY4,$SQL_QUERY5
 
    $ExcelOutputPath = "C:\PS\OUTPUT\COLLATED_OBJ_IDs\"          
    $OutputExcelTabNames= "Obj_ID_Type1","Obj_ID_Type2","Obj_ID_Type3","Obj_ID_Type4","Obj_ID_Type5"          
    ForEach ($obj in $SQL_Object_IDs)
    {
   
            $SheetNo = 0
        
              ForEach ($SqlQuery in $SqlQueries)
              {
         
                    $DestinationPath = $ExcelOutputPath    "\"  $obj ".xlsx"
                    $SqlCmd.CommandText = $SqlQuery
                    $SqlCmd.Connection = $SqlConnection
                    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
                    $SqlAdapter.SelectCommand = $SqlCmd
                    $DataSet = New-Object System.Data.DataSet
                    $SqlAdapter.Fill($DataSet)
 
                    $DataSet.Tables[0] | Export-Excel -Path $DestinationPath -AutoSize -AutoFilter  -BoldTopRow -ClearSheet -WorksheetName $OutputExcelTabNames[$SheetNo]
                
                    $SheetNo  = 1
 
              }
                 
 

uj5u.com熱心網友回復：

將引數傳遞給 T-SQL 查詢的最安全方法是宣告和系結引數：

$SQL_QUERY1 = 
"Select [r_object_id] as 'Object ID'
From [Database].[dbo].[table1]
Where columnName = @columnValue"

# ...

foreach($obj in $SQL_Object_IDs) {

    $sheetNo = 0

    foreach($SqlQuery in $SQLQueries) {
        $DestinationPath = $ExcelOutputPath    "\"  $obj  ".xlsx"
        $SqlCmd.CommandText = $SqlQuery
        $SqlCmd.Connection = $SqlConnection

        # clear any parameters from previous iterations
        $SqlCmd.Parameters.Clear()

        # bind appropriate value to parameter
        #SqlCmd.Parameters.AddWithValue('@columnValue', $obj)

        $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
        $SqlAdapter.SelectCommand = $SqlCmd
        $DataSet = New-Object System.Data.DataSet
        $SqlAdapter.Fill($DataSet)
 
        $DataSet.Tables[0] | Export-Excel -Path $DestinationPath -AutoSize -AutoFilter  -BoldTopRow -ClearSheet -WorksheetName $OutputExcelTabNames[$SheetNo]
    }
}

呼叫$SqlCmd.Parameters.AddWithValue('@columnValue', $obj)將用存盤在 中的值替換@columnValue查詢文本中的占位符$obj。

如果 Object ID 值為numeric，那么您需要在系結值之前轉換$obj為：[int]

# bind appropriate value to parameter
#SqlCmd.Parameters.AddWithValue('@columnValue', [int]$obj)

標籤：

上一篇：Powershell如何決議陣列

下一篇：Powershell替換命令不洗掉換行符