Posey's Tips & Tricks
Dynamically Construct a PowerShell GUI, Part 2
Learn how to dynamically generate PowerShell GUI elements from a SQL data source and update values with user input.
In the first part of this series, I explained that there are occasionally situations in which hard coding a PowerShell interface really isn't an option because the script is almost certainly going to evolve in the future. In situations like this, it's better to build a script that is completely dynamic. However, doing so isn't always easy.
So with that said, I want to walk you through the way that I addressed the problem. This script is somewhat lengthy, so walking you through every single line of code isn't really an option. Instead, what I want to do is to show you some of the more important blocks of code and then at the end, I will provide you with the full script. Here is the first code excerpt that I want to show you.
$YAxis = 150
$QueryStatement = "SELECT * FROM settings"
$Query = Perform-Query $QueryStatement
$Result = $Query
ForEach ($Row in $Result){
$LabelName = "$" + $Row.setting_name + "_Label"
$TextBoxName = "$" + $Row.setting_name + "_TextBox"
$LabelString = $LabelName + " = New-Object System.Windows.Forms.Label"
Invoke-Expression $LabelString
$TextBoxString = $TextBoxName + " = New-Object System.Windows.Forms.TextBox"
Invoke-Expression $TextBoxString
$YAxis = $YAxis + 50
}
The first line of code in the excerpt above creates a variable called $YAxis and sets its value to 150. Remember, this script is designed to create and display an unknown number of labels and text boxes on the screen. The $YAxis variable determines the vertical placement of the first of these GUI objects. The last command in the code excerpt increases the $YAxis value by 50 every time the loop executes. That way, the GUI objects are evenly distributed across the screen without overlapping. It's worth noting that as it stands right now, the script does not place an upper limit on the $YAxis, but I may create one later on as a way of preventing a future situation in which GUI objects scroll off the screen.
After I declare the $YAxis variable, I am performing a SQL query as a way of reading the current list of settings and their values. The subsequent loop looks at each row within the query results, so that GUI objects can be created for each setting that is defined within the database table.
So let's talk about the GUI object creation process. The loop is designed to create a text label and a text box (an input box). The first step in doing so is to form a label name and a text box name. Remember, in order to be useful, each GUI object needs to have a unique name. To form a label name, I built a string that combines a dollar sign (which is used for PowerShell variables, the setting name (which is the name that I applied to the individual setting within the database) and the word _Label. As an example, if the setting name within the database was Retention_Policy, the resulting label name would be $Retention_Policy_Label.
With the label name and the text box name defined, I could then create the label and textbox objects. In order to do so however, I had to build a string that combined the label or textbox name along with the command used to create an object. I then had to use the Invoke-Expression command to execute the full command contained within the string. I handled the attribute creation process in exactly the same way, although it is not shown in the excerpt.
The script is designed so that any existing values stored within the database are automatically added to the text boxes. That process is handled inside of the loop by these commands:
$TextBoxString = $TextBoxName + ".Text = '" + $Row.setting_value + "'"
Invoke-Expression $TextBoxString
It's worth noting that because I want to be able to modify the settings, I am not making the text boxes read only. As such, it's possible to create new values or modify existing values within the text boxes. Upon making such changes, the script requires you to click an Update button in order to save any changes. The click action that is associated with this button was the most difficult portion of the script to write. Here is the code associated with the click action:
$SettingsSubmitButton.Add_Click({
$QueryStatement = "SELECT * FROM settings"
$Query = Perform-Query $QueryStatement
$Result = $Query
ForEach ($Row in $Result){
$TextBoxName = "$" + $Row.setting_name + "_TextBox"
$TextName = $TextBoxName + ".text"
$TempValue = Invoke-Expression $TextName
$QueryStatement = "UPDATE settings SET setting_value = '" + $TempValue + "' WHERE setting_name='" + $Row.setting_name + "'"
$Query = Perform-Query $QueryStatement
}
$SettingsForm.Close()
})
Like the first code excerpt that I showed you, this block of code begins by reading all of the records stored within the database's Settings table and then uses a loop to process the results. Like before, I am determining the name of the text box that is associated with each record. These text boxes have already been defined, but I need to reference the text box name once again so that I can extract the value that has been entered into the text box.
The text box name is stored in a variable called $TextBoxName. From there, I created another variable called $TextName. Text that is entered into a text box is stored in an attribute called .Text. For example, if you have a text box called $Retention_Policy_Textbox and you wanted to see what had been typed into that text box, you would have to look at $Retention_Policy_Textbox.Text. As such, the $TextName variable appends .Text to the name of the text box.
Finally, I have created yet another variable. This one is called $TempValue. This variable stores the text that has been typed into the text box. I accomplished this by using Invoke-Expression $TextName. Had I simply used $TextName by itself, PowerShell would have displayed the text box name followed by the .Text attribute, but not the actual data that is stored within that attribute.
So now that the text that has been typed into the text box has been resolved, a SQL UPDATE statement is used as a way of updating the record within the database.
So now that I have explained how the script works, you can see what the resulting dialog box looks like in Figure 1.
[Click on image for larger view.]
Figure 1. This is what my Settings window looks like.
The SQL code used to create the database table is:
CREATE TABLE settings (
setting_display_name VARCHAR(100),
setting_name VARCHAR(100),
setting_value VARCHAR(100)
)
Finally, here is the PowerShell script in its entirety:
# SQL Server Connection String
$ConnectionString="Invoke-SQLCmd -ServerInstance <insert your PC name here>\SQLExpress -Database <insert your database name here> -Query "
Function Perform-Query {
Param (
[String]$QueryStatement
)
$QueryString = $ConnectionString + '"' + $QueryStatement + '"'
$Query = Invoke-Expression $QueryString
Return $Query
}
Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing
$QueryStatement = "SELECT * FROM settings"
$Query = Perform-Query $QueryStatement
$Result = $Query
# Form Creation
$SettingsForm = New-Object System.Windows.Forms.Form
$SettingsForm.Text = "Settings"
$SettingsForm.Size = New-Object System.Drawing.Size(1024,768)
$SettingsForm.StartPosition = "CenterScreen"
$YAxis = 150
ForEach ($Row in $Result){
$LabelName = "$" + $Row.setting_name + "_Label"
$TextBoxName = "$" + $Row.setting_name + "_TextBox"
$LabelString = $LabelName + " = New-Object System.Windows.Forms.Label"
Invoke-Expression $LabelString
$LabelString = $LabelName + ".Text = '" + $Row.setting_display_name + ":'"
Invoke-Expression $LabelString
$LabelString = $LabelName + ".Font = New-Object System.Drawing.Font('Arial', 14)"
Invoke-Expression $LabelString
$LabelString = $LabelName + ".Location = New-Object System.Drawing.Point(20," + $YAxis +")"
Invoke-Expression $LabelString
$LabelString = $LabelName + ".AutoSize = $" + "True"
Invoke-Expression $LabelString
$LabelString = "$" + "SettingsForm.Controls.Add(" + $LabelName + ")"
Invoke-Expression $LabelString
########## Text Boxes ############
$TextBoxString = $TextBoxName + " = New-Object System.Windows.Forms.TextBox"
Invoke-Expression $TextBoxString
$TextBoxString = $TextBoxName + ".Location = New-Object System.Drawing.Point(450, " + $YAxis +")"
Invoke-Expression $TextBoxString
$TextBoxString = $TextBoxName + ".Font = New-Object System.Drawing.Font('Arial', 14)"
Invoke-Expression $TextBoxString
$TextBoxString = $TextBoxName + ".Size = New-Object System.Drawing.Size(400,50)"
Invoke-Expression $TextBoxString
$TextBoxString = $TextBoxName + ".Text = '" + $Row.setting_value + "'"
Invoke-Expression $TextBoxString
$TextBoxString = "$" + "SettingsForm.Controls.Add(" + $TextBoxName + ")"
Invoke-Expression $TextBoxString
##############################
# End of the loop cleanup
$YAxis = $YAxis + 50
}
# Submit Button
$SettingsSubmitButton = New-Object System.Windows.Forms.Button
$SettingsSubmitButton.Text = "Update"
$SettingsSubmitButton.Font = New-Object System.Drawing.Font("Arial", 14)
$SettingsSubmitButton.Location = New-Object System.Drawing.Point(80, 600)
$SettingsSubmitButton.Size = New-Object System.Drawing.Size(150,50)
$SettingsSubmitButton.BackColor = "LightGreen"
# Cancel Button
$SettingsCancelButton = New-Object System.Windows.Forms.Button
$SettingsCancelButton.Text = "Cancel"
$SettingsCancelButton.Font = New-Object System.Drawing.Font("Arial", 14)
$SettingsCancelButton.Location = New-Object System.Drawing.Point(300, 600)
$SettingsCancelButton.Size = New-Object System.Drawing.Size(150,50)
$SettingsCancelButton.BackColor = "LightYellow"
# Title Label
$SettingsTitleLabel = New-Object System.Windows.Forms.Label
$SettingsTitleLabel.Text = "Application Settings"
$SettingsTitleLabel.Font = New-Object System.Drawing.Font("Arial", 18, [Drawing.FontStyle]::Bold)
$SettingsTitleLabel.AutoSize = $True
$SettingsTitleLabel.Location = New-Object System.Drawing.Point(20,20)
# Add Controls to Form
$SettingsForm.Controls.Add($SettingsTitleLabel)
$SettingsForm.Controls.Add($SettingsSubmitButton)
$SettingsForm.Controls.Add($SettingsCancelButton)
# Event Handlers
$SettingsSubmitButton.Add_Click({
$QueryStatement = "SELECT * FROM settings"
$Query = Perform-Query $QueryStatement
$Result = $Query
ForEach ($Row in $Result){
$TextBoxName = "$" + $Row.setting_name + "_TextBox"
$TextName = $TextBoxName + ".text"
$TempValue = Invoke-Expression $TextName
$QueryStatement = "UPDATE settings SET setting_value = '" + $TempValue + "' WHERE setting_name='" + $Row.setting_name + "'"
$Query = Perform-Query $QueryStatement
}
$SettingsForm.Close()
})
$SettingsCancelButton.Add_Click({
$SettingsForm.Close()
})
# Show the Form
$SettingsForm.ShowDialog() | Out-Null
About the Author
Brien Posey is a 22-time Microsoft MVP with decades of IT experience. As a freelance writer, Posey has written thousands of articles and contributed to several dozen books on a wide variety of IT topics. Prior to going freelance, Posey was a CIO for a national chain of hospitals and health care facilities. He has also served as a network administrator for some of the country's largest insurance companies and for the Department of Defense at Fort Knox. In addition to his continued work in IT, Posey has spent the last several years actively training as a commercial scientist-astronaut candidate in preparation to fly on a mission to study polar mesospheric clouds from space. You can follow his spaceflight training on his Web site.