Powershell Scripting Games - Day 4

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)

   1: $adOpenStatic = 3

   2: $adLockOptimistic = 3

3:

   4: $objConnection = New-Object -comobject ADODB.Connection

   5: $objRecordset = New-Object -comobject ADODB.Recordset

6:

   7: $objConnection.Open("Provider = Microsoft.Jet.OLEDB.4.0; `

   8:     Data Source = C:\users\msaidelk\Desktop\HighJumperDatabase.mdb")

   9: $objRecordset.Open("Select * from [High Jumper Data]", $objConnection, `

  10:     $adOpenStatic,$adLockOptimistic)

  11: $objRecordset.MoveFirst()

12:

  13: $mycollection = @() #initialize variable

14:

  15: #Create a new object with the columns

  16: $myObj = "" | Select Name, SeasonBest, PersonalBest

17:

  18: do {

  19: #go through each record and populate the data

  20: $myObj.Name = $objRecordset.Fields.Item("Name").Value

  21: $myObj.SeasonBest = $objRecordset.Fields.Item("Season Best").Value

  22: $myObj.PersonalBest = $objRecordset.Fields.Item("Personal Best").Value

23:

  24: $mycollection += $myObj

25:

  26: #Go to the next record

  27: $objRecordset.MoveNext()

  28: }

  29: until ( $objRecordset.EOF -eq $True )

30:

  31: #Now sort the record according to Season best and Personal Best

  32: #get the best result and then output the Name

  33: ($mycollection | sort -Descending SeasonBest, PersonalBest | `

  34:     select -First 1).Name

35:

  36: #Close connection to the Database

  37: $objRecordset.Close()

  38: $objConnection.Close()

2009-06-11

Powershell Scripting Games - Day 4