CODEWITHSUNDEEP
sqlpowershelldatatabledatatablesdatasource
DevilWAH
DevilWAH

Reputation: 2643

Power SQL working with Data.tables

Hi I have the following code 

function Invoke-SQL {
    param(
        [string] $dataSource = ".\SKYPE",
        [string] $database = "LcsCDR",
        [string] $sqlCommand = "SELECT * From UsersView"
      )

    $connectionString = "Data Source=$dataSource; " +
            "Integrated Security=SSPI; " +
            "Initial Catalog=$database"

    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
    $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
    $connection.Open()

    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataSet) | Out-Null

    $connection.Close()
    $dataSet.Tables

}

$results = Invoke-SQL 

Write-Host $results.Rows.Count

foreach ($row in $results.Rows)
{
Write-Host "value is : $($row[4])"

}

which does what I expect in that is pulls back the content of the UserView table and loops through it out putting it to screen. All straight forward.

But I am using the column index in the output. How can I first of all list the Column headers of the table. and then be able to loop though using header rather than index numbers.

So I would like that last line to look like below, where "userid" it the column header. 

Write-Host "value is : $($row["UserID"])"

is this possible?

Upvotes: 0

Views: 132

Answers (3)

gvee
gvee

Reputation: 17171

You don't even have to loop!

$results | Select-Object "UserID"

Alteratives:

$results | Select-Object "UserID" | ForEach-Object {
    $_
}

$results | ForEach-Object {
    $_."UserID"
}

$_ is somewhat akin to "this"

Upvotes: 2

Martin Brandl
Martin Brandl

Reputation: 59011

I would recommend you to use my generic Receive-SqlQuery function that closes the SqlConnection and SqlDataReader. You can pass in a scriptblock to process the reader. 

function Receive-SqlQuery
{
    Param
    (
        [string]$ConnectionString,
        [string]$SqlQuery,
        [scriptblock]$ResultProcessor
    )

    try 
    {
        $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $ConnectionString
        $sqlConnection.Open()

        try
        {
            $sqlCommand = New-Object System.Data.SqlClient.SqlCommand($SqlQuery, $sqlConnection)
            $reader = $sqlCommand.ExecuteReader()
            Invoke-Command $ResultProcessor -ArgumentList $reader
        }
        finally # cleanup reader
        {
            if ($reader)
            {
                $reader.Close()
            }
        }
    }
    finally
    {
        $sqlConnection.Close();
    }
}

Usage example that access the columns using the column name:

 $readerCallback = {
        Param($reader)
        while ($reader.Read()) {
            Write-Host $reader['UserID']
        }    
    }

$retrieveUsers = 
@'
SELECT * From UsersView
'@

Receive-SqlQuery -ConnectionString "yourConnectionString" -SqlQuery $retrieveUsers -ResultProcessor $readerCallback

Upvotes: 1

DevilWAH
DevilWAH

Reputation: 2643

Oh Sorry I see I can just use them! I spent 20 minutes trying it and blindly trying to use {} rather than []. A blind moment. 

foreach ($row in $results.Rows)
{
Write-Host "calue is : $($row["UserID"])"

}

write-host $results.Columns

are the commands I was after.

Upvotes: 0

Related Questions