4

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
}
codersl
  • 2,222
  • 4
  • 30
  • 33
C.E
  • 43
  • 1
  • 4
  • 2
    Possible duplicate of [Parenthesis Powershell functions](http://stackoverflow.com/questions/11060833/parenthesis-powershell-functions) – user4003407 Dec 24 '15 at 12:59
  • $da = New-Object System.Data.SqlClient.SqlDataAdapter ? – Jaqueline Vanek Dec 24 '15 at 13:17
  • @JaquelineVanek The issue is how the function is being called. Hence the dupe – Matt Dec 24 '15 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 Dec 24 '15 at 14:08
  • @FoxDeploy The problem is right here: `executeQueryAndFillTable($sqlAdapter, $sqlCommand)` – Matt Dec 24 '15 at 15:13
  • Function called before declaration is not in the other question you point. – JPBlanc Dec 24 '15 at 15:55
  • @JPBlanc But function does not called before declaration in OP question. If that was the case, then error message would be completely different. Function declaration does not have to lexically precede invocation. Function declaration just have to be invoked before function itself: `function f{g};function g{'g called'};f`. And there is no evidence that OP call `sql_pull` before (s)he declare `executeQueryAndFillTable`. – user4003407 Dec 25 '15 at 02:24
  • @PetSerAl You are probably right, my answer was based on the présentation of the code. Really The problem here are the parenthesis, the solution IS in the question you point, but an explanation is needed. Puting it as a duplicate does'nt help the guy who answer to understand the error he met. – JPBlanc Dec 25 '15 at 07:57

1 Answers1

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
codersl
  • 2,222
  • 4
  • 30
  • 33
JPBlanc
  • 70,406
  • 17
  • 130
  • 175