Automate BigCommerce Integration Tasks from PowerShell

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

PowerShell Cmdlets or ADO.NET Provider?

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

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

BigCommerce authentication is based on the standard OAuth flow. To authenticate, you must initially create an app via the Big Commerce developer platform where you can obtain an OAuthClientId, OAuthClientSecret, and CallbackURL. These three parameters will be set as connection properties to your driver.

Additionally, in order to connect to your BigCommerce Store, you will need your StoreId. To find your Store Id please follow these steps:

1. Log in to your BigCommerce account.
2. From the Home Page, select Advanced Settings > API Accounts.
3. Click Create API Account.
4. A text box named API Path will appear on your screen.
5. Inside you can see a URL of the following structure: https://api.bigcommerce.com/stores/{Store Id}/v3.
6. As demonstrated above, your Store Id will be between the 'stores/' and '/v3' path paramters.
7. Once you have retrieved your Store Id you can either click Cancel or proceed in creating an API Account in case you do not have one already.

PowerShell

1. Install the module:

   Install-Module BigCommerceCmdlets

2. Connect:

   $bigcommerce = Connect-BigCommerce -OAuthClientId "$OAuthClientId" -OAuthClientSecret "$OAuthClientSecret" -StoreId "$StoreId" -CallbackURL "$CallbackURL" -InitiateOAuth "$InitiateOAuth"
   

3. Search for and retrieve data:

   $firstname = "Bob"
   $customers = Select-BigCommerce -Connection $bigcommerce -Table "Customers" -Where "FirstName = `'$FirstName`'"
   $customers
   

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

   $customers = Invoke-BigCommerce -Connection $bigcommerce -Query 'SELECT * FROM Customers WHERE FirstName = @FirstName' -Params @{'@FirstName'='Bob'}
   

ADO.NET

1. Load the provider's assembly:

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

2. Connect to BigCommerce:

    
   $conn= New-Object System.Data.CData.BigCommerce.BigCommerceConnection("OAuthClientId=YourClientId; OAuthClientSecret=YourClientSecret; StoreId='YourStoreID'; CallbackURL='http://localhost:33333';InitiateOAuth=GETANDREFRESH;")
   $conn.Open()
   

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

   $sql="SELECT FirstName, LastName from Customers"
   
   $da= New-Object System.Data.CData.BigCommerce.BigCommerceDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.firstname $_.lastname
   }
    

Update BigCommerce Data

PowerShell

Update-BigCommerce -Connection $BigCommerce -Columns @('FirstName','LastName') -Values @('MyFirstName', 'MyLastName') -Table Customers -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.BigCommerce.BigCommerceCommand("UPDATE Customers SET FirstName='Bob' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.BigCommerce.BigCommerceParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert BigCommerce Data

PowerShell

Add-BigCommerce -Connection $BigCommerce -Table Customers -Columns @("FirstName", "LastName") -Values @("MyFirstName", "MyLastName") 

ADO.NET

$cmd = New-Object System.Data.CData.BigCommerce.BigCommerceCommand("INSERT INTO Customers (FirstName) VALUES (@myFirstName)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.BigCommerce.BigCommerceParameter("@myFirstName","Bob")))
$cmd.ExecuteNonQuery()

Delete BigCommerce Data

PowerShell

Remove-BigCommerce -Connection $BigCommerce -Table "Customers" -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.BigCommerce.BigCommerceCommand("DELETE FROM Customers WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.BigCommerce.BigCommerceParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()

CodeProject