Button - Start Job, how to multiple arguments, how to work with objects

Ask questions about creating Graphical User Interfaces (GUI) in PowerShell and using WinForms controls.
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
This topic is 5 years and 9 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
User avatar
Pallas
Posts: 14
Last visit: Fri Nov 23, 2018 4:24 am

Button - Start Job, how to multiple arguments, how to work with objects

Post by Pallas »

Hi,

Although I read carefully "PowerShell Studio: Creating Responsive Forms" and the two topics concerning this issue "Job Tracker help needed" and "Button - Start Job, how to pass arguments" I can not figure out how to pass my working code (click event) to your Button - Start Job click event.
I need to pass a bunch of (gloabl) variables + I need to build a new SMObackup object. I figured that I need to fill -ArgumentList and use Param ($Argument1) #Pass any arguments using the ArgumentList parameter. I am lost here. Any help would be greatly appreciated.

My working Code. But it is synchronous, which is not of any help when a db backup takes hours:

  1. $buttonBackupDB_Click= {   
  2.     $Global:FileName = $textbox1_FN.Text
  3.     $Server = $global:MSSQLServer
  4.     $Database = $Global:SelectedDBName
  5.     $Path = $Global:Path
  6.     $Folder = $Global:CC + '\'
  7.     $FileName = $Global:FileName
  8.     $BackupSetName = $Global:BackupSetName
  9.     $Extension = '.bak'
  10.     $FilePath = $Path + $Folder + $FileName + $Extension
  11.     $labelPathToBackupFile.Text = $FilePath #Debug welchen Pfad habe ich zusammen gebaut
  12.     #ab hier smoBackup Objekt
  13.     $smoBackup = New-Object Microsoft.SqlServer.Management.Smo.Backup # SMO Backup Objekt
  14.     $smoBackup.Action = "Database"
  15.     $smoBackup.BackupSetDescription = "Full (copyonly)backup of $($Database)"
  16.     $smoBackup.BackupSetName = $BackupSetName
  17.     $smoBackup.Database = $Database
  18.     $smoBackup.Copyonly = "True"
  19.     $smoBackup.MediaDescription = "Disk"
  20.     $smoBackup.CompressionOption = "1"
  21.     $smoBackup.PercentCompleteNotification = "10"
  22.     $smoBackup.Devices.AddDevice($FilePath, "File")
  23.        
  24.     Try
  25.     {
  26.         $smoBackup.SqlBackup($server)          
  27.     }  
  28.     Catch
  29.     {
  30.         $textbox_out.Text = $_.Exception.InnerException
  31.     }
  32.     <#$Global:FileName = $textbox1_FN.Text
  33.     $Server = $global:MSSQLServer
  34.     $Database = $Global:SelectedDBName
  35.     $Path = $Global:Path
  36.     $Folder = $Global:CC + '\'
  37.     $FileName = $Global:FileName
  38.     $Extension = '.bak'
  39.     $FilePath = $Path + $Folder + $FileName + $Extension
  40.     $labelPathToBackupFile.Text = $FilePath #Debug welchen Pfad habe ich zusammen gebaut#>
[/size][/size]

Not working:

  1. [size=85]$buttonStartJob_Click={
  2.    
  3.     $buttonStartJob.Enabled = $false   
  4.    
  5.     #Create a New Job using the Job Tracker
  6.     Add-JobTracker -Name 'BackUpDBJob' `
  7.                    -JobScript {
  8.         #--------------------------------------------------
  9.         #TODO: Set a script block
  10.         #Important: Do not access form controls from this script block.
  11.        
  12.         #ab hier smoBackup Objekt
  13.         $smoBackup = New-Object Microsoft.SqlServer.Management.Smo.Backup # SMO Backup Objekt
  14.         $smoBackup.Action = "Database"
  15.         $smoBackup.BackupSetDescription = "Full (copyonly)backup of $($Database)"
  16.         $smoBackup.BackupSetName = $BackupSetName
  17.         $smoBackup.Database = $Database
  18.         $smoBackup.Copyonly = "True" #verändert die DatabaseBackupLSN und bringt die scheduled inkrementellen Backups nicht durcheinander
  19.         $smoBackup.MediaDescription = "Disk"
  20.         $smoBackup.CompressionOption = "1"
  21.         $smoBackup.PercentCompleteNotification = "10"
  22.         $smoBackup.Devices.AddDevice($FilePath, "File")
  23.         $smoBackup.SqlBackup($server)
  24.        
  25.         Param ($Argument1) #Pass any arguments using the ArgumentList parameter
  26.        
  27.        
  28.        
  29.         #--------------------------------------------------
  30.     }`
  31.     $env:FileName = $textbox1_FN.Text
  32.     $env:Server = $global:MSSQLServer
  33.     $wnv:Database = $Global:SelectedDBName
  34.     $Path = $Global:Path
  35.     $Folder = $Global:CC + '\'
  36.     $FileName = $Global:FileName
  37.     $BackupSetName = $Global:BackupSetName
  38.     $Extension = '.bak'
  39.     $FilePath = $Path + $Folder + $FileName + $Extension
  40.     $labelPathToBackupFile.Text = $FilePath #Debug welchen Pfad habe ich zusammen gebaut
  41.     -Argumentlist #WHAT GOES HERE AND HOW?
  42.    
  43.     -CompletedScript {
  44.         Param($Job)
  45.         #$results = Receive-Job -Job $Job
  46.         #Enable the Button
  47.         $buttonStartJob.ImageIndex = -1
  48.         $buttonStartJob.Enabled = $true
  49.     }`
  50.     -UpdateScript {
  51.         Param($Job)
  52.         #$results = Receive-Job -Job $Job -Keep
  53.         #Animate the Button
  54.         if($null -ne $buttonStartJob.ImageList)
  55.         {
  56.             if($buttonStartJob.ImageIndex -lt $buttonStartJob.ImageList.Images.Count - 1)
  57.             {
  58.                 $buttonStartJob.ImageIndex += 1
  59.             }
  60.             else
  61.             {
  62.                 $buttonStartJob.ImageIndex = 0     
  63.             }
  64.         }
  65.     }`
  66.    
  67. }
  68. [/size]
[/i]
User avatar
mxtrinidad
Posts: 399
Last visit: Tue May 16, 2023 6:52 am

Re: Button - Start Job, how to multiple arguments, how to work with objects

Post by mxtrinidad »

I would say the simplest way would be to build a scriptblock with the SMO backup code, then use the Start-Job to execute in the background. Then, the button execute the code until the process completes.

I will share some code I use for an Azure sample that may be adaptable to your app:

Code: Select all

$buttonRunSMOBackup_Click={
	#TODO: Place custom script here
		$DisplayText.Text = "Display Information`r`n";
	
	## - Setup job to execute in backgroundL:
	$jobScript = {
		## - SMO Backup Code below:
		:
		:
	};
	
	$DisplayText.AppendText("Running Job - SMO Backup`r`n");
	Start-Job -ScriptBlock $jobScript -Name SMOBackup1 
	
	## - Check for job completion:
	do
	{
		$DisplayText.AppendText("Checking SMO Backup Completion`r`n");
		Start-Sleep -Seconds 5;
	}
	while ((Get-Job -Name 'SMOBackup1').State -ne 'Completed');
	$result1 = Get-Job -Name 'SMOBackup1';
	
	## - Display Job Status at EOJ:
	[array]$SMOJobResults = (Get-Job -Name 'SMOBackup1' | Receive-Job);
	
	## - Display Job Results in RichTextBox:
	$DisplayText.AppendText("Displaying Asynch Job Results:`r`n");
	$DisplayText.AppendText(($SMOJobResults | Format-Table | Out-String -Width 1000));
	
	## - Display Job result in Datagridview:
	$datagridview1.DataSource = ConvertTo-DataTable -InputObject $SMOJobResults;
	$DisplayText.AppendText("Processing Completed`r`n");
	$DisplayText.AppendText((Get-Date).ToString("MMddyyyy_HHmm"));
}
This sample code was meant to display the results to both RichTextBox and a Datagridview.
I'm hoping it will help in any way.

Let us know.

Just make sure your SMO Backup code runs before integrating it to the form and make sure DLL's are in place with the application or the system you're deploying the form.

:)
User avatar
davidc
Posts: 5913
Last visit: Mon Jul 08, 2019 8:55 am
Been upvoted: 2 times

Re: Button - Start Job, how to multiple arguments, how to work with objects

Post by davidc »

[TOPIC MOVED TO POWERSHELL GUIS FORUM BY MODERATOR]
David
SAPIEN Technologies, Inc.
User avatar
Pallas
Posts: 14
Last visit: Fri Nov 23, 2018 4:24 am

Re: Button - Start Job, how to multiple arguments, how to work with objects

Post by Pallas »

mxtrinidad wrote: Wed Jun 13, 2018 7:55 am I would say the simplest way would be to build a scriptblock with the SMO backup code, then use the Start-Job to execute in the background. Then, the button execute the code until the process completes.

...
That seems an elegant solution. So I sacrifice the use of Button - Start Job, take what is working, right now (myoldbutton click event) and build my own job, job-recieve, etc. This sample code looks promising. Thank you!!! I will work on this!
User avatar
mxtrinidad
Posts: 399
Last visit: Tue May 16, 2023 6:52 am

Re: Button - Start Job, how to multiple arguments, how to work with objects

Post by mxtrinidad »

I have to say that using the "Button - Start Job" is useful. I use it to demo executing PowerShell Core in a SAPIEN Windows Forms. And now, I'm talking about cross-platform scripting possibilities.

Don't hesitate to contact us!
User avatar
Pallas
Posts: 14
Last visit: Fri Nov 23, 2018 4:24 am

Re: Button - Start Job, how to multiple arguments, how to work with objects

Post by Pallas »

works:



  1. [size=50]$buttonBackupDBJob_Click= {
  2.     #hier wird die eigentliche Sicherung per Knopfdruck durchgeführt
  3.    
  4.     $Global:FileName = $textbox1_FN.Text
  5.     $Server = $global:MSSQLServer
  6.     $Database = $Global:SelectedDBName
  7.     $Path = $Global:Path
  8.     $Folder = $Global:CC + '\'
  9.     $FileName = $Global:FileName
  10.     $BackupSetName = $Global:BackupSetName
  11.     $Extension = '.bak'
  12.     $FilePath = $Path + $Folder + $FileName + $Extension
  13.     $labelPathToBackupFile.Text = $FilePath #Debug welchen Pfad habe ich zusammen gebaut
  14.     #ab hier smoBackup Objekt
  15.     $smoBackup = New-Object Microsoft.SqlServer.Management.Smo.Backup # SMO Backup Objekt
  16.     $smoBackup.Action = "Database"
  17.     $smoBackup.BackupSetDescription = "Full (copyonly)backup of $($Database)"
  18.     $smoBackup.BackupSetName = $BackupSetName
  19.     $smoBackup.Database = $Database
  20.     $smoBackup.Copyonly = "True" #verändert die DatabaseBackupLSN und bringt die scheduled inkrementellen Backups nicht durcheinander
  21.     $smoBackup.MediaDescription = "Disk"
  22.     $smoBackup.CompressionOption = "1"
  23.     $smoBackup.PercentCompleteNotification = "10"
  24.     $smoBackup.Devices.AddDevice($FilePath, "File")
  25.    
  26.     $smoBackup = New-Object Microsoft.SqlServer.Management.Smo.Backup # SMO Backup Objekt
  27.     $smoBackup.Action = "Database"
  28.     $smoBackup.BackupSetDescription = "Full (copyonly)backup of $($Database)"
  29.     $smoBackup.BackupSetName = $BackupSetName
  30.     $smoBackup.Database = $Database
  31.     $smoBackup.Copyonly = "True" #verändert die DatabaseBackupLSN und bringt die scheduled inkrementellen Backups nicht durcheinander
  32.     $smoBackup.MediaDescription = "Disk"
  33.     $smoBackup.CompressionOption = "1"
  34.     $smoBackup.PercentCompleteNotification = "10"
  35.     $smoBackup.Devices.AddDevice($FilePath, "File")
  36.    
  37.      $smoBackup.SqlBackup($server) 
  38.    
  39. }[/size]
[/i]
User avatar
Pallas
Posts: 14
Last visit: Fri Nov 23, 2018 4:24 am

Re: Button - Start Job, how to multiple arguments, how to work with objects

Post by Pallas »

does NOT backup:
Error:
ERROR: Exception calling "SqlBackup" with "1" argument(s): "Backup operation failed."
ERROR: + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
ERROR: + FullyQualifiedErrorId : FailedOperationException
ERROR: + PSComputerName : localhost
ERROR:

Code: Select all

$buttonBackupDBJob_Click= {
	#hier wird die eigentliche Sicherung per Knopfdruck durchgeführt
	
	$Global:FileName = $textbox1_FN.Text
	$Server = $global:MSSQLServer
	$Database = $Global:SelectedDBName
	$Path = $Global:Path
	$Folder = $Global:CC + '\'
	$FileName = $Global:FileName
	$BackupSetName = $Global:BackupSetName
	$Extension = '.bak'
	$FilePath = $Path + $Folder + $FileName + $Extension
	$labelPathToBackupFile.Text = $FilePath #Debug welchen Pfad habe ich zusammen gebaut

	
	
	$richtextbox_out.Text = "Display Information`r`n";
	
	## - Setup job to execute in background:
	## - SMO Backup Code below:
	
	$jobScript = {
	#Load SMO assemblies
	[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null #SMO #das SMO Objekt holen und den Output nach /dev/null
	[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
	$smoBackup = New-Object Microsoft.SqlServer.Management.Smo.Backup # SMO Backup Objekt
	$smoBackup.Action = "Database"
	$smoBackup.BackupSetDescription = "Full (copyonly)backup of $($Database)"
	$smoBackup.BackupSetName = $BackupSetName
	$smoBackup.Database = $Database
	$smoBackup.Copyonly = "True" #verändert die DatabaseBackupLSN und bringt die scheduled inkrementellen Backups nicht durcheinander
	$smoBackup.MediaDescription = "Disk"
	$smoBackup.CompressionOption = "1"
	$smoBackup.PercentCompleteNotification = "10"
	$smoBackup.Devices.AddDevice($FilePath, "File")
	
	 $smoBackup.SqlBackup($server)
	};
	
	$richtextbox_out.AppendText("Running Job - SMO Backup`r`n");
		Start-Job -ScriptBlock $jobScript -Name SMOBackup1
	## - Check for job completion:
	do
	{
		$richtextbox_out.AppendText("Checking SMO Backup Completion`r`n");
		Start-Sleep -Seconds 5;
	}
	while ((Get-Job -Name 'SMOBackup1').State -ne 'Completed');
	$result1 = Get-Job -Name 'SMOBackup1';
	
	## - Display Job Status at EOJ:
	[array]$SMOJobResults = (Get-Job -Name 'SMOBackup1' | Receive-Job);
	
	## - Display Job Results in RichTextBox:
	$richtextbox_out.AppendText("Displaying Asynch Job Results:`r`n");
	$richtextbox_out.AppendText(($SMOJobResults | Format-Table | Out-String -Width 1000));
	
	## - Display Job result in Datagridview:
	#$datagridview1.DataSource = ConvertTo-DataTable -InputObject $SMOJobResults;
	$richtextbox_out.AppendText("Processing Completed`r`n");
	$richtextbox_out.AppendText((Get-Date).ToString("MMddyyyy_HHmm"));
}
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Button - Start Job, how to multiple arguments, how to work with objects

Post by jvierra »

Run the script as a job at a command prompt until you have it fully debugged.

Note that none of the variables in the job script are defined. You have not created a Param statement and ther eis no "ArgumentList"

Code: Select all

$jobScript = {
    Param(
        $Database,
        $BackupSetName,
        $FilePath,
        $Server
    )
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
    $smoBackup = New-Object Microsoft.SqlServer.Management.Smo.Backup 
    $smoBackup.Action = 'Database'
    $smoBackup.BackupSetDescription = "Full (copyonly)backup of $Database"
    $smoBackup.BackupSetName = $BackupSetName
    $smoBackup.Database = $Database
    $smoBackup.Copyonly = $true
    $smoBackup.MediaDescription = 'Disk'
    $smoBackup.CompressionOption = 1
    $smoBackup.PercentCompleteNotification = 10
    $smoBackup.Devices.AddDevice($FilePath, 'File')
    $smoBackup.SqlBackup($Server)
}
$argList = @(
    $Database,
    $BackupSetName,
    $FilePath,
    $Server
)
Start-Job -ScriptBlock $jobScript -Name SMOBackup1 -ArgumentList $argList
User avatar
Pallas
Posts: 14
Last visit: Fri Nov 23, 2018 4:24 am

Re: Button - Start Job, how to multiple arguments, how to work with objects

Post by Pallas »

Hi,

I was on that argument list, but I would have missed the Param. I am still very new to this, but it works. Sort off. I ditched the data grid view, because it only returned errors and I managed to make the rich text box to scroll down. When backing up larger databases the application will close but stay minimized and never come back. At least if I run it from Power Shell Studio.
I will attach what I have for the record just in case it could help someone.
Things to do:

1. Take the working code and try to use Sapiens Start Job Button. I hope it will create a more user friendly expierience with long running backups. (Level: medium (for me)
2. I would love to get a progress bar running. If you have a great idea how to do that easy I would be glad. I do have some t-sql query which will return what the SQL Server thinks where your backup is. I would need to extract that percentage and put that into a progress bar (Level: very hard (for me))

SQL Code to query MS SQL Server for the state of backup (Name: $BackupSetName)

Code: Select all

$button1_Click={
	#TODO: Place custom script here
	$Server = $global:MSSQLServer
	$Database = $Global:SelectedDBName
	$BackupSetName =$Global:BackupSetName
	$SQLQuery = "SELECT Session_id as SPID, Command , query.text AS 'Query Text', Start_Time, Percent_Complete, dateadd(second,total_elapsed_time/1000, getdate()) as 'Total Elapsed Time',
		dateadd(second,estimated_completion_time/1000, getdate()) as 'Estimated Completion Time'
		FROM sys.dm_exec_requests request CROSS APPLY sys.dm_exec_sql_text(request.sql_handle) query
		WHERE request.command in ('BACKUP DATABASE') and query.text like '%WITH name=%$BackupSetName'"
	$myResult = Invoke-Sqlcmd -ServerInstance $Server -Database $Database -Query $SQLQuery
	$richtextbox_out.Text = $myResult
	
}
----------------------------------------------------------Working code-------------------------------------

Code: Select all

$buttonBackupDBJob_Click= {
	#hier wird die eigentliche Sicherung per Knopfdruck durchgeführt
	
	$Global:FileName = $textbox1_FN.Text
	$Server = $global:MSSQLServer
	$Database = $Global:SelectedDBName
	$Path = $Global:Path
	$Folder = $Global:CC + '\'
	$FileName = $Global:FileName
	$BackupSetName = $Global:BackupSetName
	$Extension = '.bak'
	$FilePath = $Path + $Folder + $FileName + $Extension
	$labelPathToBackupFile.Text = $FilePath #Debug welchen Pfad habe ich zusammen gebaut
	
	
	$richtextbox_out.Text = "Display Information`r`n";
	
	## - Setup job to execute in background:
	## - SMO Backup Code below:
	
	$jobScript = {
		Param (
			$Database,
			$BackupSetName,
			$FilePath,
			$Server
		)
	#Load SMO assemblies
	[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null #SMO #das SMO Objekt holen und den Output nach /dev/null
	[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
	$smoBackup = New-Object Microsoft.SqlServer.Management.Smo.Backup # SMO Backup Objekt
	$smoBackup.Action = "Database"
	$smoBackup.BackupSetDescription = "Full (copyonly)backup of $($Database)"
	$smoBackup.BackupSetName = $BackupSetName
	$smoBackup.Database = $Database
	$smoBackup.Copyonly = "True" #verändert die DatabaseBackupLSN und bringt die scheduled inkrementellen Backups nicht durcheinander
	$smoBackup.MediaDescription = "Disk"
	$smoBackup.CompressionOption = "1"
	$smoBackup.PercentCompleteNotification = "10"
	$smoBackup.Devices.AddDevice($FilePath, "File")
		
		$smoBackup.SqlBackup($server)  #Befehl der Sicherung wird ausgefuehrt
		
	};
	
	$argList = @(
		$Database,
		$BackupSetName,
		$FilePath,
		$Server
	)
	$richtextbox_out.AppendText("Running Job - SMO Backup`r`n");
	Start-Job -ScriptBlock $jobScript -Name SMOBackup1 -ArgumentList $argList
	## - Check for job completion:
	do
	{
		$richtextbox_out.AppendText("Checking SMO Backup Completion`r`n - still running");
		$richtextbox_out.ScrollToCaret()
		Start-Sleep -Seconds 5;
	}
	while ((Get-Job -Name 'SMOBackup1').State -ne 'Completed');
	$result1 = Get-Job -Name 'SMOBackup1';
	
	## - Display Job Status at EOJ:
	[array]$SMOJobResults = (Get-Job -Name 'SMOBackup1' | Receive-Job);
	
	## - Display Job Results in RichTextBox:
	$richtextbox_out.AppendText("Displaying Asynch Job Results:`r`n");
	$richtextbox_out.AppendText(($SMOJobResults | Format-Table | Out-String -Width 1000));
	$richtextbox_out.ScrollToCaret()
	
	## - Display Job result in Datagridview:
	#$datagridview1.DataSource = ConvertTo-DataTable -InputObject $SMOJobResults;
	$richtextbox_out.AppendText("Processing Completed`r`n");
	$richtextbox_out.AppendText((Get-Date).ToString("dd_MMMM_yyyy_HHmm"));
	$richtextbox_out.ScrollToCaret()
}
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Button - Start Job, how to multiple arguments, how to work with objects

Post by jvierra »

There is no way to do a progress bar with a blocking process. That is why we use the JobTracker controls.

In Net classes we do not use quoted integers and we use $true/$false for Booleans and NOT "true" or "false".

The backup does not report percentage if you do not use "SqlBackupAsync", The percentage tells how often to raise the progress event. The event is not available in Forms. To use this in a PowerShell form you would have to write a C# module and call it from a separate runspace that can call back to the main form.

You can run the code async without the event by using $bacup.Wait() which will allow database updates and prevent the job from completing before the backup is finished.
This topic is 5 years and 9 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked