Fetch a whole row from SQL Server with PowerShell

Question

So I use ExecuteScalar() to get one value from a table. But how can I get multiple values, i.e. values from multiple columns.

$connectionString = "Data Source=ServerName;Initial Catalog=DB_Name; Integrated Security=SSPI" 
$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection($connectionString)
$query = "select col1, col2, col3 from table where col1= x;" 
$command = New-Object -TypeName System.Data.SqlClient.SqlCommand($query, $connection)
$connection.Open()
$NewBatchID = $command.ExecuteScalar() 
$connection.Close()

Answer 1

One method, using .NET objects in PowerShell, is with a SqlDataAdapter. This can load multiple rows and columns into a DataTable for subsequent use.

This example assumes a single row is returned:

$connectionString = "Data Source=ServerName;Initial Catalog=DB_Name; Integrated Security=SSPI" 
$query = "select col1, col2, col3 from table where col1= x;"

$sqlDataAdapter = New-Object -TypeName System.Data.SqlClient.SqlDataAdapter($query, $connectionString)
$dataTable = New-Object -TypeName System.Data.DataTable
[void]$sqlDataAdapter.Fill($dataTable)
$col1 = $dataTable.Rows[0]["col1"]
$col2 = $dataTable.Rows[0]["col2"]
$col3 = $dataTable.Rows[0]["col3"]

Answer 2

You can also use a DataReader:

$connectionString = "Data Source=ServerName;Initial Catalog=DB_Name; Integrated Security=SSPI" 
$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection($connectionString)
$query = "select col1, col2, col3 from table where col1= x;" 
$command = New-Object -TypeName System.Data.SqlClient.SqlCommand($query, $connection)
$connection.Open()

$dataReader = $command.ExecuteReader()
$fieldCount = $dataReader.FieldCount
while ($dataReader.Read()) 
{
    for ($i = 0; $i -lt $fieldCount; $i++) 
    {
        Write-Host "$($dataReader.GetName($i)) is $($dataReader.GetValue($i))." 
    }
}

$connection.Close()

Personally I dislike both SqlDataAdapter and DataReaders because of all the boilerplate code. I think it should have been done in a more elegant way.