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?

TheMadTechnician · Accepted Answer · 2014-07-31 23:02:50Z

2

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]
}

answered Jul 31, 2014 at 23:02

TheMadTechnician

36.5k3 gold badges48 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ashes999 Over a year ago

+1 works great, thanks. Just like C#, I can use $record[0] or $record['FirstName'].

Eric Eskildsen · Accepted Answer · 2014-08-01 00:05:35Z

2

The problem is that PowerShell is trying to enumerate the reader for you when the function returns it. You can tell PowerShell to leave it alone by putting it into an array:

return ,$reader

answered Aug 1, 2014 at 0:05

Eric Eskildsen

4,9403 gold badges52 silver badges59 bronze badges

7 Comments

ashes999 Over a year ago

Maybe I didn't explain my question clearly? I am returning $reader. In C#, the equivalent code to what I have here returns an instance of SqlDataReader, which can be enumerated and has a Read function.

Eric Eskildsen Over a year ago

I know. That's one of the quirks of PowerShell that got me coming from .NET. PowerShell won't obey you and return $reader as you've told it to; instead it tries to enumerate the reader for you, returning Object[] instead. Check the return type of your ExecuteReader function as it was in your original question. ExecuteQuery($sql).GetType().FullName will return Object[]. But if you force $reader into an array, PowerShell will leave it alone, and the return type will be System.Data.SqlClient.SqlDataReader as expected. Crazy? I think so, but that's PowerShell!

Eric Eskildsen Over a year ago

With PowerShell 3, your function's return type is System.Object[]. I don't mean the type of $reader inside the function. I mean what the function actually returns. I know it's counterintuitive, but run ExecuteQuery($YourSqlHere).GetType().FullName and you'll see what I mean. PowerShell is trying to enumerate the reader when it returns it, which is why casting beforehand doesn't help. To stop that, you do have to put the reader into an array: return ,$reader. That changes the return value of the function not into an array (as we'd expect coming from C#) but to SqlDataReader.

ashes999 Over a year ago

I understand what you're saying. Unfortunately, like my first comment indicates, returning an array didn't change the type to SqlDataReader from DataRecordInternal. It's still evaluated. I used the accepted answer to iterate.

Eric Eskildsen Over a year ago

Well, main thing is it's working! Still, my geeky side's curious why it's different on our shells. Did you make $reader the second item of the array like in my answer? return ,$reader? Returning @($reader) doesn't do the trick, but making it the second item makes the return type SqlDataReader for me.

|

Collectives™ on Stack Overflow

Iterating over multiple rows of data in PowerShell

2 Answers 2

1 Comment

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related