HanaGenericRepository.dll 1.4.3

There is a newer version of this package available.
See the version list below for details.
dotnet add package HanaGenericRepository.dll --version 1.4.3
NuGet\Install-Package HanaGenericRepository.dll -Version 1.4.3
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="HanaGenericRepository.dll" Version="1.4.3" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add HanaGenericRepository.dll --version 1.4.3
#r "nuget: HanaGenericRepository.dll, 1.4.3"
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
// Install HanaGenericRepository.dll as a Cake Addin
#addin nuget:?package=HanaGenericRepository.dll&version=1.4.3

// Install HanaGenericRepository.dll as a Cake Tool
#tool nuget:?package=HanaGenericRepository.dll&version=1.4.3

Release Notes

* v1.4.3 LTS : bugfix insert/update NULL properties
* v1.4.2 : performance tweak the encode/decode utility class (30x faster)
* v1.4.1 : added [HanaDatabaseEncodedValueAttribute] to encode/decode object properties on the fly & extended SQL injections test expressions
* v1.3.3 : new func Query by sql where string & bugfixes

Features

  • usable 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 referenced)
  • 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)
  • fast execution & no performance overhead that is not needed (eg : ORM features that are not used in cloud & enterprise services)
  • sql scripts designed for SAP HANA !
  • Inversion Of Control ready
  • GDPR : encoding in stored value & decoding on access

1. Instalation

Get SAP HANA Express .NET Core x64 driver

  1. Register for SAP HANA Express & Get the downloader from here
  2. Using the downloader get SAP HANA Express 2.0 SPS 04 or later & windows_clients.zip file

Install the nuget package & add the database driver as local dependency (copy file = true)

2. Documentation

2.1 Classes & interfaces

  • CloudFoundryEnvironmentHelper - reads the ENV variables "VCAP_SERVICES" and "VCAP_APPLICATION"
  • IHanaServerInfo - used to inject & custom define Server info (returns HanaConnectionInfo)
  • HanaCloudFoundryInfo & CloudFoundryEnvironmentHelper - read VCAP_SERVICES and VCAP_APPLICATION from CF Environment
  • HanaConnectionInfo - db connection parameters specific to SAP HANA (eg : namespace, context, tenant db)
  • HanaDbConnectionFactory - opens database connections (input type : HanaConnectionInfo, output type : HanaConnection)
  • HanaGenericRepository<T> - use for model class
  • MD5Utils that adds string extension for string.ComputeMD5Hash()

2.1 Attributes

Class Attributes

  • HanaTableNamespaceAttribute- mandatory (namespace from full table name structure : namespace::context.table)
  • HanaTableContextAttribute - mandatory (context from full table name structure : namespace::context.table)
  • HanaTableNameAttribute - by default id not specified table name is considered (model class name).ToLowerCase()

Property Attributes

  • HanaIdAttribute - 1 mandatory property per model class has to be marked as Id. (int data types, string and Guid types supported as single column table id)
  • HanaColumnNameAttribute - mandatory for mapped properties (attribute is missing = property is not mapped to database)
  • HanaDatabaseEncodedValueAttribute - store encoded/decode on access

2.1 Repository Functions

* constructor receives and object created by HanaDbConnectionFactory.CreateConnection function
* NextId() - generates the next valid id (Guid, Long or String - using substring of a new Guid insatce)
* LastId() - gets the last id generated or from table id column
* Count() - returns the number of rows using `SELECT COUNT(*) from table` query
* TestConnection() - test connections with `SELECT * FROM DUMMY`
* EnableSqlInjectionPrevention() - activates sql injection tests for string properties (by default disabled)
* DisableSqlInjectionPrevention() - disables sql injection tests for string properties
* Query(Expression<Func<T, bool>> filter, Expression<Func<T, object>> orderBy = null, long take = 0, long skip = 0) - filters by LINQ expression using `SELECT * FROM table WHERE condition` query
* GetPaged(long take, long skip) - gets records ordered ASC by id value using LIMIT & OFFSET sql query
* GetById(object id) - gets object by Id value
* DeleteById(object id) - deletes object by id value (DELETE FROM sql)
* Create(T obj) - saves row (with INSERT INTO sql)
* Update(T obj) - updaet row (UPDATE table SET ... WHERE idcol = ..)
* GetColumnDistinctValues(string columnName) - returns column distinct values using `SELECT DISTINCT columnName FROM table ORDER BY column` query
* GetColumnValues(string columnName) - returns list with id & column value using `SELECT id,column FROM table ORDER BY column` query
* GetColumnName(PropertyInfo info) - needed to be used to get results from column functions : `GetColumnDistinctValues` and `GetColumnValues`

3. Sample Code

3.1 Test Database (SAP Hana Core Data Services - data.hdbcdsfile)

Create your Database schema with CDS

SAP HANA Developer Manual

File for SAP HANA DB module (Multi Target Application)
namespace testdb.database;

context data {
	
	type Guid : String(36);

	entity model1 {
		id : Guid;
		name : String(255);
		active : Boolean;
		value : Integer;
		datetime : UTCTimestamp;
	};
}

3.2 Model Class Example

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; }
    }
}

3.3 Repository Creation and 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();

3.4 IoC example for .NET Core API

In StartUp.cs file add
    services.AddSingleton<IHanaInfo, HanaCloudFoundryInfo>();
    services.AddSingleton<IHanaDbConnectionFactory, HanaDbConnectionFactory>();
In Controller add private property
    private HanaConnectionInfo _hanaConnInfo;
In Controller constructor get object from IoC container
    public TenantController([FromServices] IHanaInfo hanaInfo)
    {
        _hanaConnInfo = hanaInfo.GetInfo();
    }
In Controller functions get the values from IoC container
    [HttpGet]
    [Route("tenant/{take}/{skip}")]
    public ActionResult<List<Tenant>> Tenants([FromServices] IHanaDbConnectionFactory hanaDbConnFactory, [FromRoute] int take, [FromRoute] int skip = 0)
    {
        var hanaConn = hanaDbConnFactory.CreateConnection(this._hanaConnInfo);
            
        var repo = new HanaGenericRepository<Tenant>(hanaConn);

        return repo.GetPaged(take, skip);
    }

4. Performance

For single threaded test 100 inserts + 100 update + 100 count(*) + 100 delete transactions = 2 seconds Average runtime for below configuration is 2s / 400 transactions :

So it's very fast : 2000 ms (2s) / 400 transactions = 5 ms per transaction !

Hardware configuration for hana server & development VM : * 32 GB RAM * 8 vcores in VMSphere ESXi 6.7 (2 x processor Intel Xeon E5-2630L @1.8 Ghz)

Test VM & HANA VM on the same hypervisor!

Product Compatible and additional computed target framework versions.
.NET net5.0 was computed.  net5.0-windows was computed.  net6.0 was computed.  net6.0-android was computed.  net6.0-ios was computed.  net6.0-maccatalyst was computed.  net6.0-macos was computed.  net6.0-tvos was computed.  net6.0-windows was computed.  net7.0 was computed.  net7.0-android was computed.  net7.0-ios was computed.  net7.0-maccatalyst was computed.  net7.0-macos was computed.  net7.0-tvos was computed.  net7.0-windows was computed.  net8.0 was computed.  net8.0-android was computed.  net8.0-browser was computed.  net8.0-ios was computed.  net8.0-maccatalyst was computed.  net8.0-macos was computed.  net8.0-tvos was computed.  net8.0-windows was computed. 
.NET Core netcoreapp2.1 is compatible.  netcoreapp2.2 was computed.  netcoreapp3.0 was computed.  netcoreapp3.1 was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

NuGet packages

This package is not used by any NuGet packages.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
1.4.8 3,738 2/22/2020
1.4.7 517 2/19/2020
1.4.6 501 2/16/2020
1.4.5 619 2/16/2020
1.4.4 565 2/10/2020
1.4.3 500 2/6/2020
1.4.2 505 2/4/2020
1.4.1 510 2/3/2020
1.3.3 576 1/26/2020
1.3.2 595 1/25/2020

First version tested with the following column data types : bool, short, int, long, decimal, guid, string, DateTime