Powershell SQL SELECT 输出到变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22714531/
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
Powershell SQL SELECT output to variable
提问by GreetRufus
I am calling a function to query an SQL table. I only need the results of one cell. I am unable to successfully retrieve the cell data into a variable from the function.
我正在调用一个函数来查询 SQL 表。我只需要一个单元格的结果。我无法成功地将单元格数据从函数中检索到变量中。
For example, If I had a table with the following:
例如，如果我有一个包含以下内容的表：
FeedID Name Address
15 Bill Jones
I would need to capture the FeedID value of '15' into a variable. My SQL statement is only capturing the FeedID but I don't know how to extract the value
我需要将“15”的 FeedID 值捕获到一个变量中。我的 SQL 语句只捕获 FeedID 但我不知道如何提取值
Here is what I have so far:
这是我到目前为止所拥有的：
function Invoke-SQL {
param(
[string] $dataSource = "10.0.100.1",
[string] $database = "Database123",
[string] $sqlCommand = $("SELECT [FeedID] FROM [dbo].[FeedList] WHERE [FeedFileName] = 'filename.txt'")
)
$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
write-output $adapter.Fill($dataSet) | Out-Null
$connection.Close()
$dataSet.Tables
}
$FeedID = Invoke-SQL
$FeedID
回答by Raf
Alternatively you could use the following code, if you are looking for simple return values rather than tables for processing later.
或者，如果您正在寻找简单的返回值而不是稍后处理的表，您可以使用以下代码。
[string] $Server= "10.0.100.1",
[string] $Database = "Database123",
[string] $SQLQuery= $("SELECT [FeedID] FROM [dbo].[FeedList] WHERE [FeedFileName] = 'filename.txt'")
function GenericSqlQuery ($Server, $Database, $SQLQuery) {
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server='$Server';database='$Database';trusted_connection=true;"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = $SQLQuery
$Reader = $Command.ExecuteReader()
while ($Reader.Read()) {
$Reader.GetValue()
}
$Connection.Close()
}
回答by Mike Shepard
It looks like $FeedID (the variable you put the SQL output in) should have a FeedID property (from the single row returned).
看起来 $FeedID（您放入 SQL 输出的变量）应该有一个 FeedID 属性（来自返回的单行）。
Try this:
尝试这个：
$FeedID.FeedID
