Automate MongoDB Integration Tasks from PowerShell

The CData ADO.NET Provider for MongoDB is a standard ADO.NET Provider 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

---

ADO.NET Provider

The ADO.NET Provider provides a SQL interface for MongoDB; this tutorial shows how to use the Provider to create, retrieve, update, and delete MongoDB data.

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.

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

$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

$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

$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()