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.

Answer 3

Use Linq:

IEnumerable<dynamic> dynamicResults = from item in results                                     
                                      select new { Name = item.Name, Count = item.Count };

Then set the myDataGrid.ItemsSource = dynamicResults;

Answer 4

Found myself needing to do something similar and built this more generic solution for PS returning a PSObject[] using @Nabeel Khan's answer as a jumping off point

public DataTable PSObjectCollectionToDataTable(PSObject psObj)
{
    DataTable dt = new DataTable();
    IList collection = (IList)psObj.BaseObject; //turn this obj into an iList so we can loop it
    foreach (PSObject psRow in collection)
    {
        DataRow dtRow = dt.Rows.Add();
        foreach (var prop in psRow.Properties)
        {
            var name = prop.Name;
            var val = prop.Value;
            var type = val.GetType();
            //check if the table has the psobj's column
            if (!(dt.Columns.Contains(name)))
            {
                dt.Columns.Add(name, type); //if the table does not have the correct columns yet, add them
            }
            dtRow[name] = val;
        }
    }
    return dt;
}

Note that you will need to include the following references at the top of your code

using System.Data;
using System.Collections;