Automate MongoDB Integration Tasks from PowerShell

The CData Cmdlets for MongoDB are standard PowerShell cmdlets that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time and bidirectional access to MongoDB.

Getting Started

---

PowerShell Cmdlets or ADO.NET Provider?

The Cmdlets are not only a PowerShell interface to MongoDB, but also an SQL interface; this tutorial shows how to use both to create, retrieve, update, and delete MongoDB data. We also show examples of the ADO.NET equivalent, which is possible with the CData ADO.NET Provider for MongoDB. To access MongoDB data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for MongoDB.

Once you have acquired the necessary connection properties, accessing MongoDB data in PowerShell can be enabled in three steps.

Set the Server, Database, User, and Password connection properties to connect to MongoDB. To access MongoDB collections as tables you can use automatic schema discovery or write your own schema definitions. Schemas are defined in .rsd files, which have a simple format. You can also execute free-form queries that are not tied to the schema.

PowerShell

1. Install the module:

   Install-Module MongoDBCmdlets

2. Connect:

   $mongodb = Connect-MongoDB -Server "$Server" -Port "$Port" -Database "$Database" -User "$User" -Password "$Password"
   

3. Search for and retrieve data:

   $name = "Morris Park Bake Shop"
   $restaurants = Select-MongoDB -Connection $mongodb -Table "restaurants" -Where "Name = `'$Name`'"
   $restaurants
   

   You can also use the Invoke-MongoDB cmdlet to execute SQL commands:

   $restaurants = Invoke-MongoDB -Connection $mongodb -Query 'SELECT * FROM restaurants WHERE Name = @Name' -Params @{'@Name'='Morris Park Bake Shop'}
   

ADO.NET

1. Load the provider's assembly:

   [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for MongoDB\lib\System.Data.CData.MongoDB.dll")
    

2. Connect to MongoDB:

    
   $conn= New-Object System.Data.CData.MongoDB.MongoDBConnection("Server=MyServer;Port=27017;Database=test;User=test;Password=Password;")
   $conn.Open()
   

3. Instantiate the MongoDBDataAdapter, execute an SQL query, and output the results:

   $sql="SELECT borough, cuisine from restaurants"
   
   $da= New-Object System.Data.CData.MongoDB.MongoDBDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.borough $_.cuisine
   }
    

Update MongoDB Data

PowerShell

Update-MongoDB -Connection $MongoDB -Columns @('borough','cuisine') -Values @('Myborough', 'Mycuisine') -Table restaurants -Id "My_id"

ADO.NET

$cmd = New-Object System.Data.CData.MongoDB.MongoDBCommand("UPDATE restaurants SET Name='Morris Park Bake Shop' WHERE _id = @my_id", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.MongoDB.MongoDBParameter("@my_id","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert MongoDB Data

PowerShell

Add-MongoDB -Connection $MongoDB -Table restaurants -Columns @("borough", "cuisine") -Values @("Myborough", "Mycuisine") 

ADO.NET

$cmd = New-Object System.Data.CData.MongoDB.MongoDBCommand("INSERT INTO restaurants (Name) VALUES (@myName)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.MongoDB.MongoDBParameter("@myName","Morris Park Bake Shop")))
$cmd.ExecuteNonQuery()

Delete MongoDB Data

PowerShell

Remove-MongoDB -Connection $MongoDB -Table "restaurants" -Id "My_id"

ADO.NET

$cmd = New-Object System.Data.CData.MongoDB.MongoDBCommand("DELETE FROM restaurants WHERE _id=@my_id", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.MongoDB.MongoDBParameter("@my_id","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()

CodeProject