Passing objects to PowerShell functions

Question

I'm trying to pass a SQL adapter object to a PowerShell function but I'm getting this error:

executeQueryAndFillTable : Cannot process argument transformation on parameter 'da'. Cannot convert the "System.Object[]" value of type "System.Object[]" to type "System.Data.SqlClient.SqlDataAdapter".

Here's the code

function sql_pull
{
    # define Objects
    $xmlDoc = New-Object System.Xml.XmlDocument
    $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $sqlCommand = New-Object System.Data.SqlClient.SqlCommand
    $sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $connectionString = "Password=$password;Persist Security Info=True;User ID=$userId;Data Source=$dataSource"
    $counter = 0

    # database queries 
    $queries = @(
    "Select * from sys.configurations for xml Raw ('Cretiria'), type, ROOT('sys.configurations'), ELEMENTS");

    $sqlConnection.ConnectionString = $connectionString
    $sqlCommand.Connection = $sqlConnection

    try {
        $sqlConnection.Open()
        
        foreach($q in $queries) 
        {
            $sqlCommand.CommandText = $q
            $sqlAdapter.SelectCommand = $sqlCommand.CommandText
            $sqlAdapter.SelectCommand.CommandTimeout = 300

            $res = executeQueryAndFillTable($sqlAdapter, $sqlCommand)              
        }

        $sqlConnection.Dispose()
        $sqlCommand.Dispose()
        $sqlAdapter.Dispose()
    } 
    catch
    {
        Throw
    }    
}

function executeQueryAndFillTable
{
    param(
        [System.Data.SqlClient.SqlDataAdapter]$da,
        [System.Data.SqlClient.SqlCommand] $command
    )

    $dataTable = New-Object System.Data.DataTable
    $da.SelectCommand = $command
    $da.Fill($dataTable)
    #check
    $data = $dataTable.Rows[0][0]
    return $data
}

@JaquelineVanek The issue is how the function is being called. Hence the dupe — Matt
– Matt, Commented Dec 24, 2015 at 13:19
Please give an example of the full command/pipeline you're using here. Just showing the functions isn't enough for us to pinpoint the problem. — FoxDeploy
– FoxDeploy, Commented Dec 24, 2015 at 14:08
@FoxDeploy The problem is right here: executeQueryAndFillTable($sqlAdapter, $sqlCommand) — Matt
– Matt, Commented Dec 24, 2015 at 15:13

codersl · Accepted Answer · 2020-09-08 04:01:24Z

9

Two things:

First : In PowerShell function should be declare before usage.

Second : The way the function is called.

executeQueryAndFillTable($sqlAdapter, $sqlCommand)

This is not the right way to call a function in PowerShell. If you call it this way PowerShell thinks you are calling the function with only one parameter which is an array (very important , is the array operator in PowerShell) of two elements of distinct types (the reason why System.Object[] in the error).

The right way is :

executeQueryAndFillTable $sqlAdapter $sqlCommand

edited Sep 8, 2020 at 4:01

codersl

2,3425 gold badges32 silver badges34 bronze badges

answered Dec 24, 2015 at 15:47

JPBlanc

73k17 gold badges137 silver badges180 bronze badges

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

3 Comments

C.E Over a year ago

tnx! that's help a lot!

JPBlanc Over a year ago

@C.E Perhaps you can put the question as answered ?

granadaCoder Over a year ago

@CE, Please mark this as "The Answer", so it does not keep coming up under unanswered SOF questions.

Collectives™ on Stack Overflow

Passing objects to PowerShell functions

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related