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
$objRS = $objConnection.Execute("SELECT * FROM TableName")
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.



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: Logo

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: