Automate WooCommerce Integration Tasks from PowerShell

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

ADO.NET Provider

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

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

WooCommerce supports the following authentication methods: one-legged OAuth1.0 Authentication and standard OAuth2.0 Authentication.

Connecting using one-legged OAuth 1.0 Authentication

Specify the following properties (NOTE: the below credentials are generated from WooCommerce settings page and should not be confused with the credentials generated by using WordPress OAuth2.0 plugin):

• ConsumerKey
• ConsumerSecret

Connecting using WordPress OAuth 2.0 Authentication

After having configured the plugin, you may connect to WooCommerce by providing the following connection properties:

• OAuthClientId
• OAuthClientSecret
• CallbackURL
• InitiateOAuth - Set this to either GETANDREFRESH or REFRESH

In either case, set the Url property to the URL of the WooCommerce instance.

1. Load the provider's assembly:

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

2. Connect to WooCommerce:

    
   $conn= New-Object System.Data.CData.WooCommerce.WooCommerceConnection("Url=https://example.com/; ConsumerKey=ck_ec52c76185c088ecaa3145287c8acba55a6f59ad; ConsumerSecret=cs_9fde14bf57126156701a7563fc87575713c355e5;InitiateOAuth=GETANDREFRESH;")
   $conn.Open()
   

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

   
   $sql="SELECT ParentId, Total from Orders"
   
   $da= New-Object System.Data.CData.WooCommerce.WooCommerceDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.parentid $_.total
   }
     

Update WooCommerce Data

$cmd =  New-Object System.Data.CData.WooCommerce.WooCommerceCommand("UPDATE Orders SET ParentId='3' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.WooCommerce.WooCommerceParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert WooCommerce Data

$cmd =  New-Object System.Data.CData.WooCommerce.WooCommerceCommand("INSERT INTO Orders (ParentId) VALUES (@myParentId)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.WooCommerce.WooCommerceParameter("@myParentId","3")))
$cmd.ExecuteNonQuery()

Delete WooCommerce Data

$cmd =  New-Object System.Data.CData.WooCommerce.WooCommerceCommand("DELETE FROM Orders WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.WooCommerce.WooCommerceParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()