Vista Forums - View Single Post - System.Data.Common.DbDataReader and change in type

04-02-2008

On Apr 2, 3:04 am, "Oisin (x0n) Grehan [MVP]" <ois...@xxxxxx>
wrote:

Quote:

> On Mar 31, 2:34 am, Jakob Bindslet <ja...@xxxxxx> wrote:

Quote:

> > Hi everyone,

>

Quote:

> > 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):

>

Quote:

> > However, the type of $data isn't [System.Data.Common.DbDataReader]
> > anymore, it has become [System.Array]

>

Quote:

> > Is there any way to avoid this change in type, and why does it occur?

>

Quote:

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

>

Quote:

> > }

>

Quote:

> > $data = Query-SQL "SELECT * from master..sysdatabases" $conn

>

Quote:

> > $data.gettype() # <--[System.Data.Common.DbDataReader]

>

Quote:

> > 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:
>

Quote:

> > Return $Rset

>
> use:
>

Quote:

> > ,$Rset

>
> 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 MVPhttp://www.nivot.org/

Thanks for the answer. It appears to have solved my problem!

04-02-2008	#3 (permalink)
Jakob Bindslet n/a posts	Re: System.Data.Common.DbDataReader and change in type On Apr 2, 3:04 am, "Oisin (x0n) Grehan [MVP]" <ois...@xxxxxx> wrote: Quote: > On Mar 31, 2:34 am, Jakob Bindslet <ja...@xxxxxx> wrote: Quote: > > Hi everyone, > Quote: > > 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): > Quote: > > However, the type of $data isn't [System.Data.Common.DbDataReader] > > anymore, it has become [System.Array] > Quote: > > Is there any way to avoid this change in type, and why does it occur? > Quote: > > 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 > Quote: > > } > Quote: > > $data = Query-SQL "SELECT * from master..sysdatabases" $conn > Quote: > > $data.gettype() # <--[System.Data.Common.DbDataReader] > Quote: > > 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: > Quote: > > Return $Rset > > use: > Quote: > > ,$Rset > > 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 MVPhttp://www.nivot.org/ Thanks for the answer. It appears to have solved my problem!
My System Specs