SQL Dialog Box for PowerShell

I had a need the other day to build a PowerShell script to automate some tasks for installing a solution I built.

I've always believed that its best to limit how much untested data entry a user has to do. Hence I wanted a dialog box for SQL connections and be able to test the connection.

The following is what I came up with:

function Show-ConnectionDialog()

{

$arry = "SQL Server database login", "SQL Server", "Username", "Password*", "Database"

#create input form

$inputForm = New-Object System.Windows.Forms.Form

$inputForm.Text = $arry[0]

$inputForm.Size = New-Object System.Drawing.Size(330, 100)

$inputForm.StartPosition = "CenterScreen"

[System.Windows.Forms.Application]::EnableVisualStyles()

#handle button click events

$inputForm.KeyPreview = $true

$inputForm.Add_KeyDown(

{

if ($_.KeyCode -eq "Enter")

{

$inputForm.Close()

}

})

$inputForm.Add_KeyDown(

{

if ($_.KeyCode -eq "Escape")

{

$inputForm.Close()

}

})

#create OK button

$okButton = New-Object System.Windows.Forms.Button

$okButton.Size = New-Object System.Drawing.Size(75, 23)

$okButton.Text = "OK"

$okButton.Add_Click(

{

$inputForm.DialogResult = [System.Windows.Forms.DialogResult]::OK

})

$inputForm.Controls.Add($okButton)

$inputForm.AcceptButton = $okButton

#create Cancel button

$cancelButton = New-Object System.Windows.Forms.Button

$cancelButton.Size = New-Object System.Drawing.Size(75,23)

$cancelButton.Text = "Cancel"

$inputForm.Controls.Add($cancelButton)

$inputForm.CancelButton = $cancelButton

#create Test button

$TestButton = New-Object System.Windows.Forms.Button

$TestButton.Size = New-Object System.Drawing.Size(75, 23)

$TestButton.Text = "Test"

$TestButton.Add_Click(

{
$server = $txtBoxes[0].Text

$user = $txtBoxes[1].Text

$password = $txtBoxes[2].Text

$database = $txtBoxes[3].Text

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
If ($user -ne "")
{
$SqlConnection.ConnectionString = "Server = $server; Database = $database; Integrated Security = false; User ID = $user; Password = $password;"
}else{
$SqlConnection.ConnectionString = "Server = $server; Database = $database; Integrated Security = true;"
}

#write-host $SqlConnection.ConnectionString

try{
$SqlConnection.Open()
[System.Windows.MessageBox]::Show('Connection Successful')

}catch{
[System.Windows.MessageBox]::Show('Connection Failed')
}
})

$inputForm.Controls.Add($TestButton)

[System.Collections.Generic.List[System.Windows.Forms.TextBox]] $txtBoxes = New-Object System.Collections.Generic.List[System.Windows.Forms.TextBox]

$y = -15;

for($i=1;$i -lt $arry.Count;$i++)

{

$y+=30

$inputForm.Height += 30

#create label

$objLabel = New-Object System.Windows.Forms.Label

$objLabel.Location = New-Object System.Drawing.Size(10, $y)

$objLabel.Size = New-Object System.Drawing.Size(280, 20)

$objLabel.Text = $arry[$i] +":"

$inputForm.Controls.Add($objLabel)

$y+=20

$inputForm.Height+=20

#create TextBox

$objTextBox = New-Object System.Windows.Forms.TextBox

$objTextBox.Location = New-Object System.Drawing.Size(10, $y)

$objTextBox.Size = New-Object System.Drawing.Size(290, 20)

$inputForm.Controls.Add($objTextBox)

$txtBoxes.Add($objTextBox)

$cancelButton.Location = New-Object System.Drawing.Size(110, (35+$y))

$okButton.Location = New-Object System.Drawing.Size(35, (35+$y))

$TestButton.Location = New-Object System.Drawing.Size(185, (35+$y))

if ($arry[$i].Contains("*"))

{

$objLabel.Text = ($objLabel.Text -replace '\*','')

$objTextBox.UseSystemPasswordChar = $true

}

}

$inputForm.Topmost = $true

$inputForm.MinimizeBox = $false

$inputForm.MaximizeBox = $false

$inputForm.AutoSizeMode = [System.Windows.Forms.AutoSizeMode]::GrowAndShrink

$inputForm.SizeGripStyle = [System.Windows.Forms.SizeGripStyle]::Hide

$inputForm.Add_Shown({$inputForm.Activate(); $txtBoxes[0].Focus()})

if ($inputForm.ShowDialog() -ne [System.Windows.Forms.DialogResult]::OK)

{

exit

}

return ($txtBoxes | Select-Object {$_.Text} -ExpandProperty Text)

}

Add-Type -AssemblyName "system.windows.forms"

#variables for our SQL Server login form

$login = Show-ConnectionDialog

$server = $login[0]

$user = $login[1]

$password = $login[2]

$database = $login[3]

$ConnectionString = "Server = $server; Database = $database; Integrated Security = False; User ID = $user; Password = $password;"

$ConnectionString = "Server = $server; Database = $database; Integrated Security = True;"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

$SqlConnection.ConnectionString = $ConnectionString

$SqlConnection.Open()

Write-Output $ConnectionString