Achieving SAP HANA integration with Azure via an ASP.NET Core API

In this article we’re going to show you how to achieve on-premise SAP HANA integration with Azure Logic Apps. Our goal is to enable cloud-based software in Azure to access data from complex queries on an on-premise SAP HANA database.

This solution addresses one of the most frequent integration challenges faced today: connecting on-premise systems to software in the cloud. The good news is that Azure’s low-code development tools are making this hybrid connectivity much easier to achieve than it once was.

Why perform SAP HANA integration with Azure?

As both an SAP Partner and a Microsoft Partner specialising in Azure, we have delivered many SAP integrations using Azure Integration Services.

The reasons we and our clients prefer Azure for SAP integration are as follows:

• consumption-based pricing: start with a free Azure account and only pay for the integration volume you consume
• low-code development: Azure’s graphical interface speeds up development and increases reusability
• SAP connector: the pre-built Azure SAP connector is useful for integrating software such as SAP ECC
• on-premise connectivity: Azure’s on-premise data gateway provides quick and secure data transfer with the cloud

For an in-depth view of the business case for integrating SAP with Azure, see these recent case studies:

• Using Azure Logic Apps to integrate SAP ERP with warranty claims data
• Integrating SAP ERP with a web-based SQL Server via Azure Logic Apps

But there are cases where the Azure SAP connector doesn’t have the functionality we need. For our scenario, querying the SAP HANA database, we’ll need to create a custom connector and an an ASP.NET core API.

Let’s start by looking at the relevant part of a solution we delivered for a recent client (click for a full screen view):

Breaking down this diagram, we can see that on the Azure cloud side there are:

• an Azure Logic App
• a custom Azure connector

And on the on-premise side we have:

• an Azure on-premise data gateway
• an ASP.NET Core API
• and, of course, the SAP HANA system

Taken together, these elements enable other applications or databases to query the SAP HANA database via the API management layer.

Developer’s guide

How to enable SAP HANA integration with Azure using an ASP.NET core API

In this guide we’re going to cover the two main development processes required:

1. creating the custom Azure Logic Apps connector
2. creating the on-premise ASP.NET Core API

Part 1: create a custom connector in Azure Logic Apps

In this part you’ll see how straightforward it is to create a custom Logic Apps connector. This guide covers creating the custom connector resource, after which you’ll have to define its behaviour with an OpenAPI or Postman definition.

1. Choose “Create a resource” on the Azure services menu in the Azure portal.

2. In New, search for “logic apps custom connector”. Then select “Logic Apps Custom Connector”.

3. Select “Create” within the Logic Apps Custom Connector window.

4. Register your connector with the necessary details (see this example):

Now click on “Review + create”.

5. Review the details you entered in Create Logic Apps Custom Connector and finally select “Create”.

At this point the custom connector menu should open automatically. If not, open it directly from the relevant subscription and resource group. You’re now ready to define the connector’s behaviour with Postman or OpenAPI.

Part 2: create an ASP.NET Core API

In this second part, you’ll learn how to develop the on-premise .NET Core application. We’re assuming you already have the .NET Core SDK installed on your machine.

1. Create a new console app with the commands below:

Shell (Windows)
cd %HOMEPATH%/HANAClientsTutorial
dotnet new console -o dotNET

Shell (Linux or Mac)
export HDBDOTNETCORE=/home/dan/sap/hdbclient/dotnetcore
cd $HOME/HANAClientsTutorial
dotnet new console -o dotNET

If using Linux or Mac, you should change the HDBDOTNETCORE variable to point to the location of the libadonetHDB.so or libadonetHDB.dylib file.

2. Open the dotNET.csproj file:

Shell (Windows)
cd dotNET
notepad dotNET.csproj

Shell (Linux or Mac)
cd dotNET
pico dotNET.csproj

Next, add the code below under the PropertyGroup section to indicate where to load the SAP HANA Client .NET Core driver from. Change the HintPath section with the details of where the dll is on your machine.

Shell (Microsoft Windows)
<ItemGroup>
<Reference Include="Sap.Data.Hana.Core.v2.1">
<HintPath>C:\SAP\hdbclient\dotnetcore\v2.1\Sap.Data.Hana.Core.v2.1.dll</HintPath>
</Reference>
</ItemGroup>

Shell (Linux or Mac)
<ItemGroup>
<Reference Include="Sap.Data.Hana.Core.v2.1"> <HintPath>/home/dan/sap/hdbclient/dotnetcore/v2.1/Sap.Data.Hana.Core.v2.1.dll</HintPath>
</Reference>
</ItemGroup>

When you’ve updated the dotNet.csproj file, save and close. The SAP HANA Client interface for .NET Core is compatible with version 2.1, and 3.x releases of .NET Core.

3. Use a code editor to edit the file Program.cs.

Shell (Windows)
notepad Program.cs

Shell (Linux or Mac)
pico Program.cs

4. Copy the code below and use it to replace everything in the Program.cs file:

C#
using System;
using Sap.Data.Hana;
namespace dotNETQuery
{
class Program
{
static void Main(string[] args)
{
try
{
// User1UserKey retrieved from hdbuserstore contains server:port, UID and PWD
// encrypt must be true when connecting to HANA Cloud
// If hdbuserstore is not used to retrieve the connnection information, the format would be
// "Server=10.7.168.11:39015;UID=User1;PWD=Password1;encrypt=true;sslValidateCertificate=false"
using (var conn = new HanaConnection("key=User1UserKey;encrypt=true;sslValidateCertificate=false"))
{
conn.Open();
Console.WriteLine("Connected");
var query = "SELECT TITLE, FIRSTNAME, NAME FROM HOTEL.CUSTOMER";
using (var cmd = new HanaCommand(query, conn))
using (var reader = cmd.ExecuteReader())
{
Console.WriteLine("Query result:");
// Print column names
var sbCol = new System.Text.StringBuilder();
for (var i = 0; i < reader.FieldCount; i++)
{
sbCol.Append(reader.GetName(i).PadRight(20));
}
Console.WriteLine(sbCol.ToString());
// Print rows
while (reader.Read())
{
var sbRow = new System.Text.StringBuilder();
for (var i = 0; i < reader.FieldCount; i++)
{
sbRow.Append(reader[i].ToString().PadRight(20));
}
Console.WriteLine(sbRow.ToString());
}
conn.Close();
}
}
}
catch (Exception ex)
{
Console.WriteLine("Error - " + ex.Message);
Console.WriteLine(ex.ToString());
}
}
}
}

Now save and close the Program.cs file. Take note of the fact that the address, port, UID and PWD will be retrieved from the hdbuserstore. This app uses some of the SAP HANA client .NET Core driver methods, such as HanaConnection. To learn more about this class, take a look at the Microsoft ADO.NET Connection Properties.

5. Finally, run the app:

Make sure you are in the same directory as Program.cs and use the command:

Shell
dotnet run

And you have now successfully created a .NET Core application that queries an SAP HANA database.