Automate Box Integration Tasks from PowerShell

The CData ADO.NET Provider for Box 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 Box.

ADO.NET Provider

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

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

Box uses the OAuth standard to authenticate. To authenticate to Box, obtain the OAuthClientId, OAuthClientSecret, and CallbackURL by registering an app. See the "Getting Started" chapter of the help documentation for a guide to using OAuth.

1. Load the provider's assembly:

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

2. Connect to Box:

    
   $conn= New-Object System.Data.CData.Box.BoxConnection("OAuthClientId=MyOAuthClientId;OAuthClientSecret=MyOAuthClientSecret;CallbackURL=http://localhost:33333;InitiateOAuth=GETANDREFRESH;")
   $conn.Open()
   

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

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

Update Box Data

$cmd =  New-Object System.Data.CData.Box.BoxCommand("UPDATE Files SET Id='123' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Box.BoxParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert Box Data

$cmd =  New-Object System.Data.CData.Box.BoxCommand("INSERT INTO Files (Id) VALUES (@myId)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Box.BoxParameter("@myId","123")))
$cmd.ExecuteNonQuery()

Delete Box Data

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