Automate WooCommerce Integration Tasks from PowerShell

The CData Cmdlets for WooCommerce 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 WooCommerce.

PowerShell Cmdlets or ADO.NET Provider?

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

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

• 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.

PowerShell

1. Install the module:

   Install-Module WooCommerceCmdlets

2. Connect:

   $woocommerce = Connect-WooCommerce -Url "$Url" -ConsumerKey "$ConsumerKey" -ConsumerSecret "$ConsumerSecret" -InitiateOAuth "$InitiateOAuth"
   

3. Search for and retrieve data:

   $parentid = "3"
   $orders = Select-WooCommerce -Connection $woocommerce -Table "Orders" -Where "ParentId = `'$ParentId`'"
   $orders
   

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

   $orders = Invoke-WooCommerce -Connection $woocommerce -Query 'SELECT * FROM Orders WHERE ParentId = @ParentId' -Params @{'@ParentId'='3'}
   

ADO.NET

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

PowerShell

Update-WooCommerce -Connection $WooCommerce -Columns @('ParentId','Total') -Values @('MyParentId', 'MyTotal') -Table Orders -Id "MyId"

ADO.NET

$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

PowerShell

Add-WooCommerce -Connection $WooCommerce -Table Orders -Columns @("ParentId", "Total") -Values @("MyParentId", "MyTotal") 

ADO.NET

$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

PowerShell

Remove-WooCommerce -Connection $WooCommerce -Table "Orders" -Id "MyId"

ADO.NET

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

CodeProject