Get-AdoConnection and Invoke-AdoCommand How to

Oct 16, 2009 at 3:48 PM

Im trying to figure out how I can enumerate a table from a SQL server connection using PSCX 1.2

I have a equivalent script from Lee Holmes which works, 

There are no examples for using the PSCX functions. Get-AdoConnection and Invoke-AdoCommand

Example


 

$Provider="System.Data.SQLClient"
$ConnectionString="Data Source=magic;Initial Catalog=smdb;User ID=username;Password=********"
$Connection = Get-AdoConnection $Provider $ConnectionString
$Query = @"
SELECT * FROM smdb."_SMDBA_".Configurations Configurations 
WHERE (Configurations."Configuration Categorie ID" In ('NOTEBOOK','PC'))
"@

Invoke-AdoCommand -ProviderName $Provider -Connection $Connection -CommandText $Query -Debug # Returns no object stream??

$Connection.Close()

Write-Host "Ready"

How do I iterate over the records using this ADO stuff?
Thanks
MaBerY
Coordinator
Nov 2, 2009 at 4:43 AM

I recently used the Invoke-AdoCommand against the pubs database successfully but I used the -AsDataSet parameter and got back a DataSet object.  If you assign your command to a variable, does that variable wind up equal to $null (ie no output at all).

Dec 28, 2014 at 5:38 PM
Just in case this helps someone, here's code I wrote that works for me at least...

Reading from SQL Server...

Get-AdoConnection -Server "(local)" -Database "AdventureWorks" -ProviderName "System.Data.SQLClient"
$Provider=”System.Data.SQLClient”
$ConnectionString=”Data Source=.;Initial Catalog=AdventureWorks;Integrated Security=SSPI”
$Connection = Get-AdoConnection $Provider $ConnectionString
$Query = “SELECT top 10 * FROM person.person”

$mydata = Invoke-AdoCommand -ProviderName $Provider -Connection $Connection -CommandText $Query -AsDataSet
$mydata.tables[0] | Out-GridView

$Connection.Close()

I had to remind myself that a dataset contains a collection of returns data tables. Hope this helps someone.

Bryan