Write a GUI on Top of Existing PowerShell Functions with SAPIEN PowerShell Studio 2016

This blog article will demonstrate how to write a GUI on top of your existing PowerShell functions using SAPIEN PowerShell Studio 2016. I’ve previously written a couple of functions for managing SQL Server agent jobs. These two functions, Get-MrSqlAgentJobStatus and Start-MrSqlAgentJob can be found in my SQL repository on GitHub.

Launch PowerShell Studio. Select file, the arrow next to new, and new form:

](/images/2016/11/sqlagent-gui1a.pngsqlagent-gui1a)

For this particular GUI, I’ll select the “Dialog Style Template” since I want a fixed border without any minimize or maximize buttons:

](/images/2016/11/sqlagent-gui2a.pngsqlagent-gui2a)

One thing to note is the Toolbox auto-hides by default. It can be found on the left side of the screen:

](/images/2016/11/sqlagent-gui3a.pngsqlagent-gui3a)

Add another button to the form:

](/images/2016/11/sqlagent-gui4a.pngsqlagent-gui4a)

Click on the button on the form itself and them modify the properties in the lower right side of PowerShell Studio:

](/images/2016/11/sqlagent-gui5a.pngsqlagent-gui5a)

The text of both buttons and the size of one of them has been changed:

](/images/2016/11/sqlagent-gui6a.pngsqlagent-gui6a)

Double-click on the exit button and add the code to close the form:

$buttonExit_Click={
	$form1.Close()
}

](/images/2016/11/sqlagent-gui7a.pngsqlagent-gui7a)

Add the existing functions to the project:

](/images/2016/11/sqlagent-gui8a.pngsqlagent-gui8a)

Select the functions to add:

](/images/2016/11/sqlagent-gui9a.pngsqlagent-gui9a)

You can see that the functions have been added:

](/images/2016/11/sqlagent-gui17a.pngsqlagent-gui17a)

They can also be seen (and modified if needed) in the script pane:

](/images/2016/11/sqlagent-gui10a.pngsqlagent-gui10a)

Either double-click on the other button from the Designer tab or simply add the button click event handler for it and write the code to wire the exiting functions up to it:

$buttonRunSQLAgentJob_Click={
	$Params = @{
		ServerInstance = 'SQL011'
		Name = 'test'
	}

	$result = Start-MrSQLAgentJob @Params

	if ($result -eq $true) {
		Start-Sleep -Seconds 5
		while ((Get-MrSQLAgentJobStatus @Params).Outcome -eq 'Executing') {
			$buttonRunSQLAgentJob.Text = 'In Progress'
			$buttonRunSQLAgentJob.BackColor = 'Orange'
			Start-Sleep -Seconds 20
		}

		if ((Get-MrSQLAgentJobStatus @Params).Status -eq 'Succeeded') {
			$buttonRunSQLAgentJob.Text = 'Success'
			$buttonRunSQLAgentJob.BackColor = 'Green'
		}
		else {
			$buttonRunSQLAgentJob.Text = 'Error!'
			$buttonRunSQLAgentJob.BackColor = 'Red'
		}

	}
	else {
		$buttonRunSQLAgentJob.Text = 'Error!'
		$buttonRunSQLAgentJob.BackColor = 'Red'
	}
}

The button simply calls the functions as needed and performs the necessary logic based on the results.

](/images/2016/11/sqlagent-gui11a.pngsqlagent-gui11a)

To run this GUI as a user who has been given access to run the SQL agent jobs without actually giving those rights to their user accounts, select “Settings” underneath Package:

](/images/2016/11/sqlagent-gui13a.pngsqlagent-gui13a)

On the Output Settings tab, set the Run Mode to “RunAs user” and enter the user name and password information for that account. This account should be setup with the privileged of least principle just in case someone is able to reverse engineer the executable and retrieve the credentials.

](/images/2016/11/sqlagent-gui14b.pngsqlagent-gui14b)

Select “Build” underneath package to create an executable:

](/images/2016/11/sqlagent-gui12a.pngsqlagent-gui12a)

Run the executable and then click the “Run SQL Agent Job” button:

](/images/2016/11/sqlagent-gui18a.pngsqlagent-gui18a)

The SQL agent job is running:

](/images/2016/11/sqlagent-gui15a.pngsqlagent-gui15a)

It completed successfully:

](/images/2016/11/sqlagent-gui16a.pngsqlagent-gui16a)

The GUI shown in this blog article is a proof of concept and certainly isn’t meant to be a fully functional application. It’s meant to show how to write a GUI to leverage your existing PowerShell functions so you don’t have to reinvent the wheel. If you were writing a GUI to accomplish this task, you would at least want to first check to see if the job is running and disable the button to run it while the job is running.

Don’t own PowerShell Studio? Download the 45 day eval.

µ