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.

$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"

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 = @"
   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,
   (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
   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"