我已經弄清楚如何使用 PowerShell 運行 SQL 查詢并將它們轉換為我可以使用的陣列。我還想出了如何將變數或陣列的特定子集傳遞給查詢。像這樣：
$LastMonth = [DateTime]::Now#.AddMonths((-1))
# define target database and server
$TargetServer = 'server name'
$TargetDatabase = 'db name'
$ConnectionString = "Server=" $TargetServer ";Database=" $TargetDatabase ";Trusted_Connection=$true;";
$TargetConnection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString);
$TargetConnection.Open();
$sql = "SELECT [AVAILABILITY_SAID_ID]
,[AVAILABILITY_MINUTES]
FROM [dbo].[AVAILABILITY_MINUTES]
where [AVAILABILITY_MONTH] = $($LastMonth.Month) and [AVAILABILITY_YEAR] = $($LastMonth.Year);"
# execute SQL command
$TargetCommand = New-Object System.Data.SqlClient.SqlCommand($sql, $TargetConnection);
$reader = $TargetCommand.ExecuteReader()
$availability = @()
while ($reader.Read())
{
$row = @{}
for ($i = 0; $i -lt $reader.FieldCount; $i )
{
$row[$reader.GetName($i)] = $reader.GetValue($i)
}
$availability = new-object psobject -property $row
}
$reader.Close()
我不知道如何將其作為包含多個專案的 IN 陳述句來執行。我可以得到要傳遞的串列，但我不知道如何在它周圍加上括號，以便查詢真正起作用。
$TargetServer = 'server name'
$TargetDatabase = 'db name'
$ConnectionString = "Server=" $TargetServer ";Database=" $TargetDatabase ";Trusted_Connection=$true;";
$TargetConnection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString);
$TargetConnection.Open();
$sql = "SELECT
incidentid AS EVENT_LIFECYCLE_VALUE,
Priority AS Priority_Code,
keycode AS ASSET_KEY,
Description AS EVENT_DATA,
CreatedDateTime AS [Opened],
ClosedDateTime AS [Closed]
FROM incident
WHERE keycode IN $("'" ($said.Saidkeycode -join "','") "'")
AND Priority IN (1, 2)
AND CONVERT (date, CreatedDateTime) <= DATEADD(DAY, -2, CONVERT (date, GETDATE()))
AND CreatedDateTime >= DATEADD(DAY, -90, GETDATE())
ORDER BY EVENT_LIFECYCLE_VALUE";
$SourceCommand = New-Object System.Data.SqlClient.SqlCommand($sqlCmd, $SourceConnection);
$reader = $SourceCommand.ExecuteReader()
$incidents = @()
while ($reader.Read())
{
$row = @{}
for ($i = 0; $i -lt $reader.FieldCount; $i )
{
$row[$reader.GetName($i)] = $reader.GetValue($i)
}
$incidents = new-object psobject -property $row
}
$reader.Close()
根據我的發現，引數化是答案，但我無法弄清楚如何讓它真正發揮作用。我不確定這是否已損壞，或者我只是無法弄清楚如何從資料集中獲取結果。
$TargetServer = 'server name'
$TargetDatabase = 'db name'
$ConnectionString = "Server=" $TargetServer ";Database=" $TargetDatabase ";Trusted_Connection=$true;";
$TargetConnection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString);
$TargetConnection.Open();
$sqlCmd = $SourceConnection.CreateCommand()
$sqlCmd.Connection = $SourceConnection
$sql = "SELECT
incidentid AS EVENT_LIFECYCLE_VALUE,
Priority AS Priority_Code,
keycode AS ASSET_KEY,
Description AS EVENT_DATA,
CreatedDateTime AS [Opened],
ClosedDateTime AS [Closed]
FROM Cherwell.dbo.Incident
WHERE keycode IN (@SAID)
AND Priority IN (1, 2)
AND CONVERT (date, CreatedDateTime) <= DATEADD(DAY, -2, CONVERT (date, GETDATE()))
AND CreatedDateTime >= DATEADD(DAY, -90, GETDATE())
ORDER BY EVENT_LIFECYCLE_VALUE";
$sqlCmd.CommandText = $sql
$sqlCmd.Parameters.Add("@SAID", [Data.SQLDBType]::VarChar, 4).Value = $("'" ($said.Saidkeycode -join "','") "'")
$sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcmd
$dataSet = New-Object System.Data.DataSet
$sqlAdapter.Fill($dataSet)
uj5u.com熱心網友回復：
這里有幾個選項可供您選擇。我將采用我在您之前的問題中使用的代碼，因為我仍然在我的 IDE 中打開它。
對于分隔串列，這將只是拆分 SQL 中的值。我假設您使用的是（完全）受支持的 SQL Server 版本，因為您沒有提到您不是。這意味著您可以簡單地使用STRING_SPLIT：
$connectionString = 'Server="srvsql2019dev\Sandbox";Database=Sandbox;Trusted_Connection=true;'
$sqlConn = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)
try{
$sqlConn.Open()
$sqlCmd = $sqlConn.CreateCommand()
$sqlCmd.Connection = $sqlConn
$query = "SELECT [name]
FROM sys.databases
WHERE name IN (SELECT [value] FROM STRING_SPLIT(@Databases,','));" #IN using STRING_SPLIT
$databases = Get-Content -Path "./DBNames.txt" #txt file containing database names.
$sqlCmd.CommandText = $query
$sqlCmd.Parameters.Add("@Databases", [System.Data.SqlDbType]::NVarChar,4000).Value = $databases -join "," # Pass the values as a comma delimited list.
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
}
finally{
if ($sqlAdapter -ne $null) { $sqlAdapter.Dispose(); }
if ($sqlConn -ne $null) { $sqlConn.Close(); }
}
$dataSet.Tables
請注意，我將引數定義為4000 個字符長，而不是 4 個或其他一些非常短的值；因為這會導致截斷。
正如我所提到的，另一種方法是使用表型別引數 (TTP)。為此，您首先需要創建TYPE，還需要創建一個存盤程序。由于您在這里使用的是行內查詢，所以我不會在這里介紹。但是，如果您在 中傳遞 100 個值IN，那么移動到 TTP（帶有INDEX）可能會更高效。
另一種方法是使用 json。Json 很容易在 PS ( ConvertTo-Json) 中創建并在 SQL 查詢 ( OPENJSON) 中使用。對于簡單的分隔串列，Json 可能有點過分，但對于多列來說，它比 TVP 更容易，因為不需要創建型別。
uj5u.com熱心網友回復：
這可能不是最好的方法，但這是我最終能夠開始作業的方法。我將正確標點的陣列定義為新變數$insaid，并在 IN 陳述句中使用帶有括號的新變數。
$TargetServer = 'server name'
$TargetDatabase = 'db name'
$ConnectionString = "Server=" $TargetServer ";Database=" $TargetDatabase ";Trusted_Connection=$true;";
$TargetConnection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString);
$TargetConnection.Open();
$insaid = $("'" ($said.Saidkeycode -join "','") "'")
$sql = "SELECT
incidentid AS EVENT_LIFECYCLE_VALUE,
Priority AS Priority_Code,
keycode AS ASSET_KEY,
Description AS EVENT_DATA,
CreatedDateTime AS [Opened],
ClosedDateTime AS [Closed]
FROM incident
WHERE keycode IN ($insaid)
AND Priority IN (1, 2)
AND CONVERT (date, CreatedDateTime) <= DATEADD(DAY, -2, CONVERT (date, GETDATE()))
AND CreatedDateTime >= DATEADD(DAY, -90, GETDATE())
ORDER BY EVENT_LIFECYCLE_VALUE";
$SourceCommand = New-Object System.Data.SqlClient.SqlCommand($sqlCmd, $SourceConnection);
$reader = $SourceCommand.ExecuteReader()
$incidents = @()
while ($reader.Read())
{
$row = @{}
for ($i = 0; $i -lt $reader.FieldCount; $i )
{
$row[$reader.GetName($i)] = $reader.GetValue($i)
}
$incidents = new-object psobject -property $row
}
$reader.Close()
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/490729.html