迹忆客 专注技术分享

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 命名空间就是一个很好的例子。

我们多年来一直在使用它，虽然微软多年来使它变得更加重要，但底层结构却相匹配。这并不是说它永远不会被弃用，但是建立在上面的功能已经改变，而它经历了微小的变化。