Automate Quickbase Integration Tasks from PowerShell

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

PowerShell Cmdlets or ADO.NET Provider?

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

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

User Authentication Method

To authenticate with user credentials, specify the following connection properties:

1. Set the User and Password.
2. If your application requires an ApplicationToken;, you must provide it otherwise an error will be thrown. You can find the ApplicationToken under SpecificApp > Settings > App management > App properties > Advanced settings > Security options > Manage Application Token.

User Token Authentication

To authenticate with a user token, specify the following connection properties:

1. Set UserToken and you are ready to connect. You can find the UserToken under Quick Base > My Preferences > My User Information > Manage User Tokens.

PowerShell

1. Install the module:

   Install-Module QuickBaseCmdlets

2. Connect:

   $quickbase = Connect-QuickBase -User "$User" -Password "$Password" -Domain "$Domain" -ApplicationToken "$ApplicationToken"
   

3. Search for and retrieve data:

   $column2 = "100"
   $sampletable_1 = Select-QuickBase -Connection $quickbase -Table "SampleTable_1" -Where "Column2 = `'$Column2`'"
   $sampletable_1
   

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

   $sampletable_1 = Invoke-QuickBase -Connection $quickbase -Query 'SELECT * FROM SampleTable_1 WHERE Column2 = @Column2' -Params @{'@Column2'='100'}
   

ADO.NET

1. Load the provider's assembly:

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

2. Connect to Quickbase:

    
   $conn= New-Object System.Data.CData.QuickBase.QuickBaseConnection("[email protected];Password=password;Domain=myinstance.quickbase.com;ApplicationToken=bwkxrb5da2wn57bzfh9xn24")
   $conn.Open()
   

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

   $sql="SELECT Id, Column1 from SampleTable_1"
   
   $da= New-Object System.Data.CData.QuickBase.QuickBaseDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.id $_.column1
   }
    

Update Quickbase Data

PowerShell

Update-QuickBase -Connection $QuickBase -Columns @('Id','Column1') -Values @('MyId', 'MyColumn1') -Table SampleTable_1 -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.QuickBase.QuickBaseCommand("UPDATE SampleTable_1 SET Column2='100' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.QuickBase.QuickBaseParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert Quickbase Data

PowerShell

Add-QuickBase -Connection $QuickBase -Table SampleTable_1 -Columns @("Id", "Column1") -Values @("MyId", "MyColumn1") 

ADO.NET

$cmd = New-Object System.Data.CData.QuickBase.QuickBaseCommand("INSERT INTO SampleTable_1 (Column2) VALUES (@myColumn2)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.QuickBase.QuickBaseParameter("@myColumn2","100")))
$cmd.ExecuteNonQuery()

Delete Quickbase Data

PowerShell

Remove-QuickBase -Connection $QuickBase -Table "SampleTable_1" -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.QuickBase.QuickBaseCommand("DELETE FROM SampleTable_1 WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.QuickBase.QuickBaseParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()

CodeProject