在 Powershell 中使用 select SQL 语句检索数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1758779/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Retrieving data using select SQL statement in Powershell
提问by fenster
My goal is to assign the value of the results returned to a variable:
我的目标是将返回的结果的值分配给一个变量：
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=HOME\SQLEXPRESS;Database=master;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "select name from sysdatabases where name = 'tempdb'"
$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]
The value returned should obviously be 'tempdb', so how can I assign this to a variable so this will work:
返回的值显然应该是 'tempdb'，所以我如何将它分配给一个变量，这样才能工作：
Write-output "Database is " $variablename
Desired output: Database is tempdb
所需的输出：数据库是 tempdb
回答by Gordon Bell
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=HOME\SQLEXPRESS;Database=master;Integrated Security=True"
$SqlConnection.Open()
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "select name from sysdatabases where name = 'tempdb'"
$SqlCmd.Connection = $SqlConnection
$dbname = $SqlCmd.ExecuteScalar()
$SqlConnection.Close()
Write-output "Database is " $dbname
回答by Gordon Bell
If you are using SQL Server 2008 you should consider using the cmdlets that are available to PowerShell such as Invoke-SqlCmdthat can be used to execute queries against a SQL Server database. I've used these on a project to automate the process of applying patches to a database and recording what patches have been applied:
如果您使用的是 SQL Server 2008，则应考虑使用 PowerShell 可用的 cmdlet，例如Invoke-SqlCmd可用于对 SQL Server 数据库执行查询的 cmdlet 。我在一个项目中使用了这些来自动化将补丁应用到数据库并记录应用了哪些补丁的过程：
You will first need to use these two commands to make the SQL Server cmdlets available to your session.
您首先需要使用这两个命令使 SQL Server cmdlet 可用于您的会话。
add-pssnapin sqlserverprovidersnapin100
add-pssnapin sqlservercmdletsnapin100
Once they are available you can invoke SQL commands as follows.
一旦它们可用，您就可以调用 SQL 命令，如下所示。
$x = invoke-sqlcmd -query "select name from sysdatabases where name = 'tempdb'"
The variable $xwill hold the results of running the query.
该变量$x将保存运行查询的结果。
Check out http://msdn.microsoft.com/en-us/library/cc281720.aspxfor more details on using the SQL Server cmdlets
查看http://msdn.microsoft.com/en-us/library/cc281720.aspx了解有关使用 SQL Server cmdlet 的更多详细信息
