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 ###############################

Advertisements

2 comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s