The PowerShell Guy

Scripting Games 2008 Beginners Windows PowerShell Event 9

My solution for Event 9 in the beginners competition, as Jaykul already noted here : 2008 Scripting Games - Solution for Advanced Event 2 , when working with ADO in PowerShell it's much better to use .NET to connect to a database as the COM objects shown in the Windows PowerShell, solution from the Scripting guy's.

I used the same code as in last years Scripting Games 2007 Event 8 to connect to the database : Winter Scripting games Part 8 (Last Day) , but use a Select statement with custom columns to generate the new list with water volumes.

PoSH> .\Event9B.ps1                                                                                                     
                                                                                                                        
Customer Volume of Water                                                                                                
-------- ---------------                                                                                                
Jones            2500000                                                                                                
Myer             1500000                                                                                                
Smith            1500000                                                                                                
Kumar            1200000                                                                                                
Chen             1500000                                                                                                
Garcia           1058000                                                                                                
                                                                                                                        
                                                                                                                        
PoSH>                                                                                     

As this is a beginner event I worked it out a bit more verbose this time, and show in this example that you can create the hashtable's for the "Custom Columns" in advance, to make the select command more clear :

# Connect to database 
 
$mdb = "c:\scripts\Pool.mdb" 
$ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=$mdb" 
$Conn = new-object Data.OleDb.OleDbConnection($connString) 
$conn.open() 
 
# Open Table 
 
$cmd = new-object Data.OleDb.OleDbCommand("select * from SwimmingPool",$Conn) 
$da = new-object Data.OleDb.OleDbDataAdapter($cmd) 
$dt = new-object Data.dataTable  
[void]$da.fill($dt) 
 
# Create a custom column for volume calculations 
 
$Volume = @{ 
    label='Volume of Water' 
    expression = { 
        if ($_.Slope -eq $false) { 
            $_.Length * $_.Width * $_.Depth * 1000 
        } else { 
            $_.Length * $_.Width * (($_.SEnd + $_.SStart) / 2) * 1000 
        } 
    } 
} 
 
# use custom Volume column in the select command : 
 
$dt | ft -a Customer,$volume 

You can see that we can just use the variable $volume containing our pre-created HashTable with the formulas in the Format-Table (FT) command.

Enjoy,

Greetings /\/\o\/\/