Automate TaxJar Integration Tasks from PowerShell

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

PowerShell Cmdlets or ADO.NET Provider?

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

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

To authenticate to the TaxJar API, first obtain the API Key from the TaxJar UI.

NOTE: the API is available only for Professional and Premium TaxJar plans.

If you already have a Professional or Premium plan you can find the API Key by logging in the TaxJar UI and navigating to Account -> TaxJar API. After obtaining the API Key, you can set it in the APIKey connection property.

Additional Notes

• By default, the CData connector will retrieve data of the last 3 months in cases where the entity support date range filtering. You can set StartDate to specify the minimum creation date of the data retrieved.
• If the API Key has been created for a sandbox API account please set UseSandbox to true, but not all endpoints will work as expected. For more information, refer to the TaxJar developer documentation.

PowerShell

1. Install the module:

   Install-Module TaxJarCmdlets

2. Connect:

   $taxjar = Connect-TaxJar -APIKey "$APIKey"
   

3. Search for and retrieve data:

   $transactionid = "123"
   $orders = Select-TaxJar -Connection $taxjar -Table "Orders" -Where "TransactionID = `'$TransactionID`'"
   $orders
   

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

   $orders = Invoke-TaxJar -Connection $taxjar -Query 'SELECT * FROM Orders WHERE TransactionID = @TransactionID' -Params @{'@TransactionID'='123'}
   

ADO.NET

1. Load the provider's assembly:

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

2. Connect to TaxJar:

    
   $conn= New-Object System.Data.CData.TaxJar.TaxJarConnection("APIKey=3bb04218ef8t80efdf1739abf7257144;")
   $conn.Open()
   

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

   $sql="SELECT TransactionID, UserID from Orders"
   
   $da= New-Object System.Data.CData.TaxJar.TaxJarDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.transactionid $_.userid
   }
    

Update TaxJar Data

PowerShell

Update-TaxJar -Connection $TaxJar -Columns @('TransactionID','UserID') -Values @('MyTransactionID', 'MyUserID') -Table Orders -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.TaxJar.TaxJarCommand("UPDATE Orders SET TransactionID='123' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.TaxJar.TaxJarParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert TaxJar Data

PowerShell

Add-TaxJar -Connection $TaxJar -Table Orders -Columns @("TransactionID", "UserID") -Values @("MyTransactionID", "MyUserID") 

ADO.NET

$cmd = New-Object System.Data.CData.TaxJar.TaxJarCommand("INSERT INTO Orders (TransactionID) VALUES (@myTransactionID)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.TaxJar.TaxJarParameter("@myTransactionID","123")))
$cmd.ExecuteNonQuery()

Delete TaxJar Data

PowerShell

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

ADO.NET

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

CodeProject