如何在PowerShell SQL查询中替代变量 [英] How substitute variable in PowerShell SQL query

问题描述

我需要在SQL查询中使用一个变量.这是我的脚本:

I need to use a variable in my SQL query. This is my script:

function SQLQueryWriteToFile([string]$SQLquery, [string]$extractFile, [string]$facility)
{
   #create sql connection to connect to the sql DB
   $sqlConnection = New-Object System.Data.SqlClient.SqlConnection

   $sqlConnection.ConnectionString = "Server=blah;Database=blah_Test;User ID=blah;Password=blah" 
   $sqlConnection.Open()

   #Create a SqlCommand object to define the query
   $sqlCmd = New-Object System.Data.SqlClient.SqlCommand
   $sqlCmd.CommandText = $SQLquery
   $sqlCmd.Connection = $sqlConnection

   #create a SqlAdapter that actually does the work and manages everything
   $sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
   $sqlAdapter.SelectCommand = $sqlCmd
   $sqlAdapter.SelectCommand.CommandTimeout=300  #set timeout for query execution to 5 minutes (60x5=300)

   #create an empty dataSet for the query to fill with its results
   $dataSet = New-Object System.Data.DataSet

   #execute the query and fill the dataSet (then disconnect)
   $sqlAdapter.Fill($dataSet)
   $sqlConnection.Close()

   #dump the data to csv
   $DataSet.Tables[0] | Export-Csv $extractFile #this may not be comma delimited...need to check

} 

#start here

$SQLquery_Privilege = @"
SELECT *

 FROM "blah_Test".dbo.usr_Person_by_Privileges

WHERE 
       Status in ('Active')  
       and Facility = '$[facility]'
       and Last not like ('%test%')
       and Last not like ('%Test%')

--ORDER BY Last
"@


$extractFiles = @("C:\temp\Privileges_H_Bak.csv","C:\temp\Privileges_E_Bak.csv","C:\temp\Privileges_S_Bak.csv")
$facCode = @("H","E","S")

#Loop through list of files and queries for ProfileLong
for($i=0; $i -lt ($facCode.Length); $i++) {
   SQLQueryWriteToFile $SQLquery_Privilege $extractFiles[$i] $facCode[$i]
}

我使用了调试器，传递到函数中的查询确实在其中显示了$ facility变量，没有替换.如何获取变量替换?另外，传递给该函数的$ facility也有一个值.

I used the debugger and the query passed into the function does have the $facility variable show in it without substitution. How do I get it to do the variable substitution? Also, $facility passed into the function has a value.

每个提取文件中显示0个结果.当我只在查询中分别具有每个H，E，S并运行脚本时，它将返回大量的行.

There are 0 results showing in each extract files. When I just had each H,E,S individually in the query, and ran the script, it returns a good amount of rows.

我尝试查看替代变量powershell sql ，但我可以告诉我的查询仍然返回0行，即使我认为我在做他们正在做的事情.

I tried looking at substitute variable powershell sql but I can tell my query is still returning 0 rows even though I think I'm doing what they do.

推荐答案

使用

Use query parameters instead of string substitution (this also protects against SQL injection vectors):

$SQLquery_Privilege = @"
SELECT *

FROM "blah_Test".dbo.usr_Person_by_Privileges

WHERE 
       Status in ('Active')  
       and Facility = @facility
       and Last not like ('%test%')
       and Last not like ('%Test%')

--ORDER BY Last
"@

# inside SQLQueryWriteToFile
$sqlCmd = New-Object System.Data.SqlClient.SqlCommand
$sqlCmd.CommandText = $SQLquery_Privilege
$sqlCmd.Parameters.Add('@facility',$facility)

这篇关于如何在PowerShell SQL查询中替代变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！