Database Example

Let's take an earlier example and make it more fully databaseoriented. We'll create an Access database named Versions.mdb. In it, we'll create a table named Machines. Within the Machines table, we'll create two columns named ID and Ver. To keep things simple, each column will be of the Text type with all the default column settings. After creating the table, we'll open it and add several computer names in the ID column. All of the computers must exist, be turned on, be network-accessible, and you should be a local administrator on them. Figure 14-1 shows the Access table with our initial data.

Figure 14-1: Initial Access database table

Next we'll run Database.ps1:

Database.ps1

 #open database $conn = new-object system.data.oledb.oledbconnection $conn.connectionstring = "provider=microsoft.jet.oledb.4.0;" `   + "data source=c:\test\versions.mdb" $conn.open() #query results $query = "select id from machines" $cmd = new-object system.data.oledb.oledbcommand $cmd.commandtext = $query $cmd.connection = $conn $dr = $cmd.executereader() while ($dr.read()) {   $name = $dr.getvalue(0)   $sp = get-wmiobject Win32_OperatingSystem `         -property ServicePackMajorVersion `         -computername $name   $ver = $sp.servicepackmajorversion   #open second connection   $conn2 = new-object system.data.oledb.oledbconnection   $conn2.connectionstring = "provider=microsoft.jet.oledb.4.0;" `     + "data source=c:\test\versions.mdb"   $conn2.open()   #update table   $query2 = "update machines set ver = '$ver' where id = '$name'"   $cmd2 = new-object system.data.oledb.oledbcommand   $cmd2.commandtext = $query2   $cmd2.connection = $conn2   $cmd2.executenonquery()   #close second connection   $conn2.close() } 

Figure 14-2 shows the final populated table:

Figure 14-2: Service pack information added to table by script