On Mar 31, 2:34*am, Jakob Bindslet <ja...@xxxxxx> wrote:
Quote:
> Hi everyone,
>
> I'm truying to create a small function to execute a query against an
> SQL server. After which I try using the function ($conn is a working,
> already initialized):
>
> However, the type of $data isn't [System.Data.Common.DbDataReader]
> anymore, it has become [System.Array]
>
> Is there any way to avoid this change in type, and why does it occur?
>
> function Query-SQL {
> * * * * Param ($query, $conn)
> * * * * $sqlCmd = New-Object System.Data.SqlClient.SqlCommand
> * * * * $sqlCmd.CommandText = $query
> * * * * $sqlCmd.Connection = $conn
> * * * * $Rset = $sqlCmd.ExecuteReader()
> * * * * $Rset.gettype() *# <-- [System.Array]
> * * * * Return $Rset
>
> }
>
> $data = Query-SQL "SELECT * from master..sysdatabases" $conn
>
> $data.gettype() # <--[System.Data.Common.DbDataReader]
>
> Regards,
> Jakob Bindslet, Denmark
Hi Jakob,
Sorry noone got to you earlier than this, I just noticed your post.
You're experiencing "powershell shock" ;-) defined by the sudden
realisation that any collections, lists (or indeed data readers) that
implement IEnumerable are being automatically unravelled by
PowerShell. Example:
# define an array of int, with 3 elements
PS> [int[]]$arr = @(1,2,3)
# Now, count objects passed to measure-object
PS> $arr | measure-object
Count : 3
Average :
Sum :
Maximum :
Minimum :
Property :
As you can see, meausre-object detected three elements passed in. You
ask how can this behaviour be curbed? Well, the easiest way is to wrap
the array in another array - PowerShell will strip the outer array
leaving the inner array (contained in the first element) alone.
Easiest way to do this is to you the comma ',' which is the array
constructor operator:
PS> ,$arr | measure-object
Count : 1
<snip>
So, to answer your original question, instead of:
use:
You'll notice that I dropped the "return" statement. In powershell
this is seldom needed, and omitting it serves as a good reminder of
the functional nature of PowerShell's script.
Hope this helps,
- Oisin
PowerShell MVP
http://www.nivot.org/