What does a Query have to do with Get-Member?

Question

I have 2 scripts.

Script1 has the following:

function Query($Query) {
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection 
    $SqlConnection.ConnectionString = "Server=$DB_Server;Initial Catalog=$Database;Integrated Security=SSPI" 
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand 
    $SqlCmd.Connection = $SqlConnection 
    $SqlCmd.CommandText = $Query 
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter 
    $SqlAdapter.SelectCommand = $SqlCmd 
    $DataSet = New-Object System.Data.DataSet 
    $a = $SqlAdapter.Fill($DataSet)
    $SqlConnection.Close() 
    $DataSet.Tables[0]
}

#create .net array object for csv export
$exportObject = New-Object System.Collections.ArrayList
#create ordered dictionary so column names come out in the ordered they were created
$rowObject = [ordered]@{}

$connection_string = "Connection Timeout=120;User Id=UID1;Data Source=datasource.com;Password=password12!553"

$rowObject.'Connection Details' = $connection_string

#INSERT connection string into Table
Query "UPDATE [$someTable]
SET [connection_string] = '$connection_string'
WHERE [cname] = '$cinput' AND ([pserver] = '$pinput'"

$exportObject.Add((New-Object PSObject -Property $rowObject)) | Out-Null

$exportObject | Select-Object

Now in script2, I call script1 and "out-variable" the object then convert it to a PSCustomObject to use it with an HTML table function (not relevant to this thread so wont include in code. more info on that here).

Script2:

& ".\script1.ps1" -ViewConnection "$cinput" -OutVariable xprtOut | Format-Table -Wrap

#converting xprtOut from Arraylist to pscustomobject to be used with ConvertTo-HTMLTable
$Arr = @()
foreach ($Object in $xprtOut) {
    $i = -1
    $arrayListCount = -($Object | gm | Where-Object {$_.MemberType -like "noteproperty"}).Count

    $customObj = New-Object PSCustomObject
    do {
        $customObj | Add-Member -MemberType NoteProperty -Name (($Object | gm)[$($i)].Name) -Value ($Object."$(($Object | gm)[$($i)].Name)")
        $i--
    } while ($i -ge $arrayListCount)

    $Arr += $customObj
}

when I run script2, i get the following errors:

gm : You must specify an object for the Get-Member cmdlet.
$arrayListCount = -($Object | gm | Where-Object {$_.MemberType -l ...

Cannot index into a null array.
... dd-Member -MemberType NoteProperty -Name (($Object | gm)[$($i)].Name) ...

After some long debugging, I found the root problem: by removing the UPDATE query statement from script1, script2 stopped erroring and it started working just fine.

So the question is, why would the query statement in script1 be problematic? What does it have to do with the object conversion?

Answer 1

You have an issue with formatting the output of the first script when you output it as a variable that I have already mentioned. If you do thing the issue is with the Update statement, then I'd check your syntax is parsing correctly in PowerShell. It is also not clear how your function is receiving the $DB_Server and $Database values. I'm not absolutely sure this will fix your issue, but I think the code for your query function could probably be simplified, and here is a minimal example that works for me.

function Query {
    Param (
        [parameter(Mandatory=$true,Position = 0)][string]$SqlConnection,
        [parameter(Mandatory=$true, Position = 1)][string]$Data
    )

    # Create a new connection
    $SqlClient = New-Object System.Data.SqlClient.SqlConnection($SqlConnection)
    $SqlClient.Open()

    # Submit the query
    $Query = "UPDATE <tablename> SET <column_name> = '$Data' WHERE <column_name> = '<value>'"
    # You may want to comment out the rest of the function here and see how your Query looks.
    $Command = New-Object System.Data.SqlClient.SqlCommand($Query,$SqlClient)
    $DataSet = New-Object System.Data.DataSet
    $DataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($Command)
    $DataAdapter.Fill($DataSet) | Out-Null

    # Close and return your data. You may want to dispose of the client too.
    $DataSet.Tables[0]
    $SqlClient.Close()
    $SqlClient.Dispose()
}

$UserConnection = "Connection Timeout=120;User Id=UID1;Data Source=datasource.com;Password=password12!553"
$SqlConnection = "Server=<Server\Instance>; Database=<Database_Name>; Integrated Security=True; Connect Timeout=15"
Query -SqlConnection $SqlConnection -Data $UserConnection

You then may want to loop through a custom object of SQL Connections and the User Connections you want to add and create your output object that way. This should maintain the output in the order you provided them in.

$Export = $Connections | Foreach-Object { Query -SqlConnection $_.SqlConnection -Data $_.UserConnection }
return $Export

Then you can follow on with the actions of script two as below. I'm not sure you need a separate one.

foreach ($obj in $Export) {
    # Do stuff here.
}