Ready to get started?

Learn more about the CData ADO.NET Provider for WooCommerce or download a free trial:

Download Now

Automate WooCommerce Integration Tasks from PowerShell

Are you looking for a quick and easy way to access WooCommerce data from PowerShell? We show how to use the Cmdlets for WooCommerce and the CData ADO.NET Provider for WooCommerce to connect to WooCommerce data and synchronize, automate, download, and more.

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.

Cmdlets or ADO.NET?

The cmdlets are not only a PowerShell interface to the WooCommerce API, 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.

After obtaining the needed connection properties, accessing WooCommerce data in PowerShell consists of three basic 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, you will need to 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"
  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 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 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 System.Data.CData.WooCommerce.WooCommerceParameter("@myId","001d000000YBRseAAH")) $cmd.ExecuteNonQuery()