C# .NET Core Generic Repository for SAP HANA Database Driver x64 (sample added in June 2021)

[former_member247294]

Introduction

Latest version : 1.4.8 LTS on nuget.org

How to use : Example project in this blog post

Performance is outstanding : 11ms to get a response from a calculation view on 8 tables !

After I installed by home server with SAP HANA 2.0 SPS04, I tried lots of development ideas easily outside office environment and without corporate limitations.(how-to details here)

Every time when I tried to develop something I had to overcome some limitations :

• Spring Framework with JpaRepository consume an significant RAM memory footprint per instance (around 500 MB / instance as HANA Cokpit reports). It's easy but for large scale applications may be expensive to run in any cloud provider.


• Node.JS backend apps have to be created with XSJS compatibility option to have an all-purpose API to expose CRUD operations and All the code writen in javascript is visible to customers of the published app. Also Javascript Promises and Callback are not that easy to be learned and require a lot of code writing effort.

I'm a very big fan of C# .NET and I noticed that SAP has released an SAP HANA driver with Express edition (clients_windows.zip using the provided SAP HANA Express downloader after registering) for dotnetcore so I tried to use it.

For sure the best lifecycle tool to design and deploy the database container is HANA Core Data Services (CDS) but for handling CRUD operations in C# I could not find any suitable tool that is :

• simple to use (see example below)


• compatible with LINQ


• light weight memory footprint (running API uses 40-50 MB / instance)


• few external dependencies other that SAP HANA driver for dotnetcore (only NewtonSoft.Json is now referenced, but this reference is common in API development)


• generic use regarding of data model that is used (db container lifecycle may be maintained with WebIDE & HANA CDS for best performance)


• sql injection prevention on string properties (by default disabled but)


• fast execution & no performance overhead that is not needed (eg : ORM features that are not used in cloud & enterprise services)


• 100% specific HANA sql scripts for perfect compatibility (design for SAP HANA only !)


• Cloud Foundry read of db connection data from VCAP_SERVICES


• compatible with Inversion Of Control (IoC) principles


• custom Query function to use Linq predicates that are transformed to SQL WHERE


• custom salt Encode/Decode for sensitive data object properties (for GDPR compliance)

So I decided to develop & release the first (from my knowledge) Generic Repository NuGet package for SAP HANA dotnetcore use.

 

Pre-requirements

 

For hands on exercise you'll need access to an SAP HANA 2.0 instance (Express version is free for use) and .NET Core 2.1 x64 driver installed besides Visual Studio or Visual Code

You may download them :

Visual Code

Visual Studio

SAP HANA Express and Drivers

Features

 

Generic use and basics :

• mapped columns to properties (Auto by name or with attribute)


• database connection provider to open the connection based on specific SAP HANA specifics


• HanaConnectionInfo class that structures all the hana container informations to handle tables in generic way


• most of the standard column data types are supported (short, int, bool , Guid, string, decimal)


• Create, Get, Update, Count, Delete, NextId, LastId operations provided


• easy model class definitions and attributes to decorate class & properties


• Not mapped properties for light weight


• Query by LINQ predicate &  GetPropertyUniqueValues for dropdown datasource

 

Code Samples

 

Install NuGet package

PM> Install-Package HanaGenericRepository.dll



dotnet add package HanaGenericRepository.dll

data.hdbcds for a simple model

namespace testdb.database;



context data {

	

	type Guid : String(36);



	entity model1 {

		id : Guid;

		name : String(255);

		active : Boolean;

		value : Integer;

		datetime : UTCTimestamp;

	};

}

Model class

using HanaGenericRepository.Attributes;

using System;



namespace TestModels

{

    [HanaTableNamespace("testdb.database")]

    [HanaTableContext("data")]

    [HanaTableName("model1")]

    public class Model1

    {

        [HanaId]

        [HanaColumnName("id")]

        public Guid Id { get; set; }



        [HanaColumnName("name")]

        public String Name { get; set; } = string.Empty;



        [HanaColumnName("active")]

        public bool Active { get; set; }



        [HanaColumnName("value")]

        public int IntValue { get; set; } = 0;



        [HanaColumnName("datetime")]

        public DateTime DateTimeValue { get; set; } = DateTime.MinValue;



        public string NotMappedProperty { get; set; }

    }

}

Repository use

var hanaConnInfo = new HanaConnectionInfo()

    {

        Tenant = "HXE",

        UserName = "",

        Password = "",

        Schema = "TESTDB_SCHEMA",

        Host = "hxehost",

        Port = 39015

    };



var connFactory = new HanaDbConnectionFactory();

var hanaConn = connFactory.GetConnection(hanaConnInfo);



var repo = new HanaGenericRepository<Model1>(dbConn);



long noRecords = repo.Count();

 

Conclusions

 

It is really easy to use .NET and C# when you have good tools.

The memory footprint is bigger than XSODATA Node.JS implementation (around 30MB / instance) and 8x times lower than the Java Cloud SDK uses (500 MB / instance) and it's measured at (50-60 MB / API instance)

You can use check more info about the package on NuGet

 

SAMPLE API & HANA CONTAINER

https://github.com/radu103/hana-generic-repository-test-api

https://github.com/radu103/hana-generic-repository-test-db