Return Powershell PSObject as DataTable in C#

Question

I've written a Powershell script that reads a CSV file and returns a predetermined collection from the data. Below is a sample output of said script.

Count     Name
------   ------
  12      Rubies
   3      Pearls
  20      Emeralds

I am able to obtain the results in C# by storing it in a PSObject like so:

var shell = PowerShell.Create();
shell.Commands.AddScript("C:\\Scripts\\Powershell\\Get-MyData.ps1");
Collection<PSObject> results = shell.Invoke();

Now when I'm expecting a single object, I am able to obtain each value and assign them to variables like this:

foreach (PSObject psObject in results)
{
   localVariable = Convert.ToString(psObject.Properties["Name"].Value);
}

However, I am having trouble converting this solution to a dynamic one. That is to say, the number of rows is expected to vary. So I've implemented this before when the source is a SQL database using a solution similar to the one posted here so I've assumed that datatables should be the way to go but I cannot get it to work in this scenario. Any ideas or suggestions? Thanks!

Note: The Powershell script component here is compulsory as it includes other mechanisms to formulate the output so while I could just read from the CSV file using C#, this is simply not an option.

Answer 1

What I understood from your question is that you want to have DataTable for further data process and you want to use it in your respective collection.

You can proceed like this:

DataTable dt=new DataTable();
dt.Colums.Add("Count");
dt.Colums.Add("Name");
foreach (PSObject psObject in results)
{
   foreach(PSPropertyInfo prop in psObject.Properties)
   {
     var count=prop.Name; 
     var name=prop.Value;
     //In other words generate output as you desire.
     dt.Rows.Add(count,name);
   }
}

Hope it's helpful for you.

Documentations are present: Doc 1 and Doc 2

Answer 2

since you are already using powershell to read the csv file, why not continue?

You could invoke a powershell command/script to iterate the results of your *.ps1 file before you start to manage it in C#.

It's quite easy to invoke a script in c#, and you don't have to create the script as a file. You can just send the script text directly and invoke it.

maybe you can even combine the 2 similar to:

var shell = PowerShell.Create();
shell.Commands.AddScript("C:\\Scripts\\Powershell\\Get-MyData.ps1 | foreach {do-something $_}");
Collection<PSObject> results = shell.Invoke();

Notice that i added a pipe after your script results are returned. Remember that the 'AddScript' is very much like sending a bunch of text to powershell.exe. I've pushed entire script files from embedded resources to this method before.