How to Execute a SQL Query in PowerShell (GUI)
Introduction
It does a query from the sample Database AdventureWorks on the SQL Server where it is located and fills a datagrid control from a SQL query on a GUI form.
How to use
Enter the SQL query in the textbox, click the Query button and it will perform the query against the SQL Server and autofill the datagrid.
The Clear button clears the datagrid.
The Quit button closes the form and quits the applicaton.
Powershell source code
Function Get-Query([string]$SQLText)
{
$ConnectionString = "Server=localhost;Database=AdventureWorks2012;Trusted_Connection=yes;"
$sqlConnection = new-object System.Data.SqlClient.SqlConnection $ConnectionString
$sqlConnection.Open()
#Create a command object
$sqlCommand = $sqlConnection.CreateCommand()
$sqlCommand.CommandText = $SQLText
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcommand
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataSet) | out-null
# Close the database connection
$sqlConnection.Close()
$dataTable = new-object "System.Data.DataTable" "SQL"
$dataTable = $dataSet.Tables[0]
Return $DataTable
}
$GetQuery=
{
Trap {
Write-Debug "Error trapped in GetQuery"
Write-Warning $_.Exception.message
Continue
}
$SQLText = $txtSQLQuery.Text
$statusBar.Text = 'Getting Query: '+$SQLText
$Query=Get-Query($SQLText)
$statusBar.Text = 'Filling Datagrid'
$array= New-Object System.Collections.ArrayList
$array.AddRange( ($Query | Select *) )
$dataGridView.DataSource=$Null
$dataGridView.AutoGenerateColumns = $True
$DataGridView.DataSource = $array
$dataGridView.Refresh
$statusBar.Text = $SQLText
} #End GetQuery
$Clear=
{
Write-Debug "clearing the datagrid"
$dataGridView.DataSource=$Null
$dataGridView.Refresh
$statusBar.Text = 'Ready'
} #End Clear
$Quit=
{
Write-Debug "closing the form"
$form1.Close()
} #End Quit
function GenerateForm {
Write-Debug "Creating form objects"
$form1 = New-Object System.Windows.Forms.Form
$txtSQLQuery = New-Object System.Windows.Forms.TextBox
$btnGo = New-Object System.Windows.Forms.Button
$btnClear = New-Object System.Windows.Forms.Button
$btnQuit = New-Object System.Windows.Forms.Button
$dataGridView = New-Object System.Windows.Forms.DataGridView
$statusBar = New-Object System.Windows.Forms.StatusBar
$form1.Name = 'form1'
$form1.Text = 'Display Adventure Works Data'
$form1.DataBindings.DefaultDataSourceUpdateMode = 0
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 890
$System_Drawing_Size.Height = 359
$form1.ClientSize = $System_Drawing_Size
$form1.StartPosition = 1
$form1.BackColor = [System.Drawing.Color]::FromArgb(255,185,209,234)
$txtSQLQuery.Text = 'Select * FROM HumanResources.vEmployee;'
$txtSQLQuery.Name = 'txtSQLQuery'
$txtSQLQuery.TabIndex = 0
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 398
$System_Drawing_Size.Height = 20
$txtSQLQuery.Size = $System_Drawing_Size
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 13
$System_Drawing_Point.Y = 33
$txtSQLQuery.Location = $System_Drawing_Point
$txtSQLQuery.DataBindings.DefaultDataSourceUpdateMode = 0
$form1.Controls.Add($txtSQLQuery)
$btnGo.UseVisualStyleBackColor = $True
$btnGo.Text = 'Get Query'
$btnGo.DataBindings.DefaultDataSourceUpdateMode = 0
$btnGo.TabIndex = 1
$btnGo.Name = 'btnGo'
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 75
$System_Drawing_Size.Height = 23
$btnGo.Size = $System_Drawing_Size
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 433
$System_Drawing_Point.Y = 31
$btnGo.Location = $System_Drawing_Point
$btnGo.add_Click($GetQuery)
$form1.Controls.Add($btnGo)
$btnClear.UseVisualStyleBackColor = $True
$btnClear.Text = 'Clear'
$btnClear.DataBindings.DefaultDataSourceUpdateMode = 0
$btnClear.TabIndex = 1
$btnClear.Name = 'btnClear'
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 75
$System_Drawing_Size.Height = 23
$btnClear.Size = $System_Drawing_Size
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 533
$System_Drawing_Point.Y = 31
$btnClear.Location = $System_Drawing_Point
$btnClear.add_Click($Clear)
$form1.Controls.Add($btnClear)
$btnQuit.UseVisualStyleBackColor = $True
$btnQuit.Text = 'Close'
$btnQuit.DataBindings.DefaultDataSourceUpdateMode = 0
$btnQuit.TabIndex = 2
$btnQuit.Name = 'btnQuit'
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 75
$System_Drawing_Size.Height = 23
$btnQuit.Size = $System_Drawing_Size
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 633
$System_Drawing_Point.Y = 30
$btnQuit.Location = $System_Drawing_Point
$btnQuit.add_Click($Quit)
$form1.Controls.Add($btnQuit)
$dataGridView.RowTemplate.DefaultCellStyle.ForeColor = [System.Drawing.Color]::FromArgb(255,0,128,0)
$dataGridView.Name = 'dataGridView'
$dataGridView.DataBindings.DefaultDataSourceUpdateMode = 0
$dataGridView.ReadOnly = $True
$dataGridView.AllowUserToDeleteRows = $False
$dataGridView.RowHeadersVisible = $False
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 870
$System_Drawing_Size.Height = 260
$dataGridView.Size = $System_Drawing_Size
$dataGridView.TabIndex = 8
$dataGridView.Anchor = 15
$dataGridView.AutoSizeColumnsMode = 16
$dataGridView.AllowUserToAddRows = $False
$dataGridView.ColumnHeadersHeightSizeMode = 2
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 13
$System_Drawing_Point.Y = 70
$dataGridView.Location = $System_Drawing_Point
$dataGridView.AllowUserToOrderColumns = $True
$dataGridView.AutoResizeColumns([System.Windows.Forms.DataGridViewAutoSizeColumnsMode.AllCells]::AllCells)
$DataGridViewAutoSizeColumnsMode.AllCells
$form1.Controls.Add($dataGridView)
$statusBar.Name = 'statusBar'
$statusBar.DataBindings.DefaultDataSourceUpdateMode = 0
$statusBar.TabIndex = 4
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 428
$System_Drawing_Size.Height = 22
$statusBar.Size = $System_Drawing_Size
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 0
$System_Drawing_Point.Y = 337
$statusBar.Location = $System_Drawing_Point
$statusBar.Text = 'Ready'
$form1.Controls.Add($statusBar)
#Show the Form
Write-Debug "ShowDialog()"
$form1.ShowDialog()| Out-Null
} #End Function
#Call the Function
Write-Debug "Call GenerateForm"
GenerateForm