Although PowerShell is best suited for console applications there are times when a GUI interface just makes sense, however hand cranking PowerShell code to display a GUI seems almost anti-productive when we are used to rich IDE's with WYSIWIG development available in other programming languages. Fortunately there are several 3rd party tools like
PowerGUI, CodePlex projects like
Powerboots as well as many great data visualization scripts which reduce the amount of time to build GUIs in PowerShell. In this blog post we'll look at another option for buidling GUIs, a free utility from
SAPIEN called
Primal Forms.
To demonstrate Primal Forms, I thought it would be interesting to create a basic CRUD form for a SQL Server table, so I adapted the example on
this MSDN page to PowerShell. Although the example uses the authors table in the sample pubs database, I can think of many real-world applications with tables used solely by sys admins to control user access or store configuration data about an application. In order to provide an interface to security and configuration tables a web front end could be developed, PowerShell scripts or cmdlets created or an MS Access front end could be used. All true, but I think the use of PowerShell and a WinForm datagridview provides a light-weight alternative that is completely accessible to PowerShell savvy admins.
Primal Forms is a simple IDE for building WinForms; if you've used Visual Studio, the form development feels similar. In the screenshot below I created a form with three controls: a dataGridView, and two buttons (reload and submit). Once you've created the form you can save the form definition in an XML format for later editing within Primal Forms. When you're ready to create a PowerShell script, select Export to PowerShell. Primal Forms creates all the necessary PowerShell code for the WinForm and controls which you can save to a .ps1 file. In the example that follows, I've named the script dataGrid.ps1.
Primal Forms IDE
All that's left to do is add event handling to the form load and various forms controls (buttons). Note the actual user of the PowerShell script does not need to have Primal Forms installed. Primal Forms simply generates 100% compliant PowerShell code for WinForms. Adding the event handling must be done in a text editor. Open the newly created dataGrid.ps1 file in your PowerShell script editor of choice. Primal Forms creates placeholders for the events. In this example the following TODO placeholders are generated:
#Provide Custom Code for events specified in PrimalForms.
$Form1_Load=
{
#TODO: Place custom script here}
$submitButton_Click=
{
#TODO: Place custom script here
}
$reloadButton_Click=
{
#TODO: Place custom script here
}
To populate the datagridview and events for the submit and reload buttons add the following code:
#endregion Generated Form Objects
$bindingSource1 = new-object System.Windows.Forms.BindingSource
$dataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$serverName = "$env:computername\sqlexpress"
$databaseName = "Northwind"
$query = 'select * from Customers'#----------------------------------------------
#Generated Event Script Blocks
#----------------------------------------------
#Provide Custom Code for events specified in PrimalForms.
$Form1_Load=
{
$dataGridView1.DataSource = $bindingSource1
$connString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;"
$dataAdapter.SelectCommand = new-object System.Data.SqlClient.SqlCommand ($query,$connString)
$commandBuilder = new-object System.Data.SqlClient.SqlCommandBuilder $dataAdapter
$dt = New-Object System.Data.DataTable
[void]$dataAdapter.fill($dt)
$bindingSource1.DataSource = $dt
$dataGridView1.AutoResizeColumns([System.Windows.Forms.DataGridViewAutoSizeColumnsMode]::AllCellsExceptHeader)
}
$submitButton_Click=
{
$dataAdapter.Update($bindingSource1.DataSource)
}
$reloadButton_Click=
{
$dataGridView1.DataSource = $bindingSource1
$connString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;"
$dataAdapter.SelectCommand = new-object System.Data.SqlClient.SqlCommand ($query,$connString)
$commandBuilder = new-object System.Data.SqlClient.SqlCommandBuilder $dataAdapter
$dt = New-Object System.Data.DataTable
[void]$dataAdapter.fill($dt)
$bindingSource1.DataSource = $dt
$dataGridView1.AutoResizeColumns([System.Windows.Forms.DataGridViewAutoSizeColumnsMode]::AllCellsExceptHeader)
}
One minor issue with adding custom code, if you later go back to edit the form in Primal Forms you'll have to re-add your custom code. Since the form definition is saved in seperate file this isn't a big deal, although it would be nice if Primal Forms stored allowed you to store the custom code.
Running the script ./datagrid.ps1 produces the following output
You can insert new rows or update existing rows and then click submit button to commit the changes to the SQL Server table. To delete rows highlight a row by right clicking and pressing the shift key and then the delete key.
As a finishing touch I've created a cmd file to call PowerShell and execute the script with the following parameters:
powershell -noprofile -Noninteractive -command c:\users\u00\bin\dataGrid.ps1
If you load a lot of functions and snapins in my profile like I do, the -noprofile option starts PowerShell much faster. This is useful if all you want to do is load the WinForm and don't happen to have a PowerShell console open. As an added bonus, the cmd file allows you to launch a simple application with a double-click without first starting PowerShell.
Next Steps -- Get
Primal Forms, download the sample code and create GUIs!
