使用 PowerShell 查询 Microsoft SQL Server (MSSQL) 数据库

在本文中，我们将讨论连接到 Microsoft SQL Server 并从 PowerShell 运行 SQL 查询的所有有效方法。使用 PowerShell 操作 SQL Server 的方法有很多种，当您研究 Web 上的大量文章时，很容易感到困惑，因为它们都描述了不同的方法，即使是经验丰富的管理员也可能会有疑问。

使用 System.Data.OleDb 在 PowerShell 中进行 T-SQL 查询

由于 PowerShell 可以访问 .NET Framework 类，因此您可以使用 System.Data.OleDb 中的类来执行 T-SQL 查询。

以下是使用 System.Data.OleDb 类连接 SQL Server 的示例 PowerShell 脚本。让我们对 MS SQL 数据库中的表运行 SELECT 查询：

$dataSource = “lon-sql01\testdb”
$database = “master”
$sql = “SELECT * FROM sysdatabases”
$auth = “Integrated Security=SSPI;”
$connectionString = “Provider=sqloledb; ” +
“Data Source=$dataSource; “ +
“Initial Catalog=$database; “ +
“$auth; “
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
$command = New-Object System.Data.OleDb.OleDbCommand $sql,$connection
$connection.Open()
$adapter = New-Object System.Data.OleDb.OleDbDataAdapter $command
$dataset = New-Object System.Data.DataSet
[void] $adapter.Fill($dataSet)
$connection.Close()
$rows=($dataset.Tables | Select-Object -Expand Rows)
echo $rows

以下是针对 MSSQL 数据库执行 INSERT/UPDATE/DELETE 查询的 PowerShell 脚本示例：

$dataSource = “lon-sql01\testdb”
$database = “test”
$sql = "insert into test_table (test_col) Values ('Test')"
$auth = “Integrated Security=SSPI;”
$connectionString = “Provider=sqloledb; ” +
“Data Source=$dataSource; ” +
“Initial Catalog=$database; ” +
“$auth; “
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
$command = New-Object System.Data.OleDb.OleDbCommand $sql,$connection
$connection.Open()
$command = New-Object data.OleDb.OleDbCommand $sql
$command.connection = $connection
$rowsAffected = $command.ExecuteNonQuery()

这

$rowsAffected

变量包含添加或更改的行数。要运行更新或删除查询，只需更改 SQL 查询的行

$sql

多变的。

使用 System.Data.SqlClient 类在 PowerShell 中运行 SQL 查询

要从 PowerShell 访问 MS SQL Server，您可以使用另一个内置 .NET 类 - System.Data.SqlClient。以下是使用 SqlClient 在 PowerShell 脚本中执行 SELECT 查询的示例：

$server = "lon-sql01\testdb"
$database = "Test"
$sql = "select * from test_table"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$server;Database=$database;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $sql
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]

INSERT/DELETE/UPDATE 查询的示例：

$server = "lon-sql01\testdb"
$database = "Test"
$sql = "insert into test_table (test_col) Values ('Test')"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$server;Database=$database;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $sql
$SqlCmd.Connection = $SqlConnection
$SqlConnection.Open()
$rowsAffected = $SqlCmd.ExecuteNonQuery();
$SqlConnection.Close()

注意。包含 SqlClient 类的代码与包含 OleDB 的代码非常相似。这些类的工作方式类似：

1. 创建一个MSSQL服务器连接对象；

2. 创建带有 SQL 查询的对象，并将连接对象分配给它；

3. 然后，在运行 SELECT 查询的情况下，将创建一个适配器对象，并在该对象的上下文中执行查询；

4. 在运行 INSERT/UPDATE/DELETE 查询的情况下，具有查询的对象（包含连接对象）执行

   ExecuteNonQuery()

   方法。

使用 SQL Server Management Studio 模块在 PowerShell 中进行 SQL 查询

要使用 Microsoft.SqlServer.Smo (SMO) 类，您的计算机上必须安装 SQL Server Management Studio 。

加载 SMO 模块，创建一个新的服务器对象，然后运行 SELECT 查询：

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");
$serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "lon-sql01\testdb"
$results = $serverInstance.Databases['test'].ExecuteWithResults('select * from test_table')
foreach ($res in $results.Tables) {
$nbsp;echo $res
}

对于插入/更新/删除查询，运行

ExecuteNonQuery

$db = $serverInstance.Databases['test']
$db.ExecuteNonQuery("insert into test_table (test_col) Values ('123456')")

注意。您还可以通过 NuGet 包管理器安装 SMO 库：

1. 下载nuget.exe https://www.nuget.org/downloads；

2. 以管理员身份运行PowerShell，进入nuget.exe所在目录；

3. 跑步：

   .\nuget.exe Install Microsoft.SqlServer.SqlManagementObjects

   .
   

   

4. 当前目录中将出现包含所有 DLL 的 Microsoft.SqlServer.SqlManagementObjects 文件夹；

5. 将 SMO 库从 DLL 文件加载到 PowerShell 会话中。将其添加到您的脚本中：

add-type -Path "C:\Users\username\Downloads\Microsoft.SqlServer.SqlManagementObjects.150.18208.0\lib\net45\Microsoft.SqlServer.Smo.dll"

然后 SMO 课程就可以使用了。

从 SQLServer PowerShell 模块调用 Sqlcmd Cmdlet

要使用 Invoke-Sqlcmd cmdlet，请安装 SqlServer for PowerShell 模块。以管理员权限运行PowerShell并执行命令：

Install-Module -Name SqlServer

（按 Y，然后按 ENTER 接受安装程序通知。）

安装完成后，您可以通过运行以下命令来确保模块已正确安装：

Get-Module SqlServer -ListAvailable

与从 PowerShell 连接到 Microsoft SQL Server 的其他方式相比，Invoke-Sqlcmd cmdlet 更简单、更直观。 Invoke-Sqlcmd 对 SELECT 和 INSERT/UPDATE/DELETE 查询使用相同的语法。

以下是选择查询的示例：

Invoke-Sqlcmd -ServerInstance "lon-sql01\testdb" -Query "sp_who"

这是 INSERT 查询的示例：

Invoke-Sqlcmd -ServerInstance "lon-sql01\testdb" -Database "test1" -Query "insert into test_table (test_col) Values (‘123321’)"

与其他方法不同的是，查询

Invoke-Sqlcmd

始终设置在

-Query

范围。

您应该使用哪个 SQL 连接选项？

在 oledb/smo/sqlclient/invoke-sqlcmd 之间进行选择取决于任务以及要运行 PowerShell 脚本的环境。

如果您想将脚本部署到多个服务器（例如，您的脚本在本地收集监控数据），那么使用 SMO 或 SqlServer PowerShell 模块（Invoke-SQLcmd）是不合理的，因为您必须在远程主机上安装额外的软件包才能运行脚本，如果有很多服务器，最好避免使用。

反过来，SqlServer for PowerShell 模块提供了许多其他 cmdlet 来与 SQL Server 配合使用（您可以在此处了解更多信息：https://docs.microsoft.com/en-us/powershell/module/sqlserver）。该模块包含更多用于管理 SQL Server 本身的命令。

如果您的脚本将执行非管理任务（例如，负责业务逻辑的某些部分），则值得使用 System.Data.SqlClient/SMO，因为它们提供了更方便的开发工具。 OleDB 的一个优点是它不仅可以与 SQL Server 一起使用，还可以与 Access/Oracle/Firebird/Interbase 一起使用。