UblionSql.psm1
|
function Start-LeftConnectJobSql { param ($taskInformation) if (-not $taskInformation.command) { return @{ message= "No command found" sucess= $false } } $sqlConfiguration = Get-LeftConnectSqlConfiguration $sqlConn = New-Object System.Data.SqlClient.SqlConnection $sqlConn.ConnectionString = $sqlConfiguration.connectionString $ErrorActionPreference = "Stop" try { $sqlConn.Open() $sqlcmd = $sqlConn.CreateCommand() $sqlcmd.Connection = $sqlConn $query = $taskInformation.Command # if a channel id is specified write-host "$taskInformation.ChannelId" if ($taskInformation.ChannelId) { if (-not $sqlConfiguration.channelId) { return } elseif (-not $sqlConfiguration.channelId -eq $taskInformation.ChannelId) { return } } $sqlcmd.CommandText = $query $sqlresult = $sqlcmd.ExecuteReader() $table = new-object System.Data.DataTable $table.Load($sqlresult) $returnValue = @{ data= @($table | select $table.Columns.ColumnName) success= $true } } catch { Log("Connection issue with getting the data. $_") $returnValue = @{ message= $Error[0].Exception success= $false } } $ErrorActionPreference = "Continue" $sqlConn.Close() return $returnValue } $functionsThread = { function Start-LeftConnectJobSql { param ( $taskInformation, $sqlConfiguration) if (-not $taskInformation.command) { return @{ message= "No command found" sucess= $false } } $sqlConn = New-Object System.Data.SqlClient.SqlConnection $sqlConn.ConnectionString = $sqlConfiguration.connectionString $ErrorActionPreference = "Stop" try { $sqlConn.Open() $sqlcmd = $sqlConn.CreateCommand() $sqlcmd.Connection = $sqlConn $query = $taskInformation.Command # if a channel id is specified write-host "$taskInformation.ChannelId" if ($taskInformation.ChannelId) { if (-not $sqlConfiguration.channelId) { return } elseif (-not $sqlConfiguration.channelId -eq $taskInformation.ChannelId) { return } } $sqlcmd.CommandText = $query $sqlresult = $sqlcmd.ExecuteReader() $table = new-object System.Data.DataTable $table.Load($sqlresult) $returnValue = @{ data= @($table | select $table.Columns.ColumnName) success= $true } } catch { Log("Connection issue with getting the data. $_") $returnValue = @{ message= $Error[0].Exception success= $false } } $ErrorActionPreference = "Continue" $sqlConn.Close() return $returnValue } } function Start-LeftConnectJobSqlThread { param ($taskInformation) $sqlConfiguration = Get-LeftConnectSqlConfiguration Start-Job -InitializationScript $functionsThread -ScriptBlock { Start-LeftConnectJobSql -taskInformation $taskInformation -sqlConfiguration $sqlConfiguration } -ArgumentList $taskInformation, $sqlConfiguration | Wait-Job| Receive-Job } function Get-LeftConnectRemoteJob{ $channelId = "" $configuration = Get-LeftConnectSqlConfiguration if ($configuration.channelId) { $channelId = "?channelId=$($configuration.channelId)" } $deliverResult = "api/services/app/RemoteControl/SetDeliveries" $getTask= "api/services/app/RemoteControl/GetAssignment$channelId" #Get the job from the server $job = (Get-LeftConnectResult -request $getTask).result if (-not $job){ return } Log("Receive the following job: $job") if ($job.Action -eq "SQL") { $result = Start-LeftConnectJobSql -taskInformation $job $result | Add-Member -MemberType NoteProperty -Name "Id" -value $job.Id -Force -ErrorAction SilentlyContinue } elseif ($job.Action -eq "FILE") { $result = Start-LeftConnectJobFile -taskInformation $job $result | Add-Member -MemberType NoteProperty -Name "Id" -value $job.Id -Force -ErrorAction SilentlyContinue } elseif ($job.Action -eq "SELFUPDATE") { $result = [PSCustomObject]@{ success = $false message = Start-LeftConnectSelfUpdate } }else { $result = [PSCustomObject]@{ success = $false message = ("There is no handler known for given action: " + $job.Action) } } # Send result to server $result | Add-Member -MemberType NoteProperty -Name "CreationDate" -value (Get-Date).ToString("o") Get-LeftConnectResult -request $deliverResult -body ($result | ConvertTo-Json) if ($result.success) { Remove-Variable result Get-LeftConnectRemoteJob } Remove-Variable result } function Start-LeftConnectSqlQuery{ param ($query) $fakeStart = Start-LeftConnectJobSql -taskInformation @{ Action = "SQL" Command = $query } $fakeStart } Start-LeftConnectJobSqlThread -taskInformation @{command="Select * from table"} |