Automate Dropbox Integration Tasks from PowerShell

The CData ADO.NET Provider for Dropbox is a standard ADO.NET Provider that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time and bidirectional access to Dropbox.

ADO.NET Provider

The ADO.NET Provider provides a SQL interface for Dropbox; this tutorial shows how to use the Provider to create, retrieve, update, and delete Dropbox data.

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

Dropbox uses the OAuth authentication standard. To authenticate using OAuth, you can use the embedded credentials or register an app with Dropbox.

See the Getting Started guide in the CData driver documentation for more information.

1. Load the provider's assembly:

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

2. Connect to Dropbox:

    
   $conn= New-Object System.Data.CData.Dropbox.DropboxConnection("InitiateOAuth=GETANDREFRESH;")
   $conn.Open()
   

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

   
   $sql="SELECT Id, Name from Files"
   
   $da= New-Object System.Data.CData.Dropbox.DropboxDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.id $_.name
   }
     

Update Dropbox Data

$cmd =  New-Object System.Data.CData.Dropbox.DropboxCommand("UPDATE Files SET Id='1' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Dropbox.DropboxParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert Dropbox Data

$cmd =  New-Object System.Data.CData.Dropbox.DropboxCommand("INSERT INTO Files (Id) VALUES (@myId)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Dropbox.DropboxParameter("@myId","1")))
$cmd.ExecuteNonQuery()

Delete Dropbox Data

$cmd =  New-Object System.Data.CData.Dropbox.DropboxCommand("DELETE FROM Files WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Dropbox.DropboxParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()