PowerShell GUI

Part 3 Adding Tab Controls – Create your own SQL tools with PowerShell and windows forms

Using Tab Controls can help you organize and define your work space. In this blog post, I am going to be adding Tab Controls with the following Tab Pages for CPU, Disk, Memory to my SQL Tool app. I am going to highlight how you can take my code and add your own Tab Pages to meet your needs. Drop me a comment and let me know how your SQL Tool app is coming along or if you want me to cover a topic. Check out Part One where I introduced the SQL Tool app and in Part Two were I went over how to add a new Check Box.

 

TabControl

 

Adding New Tab Pages

Step 1.

For example, you want to add a new Tab Page called Tempdb , under the heading called #Form Setup, you will need to add a new system Tab Page object.

$TempDBPage = New-Object System.Windows.Forms.TabPage

Step 2.

Next you will need to create the Tab Page, this can be added under a previous Tab Page.

#TempDB Page
$TempDBPage.DataBindings.DefaultDataSourceUpdateMode = 0
$TempDBPage.UseVisualStyleBackColor = $True
$TempDBPage.Text = "TempDB”
$tabControl.Controls.Add($TempDBPage)

 

Full Code

function CreateForm {
#[reflection.assembly]::loadwithpartialname("System.Windows.Forms") | Out-Null
#[reflection.assembly]::loadwithpartialname("System.Drawing") | Out-Null

Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.drawing

#Form Setup
$form1 = New-Object System.Windows.Forms.Form
$button1 = New-Object System.Windows.Forms.Button
$button2 = New-Object System.Windows.Forms.Button
$checkBox1 = New-Object System.Windows.Forms.CheckBox
$checkBox2 = New-Object System.Windows.Forms.CheckBox
$checkBox3 = New-Object System.Windows.Forms.CheckBox
$checkBox4 = New-Object System.Windows.Forms.CheckBox
$TabControl = New-object System.Windows.Forms.TabControl
$SQLHealthPage = New-Object System.Windows.Forms.TabPage
$CPUPage = New-Object System.Windows.Forms.TabPage
$DiskPage = New-Object System.Windows.Forms.TabPage
$MemoryPage = New-Object System.Windows.Forms.TabPage

$InitialFormWindowState = New-Object System.Windows.Forms.FormWindowState

#Form Parameter
$form1.Text = "My PowerShell Form"
$form1.Name = "form1"
$form1.DataBindings.DefaultDataSourceUpdateMode = 0
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 725
$System_Drawing_Size.Height = 450
$form1.ClientSize = $System_Drawing_Size


#Tab Control 
$tabControl.DataBindings.DefaultDataSourceUpdateMode = 0
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 75
$System_Drawing_Point.Y = 85
$tabControl.Location = $System_Drawing_Point
$tabControl.Name = "tabControl"
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Height = 300
$System_Drawing_Size.Width = 575
$tabControl.Size = $System_Drawing_Size
$form1.Controls.Add($tabControl)

#SQLHealth Page
$SQLHealthPage.DataBindings.DefaultDataSourceUpdateMode = 0
$SQLHealthPage.UseVisualStyleBackColor = $True
$SQLHealthPage.Name = "SQLHealthPage"
$SQLHealthPage.Text = "SQL Health Check”
$tabControl.Controls.Add($SQLHealthPage)

#CPU Page
$CPUPage.DataBindings.DefaultDataSourceUpdateMode = 0
$CPUPage.UseVisualStyleBackColor = $True
$CPUPage.Name = "CPUPage"
$CPUPage.Text = "CPU”
$tabControl.Controls.Add($CPUPage)

#Disk Page
$DiskPage.DataBindings.DefaultDataSourceUpdateMode = 0
$DiskPage.UseVisualStyleBackColor = $True
$DiskPage.Name = "DiskPage"
$DiskPage.Text = "Disk”
$tabControl.Controls.Add($DiskPage)

#Memory Page
$MemoryPage.DataBindings.DefaultDataSourceUpdateMode = 0
$MemoryPage.UseVisualStyleBackColor = $True
$MemoryPage.Name = "MemoryPage"
$MemoryPage.Text = "Memory”
$tabControl.Controls.Add($MemoryPage)

#Add Label and TextBox
$objLabel = New-Object System.Windows.Forms.Label
$objLabel.Location = New-Object System.Drawing.Size(10,45)  
$objLabel.Size = New-Object System.Drawing.Size(110,50)  
$objLabel.Text = "Enter Server Name"
$form1.Controls.Add($objLabel)
$objTextBox = New-Object System.Windows.Forms.TextBox 
$objTextBox.Location = New-Object System.Drawing.Size(120,45) 
$objTextBox.Size = New-Object System.Drawing.Size(200,20)  
$form1.Controls.Add($objTextBox) 
 
#Button 1 Action 
$button1_RunOnClick= 
{   
    if ($checkBox1.Checked)     {  SQLVersion }
    if ($checkBox2.Checked)    {  LastReboot }
    if ($checkBox3.Checked)    {  Requests }   
}

#Button 2 Action
$button2_RunOnClick= 
{   
    if ($checkBox1.Checked) {$checkBox1.CheckState = 0}
    if ($checkBox2.Checked) {$checkBox2.CheckState = 0}
    if ($checkBox3.Checked) {$checkBox3.CheckState = 0}
}

$OnLoadForm_StateCorrection=
{
    $form1.WindowState = $InitialFormWindowState
}
 
#Button 1
$button1.TabIndex = 4
$button1.Name = "button1"
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 75
$System_Drawing_Size.Height = 25
$button1.Size = $System_Drawing_Size
$button1.UseVisualStyleBackColor = $True
$button1.Text = "Run"
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 350
$System_Drawing_Point.Y = 45
$button1.Location = $System_Drawing_Point
$button1.DataBindings.DefaultDataSourceUpdateMode = 0
$button1.add_Click($button1_RunOnClick)
$form1.Controls.Add($button1)

#Button 2
$button2.TabIndex = 4
$button2.Name = "button2"
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 150
$System_Drawing_Size.Height = 25
$button2.Size = $System_Drawing_Size
$button2.UseVisualStyleBackColor = $True
$button2.Text = "Clear CheckBox"
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 450
$System_Drawing_Point.Y = 45
$button2.Location = $System_Drawing_Point
$button2.DataBindings.DefaultDataSourceUpdateMode = 0
$button2.add_Click($button2_RunOnClick)
$form1.Controls.Add($button2)


#SQLVersion
$checkBox1.UseVisualStyleBackColor = $True
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 104
$System_Drawing_Size.Height = 24
$checkBox1.Size = $System_Drawing_Size
$checkBox1.TabIndex = 0
$checkBox1.Text = "SQL Version"
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 25
$System_Drawing_Point.Y = 25
$checkBox1.Location = $System_Drawing_Point
$checkBox1.DataBindings.DefaultDataSourceUpdateMode = 0
$checkBox1.Name = "checkBox1"
$SQLHealthPage.Controls.Add($checkBox1)



#LastReBoot
$checkBox2.UseVisualStyleBackColor = $True
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 104
$System_Drawing_Size.Height = 24
$checkBox2.Size = $System_Drawing_Size
$checkBox2.TabIndex = 1
$checkBox2.Text = "Last Reboot"
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 25
$System_Drawing_Point.Y = 50
$checkBox2.Location = $System_Drawing_Point
$checkBox2.DataBindings.DefaultDataSourceUpdateMode = 0
$checkBox2.Name = "checkBox2"
$SQLHealthPage.Controls.Add($checkBox2)


#Request
$checkBox3.UseVisualStyleBackColor = $True
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 104
$System_Drawing_Size.Height = 24
$checkBox3.Size = $System_Drawing_Size
$checkBox3.TabIndex = 0
$checkBox3.Text = "Requests"
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 25
$System_Drawing_Point.Y = 75
$checkBox3.Location = $System_Drawing_Point
$checkBox3.DataBindings.DefaultDataSourceUpdateMode = 0
$checkBox3.Name = "checkBox3"
$SQLHealthPage.Controls.Add($checkBox3)


#Save the initial state of the form
$InitialFormWindowState = $form1.WindowState
#Init the OnLoad event to correct the initial state of the form
$form1.add_Load($OnLoadForm_StateCorrection)
#Show the Form
$form1.ShowDialog()| Out-Null
} #End function CreateForm
 
 function Invoke-Sqlcmd3

{
    param(
    [string]$Query,             
    [string]$Database="tempdb",
    [Int32]$QueryTimeout=30
    )
    $conn=new-object System.Data.SqlClient.SQLConnection
    $conn.ConnectionString="Server={0};Database={1};Integrated Security=True" -f $Server,$Database
    $conn.Open()
    $cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
    $cmd.CommandTimeout=$QueryTimeout
    $ds=New-Object system.Data.DataSet
    $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
    [void]$da.fill($ds)
    $conn.Close()
    $ds.Tables[0]
}

 

Function SQLVersion
{
[string]$SQLVersion = @"
SELECT  @@Version
"@ 
 $Server = $objTextBox.text
Invoke-Sqlcmd3 -ServerInstance $Server -Database Master -Query $SQLVersion | Out-GridView -Title "$server SQL Server Version"
}

Function LastReboot
{
$Server = $objTextBox.text
$wmi = Get-WmiObject -Class Win32_OperatingSystem -Computer $server
$wmi.ConvertToDatetime($wmi.LastBootUpTime) | Select DateTime | Out-GridView -Title "$Server Last Reboot"
}

Function Requests
{
[string]$Requests = @"
SELECT
   db_name(r.database_id) as database_name, r.session_id AS SPID,r.status,s.host_name,
     r.start_time,(r.total_elapsed_time/1000) AS 'TotalElapsedTime Sec',
   r.wait_type as current_wait_type,r.wait_resource as current_wait_resource,
   r.blocking_session_id,r.logical_reads,r.reads,r.cpu_time as cpu_time_ms,r.writes,r.row_count,
   substring(st.text,r.statement_start_offset/2,
   (CASE WHEN r.statement_end_offset = -1 THEN len(convert(nvarchar(max), st.text)) * 2 ELSE r.statement_end_offset END - r.statement_start_offset)/2) as statement
FROM
   sys.dm_exec_requests r
      LEFT OUTER JOIN sys.dm_exec_sessions s on s.session_id = r.session_id
      LEFT OUTER JOIN sys.dm_exec_connections c on c.connection_id = r.connection_id       
      CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st 
WHERE r.status NOT IN ('background','sleeping')
"@ 
 $Server = $objTextBox.text
Invoke-Sqlcmd3 -ServerInstance $Server -Database Master -Query $Requests | Out-GridView -Title "$server Requests"
}



#Call the Function

CreateForm

Part2 Adding a New CheckBox – Create your own SQl tools with powershell and windows forms

 

This blog post is about adding a new CheckBox to the SQL Tool base form from my first blog post. In the first blog post I just gave an introduction on the SQL Tool, now this is where the fun starts getting creative by adding your own SQL queries and PowerShell scripts. How cool is that! You’re making your own SQL tools tailored to your own needs. The CheckBox that I am going to be adding is for an SQL process, but it is universal. The CheckBox is just starting a PowerShell Function, what ever you dream up you can start that process from your own SQL Tool. If you started making your own SQL Tools or you would like for me to cover a topic please leave me a comment.

Add a new CheckBox

Step 1. In this step we will be adding a new CheckBox object called $checkBox3.

Open up Windows PowerShell ISE and then copy the Powershell Script from my first blog post. Then you can follow the next 5 steps to add a new CheckBox to the SQL Tool Form.

Find “$checkBox2 = New-Object System.Windows.Forms.CheckBox”, it is near the top of the script. You can either copy this line of code and paste it right underneath  and change from $checkBox2 to $checkBox3  or copy\paste the $checkBox3 object below. This Step will create a new Checkbox object called $checkBox3.

$checkBox3 = New-Object System.Windows.Forms.CheckBox  

 

Step 2. In this step we will be adding $checkBox3 to the click action of Button 1.

In the Section “#Button 1 Action” find “if ($checkBox2.Checked)    {  LastReboot }”, You can either copy this line of code and paste it right underneath and change from $checkBox2 to $checkBox3 and also change “LastReboot” to “Request” or you can copy\paste the line of code below. When CheckBox3 is checked and you click the Run button, it will run the PowerShell Function called “Requests” that we will create is step 5.

if ($checkBox3.Checked)    {  Requests }

Step 3. Need to Add $checkBox3 to the Button 2 on click action

In the Section “#Button 2 Action” find “if ($checkBox2.Checked) {$checkBox2.CheckState = 0}”, You can either copy this line of code and paste it right underneath and change from $checkBox2 to $checkBox3 or or you can copy\paste the line of code below. When the Clear CheckBox Button is click it will clear all the CheckBox’s if checked.

if ($checkBox3.Checked) {$checkBox3.CheckState = 0}

 

Step 4. This step will add the CheckBox to the SQL Tool form

Copy the entire section called “#LastReBoot” and paste it into NotePad. Then use Find & Replace to find “checkBox2” and replace with “checkBox3”. Next copy the code from Notepad and paste it back into the PowerShell script under the section called  “#LastReBoot”. Name the new section #Request. Change the “$System_Drawing_Point.Y =” value from 80 to 110, this will lower Request Checkbox under the LastReBoot.

#Request
$checkBox3.UseVisualStyleBackColor = $True
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 104
$System_Drawing_Size.Height = 24
$checkBox3.Size = $System_Drawing_Size
$checkBox3.TabIndex = 0
$checkBox3.Text = "Requests"
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 25
$System_Drawing_Point.Y = 110
$checkBox3.Location = $System_Drawing_Point
$checkBox3.DataBindings.DefaultDataSourceUpdateMode = 0
$checkBox3.Name = "checkBox3"
$form1.Controls.Add($checkBox3)

Step 5. Create the PowerShell Function for the new CheckBox

Copy\paste the code below underneath the Section called SQLVersion or you can add your own SQL Script. If you add your own SQL script remember to change Requests to the new name in the PowerShell script.

 

Function Requests
{
[string]$SQLVersion = @"
SELECT
   db_name(r.database_id) as database_name, r.session_id AS SPID,r.status,s.host_name,
     r.start_time,(r.total_elapsed_time/1000) AS 'TotalElapsedTime Sec',
   r.wait_type as current_wait_type,r.wait_resource as current_wait_resource,
   r.blocking_session_id,r.logical_reads,r.reads,r.cpu_time as cpu_time_ms,r.writes,r.row_count,
   substring(st.text,r.statement_start_offset/2,
   (CASE WHEN r.statement_end_offset = -1 THEN len(convert(nvarchar(max), st.text)) * 2 ELSE r.statement_end_offset END - r.statement_start_offset)/2) as statement
FROM
   sys.dm_exec_requests r
      LEFT OUTER JOIN sys.dm_exec_sessions s on s.session_id = r.session_id
      LEFT OUTER JOIN sys.dm_exec_connections c on c.connection_id = r.connection_id       
      CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st 
WHERE r.status NOT IN ('background','sleeping')
"@ 
 $Server = $objTextBox.text
Invoke-Sqlcmd3 -ServerInstance $Server -Database Master -Query $SQLVersion | Out-GridView -Title "$server Request"
}

Create your own SQL Tools with PowerShell and Windows Forms

By combining PowerShell and Windows Forms you can create your own SQL Tools. From the Windows Form you can execute your SQL and PowerShell scripts with just a couple clicks of a mouse. The added bonus is now your scripts are in a central location that is tailored to your needs.   I have created a base form below so you can get started adding you own scripts. If you’re new to PowerShell save the code below as a ps1 file and then run it from the PowerShell.

Untitled


####################### PowerShell Code ###############################


function CreateForm {
Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.drawing


$form1 = New-Object System.Windows.Forms.Form
$button1 = New-Object System.Windows.Forms.Button
$button2 = New-Object System.Windows.Forms.Button
$checkBox1 = New-Object System.Windows.Forms.CheckBox
$checkBox2 = New-Object System.Windows.Forms.CheckBox
$InitialFormWindowState = New-Object System.Windows.Forms.FormWindowState

#Form Parameter
$form1.Text = “My PowerShell Form”
$form1.Name = “form1”
$form1.DataBindings.DefaultDataSourceUpdateMode = 0
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 550
$System_Drawing_Size.Height = 150
$form1.ClientSize = $System_Drawing_Size

#Add Label and TextBox
$objLabel = New-Object System.Windows.Forms.Label
$objLabel.Location = New-Object System.Drawing.Size(10,25)
$objLabel.Size = New-Object System.Drawing.Size(110,50)
$objLabel.Text = “Enter Server Name”
$form1.Controls.Add($objLabel)
$objTextBox = New-Object System.Windows.Forms.TextBox
$objTextBox.Location = New-Object System.Drawing.Size(120,25)
$objTextBox.Size = New-Object System.Drawing.Size(200,20)
$form1.Controls.Add($objTextBox)

#Button 1 Action
$button1_RunOnClick=
{
if ($checkBox1.Checked) { SQLVersion }
if ($checkBox2.Checked) { LastReboot }
}

#Button 2 Action
$button2_RunOnClick=
{
if ($checkBox1.Checked) {$checkBox1.CheckState = 0}
if ($checkBox2.Checked) {$checkBox2.CheckState = 0}
}

$OnLoadForm_StateCorrection=
{
$form1.WindowState = $InitialFormWindowState
}

#Button 1
$button1.TabIndex = 4
$button1.Name = “button1”
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 75
$System_Drawing_Size.Height = 23
$button1.Size = $System_Drawing_Size
$button1.UseVisualStyleBackColor = $True
$button1.Text = “Run”
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 350
$System_Drawing_Point.Y = 25
$button1.Location = $System_Drawing_Point
$button1.DataBindings.DefaultDataSourceUpdateMode = 0
$button1.add_Click($button1_RunOnClick)
$form1.Controls.Add($button1)


#Button 2
$button2.TabIndex = 4
$button2.Name = “button2”
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 150
$System_Drawing_Size.Height = 23
$button2.Size = $System_Drawing_Size
$button2.UseVisualStyleBackColor = $True
$button2.Text = “Clear CheckBox”
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 350
$System_Drawing_Point.Y = 60
$button2.Location = $System_Drawing_Point
$button2.DataBindings.DefaultDataSourceUpdateMode = 0
$button2.add_Click($button2_RunOnClick)
$form1.Controls.Add($button2)
#SQLVersion
$checkBox1.UseVisualStyleBackColor = $True
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 104
$System_Drawing_Size.Height = 24
$checkBox1.Size = $System_Drawing_Size
$checkBox1.TabIndex = 0
$checkBox1.Text = “SQL Version”
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 25
$System_Drawing_Point.Y = 80
$checkBox1.Location = $System_Drawing_Point
$checkBox1.DataBindings.DefaultDataSourceUpdateMode = 0
$checkBox1.Name = “checkBox1”
$form1.Controls.Add($checkBox1)

#LastReBoot
$checkBox2.UseVisualStyleBackColor = $True
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 104
$System_Drawing_Size.Height = 24
$checkBox2.Size = $System_Drawing_Size
$checkBox2.TabIndex = 1
$checkBox2.Text = “Last Reboot”
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 175
$System_Drawing_Point.Y = 80
$checkBox2.Location = $System_Drawing_Point
$checkBox2.DataBindings.DefaultDataSourceUpdateMode = 0
$checkBox2.Name = “checkBox2”
$form1.Controls.Add($checkBox2)

#Save the initial state of the form
$InitialFormWindowState = $form1.WindowState
#Init the OnLoad event to correct the initial state of the form
$form1.add_Load($OnLoadForm_StateCorrection)
#Show the Form
$form1.ShowDialog()| Out-Null
} #End function CreateForm

function Invoke-Sqlcmd3
{
param(
[string]$Query,
[string]$Database=”tempdb”,
[Int32]$QueryTimeout=30
)
$conn=new-object System.Data.SqlClient.SQLConnection
$conn.ConnectionString=”Server={0};Database={1};Integrated Security=True” -f $Server,$Database
$conn.Open()
$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
$ds.Tables[0]
}

Function SQLVersion
{
[string]$SQLVersion = @”
SELECT @@Version
“@
$Server = $objTextBox.text
Invoke-Sqlcmd3 -ServerInstance $Server -Database Master -Query $SQLVersion | Out-GridView -Title “$server SQL Server Version”
}

Function LastReboot
{
$Server = $objTextBox.text
$wmi = Get-WmiObject -Class Win32_OperatingSystem -Computer $server
$wmi.ConvertToDatetime($wmi.LastBootUpTime) | Select DateTime | Out-GridView -Title “$Server Last Reboot”
}
#Call the Function
CreateForm


####################### PowerShell Code ###############################