可以PowerShell的接收在职返回一个数据集？ [英] Can Powershell Receive-Job return a DataSet?

问题描述

背景信息

我有一个应用程序，使多个数据库，目前需要一个非常非常长的时间来执行多个SQL连接。

I have an application that makes several SQL connections to multiple databases which currently takes a very very long time to execute.

Powershell的（.NET）会等待每个程序的 SQL-GET 的功能来完成，然后才能火断下。我在IM pression我可以解雇每个大大加快这个程序了 SQL-GET 的在自己的后台作业功能的同时！然后，我会检索每个作业的数据，他们完。理想的情况是作为DataSet系统对象。

Powershell (.NET) will wait for each proceeding "SQL-GET" function to finish before it can fire off the next. I am under the impression I can speed this app up dramatically by firing each "SQL-GET" function in their own background job simultaneously!I will then retrieve the data from each job as they finish. Ideally as a DataSet system object.

的问题

在检索从后台作业的数据，我只能设法获得的的的System.Array 的对象返回。我究竟以后，是 System.DataSet 的对象。这是必要的，因为应用程序中的所有逻辑是依赖于一个DataSet对象。

When retrieving the data from the background job, I can ONLY manage to get a System.Array object back. What I am actually after, is a System.DataSet object. This is necessary because all the logic within the app is dependant on a DataSet object.

的code

下面是code，将创建一个SQL连接，并填写结果新创建的DataSet对象返回v.simple片。作品一种享受。在 $结果是一个DataSet对象，我可以很好地处理这一点。

Here is a v.simple slice of code that will create a sql connection and fill a newly created dataset object with the results returned. Works a treat. The $results is a DataSet object and I can manipulate this nicely.

$query = "SELECT * FROM [database]..[table] WHERE column = '123456'"

$Connection = New-Object System.Data.SqlClient.SQLConnection      
$ConnectionString = "Server='SERVER';Database='DATABASE';User ID='SQL_USER';Password='SQL_PASSWORD'"  
$Connection.ConnectionString = $ConnectionString     
$Connection.Open() 
$Command = New-Object system.Data.SqlClient.SqlCommand($Query,$Connection) 
$Adapter = New-Object system.Data.SqlClient.SqlDataAdapter
$Adapter.SelectCommand = $Command
$Connection.Close() 
[System.Data.SqlClient.SqlConnection]::ClearAllPools()

$results = New-Object system.Data.DataSet 

[void]$Adapter.fill($results)

$results.Tables[0]

和这里要说的是同样的code封装到一个新的后台作业的脚本块参数。只有在呼叫接收-工作，我得到一个数组回来，而不是一个数据集。

And here is that VERY SAME CODE wrapped into the scriptblock parameter of a new background job. Only upon calling Receive-Job, I get an array back, not a dataset.

     $test_job = Start-Job -ScriptBlock {

$query = "SELECT * FROM [database]..[table] WHERE column = '123456'"

$Connection = New-Object System.Data.SqlClient.SQLConnection      
$ConnectionString = "Server='SERVER';Database='DATABASE';User ID='SQL_USER';Password='SQL_PASSWORD'"  
$Connection.ConnectionString = $ConnectionString     
$Connection.Open() 
$Command = New-Object system.Data.SqlClient.SqlCommand($Query,$Connection) 
$Adapter = New-Object system.Data.SqlClient.SqlDataAdapter
$Adapter.SelectCommand = $Command
$Connection.Close() 
[System.Data.SqlClient.SqlConnection]::ClearAllPools()

$results = New-Object system.Data.DataSet 

[void]$Adapter.fill($results) 
return $results.Tables[0]

}

Wait-Job $test_job
$ret_results = Receive-Job $test_job

任何帮助将是很大的AP preciated！

Any help would be greatly appreciated!!!

研究迄今

我已经做旧的谷歌，但所有的帖子，博客和文章中，我偶然发现似乎进入EXTREME深度有关管理工作和所有的花里胡哨解决这个问题。它是PowerShell中的基本性质，通过接收工作cmdlet将只返回一个数组？

I have done the old Google, but all of the posts, blogs and articles I stumble across seem to go into EXTREME depth about managing jobs and all the bells and whistles around this. Is it the underlying nature of powershell to ONLY return an array through the receive-job cmdlet?

我读了堆栈后有关回归前pression。以为我是对的东西。尝试：

I have read a stack post about the return expression. Thought I was on to something. Attempted:

• 返回$ results.Tables [0]
• 返回，$ results.Tables [0]
• 返回，$结果

• return $results.Tables[0]
• return ,$results.Tables[0]
• return ,$results

都还是返回数组。

我见过的人，而是非常笨重，手动变换数组回一个数据集对象 - 虽然这看起来很'脏' - 我是迂腐和生活在希望中，必须有一种方式来遍历这个神奇的DataSet对象后台工作，并进入我的当前会话！ ：）

I have seen people, rather cumbersomely, manually transform the array back into a dataset object - though this seems very 'dirty' - I am pedantic and live in hope there must be a way for this magical dataset object to traverse through the background job and into my current session! :)

重申

基本上，我想是有$ ret_results对象从接收在职cmdlet的检索是一个DataSet ......甚至一个DataTable。我不是坐......只是不是一个数组：）

Basically, all I would like is to have the $ret_results object retrieved from the Receive-Job cmdlet to be a DataSet...or even a DataTable. I'll take either...JUST NOT AN ARRAY :)

推荐答案

在PowerShell的，它是常见的一组的任意类型的多个对象在一个集合中返回。考虑这种改变的例子，我建立我自己的表：

In powershell, it is common for a set of more than one objects of an arbitrary type to return in a collection. Consider this altered example where I build my own table:

PS C:\> $job = Start-Job -ScriptBlock {
>>
>> $table = New-Object system.Data.DataTable "MyTable"
>>
>> $col1 = New-Object system.Data.DataColumn MyFirstCol,([string])
>> $col2 = New-Object system.Data.DataColumn MyIntCol,([int])
>>
>> $table.columns.add($col1)
>> $table.columns.add($col2)
>>
>> $row1 = $table.NewRow()
>> $row1.MyFirstCol = "FirstRow"
>> $row1.MyIntCol = 1
>> $row2 = $table.NewRow()
>> $row2.MyFirstCol = "SecondRow"
>> $row2.MyIntCol = 2
>>
>> $table.Rows.Add($row1)
>> $table.Rows.Add($row2)
>>
>> $dataSet = New-Object system.Data.DataSet
>> $dataSet.Tables.Add($table)
>>
>> $dataSet.Tables[0]
>>
>> }
>>
PS C:\> $output = Receive-Job -Job $job

输出好评。那么我们什么得到什么？

Output received. So what did we get?

PS C:\> $output.GetType()

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     Object[]                                 System.Array

这是数组，如你所描述。但是，这是整个对象。如果我们分析它的成员，请他们输送至获取会员？

An array, as you've described. But that's the whole object. What if we analyze its members individually, by piping them to Get-Member?

PS C:\> $output | gm

   TypeName: Deserialized.System.Data.DataRow

Name               MemberType   Definition
----               ----------   ----------
ToString           Method       string ToString(), string ToString(string format, System.IFormatProvider formatProvi...
PSComputerName     NoteProperty System.String PSComputerName=localhost
PSShowComputerName NoteProperty System.Boolean PSShowComputerName=False
RunspaceId         NoteProperty System.Guid RunspaceId=186c51c3-d3a5-404c-9a4a-8ff3d3a7f024
MyFirstCol         Property     System.String {get;set;}
MyIntCol           Property     System.Int32 {get;set;}

PS C:\> $output


RunspaceId : 186c51c3-d3a5-404c-9a4a-8ff3d3a7f024
MyFirstCol : FirstRow
MyIntCol   : 1

RunspaceId : 186c51c3-d3a5-404c-9a4a-8ff3d3a7f024
MyFirstCol : SecondRow
MyIntCol   : 2

考虑以下几点：

Consider the following:

• 在你的工作，你已经指定了 $ results.Tables [0] 应返回。通过指定特定的表迭代，你回来，描述在这种情况下，数据行的表...也许一个DataTable，或对象......而不是一个DataSet像你似乎在期待？

• In your job, you have specified that $results.Tables[0] should be returned. By specifying a particular Tables iterate, you're returning the object that describes that table... perhaps a DataTable, or in this case DataRows... instead of a DataSet like you seem to be expecting?

数据表都行。如果数据表中有多个行，PowerShell的将数据行集合返回它，正如我以上证明。你可能会惊讶地得知，这不是单行返回的情况下 - 它只会返回，而不是DataRow对象的集合的单个DataRow对象。

DataTables have rows. If the DataTable has more than one row, powershell will return it in a collection of DataRows, as I've demonstrated above. You may be surprised to learn that this is not the case for a single row returning -- it will only return the single DataRow object instead of a collection of DataRow objects.

如果这真的是你期望的输出，你可能要迫使它总是被指定为输出@返回集合（$ results.Tables [0]）。这样一来，你总是知道会收集并能妥善处理得到的内容（通过遍历集合管理单个对象）。

If this really is the output you are expecting, you may want to force it to always return in a collection by specifying the output as @($results.Tables[0]). That way, you always know to expect a collection and can handle the resulting content appropriately (by iterating through the collection to manage individual objects).

这篇关于可以PowerShell的接收在职返回一个数据集？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！