Use PowerShell to Query and Display Data

• PSShowSQLData

Introduction

Designed by Microsoft to become the primary command-line tool for network and server admins, PowerShell is vastly overpowered relative to that ecospace. It is a fully functional language in itself and integrates well with many other tools and languages, including especially the functions available in the .NET Framework that all .NET programmers use.

Background

The idea of this presentation is to demonstrate how PowerShell can be used to do a SQL query and display the resulting data in a simple GUI. This should suggest to readers what possibilities there are to be explored in using PowerShell for a large variety of purposes.

This solution gives an illustration of how to use .NET Framework functions in the context of PowerShell, including
the attachment of a Windows form from within Powershell.

Using the Code

As one might do in C#, the code sets up references to the Windows Forms DLL and the SQLClient DLL. The references are instantiated and their methods invoked to query and display some data.

To run the code, you need to build a SQL Server Express database and table on your machine: PowerShellDemo\PersonInfo. The table should have columns: ID [smallint], Last, First, Middle, and CellPhone, all being nchar[100]. The ID column should be an 'identity' column. Populate five or six users into the table for use in the app.

The code can be downloaded to a directory and run using a command at the PowerShell prompt: <& 'YourAppName.ps1'>. If you don't precede the file with the '&', PowerShell will not run the script. If you are using Windows 7, PowerShell v.2 is included and you can also open the .ps1 file in the PowerShell ISE, the 'integrated scripting environment', a free development tool for PowerShell. Things to note: in PS, the '#' symbol is used for comments. A multiline comment would be started with '<#' and then closed with '#>.' The scopes for variables are Current, Local, Script, and Global, the details of which can be viewed by using the 'Help' button on the PS ISE toolbar.

##Script-level variables
$ctr = $null#0
$records_counter = $null#0
$btn_counter = 0

#arrays for values
$ids = [string[]]$id
$lastnames = [string[]]$last = $null
$firstnames = [string[]]$first = $null
$middlenames = [string[]]$middle = $null
$cellphones = [string[]]$cphone = $null

##SQL, Form and Drawing references and new SQL objects
$sql_client_dll = 'System.Data.SqlClient'
$formtype = 'System.Windows.Forms'
$drawinfo = 'System.Drawing'

##new objects based on references above
$form = New-Object "$formtype.form" 
$sql_client = New-Object "$sql_client_dll.SqlConnection"
$sql_client_command = New-Object "$sql_client_dll.SqlCommand"
$textbox1 = New-Object "$formtype.textbox"
$textbox2 = New-Object "$formtype.textbox"
$textbox3 = New-Object "$formtype.textbox"
$textbox4 = New-Object "$formtype.textbox"
$textbox5 = New-Object "$formtype.textbox"
$button = New-Object "$formtype.button"
$button_next = New-Object "$formtype.button"
$point1 = New-Object "$drawinfo.point"
$point2 = New-Object "$drawinfo.point"
$point3 = new-object "$drawinfo.point"

##provide 'click' event handlers for the buttons
$button.add_Click({button_click_function(0)})
$button_next.add_Click({next_record})

##organize the form
$form.Controls.Add($button)
$form.Controls.Add($button_next)
$form.Controls.Add($textbox1)
$form.Controls.Add($textbox2)
$form.Controls.Add($textbox3)
$form.Controls.Add($textbox4)
$form.Controls.Add($textbox5)
$point1.X = 170;
$point1.Y = 250;
$point3.X = 270;
$point3.Y = 250;
$button.Location = $point1 
$button_next.Location = $point3
$button_next.Width = 300
$button.Text = "Run Query" 
$button_next.Text = "Click to see records" 
$point2.X = 50
$point2.Y = 50
$textbox1.Location = $point2
$point2.Y = 100
$textbox2.Location = $point2
$point2.Y = 150
$textbox3.Location = $point2
$textbox3.Width = 100
$point2.Y = 200
$textbox4.Location = $point2
$point2.Y = 250
$textbox5.Location = $point2
$point2.Y = 300

##show the form
$form.Showdialog()
 
## 3 functions
function button_click_function($y)
{
  $Script:ctr=0 #re-set this Script-level variable to zero
  get_sql_data  #query the database
  $Global:button.Enabled = $false
}

function next_record()
{
#this function pages through the query results
   $Script:textbox1.Text = $Script:ids[$Script:ctr]
   $Script:textbox2.Text = $Script:firstnames[$Script:ctr]
   $Script:textbox3.Text = $Script:middlenames[$Script:ctr]
   $Script:textbox4.Text = $Script:lastnames[$Script:ctr]
   $Script:textbox5.Text = $Script:cellphones[$Script:ctr]
   $Script:ctr+=1
   
   if($Script:ctr -gt $Script:records_counter)
   {
       $Script:ctr = 0  #when user clicks beyond number of records, go back to zero
       write-host 'CTR= ',$Script:ctr
   }
}

function get_sql_data()
{
$command = $Global:sql_client.ConnectionString = `
 "Data Source=OWNER-PC\SQLEXPRESS; Initial Catalog=PowerShellDemo;Integrated Security = true;"
$Global:sql_client_command.CommandText = `
    'select convert(nvarchar,id), Last, First, Middle, CellPhone from PersonInfo' 
$Global:sql_client_command.Connection = $Global:sql_client
$Global:sql_client.Open()
$rdr = $Global:sql_client_command.ExecuteReader()  
$flds_ctr = $rdr.FieldCount
write-host 'field count = ',$flds_ctr

#initialize an array to hold five fields for the query
[string[]] $vals = 'dog','cat','pony','horse','pig'
while($rdr.Read())
{
$Script:records_counter +=1
$rdr.GetValues($vals)
#write-host 'VALS = ',$vals

$Script:ids += ,$vals[0].ToString().Trim()
$Script:lastnames += ,$vals[1].ToString().Trim()
$Script:firstnames += ,$vals[2].ToString().Trim()
$Script:middlenames += ,$vals[3].ToString().Trim()
$Script:cellphones += ,$vals[4].ToString().Trim()

write-host $Script:ids
write-host $Script:lastnames
write-host $Script:firstnames
write-host $Script:middlenames
write-host $Script:cellphones

}
$Global:sql_client.Close()
}

Learning a new language always involves the same process: finding out what is the same or similar between your known language and the new language and then finding out what is different in the new language. As an example, in C#, the syntax for adding a click event to a button is this.button1.Click += new System.EventHandler(this.button_click). While the general idea is the same in PowerShell, the exact syntax is $button1.add_Click({button_click(0)}).

I hope developers who are interested in learning about Powershell can use this solution as an introduction to the language.