locked
Avoid Double same function RRS feed

  • Question

  • Hi Experts,

    I need to pipeline the output of one function into another which am able to do if I create 2 similar different name functions. 

    1. Test_Connection

    2. Test_Connection_1

    Is it possible to have only single function to support pipeline pscustomobjects. Script below

    function Test-Connection {
        param(
            [Parameter(Mandatory)]
    		[string]$ServerName,
    
            [Parameter(Mandatory)]
            [string]$DatabaseName
     )
    
        $ErrorActionPreference = 'Stop'
    
        try {
            
            $userName = 'Sa'
            $password = "Password"
            $connectionString = 'Data Source={0};database={1};User ID={2};Password={3}' -f $ServerName,$DatabaseName,$userName,$password
            $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $ConnectionString
            $sqlConnection.Open()
           
            $true
             
    
        } catch {
            $false
        } finally {
            
            $sqlConnection.Close()
        }
    }
    
    
    
    function Test-Connection_1 {
        param(
            [Parameter(Mandatory)]
    		[string]$ServerName,
    
            [Parameter(Mandatory)]
            [string]$DatabaseName
     )
    
        $ErrorActionPreference = 'Stop'
    
        try {
            
            $userName = 'Sa'
            $password = "Password"
            $connectionString = 'Data Source={0};database={1};User ID={2};Password={3}' -f $ServerName,$DatabaseName,$userName,$password
            $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $ConnectionString
            $sqlConnection.Open()
           
           [pscustomobject]@{'ServerName' = $ServerName;'DatabaseName' = $DatabaseName; 'UserName' = $userName; 'Password' = $password }
           
        } catch {
            $false
        } finally {
            
            $sqlConnection.Close()
        }
    }
    
    function Get-Test
    {
    
     param(
            [Parameter(Mandatory,ValueFromPipeline)]
            [pscustomobject]$InputObject
    
          )
    process
    {
     
     Invoke-Sqlcmd -ServerInstance $InputObject.ServerName -Database $InputObject.DatabaseName -Query "PRINT 'This is output'" -Username $InputObject.UserName -Password $InputObject.Password -Verbose
    
    }
    }
    
    
    $array = 'Server1','Server2'
    foreach ($item in $array)
    {
        if ((Test-Connection -ServerName $item -DatabaseName 'Database1') -eq $true )
        {
            Test-Connection_1 -ServerName $item -DatabaseName 'Database1'| Get-Test
            break
        }
    }
    

    Thanks in advance 

    Priya

    Wednesday, September 25, 2019 7:11 AM

Answers

  • You code serve4rs no purpose. Just add t5he break t my code and you have the whole thing.

    $props = @{
    	Database = 'database1' 
    	Query = "PRINT 'This is output'"
    	Username = 'sa'
    	Password = 'Password'
    	Verbose = $true
    	ErrorAction = 'Stop'
    }
    
    $servers = 'Server1','Server2'
    foreach ($server in $servers){ 
    	Try{
    		Invoke-Sqlcmd -ServerInstance $server @props
    		break
            }
    	Catch{
    		Write-Host $_	
    	}	
    }
    	


    \_(ツ)_/


    • Edited by jrv Wednesday, September 25, 2019 8:07 AM
    • Marked as answer by Priya Bange Wednesday, September 25, 2019 8:12 AM
    Wednesday, September 25, 2019 8:06 AM

All replies

  • Your question does not make any sense.

    Your code does not have any pipelines. 

    There is no need for two functions.


    \_(ツ)_/

    Wednesday, September 25, 2019 7:20 AM
  • Your question does not make any sense.

    Your code does not have any pipelines. 

    There is no need for two functions.


    \_(ツ)_/

    Hi Sir,

    This the pipeline code inside the foreach block which uses the [pscustomobject]

    Test-Connection_1 -ServerName $item -DatabaseName 'Database1'| Get-Test

    My first function returns $true which is used to break the loop. The second function which is the same consists of pscustomobject that pipelines the output to the function Get-Test.

    So am asking how my single function can satisfy break condition $true & [pscustomobject] . As when I tried to have both of them in same function.. Its not working for me.

    thanks

    Wednesday, September 25, 2019 7:26 AM
  • That does not explain what you question is. What is not happening? What are the errors. You have to be clear when asking technical questions.

    Why do you need two functions to test the same connection?  What are the functions supposed to do.

    With Invoke-SqlCmd there is no need to test a connection.  The command tests the connection for you.

    I think you should take some time to learn how to write code. You are just guessing at what to do.  Almost all of your code is unnecessary.

    The following is all you need to do.

    $props = @{
    	Database = 'database1' 
    	Query = "PRINT 'This is output'"
    	Username = 'sa'
    	Password = 'Password'
    	Verbose = $true
    	ErrorAction = 'Stop'
    }
    
    $servers = 'Server1','Server2'
    foreach ($server in $servers){ 
    	Try{
    		Invoke-Sqlcmd -ServerInstance $server @props
            }
    	Catch{
    		Write-Host $_	
    	}	
    }
    	
    The error output will tell you which servers failed to connect.


    \_(ツ)_/



    • Edited by jrv Wednesday, September 25, 2019 7:41 AM
    Wednesday, September 25, 2019 7:40 AM
  • That does not explain what you question is. What is not happening? What are the errors. You have to be clear when asking technical questions.

    Why do you need two functions to test the same connection?  What are the functions supposed to do.

    With Invoke-SqlCmd there is no need to test a connection.  The command tests the connection for you.

    I think you should take some time to learn how to write code. You are just guessing at what to do.  Almost all of your code is unnecessary.

    The following is all you need to do.

    $props = @{
    	Database = 'database1' 
    	Query = "PRINT 'This is output'"
    	Username = 'sa'
    	Password = 'Password'
    	Verbose = $true
    	ErrorAction = 'Stop'
    }
    
    $servers = 'Server1','Server2'
    foreach ($server in $servers){ 
    	Try{
    		Invoke-Sqlcmd -ServerInstance $server @props
            }
    	Catch{
    		Write-Host $_	
    	}	
    }
    	
    The error output will tell you which servers failed to connect.


    \_(ツ)_/



    Thank you Sir for deep explanation. Basically, I am trying to accomplish even if from a list of 5 server am able to find 1 working server I need to proceed with further processing. That's why I was firstly testing if the connection is a success if yes break the for each & continue with same server details further. The Invoke SQL based function namely Get-Test will contain more nested pipelines.

    Thanks again.


    • Edited by Priya Bange Wednesday, September 25, 2019 7:50 AM .
    Wednesday, September 25, 2019 7:49 AM
  • Then just add beak after the success of the command and the loop will terminate.


    \_(ツ)_/

    Wednesday, September 25, 2019 7:53 AM
  • Then just add beak after the success of the command and the loop will terminate.


    \_(ツ)_/

    Hi Sir, 

    This workflow is already working as shared in the above script block. Am sorry for not being descriptive here. But what am requesting is if you see I have 2 similar functions 

    1. Test- Connection : This returns the $true value used to break the For-Each

    2. Test-Connection_1 : This returns the PSCustomObjects that I pass to my Invoke-SQL function

    Am asking can I handle both the return $true & PScustomObjects inside the same function to avoid duplicate code. I tried doing it but unable to achieve it.

    $array = 'Server1','Server2'
    foreach ($item in $array)
    {
        if ((Test-Connection -ServerName $item -DatabaseName 'Database1') -eq $true )
        {
            Test-Connection_1 -ServerName $item -DatabaseName 'Database1'| Get-Test
            break
        }
    }

    Thanks

    Priya

    Wednesday, September 25, 2019 8:01 AM
  • You code serve4rs no purpose. Just add t5he break t my code and you have the whole thing.

    $props = @{
    	Database = 'database1' 
    	Query = "PRINT 'This is output'"
    	Username = 'sa'
    	Password = 'Password'
    	Verbose = $true
    	ErrorAction = 'Stop'
    }
    
    $servers = 'Server1','Server2'
    foreach ($server in $servers){ 
    	Try{
    		Invoke-Sqlcmd -ServerInstance $server @props
    		break
            }
    	Catch{
    		Write-Host $_	
    	}	
    }
    	


    \_(ツ)_/


    • Edited by jrv Wednesday, September 25, 2019 8:07 AM
    • Marked as answer by Priya Bange Wednesday, September 25, 2019 8:12 AM
    Wednesday, September 25, 2019 8:06 AM
  • You code serve4rs no purpose. Just add t5he break t my code and you have the whole thing.

    $props = @{
    	Database = 'database1' 
    	Query = "PRINT 'This is output'"
    	Username = 'sa'
    	Password = 'Password'
    	Verbose = $true
    	ErrorAction = 'Stop'
    }
    
    $servers = 'Server1','Server2'
    foreach ($server in $servers){ 
    	Try{
    		Invoke-Sqlcmd -ServerInstance $server @props
    		break
            }
    	Catch{
    		Write-Host $_	
    	}	
    }
    	


    \_(ツ)_/


    Thanks for all the patience & solution. I guess its pretty late for you in US. 
    TC

    Wednesday, September 25, 2019 8:09 AM