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:

sqlagent-gui1a.png

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

sqlagent-gui2a.png

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

sqlagent-gui3a.png

Add another button to the form:

sqlagent-gui4a.png

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

sqlagent-gui5a.png

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

sqlagent-gui6a.png

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

$buttonExit_Click={
    $form1.Close()
}

sqlagent-gui7a.png

Add the existing functions to the project:

sqlagent-gui8a.png

Select the functions to add:

sqlagent-gui9a.png

You can see that the functions have been added:

sqlagent-gui17a.png

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

sqlagent-gui10a.png

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.

sqlagent-gui11a.png

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:

sqlagent-gui13a.png

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.

sqlagent-gui14b.png

Select “Build” underneath package to create an executable:

sqlagent-gui12a.png

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

sqlagent-gui18a.png

The SQL agent job is running:

sqlagent-gui15a.png

It completed successfully:

sqlagent-gui16a.png

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.

µ