Converting Database Records into PowerShell objects

by Dec 21, 2009

When you access a database, the result is not automatically wrapped as objects so you cannot pipe the result into other cmdlets like Sort-Object or Group-Object. You can with PowerShell v.2. Here is a sample based on a previous tip (refer to the previous tip regarding general database access):

$objConnection = New-Object -comobject ADODB.Connection

$objRS = $objConnection.Execute("SELECT * FROM Kunden")
while ($objRS.EOF -ne $True) {
$hash = @{}
foreach ($field in $objRS.Fields) {
$hash.$($ = $field.value
New-Object PSObject -property $hash

The point which you should review occurs inside the while loop. For each recordset, an empty hash table is created and then filled with the fields and field values. In PowerShell v.2, New-Object can convert a hash table into a real object, which is then output. As you see, all database records surface as real objects, and each table column becomes an object property.

Twitter This Tip! ReTweet this Tip!