Automate BigCommerce Integration Tasks from PowerShell

Ready to get started?

Download for a free trial:

Download Now

Learn more:

BigCommerce ADO.NET Provider

Rapidly create and deploy powerful .NET applications that integrate with BigCommerce Ecommerce Software.



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

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.

Cmdlets or ADO.NET?

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

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