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.
User avatar
Pallas
Posts: 14
Joined: Wed Jan 18, 2017 2:44 am
Contact:

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

Post by Pallas » Wed Jun 13, 2018 6:04 am

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:

Code: Select all

$buttonBackupDB_Click= {
$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
#ab hier smoBackup Objekt
$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"
$smoBackup.MediaDescription = "Disk"
$smoBackup.CompressionOption = "1"
$smoBackup.PercentCompleteNotification = "10"
$smoBackup.Devices.AddDevice($FilePath, "File")

Try
{
$smoBackup.SqlBackup($server)
}
Catch
{
$textbox_out.Text = $_.Exception.InnerException
}
<#$Global:FileName = $textbox1_FN.Text
$Server = $global:MSSQLServer
$Database = $Global:SelectedDBName
$Path = $Global:Path
$Folder = $Global:CC + '\'
$FileName = $Global:FileName
$Extension = '.bak'
$FilePath = $Path + $Folder + $FileName + $Extension
$labelPathToBackupFile.Text = $FilePath #Debug welchen Pfad habe ich zusammen gebaut#>
[/size][/size]

Not working:

Code: Select all

[size=85]$buttonStartJob_Click={

$buttonStartJob.Enabled = $false

#Create a New Job using the Job Tracker
Add-JobTracker -Name 'BackUpDBJob' `
-JobScript {
#--------------------------------------------------
#TODO: Set a script block
#Important: Do not access form controls from this script block.

#ab hier smoBackup Objekt
$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)

Param ($Argument1) #Pass any arguments using the ArgumentList parameter



#--------------------------------------------------
}`
$env:FileName = $textbox1_FN.Text
$env:Server = $global:MSSQLServer
$wnv: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
-Argumentlist #WHAT GOES HERE AND HOW?

-CompletedScript {
Param($Job)
#$results = Receive-Job -Job $Job
#Enable the Button
$buttonStartJob.ImageIndex = -1
$buttonStartJob.Enabled = $true
}`
-UpdateScript {
Param($Job)
#$results = Receive-Job -Job $Job -Keep
#Animate the Button
if($null -ne $buttonStartJob.ImageList)
{
if($buttonStartJob.ImageIndex -lt $buttonStartJob.ImageList.Images.Count - 1)
{
$buttonStartJob.ImageIndex += 1
}
else
{
$buttonStartJob.ImageIndex = 0
}
}
}`

}
[/size]
[/i]

User avatar
mxtrinidad
Site Admin
Posts: 260
Joined: Sun Mar 03, 2013 12:42 pm

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

Post by mxtrinidad » 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.

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: 5713
Joined: Thu Aug 18, 2011 4:56 am

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

Post by davidc » Wed Jun 13, 2018 8:59 am

[TOPIC MOVED TO POWERSHELL GUIS FORUM BY MODERATOR]
David
SAPIEN Technologies, Inc.

User avatar
Pallas
Posts: 14
Joined: Wed Jan 18, 2017 2:44 am
Contact:

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

Post by Pallas » Wed Jun 13, 2018 12:32 pm

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
Site Admin
Posts: 260
Joined: Sun Mar 03, 2013 12:42 pm

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

Post by mxtrinidad » Wed Jun 13, 2018 12:47 pm

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
Joined: Wed Jan 18, 2017 2:44 am
Contact:

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

Post by Pallas » Thu Jun 14, 2018 3:11 am

works:



Code: Select all

[size=50]$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
#ab hier smoBackup Objekt
$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 = 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)

}[/size]
[/i]

User avatar
Pallas
Posts: 14
Joined: Wed Jan 18, 2017 2:44 am
Contact:

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

Post by Pallas » Thu Jun 14, 2018 3:13 am

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"));
}

User avatar
jvierra
Posts: 13265
Joined: Tue May 22, 2007 9:57 am
Contact:

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

Post by jvierra » Thu Jun 14, 2018 3:36 am

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
Joined: Wed Jan 18, 2017 2:44 am
Contact:

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

Post by Pallas » Thu Jun 14, 2018 5:33 am

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()
}

User avatar
jvierra
Posts: 13265
Joined: Tue May 22, 2007 9:57 am
Contact:

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

Post by jvierra » Thu Jun 14, 2018 5:52 am

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.

Locked