Automate Odoo Integration Tasks from PowerShell

The CData ADO.NET Provider for Odoo 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 Odoo.

Getting Started

---

ADO.NET Provider

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

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

To connect, set the Url to a valid Odoo site, User and Password to the connection details of the user you are connecting with, and Database to the Odoo database.

1. Load the provider's assembly:

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

2. Connect to Odoo:

    
   $conn= New-Object System.Data.CData.Odoo.OdooConnection("User=MyUser;Password=MyPassword;URL=http://MyOdooSite/;Database=MyDatabase;")
   $conn.Open()
   

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

   
   $sql="SELECT name, email from res_users"
   
   $da= New-Object System.Data.CData.Odoo.OdooDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.name $_.email
   }
     

Update Odoo Data

$cmd =  New-Object System.Data.CData.Odoo.OdooCommand("UPDATE res_users SET id='1' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Odoo.OdooParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert Odoo Data

$cmd =  New-Object System.Data.CData.Odoo.OdooCommand("INSERT INTO res_users (id) VALUES (@myid)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Odoo.OdooParameter("@myid","1")))
$cmd.ExecuteNonQuery()

Delete Odoo Data

$cmd =  New-Object System.Data.CData.Odoo.OdooCommand("DELETE FROM res_users WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Odoo.OdooParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()