Powershell resultset not being picked up in C#

Question

When button1 is clicked, the below code is executed which will run a PowerShell script to get the current SQL Server Instances. However when this is run, the result set (results variable) has a count of 0 rows from the PowerShell output. When I run the same code in native PowerShell it displays 3 rows with the instance names.

Can anyone advise if I am missing something?

private void button1_Click(object sender, EventArgs e)
{
    //If the logPath exists, delete the file
    string logPath = "Output.Log";
    if (File.Exists(logPath))
    {
        File.Delete(logPath);
    }
    string[] Servers = richTextBox1.Text.Split('\n');

    //Pass each server name from the listview to the 'Server' variable
    foreach (string Server in Servers)
    {
        //PowerShell Script
        string PSScript = @"
        param([Parameter(Mandatory = $true, ValueFromPipeline = $true)][string] $server)

        Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy RemoteSigned -Force;
        Import-Module SQLServer;
        Try 
        {
            Set-Location SQLServer:\\SQL\\$server -ErrorAction Stop; 
            Get-ChildItem | Select-Object -ExpandProperty Name;
        } 
        Catch 
        {
            echo 'No SQL Server Instances'; 
        }
        ";

        //Create PowerShell Instance
        PowerShell psInstance = PowerShell.Create();

        //Add PowerShell Script
        psInstance.AddScript(PSScript);

        //Pass the Server variable in to the $server parameter within the PS script
        psInstance.AddParameter("server", Server);

        //Execute Script
        Collection<PSObject> results = new Collection<PSObject>();
        try
        {
            results = psInstance.Invoke();
        }
        catch (Exception ex)
        {
            results.Add(new PSObject((Object)ex.Message));
        }

        //Loop through each of the results in the PowerShell window
        foreach (PSObject result in results)
        {
           File.AppendAllText(logPath, result + Environment.NewLine);
           // listBox1.Items.Add(result);
        }
        psInstance.Dispose();
    }
}

Answer 1

I managed to get round this issue by using the Win32_service instead of SQLPS.

Param([Parameter(Mandatory = $true, ValueFromPipeline = $true)][string] $server)

$localInstances = @()
[array]$captions = GWMI Win32_Service -ComputerName $server | ?{$_.Name -match 'mssql *' -and $_.PathName -match 'sqlservr.exe'} | %{$_.Caption}

ForEach($caption in $captions)
{
   if ($caption -eq 'MSSQLSERVER') 
   {
       $localInstances += 'MSSQLSERVER'
   }
   else 
   {
       $temp = $caption | %{$_.split(' ')[-1]} | %{$_.trimStart('(')} | %{$_.trimEnd(')')}
       $localInstances += ""$server\$temp""
   }
}
$localInstances;

Answer 2

The reason it isn't working is that psInstance.AddParameter only adds parameters to commands, it doesn't work with a script. You'll need to find another way of getting the $server parameter into the script. Try these two powershell examples to see what I mean. The first will output all processes (ignores the AddParameter) while the second only shows svchost processes.

1)

$ps = [system.management.automation.powershell]::create()
$ps.AddScript("get-process")
$ps.AddParameter("name","svchost")
$ps.invoke()

2)

$ps = [system.management.automation.powershell]::create()
$ps.AddCommand("get-process")
$ps.AddParameter("name","svchost")
$ps.invoke()

Answer 3

To get an possible PowerShell error I would try sth. like this:

private void button1_Click(object sender, EventArgs e)
        {
            //If the logPath exists, delete the file
            string logPath = "Output.Log";
            if (File.Exists(logPath)) {
                File.Delete(logPath);
            }

            string[] Servers = richTextBox1.Text.Split('\n');

            //Pass each server name from the listview to the 'Server' variable
            foreach (string Server in Servers) {
                //PowerShell Script
                string PSScript = @"
            param([Parameter(Mandatory = $true, ValueFromPipeline = $true)][string] $server)

            Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy RemoteSigned -Force;
            Import-Module SQLServer;
            Try 
            {
                Set-Location SQLServer:\\SQL\\$server -ErrorAction Stop; 
                Get-ChildItem | Select-Object -ExpandProperty Name;
            } 
            Catch 
            {
                echo 'No SQL Server Instances'; 
            }
            ";
                using (PowerShell psInstance = PowerShell.Create()) {                               
                    psInstance.AddScript(PSScript);
                    psInstance.AddParameter("server", Server);
                    Collection<PSObject> results = psInstance.Invoke();
                    if (psInstance.Streams.Error.Count > 0) {
                        foreach (var errorRecord in psInstance.Streams.Error) {
                            MessageBox.Show(errorRecord.ToString());
                        }
                    }               
                    foreach (PSObject result in results) {
                        File.AppendAllText(logPath, result + Environment.NewLine);
                        // listBox1.Items.Add(result);
                    }               
                }

            }
        }