Geek asks and answers

Can Powershell Receive-Job return a DataSet?

Background Information :

I have an application that makes multiple SQL connections to multiple databases, which currently take a lot of time.

Powershell (.NET) will wait for each SQL-GET function to complete before it can launch the next. I have the impression that I can speed up this application by drastically activating every SQL-GET function in my own background task! Then I will extract data from each job as it is completed. Ideal as a system object DataSet.

The problems are :

When retrieving data from a background job, I can ONLY get a System.Array object . In fact, I am, this is a System.DataSet object . This is necessary because all the logic in the application depends on the DataSet.

Code :

Here is a simple piece of code that will create a sql connection and populate a newly created dataset object with the returned results. Works. The result of $ is a DataSet, and I can handle it well.

$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]

And here is VERY SAME CODE wrapped in the scriptblock parameter of the new background job. Only after calling Receive-Job do I get the 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

Any help would be greatly appreciated.

Research in this way :

Google, , , , . powershell -?

. , - . :

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

.

, , , "" - , !:)

:

, , , $ret_results, Receive-Job, DataSet... DataTable. ... :)

+4
2

powershell . , :

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

. , ?

PS C:\> $output.GetType()

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

, . . , , 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

:

  • , $results.Tables[0] . , , ... , DataTable DataRows... DataSet, , , ?

  • . DataTable , PowerShell DataRows, . , , - ​​ DataRow DataRow.

  • , , , @($results.Tables[0]). , , ( ).

+2

PS, (pid) . , Receive-Job, ( (, string/number/etc), .

- . Runspace - , pid . ( ). :

$script =  {
    $dt = new-object System.Data.DataTable
    $dt.Columns.add() | Out-Null
    $dt.Columns.add() | Out-Null
    $dt.Rows.Add(1,2) | Out-Null
    $dt.Rows.Add(3,4) | Out-Null
    $ds = New-Object System.Data.DataSet
    $ds.Tables.Add($dt)
    Write-Output @{ds = $ds}
}

$PowerShell = [powershell]::Create()
$Runspace = [runspacefactory]::CreateRunspace()
$PowerShell.runspace = $Runspace
$Runspace.Open()

[void]$PowerShell.AddScript($script)
$result = $PowerShell.Invoke()
$result.ds.gettype()

$ script . ( BeginInvoke/EndInvoke, ), , , DataSet/DataTable, PSObject

, Scripting Guy: https://blogs.technet.microsoft.com/heyscriptingguy/2015/11/26/beginning-use-of-powershell-runspaces-part-1/ PoshRSJob - , Runspaces ( )

0

More articles:


All Articles