Skip to Content

Connect to Data Lake Relational Engine Using the .NET Driver

Create and debug a .NET application that connects to a data lake Relation Engine.
You will learn
  • How to install the .NET SDK
  • How to create and debug a .NET application that queries a data lake Relational Engine
danielvaDan van LeeuwenSeptember 20, 2022
Created by
danielva
September 20, 2022
Contributors
danielva

Prerequisites

  • You have completed the first tutorial in this group.

.NET is a free and open source software framework for Microsoft Windows, Linux and Mac operating systems and is the successor to the .NET Framework. .NET was previously known as .NET Core.

  • Step 1

    The first step is to check if you have the .NET SDK installed and what version it is. Enter the following command:

    Shell
    Copy
    dotnet --version  
    

    If the dotnet command is not recognized, it means that the .NET SDK has not been installed. If the SDK is installed, the command returns the currently installed version, such as 6.0.201.

    If the .NET SDK is not installed, download it from Download .NET and run the installer on Microsoft Windows.

    Note: Select the ‘Download .NET SDK x64’ option.

    .NET Core SDK Install

    On Linux, follow the instructions for the appropriate Linux version such as Install the .NET SDK or the .NET Runtime on openSUSE.

    In order for the shell to recognize that the .NET SDK is installed and for any dotnet commands in future steps to be recognized, a new shell window needs to be opened.

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

      Shell (Microsoft Windows)
      Copy
      cd %HOMEPATH%/DataLakeClientsTutorial
      dotnet new console -o dotNET
      
      Shell (Linux)
      Copy
      cd $HOME/DataLakeClientsTutorial
      dotnet new console -o dotNET
      
    2. Open the dotNET.csproj file:

      Shell (Microsoft Windows)
      Copy
      cd dotNET
      notepad dotNET.csproj
      
      Shell (Linux)
      Copy
      cd dotNET
      pico dotNET.csproj
      

      Add the following below the PropertyGroup section (within the Project section) to indicate where to load the data lake Relational Engine Client .NET driver from. Modify the HintPath section with the information about where the dll is located on your machine.

      Shell (Microsoft Windows)
      Copy
      <ItemGroup>
        <Reference Include="Sap.Data.SQLAnywhere.Core.v2.1">
          <HintPath>C:\SAP\DLClient\IQ-17_1\Assembly\Core2.1\Sap.Data.SQLAnywhere.Core.v2.1.dll</HintPath>
        </Reference>
      </ItemGroup>
      
      Shell (Linux)
      Copy
      <ItemGroup>
        <Reference Include="Sap.Data.SQLAnywhere.Core.v2.1">
          <HintPath>/home/dan/DLClient/IQ-17_1/assembly/core2.1/Sap.Data.SQLAnywhere.Core.v2.1.dll</HintPath>
        </Reference>
      </ItemGroup>
      
      dotNET.csproj code

      Once the dotNet.csproj file has been updated, save and close the file.

    3. Open an editor to edit the file Program.cs.

      Shell (Windows)
      Copy
      notepad Program.cs
      
      Shell (Linux)
      Copy
      pico Program.cs
      
    4. Replace the entire contents of Program.cs with the code below:

      C#
      Copy
      using System;
      using Sap.Data.SQLAnywhere;
      namespace dotNETQuery
      {
          class Program
          {
              static void Main(string[] args)
              {
                  try
                  {
                      var connStr = "host=XXXX.iq.hdl.trial-us10.hanacloud.ondemand.com:443;UID=USER1;PWD=Password1;ENC=TLS(tls_type=rsa;direct=yes)";
                      using (var conn = new SAConnection(connStr)) {
                          conn.Open();
                          var query = "SELECT TITLE, FIRSTNAME, NAME FROM HOTEL.CUSTOMER";
                          using (var cmd = new SACommand(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++) {
                                          var value = reader[i].ToString();
                                          if (value != null) {
                                              sbRow.Append(value.PadRight(20));
                                          }
                                      }
                                      Console.WriteLine(sbRow.ToString());
                                  }
                                  conn.Close();
                              }
                          }
                      }
                  }
                  catch (Exception ex) {
                      Console.WriteLine("Error - " + ex.Message);
                      Console.WriteLine(ex.ToString());
                  }
              }
          }
      }
      

      Save and close the Program.cs file after replacing the code.

      The above app makes use of some of the data lake Relational Engine .NET driver methods, such as SAConnection. Connection details for this class can be found at Connection Properties. See also the .NET Driver in the SAP HANA Cloud, data Lake client interfaces guide. Further .NET API details can be found in the .NET API browser.

    5. Run the app:

      Shell
      Copy
      dotnet run
      

      Before running the program make sure to be in the directory where Program.cs is saved

      Result of running the app
  • Step 3
    1. If you have not already done so, download Visual Studio Code.

    2. If you have not already done so, in Visual Studio Code, choose File | Add Folder to Workspace, and then add the DataLakeClientsTutorial folder.

      Workspace
    3. Open the file Program.cs.

      C# Extension

      Visual Studio Code will recognize the cs file extension and will suggest installing the C# for Visual Studio Code extension. Click Install.

    4. Place a breakpoint on the line sbRow.Append line. Select Run | Start Debugging | .NET Core. A configuration will be added. Choose Run | Start Debugging.

      Notice that the debug view becomes active and that the RUN option is .NET Launch.

      Notice that the program stops running at the breakpoint that was set.

      Observe the variable values in the leftmost pane. Step through code.

      VS Code Debugging

      For further information on debugging .NET apps consult Tutorial: Debug a .NET Core console application using Visual Studio Code and Instructions for setting up the .NET Core debugger.

    Congratulations! You have now created and debugged a .NET application that connects to and queries an SAP HANA database.

    Which of the following statements are true?

Back to top