HowTo: Create a SQL Tester w/ GUI

Be sure to check out my Scripting4Crypto initiative. It’s a fun way to get into using cryptocurrencies all while getting your PowerShell needs met.

In this post I will show you how to create a GUI for testing Select SQL statements.  I have used this for developing / testing Select SQL statements to be used within Environment Attributes when I don’t have SQL Management Studio available. Comes in handy.

All of the ProjectWise related cmdlets are available using the PWPS_DAB module. At the time of this post, I am using version 1.29.4.0. Take a look at the help for each of the cmdlets to become familiar with their functionality, available parameters, etc.

• New-PWLogin
• Undo-PWLogin
• Get-PWCurrentDatasource
• Select-PWSQL

---

Define GUI

The first thing I want to do is specify how the GUI will look and which controls will be included. I will be including a method to log in and out of a ProjectWise datasource.  There will a text box for entering the SQL Select statement to test and a datagridview to display the returned data.

Default Values

The default values are standard control values such as the size of a control or alignment of the text. These will be used to define the controls.

#region GUI DEFINITION

#region DEFAULT VALUES

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

[int] $frmSizeHeight = 500
[int] $frmSizeWidth = 800
$Font = 'Microsoft Sans Serif,10'
$lblAlign = 'MiddleRight'
$lblSize = '120, 20'
$txtAlign = 'Left'
$txtSize = '360, 20'
$btnSize = '25, 22'
$pbSize = "$($frmSizeWidth), 20"
$txtWidthDerived = $($frmSizeWidth - 120) / 2

#endregion DEFAULT VALUES

Create FORM

Here we will create the form definition.

#region FORM

$frmUserInfo = New-Object -TypeName System.Windows.Forms.Form
$frmUserInfo.ClientSize = New-Object -TypeName System.Drawing.Size( $frmSizeWidth, $frmSizeHeight)
$frmUserInfo.Text = 'ProjectWise SQL Tester'
$frmUserInfo.TopMost = $true
$frmUserInfo.TopLevel = $true
$frmUserInfo.MinimumSize = New-Object -TypeName System.Drawing.Size( $($frmSizeWidth/2), $($frmSizeHeight/2))
$frmUserInfo.StartPosition = 'CenterScreen'
$frmUserInfo.Font = $Font

#endregion FORM

Create Datasource Controls

Here we will define all of the controls related to the ProjectWise Datasource.

#region DATASOURCE

#region DATASOURCE (Server/Name)

# Control Y location Value
$Datasource_Y = 10
$lblDatasource = New-Object -TypeName System.Windows.Forms.Label
$lblDatasource.Location = New-Object -TypeName System.Drawing.Point -ArgumentList (10,$Datasource_Y)
$lblDatasource.Text = 'Datasource:'
$lblDatasource.AutoSize = $false
$lblDatasource.Size = $lblSize
$lblDatasource.TextAlign = $lblAlign

$txtDatasource = New-Object -TypeName System.Windows.Forms.TextBox
$txtDatasource.Location = New-Object -TypeName System.Drawing.Point -ArgumentList (130,$Datasource_Y)
$txtDatasource.Multiline = $false
$txtDatasource.Width = $($frmSizeWidth - $lblDatasource.Width - 160)
$txtDatasource.Size = ("$($txtDatasource.Width), 20") 
$txtDatasource.ReadOnly = $true
$txtDatasource.Anchor = 'top,right,left'

$btnDatasource = New-Object -TypeName System.Windows.Forms.Button
$btnDatasource.Location = New-Object -TypeName System.Drawing.Point -ArgumentList ($($frmSizeWidth - 135),$Datasource_Y)
$btnDatasource.Text = 'Login'
$btnDatasource.Size = '100, 25'
$btnDatasource.Anchor = 'top,right'

#endregion DATASOURCE (Server/Name)

#region DATASOURCE SERVER

# Control Y location Value
$Datasource_Y = 40
$lblDSServer = New-Object -TypeName System.Windows.Forms.Label
$lblDSServer.Location = New-Object -TypeName System.Drawing.Point -ArgumentList (10,$Datasource_Y)
$lblDSServer.Text = 'Server:'
$lblDSServer.AutoSize = $false
$lblDSServer.Size = $lblSize
$lblDSServer.TextAlign = $lblAlign

$txtDSServer = New-Object -TypeName System.Windows.Forms.TextBox
$txtDSServer.Location = New-Object -TypeName System.Drawing.Point -ArgumentList (130,$Datasource_Y)
$txtDSServer.Multiline = $false
$txtDSServer.Width = $txtWidthDerived
$txtDSServer.ReadOnly = $true
$txtDSServer.Anchor = 'top,left'

#endregion DATASOURCE SERVER

#region DATASOURCE NAME

# Control Y location Value
$Datasource_Y = 40
$lblDSName = New-Object -TypeName System.Windows.Forms.Label
$lblDSName.Location = New-Object -TypeName System.Drawing.Point -ArgumentList ($($txtDSServer.Right),$Datasource_Y)
$lblDSName.Text = 'Name:'
$lblDSName.AutoSize = $true
$lblDSName.TextAlign = $lblAlign

$txtDSName = New-Object -TypeName System.Windows.Forms.TextBox
$txtDSName.Location = New-Object -TypeName System.Drawing.Point -ArgumentList ($($($lblDSName.Left) + 50),$Datasource_Y)
$txtDSName.Multiline = $false
$txtDSName.Width = $txtWidthDerived - 95
$txtDSName.ReadOnly = $true
$txtDSName.Anchor = 'top,right,left'

#endregion DATASOURCE NAME
#endregion DATASOURCE

Create SQL Controls

Now we create the SQL related controls.

#region SQL

$lblSQL = New-Object -TypeName System.Windows.Forms.Label
$lblSQL.Location = New-Object -TypeName System.Drawing.Point -ArgumentList (10, 80)
$lblSQL.Text = 'SQL Statement:'
$lblSQL.AutoSize = $false
$lblSQL.Size = $lblSize
$lblSQL.TextAlign = $lblAlign

$txtSQL = New-Object -TypeName System.Windows.Forms.TextBox
$txtSQL.Location = New-Object -TypeName System.Drawing.Point -ArgumentList ('130', '80')
$txtSQL.Multiline = $true
$txtSQL.Width = $($frmSizeWidth - 165)
$txtSQL.Height = '60'
$txtSQL.ReadOnly = $true
$txtSQL.Anchor = 'top,left,right'

$btnSQL = New-Object -TypeName System.Windows.Forms.Button
$btnSQL.Location = New-Object -TypeName System.Drawing.Point -ArgumentList ($($lblSQL.Location.X + 10), $($lblSQL.Location.X + 100))
$btnSQL.Text = 'RUN'
$btnSQL.Size = '100, 25'
$btnSQL.Anchor = 'top,left'
$btnSQL.Enabled = $false

$dgvSQL = New-Object -TypeName System.Windows.Forms.DataGridView
$dgvSQL.Location = New-Object -TypeName System.Drawing.Point -ArgumentList (10, 180)
$dgvSQL.Text = 'Type;Value'
$dgvSQL.Width = $($frmSizeWidth - 20)
$dgvSQL.Height = 250
$dgvSQL.AllowUserToAddRows = $false
$dgvSQL.AllowUserToDeleteRows = $false
$dgvSQL.Font = $Font
$dgvSQL.Anchor = 'top,left,right,bottom'

#endregion SQL

Create Status Bar Control

Add a status bar for messages pertaining to the process.

#region STATUS BAR

$StatusBar1 = New-Object -TypeName System.Windows.Forms.StatusBar
$StatusBar1.location = New-Object -TypeName System.Drawing.Point -ArgumentList (0,($frmUserInfo.ClientSize.Height - $StatusBar1.Size.Height))
$StatusBar1.Size = $pbSize
$StatusBar1.Anchor = 'right,bottom,left'
$StatusBar1.Text = 'Status'
$StatusBar1.Font = $Font

#endregion STATUS BAR

Add Controls to the FORM

Finally, we add all of the controls to the form. If you do not add the controls here, they will not show in the form when it is displayed.

#region FORMS CONTROLS

$frmUserInfo.controls.AddRange(@( 
    $lblDatasource, $txtDatasource, $btnDatasource,
    $lblDSServer, $txtDSServer,
    $lblDSName, $txtDSName,
    $lblSQL, $txtSQL, $btnSQL, $dgvSQL,
    $StatusBar1
))

#endregion FORMS CONTROLS

 

Define GUI Events

The GUI Events tell the form what to do when a user interacts with the controls. For example, when a button is clicked or text is a field is changed.

Form Activated and Load Events

We didn’t specify anything to do for these two events.

$frmUserInfo.Add_Activated({ }) # end $frmUserInfo.Add_Activated...
$frmUserInfo.Add_Load({ }) # end $frmUserInfo.Add_Load...

Datasource Controls Events

These events deal with the datasource controls.

#region DATASOURCE EVENTS

$btnDatasource.Add_Click({

    if($btnDatasource.Text -eq 'Logout'){
        Undo-PWLogin
        $StatusBar1.Text = "Successfully logged out of ProjectWise datasource '$($txtDatasource.Text)'."
        $btnDatasource.Text = 'Login'
        $txtDatasource.ResetText()
        $txtSQL.ResetText()
        $dgvSQL.DataSource = [string]::Empty 
    } else {
        if(New-PWLogin){
            $txtDatasource.Text = Get-PWCurrentDatasource
            $StatusBar1.Text = "Successfully logged into ProjectWise datasource '$($txtDatasource.Text)'."
            $btnDatasource.Text = 'Logout'
        } else {
            $StatusBar1.Text = 'Failed to log into ProjectWise datasource.'
        }
    }
}) # end $btnDatasource.Add_Click...

$txtDatasource.Add_TextChanged({
    [string[]]$pwDS = (Get-PWCurrentDatasource).Split(':')
    $txtDSServer.Text = $pwDS[0]
    $txtDSName.Text = $pwDS[1]

    if($txtDatasource.Text.Length -gt 0){
        $txtSQL.ReadOnly = $false
    } else {
        $txtSQL.ReadOnly = $true
    }
}) # end $txtDatasource.Add_TextChanged... 

#endregion DATASOURCE EVENTS

SQL Query Controls Events

These events deal with the SQL Query controls.

#region Run Query

$txtSQL.Add_TextChanged({

if($txtSQL.Text.Length -gt 0 -and $txtSQL.Text.ToLower().StartsWith('select')){
    $btnSQL.Enabled = $true
} else {
    $btnSQL.Enabled = $false
    $StatusBar1.Text = "SQL Statement MUST start with SELECT."
}

})

$btnSQL.Add_Click({

    if($btnSQL.Text -eq "Clear"){
        $StatusBar1.Text = "Cleared datagridview."
        $dgvSQL.DataSource = [string]::Empty
        $btnSQL.Text = "Run"
    } else {

        $SQLStatement = $txtSQL.Text
        if($SQLStatement.ToLower().StartsWith('select')){

            $StatusBar1.Text = "'$SQLStatement' is a valid Select statement."

            $SQLResults = Select-PWSQL -SQLSelectStatement $SQLStatement

            if($SQLResults.Rows.Count -gt 0){
                $StatusBar1.Text = "$($SQLResults.Rows.Count) rows returned."

                # Populate datagridview.
                $dgvSQL.DataSource = $SQLResults

                $btnSQL.Text = "Clear"

            } else {
                $StatusBar1.Text = "No data returned."
            }
        } else {
            $StatusBar1.Text = "Invalid Select statement."
        }
    }

}) # end $btnSQL.Add_Click({...

#endregion Run Query

ProjectWise Login

The following deals with the logging in and out of a ProjectWise datasource.

#region PROJECTWISE LOGIN

if(Get-PWCurrentDatasource) {
    $txtDatasource.Text = Get-PWCurrentDatasource
    $StatusBar1.Text = "Currently logged into ProjectWise datasource '$($txtDatasource.Text)'."
    $btnDatasource.Text = 'Logout'
}

#endregion PROJECTWISE LOGIN

Show Dialog

The .ShowDialog() method will display the form for use.

[void]$frmUserInfo.ShowDialog() 

 

Using the SQL Tester

Log Into ProjectWise Datasource

That we have the form defined, we can run it. The first thing we need to do is log into a ProjectWise datasource. Click on the Login button. You will be prompted with the standard ProjectWise log in dialog.

Running a Simple Query

Once we are logged in, the SQL Statement textbox will enable and we can enter a query to run. The query MUST begin with the word Select. No space before Select. The RUN button will not enable until a valid select statement is entered.

The following is a simple query. And a sample of the data returned.

Click the Clear button to reset the datagridview.

Running a More Difficult Query

Here is a slightly more complicated query just to demonstrate a little more functionality. This should handle most SQL select statements. I cannot guarantee it will handle all.

Log Out of ProjectWise Datasource

Again you can use the Clear button to reset the datagridview.

Be sure to log  out of your ProjectWise datasource prior to closing the form. You do this by clicking on the Logout button. When you log out of your datasource, all of the controls within the form should be reset.

Summary

Here we created a simple tool to develop and test SQL queries and demonstrated its use. This tool has come in handy for me and I hope you find it useful as well.

---

Experiment with it and have fun.

Hopefully, you find this useful. Please let me know if you have any questions or comments.  If you like this post, please click the Like button at the bottom of the page.