0

I have an example solution(VS 2017, C#) with 2 projects:

  • Console Application:
    • Program.cs
    • Scripts folder:
      • Aaa.ps1 (reference)
    • nuget packages:
      • Npgsql v4.02
      • System.Management.Automation.dll v10.0.10586
  • PowerShell Script Project:
    • Aaa.ps1

The Aaa.ps1 does simple Select query to retrieve one value and pass it to Console Application Program cs and display it on console.

class Program
    {
        static void Main(string[] args)
        {
            // get a ps file script path.
            string pathToScriptsFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Scripts");
            string filter = "*.ps1";
            string[] files = Directory.GetFiles(pathToScriptsFile, filter);
            string filePath = files[0];

            // read text from a ps file script.
            string scriptContent = string.Empty;
            using (StreamReader sr = new StreamReader(filePath))
            {
                scriptContent = sr.ReadToEnd();
                sr.Close();
            }

            // get dbValue from powershell script            
            using (PowerShell powerShellInstance = PowerShell.Create())
            {
                powerShellInstance.AddScript(scriptContent);

                try
                {
                    Collection<PSObject> PSOutput = powerShellInstance.Invoke();

                    foreach (PSObject outputItem in PSOutput)
                    {
                        if (outputItem != null)
                        {
                            var outputValue = outputItem.BaseObject;

                            if (outputValue.GetType() == typeof(System.Data.DataRow))
                            {
                                var dbValue = (outputValue as System.Data.DataRow).ItemArray.FirstOrDefault();
                                Console.WriteLine($"Db value is:\n{dbValue}\n");
                            }

                            Console.WriteLine($"Value form powershell script is:\n{outputItem.ToString()}\n");
                        }
                    }

                    if (!PSOutput.Any())
                    {
                        Console.WriteLine($"No Value form powershell script is:\n");
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"\n\n{ex}\n{ex.Message}\n{ex.InnerException}\n\n");
                }                
            }

            Console.ReadKey();
        }
    }

Aaa.ps1

#
# Aaa.ps1
#

# Load Npgsql dll
Add-Type -Path ".\Npgsql.dll"

# get DB connection
function getDBConnection ($MyDBServer, $MyDBPort, $MyDBName, $MyUserId, $MyPassword) {
    $DBConnectionString = "server=$MyDBServer;port=$MyDBPort;user id=$MyUserId;password=$MyPassword;database=$MyDBName;pooling=false"
    $DBConn = New-Object Npgsql.NpgsqlConnection;
    $DBConn.ConnectionString = $DBConnectionString
    $DBConn.Open()

    return $DBConn
}

# close DB connection
function closeDBConnection ($DBConn)
{
    $DBConn.Close
}

# DB connection string details
$MyDBServer = "xxxxxxxxxxxxxxx";
$MyDBPort = yyyy;
$MyDBName = "xxxxxxxx";
$MyUserId = "xxxxxx";
$MyPassword = "";

# execute sql query
$MyDBConnection = getDBConnection $MyDBServer $MyDBPort $MyDBName $MyUserId $MyPassword
$query = "SELECT xxxxx FROM xxxxx ..."
$DBCmd = $MyDBConnection.CreateCommand()
$DBCmd.CommandText = $query
$adapter = New-Object -TypeName Npgsql.NpgsqlDataAdapter $DBCmd
$dataset = New-Object -TypeName System.Data.DataSet
$adapter.Fill($dataset)
$dataset.Tables[0]
closeDBConnection($MyDBConnection)

and above works fine. I always have PSOutput collection with 3 items and a data I need back from SQL.

However, when I applied same approach to my working bigger solution it doesn't work. What I did:

  • Created same PowerShell Script Project with same Aaa.ps1 script
  • Added same implementation like in Program.cs to my CodedUI project into Utility method. Also added same nuget packages to CodedUI project to handle PowerShell and Postgres bit.

As a result I have two different errors/behaviors:

  • sometimes I get: Cannot perform operation because the runspace is not in the 'Opened' state. Current state of runspace is 'Broken'.
  • and sometimes PSOutput collection has two items which are always null.

Does anybody can tell me what I am doing wrong ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
mazury
  • 177
  • 1
  • 2
  • 12
  • I can't really help but `Add-Type -Path ".\Npgsql.dll"` does that relative path resolve properly in your big project? – Matt Jul 12 '18 at 15:03
  • The path is fine. One thing I have to add. In bigger solution CodedUI project Utility method instead of having: – mazury Jul 13 '18 at 08:12
  • The path is fine. One thing I have to add. In bigger solution CodedUI project Utility method instead of having: `string pathToScriptsFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Scripts");` i have: `string pathToScriptsFile = Directory.GetParent(Directory.GetParent(Directory.GetCurrentDirectory()).Parent.FullName).FullName + "\\Xxxxxxxxxxx";` because during building CUIT project App.ps1 file is not created together with Script file in TestResults\username 2018-06-13 09_08_16\Out directory. Therefore I have to use above workaround to be able to pick a Aaa.ps1 file up. – mazury Jul 13 '18 at 08:20
  • Also I created Simple CUIT project in an example small solution. I added reffernce to script, pulled required nuget packages, and navigate explicitly to Aaa.ps1: `string pathToScriptsFile = "C:\\coding\\PowerShell\\PowerShellProject1\\CodedUITestProject1\\bin\\Debug\\Scripts";` . Thanks for that I got same error/behavior which is 2 null object in PSOutput collection. So I think, that must be sth with this refferencing CUIT project to Aaa.ps1. Any ideas how should I resolve it properly? – mazury Jul 13 '18 at 08:30

0 Answers0