Learn more
 

Ready to get started?

Download a free trial of the IBM Cloud Data Engine Data Provider to get started:

 Download Now

Learn more:

IBM Cloud Data Engine Icon IBM Cloud Data Engine ADO.NET Provider

Rapidly create and deploy powerful .NET applications that integrate with IBM Cloud Data Engine.

In this article

Related articles

Automate IBM Cloud Data Engine Integration Tasks from PowerShell


Are you in search of a quick and easy way to access IBM Cloud Data Engine data from PowerShell? This article demonstrates how to utilize the IBM Cloud Data Engine Cmdlets for tasks like connecting to IBM Cloud Data Engine data, automating operations, downloading data, and more.

The CData Cmdlets for IBM Cloud Data Engine 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 IBM Cloud Data Engine.

PowerShell Cmdlets or ADO.NET Provider?

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

Once you have acquired the necessary connection properties, accessing IBM Cloud Data Engine data in PowerShell can be enabled in three steps.

IBM Cloud Data Engine uses the OAuth and HMAC authentication standards. See the "Getting Started" chapter of the help documentation for a guide to using OAuth.

PowerShell

  1. Install the module:

    Install-Module IBMCloudDataEngineCmdlets

  2. Connect:

    $ibmclouddataengine = Connect-IBMCloudDataEngine -Api Key "$Api Key" -Instance CRN "$Instance CRN" -Region "$Region" -Schema "$Schema" -OAuth Client Id "$OAuth Client Id" -OAuth Client Secret "$OAuth Client Secret"

  3. Search for and retrieve data:

    $userid = "user@domain.com" $jobs = Select-IBMCloudDataEngine -Connection $ibmclouddataengine -Table "Jobs" -Where "UserId = `'$UserId`'" $jobs

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

    $jobs = Invoke-IBMCloudDataEngine -Connection $ibmclouddataengine -Query 'SELECT * FROM Jobs WHERE UserId = @UserId' -Params @{'@UserId'='user@domain.com'}

ADO.NET

  1. Load the provider's assembly:

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

  2. Connect to IBM Cloud Data Engine:

    $conn= New-Object System.Data.CData.IBMCloudDataEngine.IBMCloudDataEngineConnection("Api Key=MyAPIKey;Instance CRN=myInstanceCRN;Region=myRegion;Schema=mySchema;OAuth Client Id=myOAuthClientId;OAuth Client Secret=myOAuthClientSecret;InitiateOAuth=GETANDREFRESH") $conn.Open()

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

    $sql="SELECT Id, Status from Jobs" $da= New-Object System.Data.CData.IBMCloudDataEngine.IBMCloudDataEngineDataAdapter($sql, $conn) $dt= New-Object System.Data.DataTable $da.Fill($dt) $dt.Rows | foreach { Write-Host $_.id $_.status }

Update IBM Cloud Data Engine Data

PowerShell

Update-IBMCloudDataEngine -Connection $IBMCloudDataEngine -Columns @('Id','Status') -Values @('MyId', 'MyStatus') -Table Jobs -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.IBMCloudDataEngine.IBMCloudDataEngineCommand("UPDATE Jobs SET UserId='user@domain.com' WHERE Id = @myId", $conn) $cmd.Parameters.Add((New-Object System.Data.CData.IBMCloudDataEngine.IBMCloudDataEngineParameter("@myId","10456255-0015501366"))) $cmd.ExecuteNonQuery()

Insert IBM Cloud Data Engine Data

PowerShell

Add-IBMCloudDataEngine -Connection $IBMCloudDataEngine -Table Jobs -Columns @("Id", "Status") -Values @("MyId", "MyStatus")

ADO.NET

$cmd = New-Object System.Data.CData.IBMCloudDataEngine.IBMCloudDataEngineCommand("INSERT INTO Jobs (UserId) VALUES (@myUserId)", $conn) $cmd.Parameters.Add((New-Object System.Data.CData.IBMCloudDataEngine.IBMCloudDataEngineParameter("@myUserId","user@domain.com"))) $cmd.ExecuteNonQuery()

Delete IBM Cloud Data Engine Data

PowerShell

Remove-IBMCloudDataEngine -Connection $IBMCloudDataEngine -Table "Jobs" -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.IBMCloudDataEngine.IBMCloudDataEngineCommand("DELETE FROM Jobs WHERE Id=@myId", $conn) $cmd.Parameters.Add((New-Object System.Data.CData.IBMCloudDataEngine.IBMCloudDataEngineParameter("@myId","001d000000YBRseAAH"))) $cmd.ExecuteNonQuery()