PowerShell function returns table in List view

Question

Below is the PowerShell function I created to return a table.

         function GetJOINConstraints
        {
        [CmdletBinding()]
            param(
            [Parameter( Mandatory=$true)] $ConnectionString,
            [Parameter( Mandatory=$true)] $tableName
                ) 

         $Conn = new-object System.Data.SqlClient.SQLConnection
         $Conn.connectionString=$ConnectionString
         $Conn.Open()
         $Cmd = New-Object System.Data.SqlClient.SqlCommand
         $Cmd.Connection = $Conn
         $cmd.CommandText = "Exec TemplateTransport.usp_Get_JOINConstraints 'Action'"  
         #$Cmd.CommandType = [System.Data.CommandType]'StoredProcedure'
         #$Cmd.Parameters.Add("@TargetTableName", $tableName)
         $SQLAdapter = New-Object System.Data.Sqlclient.SqlDataAdapter
         $SqlAdapter.SelectCommand = $Cmd
         $rs = New-Object System.Data.DataSet
         $SqlAdapter.Fill($rs)
         Return  $rs.Tables[0] | Format-Table
         $Con

    n.Close()

 }

When I try to Run this function it returns the result like below

fkeyid   : Action
fkeycol  : ActionTypeID
fkeynull : False
rkeyid   : ActionType
rkeycol  : ID
nkcol    : Name
alias    : 
nullable : False
fkOrder  : 1

fkeyid   : Action
fkeycol  : DefaultTimeTrackingTaskID
fkeynull : True
rkeyid   : TimeTrackingTask
rkeycol  : ID
nkcol    : Name
alias    : 
nullable : True
fkOrder  : 1

I'm looking for a result like

fkeyid   fkeycol  fkeynull rkeyid   rkeycol  nkcol    alias    nullable fkOrder
------   -------  -------- ------   -------  -----    -----    -------- -------
Action   Actio...    False Actio... ID       Name                 False       1
Action   Defau...     True TimeT... ID       Name                  True       1
Action   Proce...    False Process  ID       Proce... Proce...    False       1
Action   TimeT...     True TimeT... ID       Name                  True       1

I can achieve that using Format-Table . but cannot save the resultset to an Array. I want to be able to save it to an Array and work on it. Any help is much appreciated.

Thanks.

TheMadTechnician · Accepted Answer · 2014-10-02 21:13:23Z

5

You should avoid formatting text for output as a return from a function. What would be better use for you is to remove that line entirely, store the output into a variable, and then pipe that variable to format-table. So, remove the |Format-Table from your function, and then do something like this:

$TableJoins = GetJOINConstraints -ConnectionString $ConString -TableName $tblName
$TableJoins | Format-Table

Alternatively you can use Tee-Object to store the output into a variable, and still pipe it to Format-Table:

GetJOINConstraints -ConnectionString $ConString -TableName $tblName | Tee-Object -Variable TableJoins | Format-Table

answered Oct 2, 2014 at 21:13

TheMadTechnician

36.5k3 gold badges48 silver badges63 bronze badges

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

3 Comments

Avinash Over a year ago

I want to save the result set to an Array and loop through the array. so I tried [Array]$dt = GetJOINConstraints -ConnectionString $ConnString -tableName $tableName1 But when I try to get $dt[1][2] it says unable to index into an object of type system.Int32

TheMadTechnician Over a year ago

Are you expecting an array of arrays? Wouldn't you want to do something like $dt[1].fkeynull?

Avinash Over a year ago

I'm new to powershell. I was expecting a 2D Array. A similar function with a different Sproc returned a 2d array. But I'm sure I can work with $dt[1].fkeynull. thanks. I'll keep you posted.

Collectives™ on Stack Overflow

PowerShell function returns table in List view

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related