Function is an object?

This topic contains 8 replies, has 4 voices, and was last updated by Profile photo of Curtis Smith Curtis Smith 1 week, 2 days ago.

  • Author
    Posts
  • #72944
    Profile photo of Creed Cordonier
    Creed Cordonier
    Participant

    Hello,

    I set up a SQL connection inside a Function. When I run the Function inside a variable, I get the correct query results, plus an additional result of '0'. $variable.count also gives me an additional value on top of the query results.

    Here is the setup...

    Function newStaffSelect() {
    
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "my=connection=string"
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandType = [System.Data.CommandType]'StoredProcedure'
    $SqlCmd.CommandText = "dbo._GetNewHires"
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.CommandTimeout = 0
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
    $SqlConnection.Close()
    $DataSet.Tables[0]
    }
    
    
    #Put query result into variable called $newStaff
    
    $newStaff = newStaffSelect
    

    If I do $newStaff.count, the value is 1 if the query result is empty. Otherwise $newStaff.count is the number of results, +1.

    Is the function itself seen as an object in the variable?

    Thank you!

  • #72947
    Profile photo of Don Jones
    Don Jones
    Keymaster

    The function is not seen as an object in the variable. I'm not sure what that means, actually.

    Your function is outputting $DataSet.Tables[0]. What happens if you just run your function, without assigning it to a variable? If you get nothing, then either:

    1. Your query is wrong and it isn't producing any results

    Or

    2. The data table couldn't be filled before you closed the connection

    • #72961
      Profile photo of Creed Cordonier
      Creed Cordonier
      Participant

      Hello, thanks for helping out...

      If I run the Function by itself, the first thing it returns is the count of the objects returned.

      The first 'object' in the results is the value '23'... followed by 23 actual results from my query. It is the correct number of results from just running the query in SQL.

      When I put it in a variable and do $variable.count...it returns '24'.

  • #72965
    Profile photo of Don Jones
    Don Jones
    Keymaster

    So you're probably just going to have to discard the first object, if that's what's being output. You're always getting back one objects with the result count. You could do this in your function by piping your data table to " | Select-Object -skip 1" if you wanted.

    • #72968
      Profile photo of Creed Cordonier
      Creed Cordonier
      Participant

      I guess it makes sense to me. I will see about skipping the first result. The only bothersome thing about it is that extra result always fails down the pipeline. I guess my question was, 'What is that extra object?'.

      Thank you!

  • #72988
    Profile photo of Kiran P
    Kiran P
    Participant

    Try this, mostly it works...
    I have tried.

    ...
    .
    $SqlAdapter.Fill($DataSet) | Out-Null
    $SqlTbl = New-Object System.Data.Datatable
    $SqlTbl = $DataSet.Tables[0]
    $SqlConnection.Close()
    return, $sqlTbl
    }
    
  • #73021
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    Hey @creed-cordonier,
    That "extra object" you are seeing is actually the returned value from your $SqlAdapter.Fill($DataSet)

    The fill method for the System.Data.SqlClient.SqlDataAdapter has a return value as follows

    Return Value
    Type: System.Int32
    The number of rows successfully added to or refreshed in the DataSet. This does not include rows affected by statements that do not return rows.

    Since you are not capturing that return value in a variable, or outputting it to Null like @its4kiran is in his example, the return value is sent to the default output.

    • #73052
      Profile photo of Creed Cordonier
      Creed Cordonier
      Participant

      Excellent info, thank you!

      How did you get this return value information on the Fill method?

      Thank you all for chiming in.

  • #73109
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    Well, it was like this.

    http://bfy.tw/CQKG

    The first and second link get you there. You just have to select the fill method.

You must be logged in to reply to this topic.