Table of Contents

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