Powershell Scripting Games - Day 4 • Technodrone

This is my solution for Beginner Event 4

Connecting and extracting data from a Access Database and retrieving the best result. Now of course you could get the correct result with an SQL statement, by I preferred to do it Powershell (this is actually what the whole purpose of this competition is for)

$adOpenStatic = 3
$adLockOptimistic = 3
  
$objConnection = New-Object -comobject ADODB.Connection
$objRecordset = New-Object -comobject ADODB.Recordset
  
$objConnection.Open("Provider = Microsoft.Jet.OLEDB.4.0; `
    Data Source = C:\users\msaidelk\Desktop\HighJumperDatabase.mdb")
$objRecordset.Open("Select * from [High Jumper Data]", $objConnection, `
    $adOpenStatic,$adLockOptimistic)
$objRecordset.MoveFirst()
  
$mycollection = @() #initialize variable
  
#Create a new object with the columns
$myObj = "" | Select Name, SeasonBest, PersonalBest 
  
do {
#go through each record and populate the data
$myObj.Name = $objRecordset.Fields.Item("Name").Value
$myObj.SeasonBest = $objRecordset.Fields.Item("Season Best").Value
$myObj.PersonalBest = $objRecordset.Fields.Item("Personal Best").Value
  
$mycollection += $myObj

#Go to the next record
$objRecordset.MoveNext()
}
until ( $objRecordset.EOF -eq $True )
  
#Now sort the record according to Season best and Personal Best 
#get the best result and then output the Name
($mycollection | sort -Descending SeasonBest, PersonalBest | `
    select -First 1).Name
  
#Close connection to the Database
$objRecordset.Close()
$objConnection.Close()