Converting Database Records into PowerShell objects

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 post regarding general database access):

$objConnection = New-Object -comobject ADODB.Connection
$objConnection.Open("myDB")
$objRS = $objConnection.Execute("SELECT * FROM TableName")
while ($objRS.EOF -ne $True) {
$hash = @{}
foreach ($field in $objRS.Fields) {
$hash.$($field.name) = $field.value
}
#$hash
New-Object PSObject -property $hash
$objRS.MoveNext()
}

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.

[via Powershell.com]

Advertisements

About Cameron
I'm a final year Computer Science/Information Systems major. Already finished my BA in Politics/Philosophy. I do web and software freelance on the side, while I finish studying. Hoping to be self-employed by the end of my degree, otherwise off into the real-world I go....

One Response to Converting Database Records into PowerShell objects

  1. Pingback: uberVU - social comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: