Ready to get started?

Learn more about the CData ADO.NET Provider for SAP Business One DI or download a free trial:

Download Now

Automate SAP Business One DI Integration Tasks from PowerShell

Are you looking for a quick and easy way to access SAP Business One DI data from PowerShell? We show how to use the Cmdlets for SAP Business One DI and the CData ADO.NET Provider for SAP Business One DI to connect to SAP Business One DI data and synchronize, automate, download, and more.

The CData Cmdlets for SAP Business One DI 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 SAP Business One DI.

Cmdlets or ADO.NET?

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

After obtaining the needed connection properties, accessing SAP Business One DI data in PowerShell consists of three basic steps.

To connect to SAP Business One DI data, specify the following connection properties:

  • DBServerType: The type of server being connected to.
  • Server: The name or IP address of the Business One DI server to connect to.
  • CompanyDB: The company to connect to.
  • User: The username used when connecting to the LicenseServer.
  • Password: The password used when connecting to the LicenseServer.
  • LicenseServer (optional): Set this if your License Server is different from the Server.
  • UseTrusted (optional): Set to TRUE to connect using Windows credentials.

PowerShell

  1. Install the module:

    Install-Module SAPBusinessOneDICmdlets
  2. Connect:

    $sapbusinessonedi = Connect-SAPBusinessOneDI -Server "$Server" -DBServerType "$DBServerType" -CompanyDB "$CompanyDB" -User "$User" -Password "$Password"
  3. Search for and retrieve data:

    $acctname = "account_name" $oact = Select-SAPBusinessOneDI -Connection $sapbusinessonedi -Table "OACT" -Where "AcctName = `'$AcctName`'" $oact

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

    $oact = Invoke-SAPBusinessOneDI -Connection $sapbusinessonedi -Query 'SELECT * FROM OACT WHERE AcctName = @AcctName' -Params @{'@AcctName'='account_name'}

ADO.NET

  1. Load the provider's assembly:

    [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for SAP Business One DI\lib\System.Data.CData.SAPBusinessOneDI.dll")
  2. Connect to SAP Business One DI:

    $conn= New-Object System.Data.CData.SAPBusinessOneDI.SAPBusinessOneDIConnection("Server=ServerName;DBServerType=MSSQL_2016;CompanyDB=SBODemoCA;User=manager;Password=manager;") $conn.Open()
  3. Instantiate the SAPBusinessOneDIDataAdapter, execute an SQL query, and output the results:

    $sql="SELECT AcctCode, AcctName from OACT" $da= New-Object System.Data.CData.SAPBusinessOneDI.SAPBusinessOneDIDataAdapter($sql, $conn) $dt= New-Object System.Data.DataTable $da.Fill($dt) $dt.Rows | foreach { Write-Host $_.acctcode $_.acctname }

Update SAP Business One DI Data

PowerShell

Update-SAPBusinessOneDI -Connection $SAPBusinessOneDI -Columns @('AcctCode','AcctName') -Values @('MyAcctCode', 'MyAcctName') -Table OACT -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.SAPBusinessOneDI.SAPBusinessOneDICommand("UPDATE OACT SET AcctName='account_name' WHERE Id = @myId", $conn) $cmd.Parameters.Add(new System.Data.CData.SAPBusinessOneDI.SAPBusinessOneDIParameter("@myId","10456255-0015501366")) $cmd.ExecuteNonQuery()

Insert SAP Business One DI Data

PowerShell

Add-SAPBusinessOneDI -Connection $SAPBusinessOneDI -Table OACT -Columns @("AcctCode", "AcctName") -Values @("MyAcctCode", "MyAcctName")

ADO.NET

$cmd = New-Object System.Data.CData.SAPBusinessOneDI.SAPBusinessOneDICommand("INSERT INTO OACT (AcctName) VALUES (@myAcctName)", $conn) $cmd.Parameters.Add(new System.Data.CData.SAPBusinessOneDI.SAPBusinessOneDIParameter("@myAcctName","account_name")) $cmd.ExecuteNonQuery()

Delete SAP Business One DI Data

PowerShell

Remove-SAPBusinessOneDI -Connection $SAPBusinessOneDI -Table "OACT" -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.SAPBusinessOneDI.SAPBusinessOneDICommand("DELETE FROM OACT WHERE Id=@myId", $conn) $cmd.Parameters.Add(new System.Data.CData.SAPBusinessOneDI.SAPBusinessOneDIParameter("@myId","001d000000YBRseAAH")) $cmd.ExecuteNonQuery()