在 PowerShell 中執行 SQL 查詢

Marion Paul Kenneth Mendoza 2023年1月30日 2022年5月16日

PowerShell PowerShell SQL

在 PowerShell 中使用 Invoke-SqlCmd Cmdlet
在 PowerShell 中執行 CRUD 操作
在 PowerShell 中使用 .NET 框架的替代方法

Windows PowerShell 具有許多用於處理 SQL Server 的單行命令。它們在我們需要快速執行指令碼或測試程式碼的許多開發環境中很有幫助。

本文介紹如何呼叫 SQL 伺服器的命令、執行 CRUD 操作以及查詢 SQL 的其他替代方法。

在 PowerShell 中使用 `Invoke-SqlCmd` Cmdlet

當你連線到 SQL Server 時，我們將使用受信任的連線或經過 SQL Server 身份驗證的使用者。因此，如果我們嘗試登入 SQL Server，我們會在連線時將它們視為前兩個選項。

示例程式碼：

$SQLServer = "SQL-01"
$db1 = "TestDB1"
$qcd = "PRINT 'This is output'"

Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db3 -Query $qcd

Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db1 -Query $qcd -Verbose

Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db1 -Query $qcd -Username "User01" -Password "Password01" -Verbose

我們從三個並排的示例中看到呼叫此函式的兩個基本基礎：我們如何指定我們的憑據以及我們是否要輸出。

無論我們使用 SQL Server 身份驗證還是整合安全，我們的使用者都必須有權使用他們呼叫的物件，例如檢視、表、儲存過程等。

同樣，verbose 可以幫助我們在呼叫 Invoke-SqlCmd 時檢視正在發生的事情的輸出，如果沒有它，我們可能無法獲得所需的確認。

在 PowerShell 中執行 CRUD 操作

我們可以使用這個單行函式來執行許多 SQL 命令，從建立物件到執行 CRUD 操作。例如，在下面的程式碼片段中，我們執行五個語句來完成以下操作：建立表、插入、更新、刪除和選擇一些資料。

示例程式碼：

$SQLServer = "SQL-01"
$db1 = "TestDB1"
$drop_table = "DROP TABLE invokeTable"
$create_table = "CREATE TABLE invokeTable (Id TINYINT, IdData VARCHAR(5))"
$insert_data = "INSERT INTO invokeTable VALUES (1,'A'), (2,'B'), (3,'C'), (4,'E'),(5,'F')"
$update_data = "UPDATE invokeTable SET IdData = 'D' WHERE Id = 4"
$delete_data = "DELETE FROM invokeTable WHERE Id = 5"
$select_data = "SELECT Id, IdData FROM invokeTable"

Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db1 -Query $create_table

Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db1 -Query $insert_data

Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db1 -Query $update_data

Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db1 -Query $delete_data

Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db1 -Query $select_data

在 PowerShell 中使用 `.NET` 框架的替代方法

使用 Invoke-SqlCmd cmdlet 有一些缺點，我們可能更喜歡使用不同的指令碼編寫方法。一個例子是 -QueryTimeout 引數，它是我們在超時之前必須執行的查詢的時間長度。

根據微軟的說法，我們必須指定一個介於 1 和 65535 之間的整數，以避免返回錯誤。可能會出現我們希望將命令超時確定為 0 的特定情況。

同樣，我們可能希望硬編碼可以通過自定義函式或環繞 Invoke-SqlCmd 實現的命令。

function Invoke-SQL {
    param(
        [string] $dataSource = ".\SQLEXPRESS",
        [string] $database = "MasterData",
        [string] $sqlCommand = $(throw "Please specify a query.")
      )

    $connectionString = "Data Source=$dataSource; " +
            "Integrated Security=SSPI; " +
            "Initial Catalog=$database"

    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
    $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
    $connection.Open()

    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataSet) | Out-Null

    $connection.Close()
    $dataSet.Tables

}

除了可以完全控制引數（包括限制某些引數）之外，使用底層 .NET 庫的自定義函式很少會經歷與內建函式一樣多的更改。

如果我們選擇使用諸如 Invoke-SqlCmd 之類的本機函式，我們必須意識到這些可能會更改並重新處理任何呼叫。

使用底層的 .NET 庫，如果發現錯誤或安全漏洞，其中一些可能會發生變化，但我們認為這些變化沒有內建 PowerShell 函式那麼多。SqlClient 名稱空間就是一個很好的例子。

我們多年來一直在使用它，雖然微軟多年來使它變得更加重要，但底層結構卻相匹配。這並不是說它永遠不會被棄用，但是建立在上面的功能已經改變，而它經歷了微小的變化。

Author: Marion Paul Kenneth Mendoza

Marion specializes in anything Microsoft-related and always tries to work and apply code in an IT infrastructure.

在 PowerShell 中使用 Invoke-SqlCmd Cmdlet

在 PowerShell 中執行 CRUD 操作

在 PowerShell 中使用 .NET 框架的替代方法

相關文章 - PowerShell SQL

在 PowerShell 中使用 `Invoke-SqlCmd` Cmdlet

在 PowerShell 中使用 `.NET` 框架的替代方法