Iterating over multiple rows of data in PowerShell

Question

I'm writing some PowerShell code to connect to a database, read multiple rows of data, and return them back via a SqlDataReader.

I did this "the C# way" by writing it how I would write this in C#:

function ExecuteQuery($sql) {
    $connection = new-object system.data.SqlClient.SQLConnection($ConnectionString);
    $connection.Open();

    $cmd = $connection.CreateCommand();
    $cmd.CommandText = $sql;
    $cmd.Connection = $connection    
    $reader = [System.Data.SqlClient.SqlDataReader]$cmd.ExecuteReader()
    return $reader;
}

(The caller of this function has to close the connection themselves responsibly.)

I expected, even without the typecast, that I would get an instance of System.Data.SqlClient.SqlDataReader. This is important, because I want to use Read to iterate through row by row, not to mention that I want to use square-brackets to access data by column name (accessing by ID is too fragile).

Unfortunately, I get back an instance of System.Data.Common.DataRecordInternal, which does not contain a Read method, nor does it allow me to access records by array index.

My question is: how can I read and iterate through multiple rows of data in PowerShell?

Answer 1

There's a couple of ways to iterate through members of an array ('rows of data' in your case). The first that comes to mind is:

ForEach($Record in (ExecuteQuery $CommandString){
    Do Stuff to $Record
}

Or you could pipe it to a ForEach loop (you can pipe output this way, such as to Out-File if desired):

ExecuteQuery $CommandString | ForEach{Do stuff to $_}

Or if you really want more granular control I suppose you could use a For loop and do something like:

$Results = ExecuteQuery $CommandString 
For($i=0;$i -le $Results.count;$i++){
    Do stuff to $Results[$i]
}