Automate Google Contacts Integration Tasks from PowerShell

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

ADO.NET Provider

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

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

You can connect to Google APIs on behalf of individual users or on behalf of a domain. Google uses the OAuth authentication standard. See the "Getting Started" section of the help documentation for a guide.

1. Load the provider's assembly:

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

2. Connect to Google Contacts:

    
   $conn= New-Object System.Data.CData.GoogleContacts.GoogleContactsConnection("")
   $conn.Open()
   

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

   
   $sql="SELECT Summary, StartDateTime from Friends"
   
   $da= New-Object System.Data.CData.GoogleContacts.GoogleContactsDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.summary $_.startdatetime
   }
     

Update Google Contacts Data

$cmd =  New-Object System.Data.CData.GoogleContacts.GoogleContactsCommand("UPDATE Friends SET SearchTerms='Durham' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.GoogleContacts.GoogleContactsParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert Google Contacts Data

$cmd =  New-Object System.Data.CData.GoogleContacts.GoogleContactsCommand("INSERT INTO Friends (SearchTerms) VALUES (@mySearchTerms)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.GoogleContacts.GoogleContactsParameter("@mySearchTerms","Durham")))
$cmd.ExecuteNonQuery()

Delete Google Contacts Data

$cmd =  New-Object System.Data.CData.GoogleContacts.GoogleContactsCommand("DELETE FROM Friends WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.GoogleContacts.GoogleContactsParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()