HanaGenericRepository.dll 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!