Interact With MongoDB Using PowerShell Script (GP restricted) From C# Console App

It is very easy to interact with MongoDB using PowerShell script. But if executing a PowerShell script by just double-clicking on the "ps1" file is restricted by your Infra Team through policies on the machine, you can still execute it through C#. I have used a simple Console program to demonstrate this.

Objective

 

I wanted to do a bulk records update and read in MongoDB using my client program which is written in C# console application (as my obvious love for that language ).

 

Approach

 

I could have used the MongoDB APIs in the application and written functions to do read, update, etc. but at the time of writing that program, I had never used that API. I knew MongoDB native commands and C# and I wanted to reduce the learning curve at that time. As I started researching the smarter way of achieving my objective, I concluded the following. (I am not claiming that it is the smartest, but it is a way around to achieve with the syntax knowledge that I had at that time! ). For now, the approach that I decided,

1. MongoDB activities run faster if you use the native MongoDB commands in its syntax. In fact, the single-line command to bulk upload a CSV file is so much easier to read, understand and manage! The catch is, you should know the syntax. I will show the required syntax here and you can take it further if interested in the technology. 
2. PowerShell script helps you run the above discussed MongoDB code from outside the MongoDB console. This helps me externalize the code and also allows my custom logic in preparing the script (the MongoDB one!), which may vary in every run.
3. Since I had a couple of other tasks to perform, like Excel scraping, validating, data cleansing and finally loading everything to SharePoint etc, I chose the C# Console program to call the Powershell script and do the job.
4. Overall, accessing the MongoDB is just a small part of my workflow and thus I was not very interested in learning the syntax of the MongoDB API in C#. I am well versed in MongoDB command syntax and preliminary PowerShell syntax and thought to apply the same.

Surprise! Surprise!

 

I took an hour to finalize a sample MongoDB collection, read and run it from the PowerShell console, without opening the MongoDB console. It looked fine but here comes the first roadblock! The moment I saved the PowerScript file as "PS1" and tried executing it from command prompt - I got the message that executing PS1 script is blocked as security measures implemented by my company Infra team. So without PowerScript, I have no bridge between my C# program and MongoDB native commands.

 

Way Around!

 

Use the System.Management.Automation to run the PowerShell script in the memory itself when the C# program runs. Store the JS or JSON command/data in a file and pass the path as a parameter to the PowerShell script. So having finalized the options, here is the code.

//-- libraries to be used (I am only including the non-default ones, here)
using System.Management.Automation;
using System.Collections.ObjectModel;
using System.Collections.Generic;
using System.Linq;
//----------------------------
//---Example function to upload data in a CSV (multiple rows) to MongoDB
public void Upload2Mongo(string csvfilepath)
{
    using (PowerShell PowerShellInstance = PowerShell.Create())
    {
        PowerShellInstance.AddScript("param($filepath) mongoimport --db dome --collection demands  --type csv --mode=upsert --headerline --file $filepath --ignoreBlanks");
        PowerShellInstance.AddParameter("filepath", csvfilepath);
        Collection<PSObject> PSOutput = PowerShellInstance.Invoke();
    }
}

What we are doing here? The PowerShell object is from the Automation DLL. Line No 05: is adding a script line to the PowerScript instance. If you were writing a physical PS1 file, you would have written the line in that file. This is a MongoDB syntax. The parameters are self-explanatory. I have used a database named dome that contains a collection called demands. I am uploading a file of type CSV in an upsert mode. There is a header line in the file (1st row of CSV). Don't create an element if the value is blank.

 //---Example function to read from the collection. This is a three-step process and a bit lengthy. I agree!  Use the piece here to know the way how it is done.
//FUNCTION 1
public List<Entity.YourCollection> FetchDemands()
{
    List<Entity.YourCollection> result = null;
   string csvFile = @"D:\mongoscripts\demandsfromDB.csv";
    //now fetch the clean file
    using (PowerShell PowerShellInstance = PowerShell.Create())
    {
        string fieldnames = "DemandID,DemandTitle,DemandFirstReceivedOn,LocationCity,RequiredByDate,BillingStartDate,DemandStatus";
        string script = "mongoexport --db dome --collection demands --type csv --fields "
                        + fieldnames + " --query \"{'DemandFirstReceivedOn':'"
                        + DateTime.Now.ToString("d-MMM-yy") + "'}\" --noHeaderLine --out "
                        + csvFile;
        PowerShellInstance.AddScript(script);
        Collection<PSObject> PSOutput = PowerShellInstance.Invoke();
        //now fetch the list
        result = ConvertCSVtoList(csvFile);

    }
    return result;
}
//FUNCTION 2
private List<Entity.YourCollection> ConvertCSVtoList(string csvfile)
{
    List<Entity.YourCollection> values;
    values = File.ReadAllLines(csvfile).Skip(0)
                                       .Select(v => PutLineFromFileToClassObject(v))
                                       .ToList();
    return values;
}
//FUNCTION 3
private Entity.YourCollection PutLineFromFileToClassObject(string csvLine)
{
    //split the csv line items into an string array
    string[] lineitems = csvLine.Split(',');
    //create the entity instance from the csv line
    Entity.YourCollection detail = new Entity.YourCollection();
    detail.DemandID = lineitems[0].ToString();// DemandID,
    detail.DemandTitle = lineitems[1].ToString();// DemandTitle,
    detail.DemandFirstReceivedOn = Convert.ToDateTime(lineitems[2].ToString());// DemandFirstReceivedOn,
    detail.LocationCity = lineitems[3].ToString();// Location_City,
    detail.RequiredByDate = FromExcelSerialDate(Convert.ToInt32(lineitems[4].ToString()));// RequiredByDate,
    detail.BillingStartDate = FromExcelSerialDate(Convert.ToInt32(lineitems[5].ToString()));// BillingStartDate,
    detail.DemandStatus = lineitems[6].ToString();// DemandStatus,
    ////now return the entity
    return detail;
}
//FUNCTION 4
private DateTime FromExcelSerialDate(int SerialDate)
{
   if (SerialDate > 59) SerialDate -= 1; //Excel/Lotus 2/29/1900 bug
   return new DateTime(1899, 12, 31).AddDays(SerialDate);
}

FUNCTION 1 is the main function to fetch some of the fields from the same collection in which we dumped the data. However, it also has a WHERE condition (e.g. here is to fetch all demands that came today). Now all the records are dumped in a CSV file. Also, note that we did not include a header row (--noHeaderLine). 

 

Function 2 is called to convert the entries in the CSV file to your object collection. As a LINQ query is used to fetch each of the lines, you can opt for which field from the file you want to dump in which of your elements. (Function 3)

 

Function 4 is a special case. The date from MongoDB, when dumped in CSV, converts into an Integer value. To get the date back, you need to execute this function.

//---Example function to execute any MongoDB command. All you need to do is to put everything that you would have written in a
//---MongoDB console, into a JS file and pass it as a parameter from PowerShell. Cakewalk !!
public void DeleteRecords()
{
    using (PowerShell PowerShellInstance = PowerShell.Create())
    {
        string appExecitionPath =; //some path on the hard drive / network;
        string jsFile = appExecitionPath + @"\mongoscripts\deleteRecords.js";
        string script = "Get-Content '" + jsFile +"' | mongo.exe";
        PowerShellInstance.AddScript(script);
        Collection<PSObject> PSOutput = PowerShellInstance.Invoke();
    }
}