Automate GitHub Integration Tasks from PowerShell

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

ADO.NET Provider

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

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

GitHub uses the OAuth 2 authentication standard. To authenticate using OAuth, create an app to obtain the OAuthClientId, OAuthClientSecret, and CallbackURL connection properties. See the Getting Started chapter of the CData help documentation for an authentication guide.

1. Load the provider's assembly:

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

2. Connect to GitHub:

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

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

   
   $sql="SELECT Name, Email from Users"
   
   $da= New-Object System.Data.CData.GitHub.GitHubDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.name $_.email
   }
     

Update GitHub Data

$cmd =  New-Object System.Data.CData.GitHub.GitHubCommand("UPDATE Users SET UserLogin='mojombo' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.GitHub.GitHubParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert GitHub Data

$cmd =  New-Object System.Data.CData.GitHub.GitHubCommand("INSERT INTO Users (UserLogin) VALUES (@myUserLogin)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.GitHub.GitHubParameter("@myUserLogin","mojombo")))
$cmd.ExecuteNonQuery()

Delete GitHub Data

$cmd =  New-Object System.Data.CData.GitHub.GitHubCommand("DELETE FROM Users WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.GitHub.GitHubParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()