How to create a simple API with Hana Generic Repository

Introduction

The first example on using the Hana Generic Repository that is described here and published on nuget.org here

Pre-requirements

• SAP Cloud Platform Trial Account with Cloud Foundry Org and Hana as a Service instance or Hana Cloud instance
• HANA test container deployed containing test schema from here
• or any other Hana schema connection available
• Visual Studio Community and dotnetcore SDK 2.1
• Hana Client Install Windows (x86_64) zip file containing dotnetcore x64 driver from here
• dotnetcore driver installed after the clients file download

Create a new Project

• Create a new Project
• ASP NET Core Web Application with C#
  • App name: WebApplication1
  • Choose the right folder
  • Solution name : WebApplication1
  • Click Next button
  • From templates list choose API
  • Click Create button

Create new Controller

• On Controllers Folder, right click and choose Add -> Controller
• Choose API Controller Empty
• Click Add
• Enter new Controller Name : ModelController
• Click Add
• Delete the auto-generated controller (in my case it was WeatherForecastController.cs)
• Delete the WeatherForecast.cs model class also
• Build Solution
• Run on IIS Express

Add Reference to Nuget Package

• on WebApplication1 project right clieck and Choose Manage Nuget Packages
• on Browse tab of the opened window Search for HanaGenericRepository.dll
• Select and click Install button
• click OK button and the I Accept button for the licence

Add Reference to SAP HANA 2.0 dotnetcore x64 driver

• On Dependencies, under WebApplication1 project, right click and choose Add Reference
• In the new window click on the Browse button, found in the bottom, near the Ok/Cancel buttons
• Choose the path file where you installed the Hana Driver for dotnetcore, select the file
• click Add button
• Build the solution
• Select the driver dependency and choose to always Copy Local on build

Create New Model Class

Create a new project folder with name : Models

Create a new class with name : Model1.cs

Paste the code below in the new file

using HanaGenericRepository.Attributes;
using System;

namespace WebApplication1.Models
{
    [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;

        [HanaColumnName("email")]
        [HanaDatabaseEncodedValue("seed")]
        public string Email { get; set; } = string.Empty;

        public string NotMappedProperty { get; set; }

        public double NotMappedProperty2 { get; set; }
    }
}

How to create Database Connection Info Object

HanaConnectionInfo hanaConnInfo = new HanaConnectionInfo()
{
    Tenant = "HXE",
    UserName = "D118DA114BA14CA5AA1ECF261DBAE68B_74MGARCMNT0WWZ2LRKPVPNKVN_RT",
    Password = "Pr3W0jUm8.5iV1Sn3qwIb4NHFHFUZlhc6Px3QKsZKXj8H6X_3jqIu_FG65K7x3clInZ3mKNPKMXh1ScL7nZeHGzNPvlZkAWHFa_7yJF.dKJORTGj5JS22fj9jWwHFStf",
    Schema = "D118DA114BA14CA5AA1ECF261DBAE68B",
    Host = "hxehost",
    Port = 39015
};

How to Create a Repository Object

var connFactory = new HanaDbConnectionFactory();
var hanaConnection = connFactory.CreateConnection(hanaInfo);
repo1 = new HanaGenericRepository<Model1>(hanaConnection, true);

Create a new Repository Object in the ModelController.cs

Paste the code below in the ModelController.cs

using HanaGenericRepository;
using Microsoft.AspNetCore.Mvc;
using Sap.Data.Hana;
using System;
using System.Collections.Generic;
using TestAPIWithHanaGenericRepository.Models;

namespace TestAPIWithHanaGenericRepository.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class ModelController : ControllerBase
    {
        private HanaConnectionInfo _hanaConnInfo = null;
        private HanaConnection _hanaConnection = null;
        private bool _preventSqlInjection = true;

        private HanaGenericRepository<Model1> repo1 = null;

        public ModelController()
        {
            _hanaConnInfo = new HanaConnectionInfo()
            {
                Tenant = "HXE",
                UserName = "D118DA114BA14CA5AA1ECF261DBAE68B_183JN8QS8E7HWULO7SL3FNIUQ_RT",
                Password = "Wk98eti9PfZt0UDYB90nFHXExBkV0H19A4COXLwkzGVf5TAelMQOU-U1AhzhRh_rMlTRlqNaOW8y1nRbES-ddKyzeOikp_n_0bM4gxOTwGB.FeFX062GBnUiMzha89pe",
                Schema = "D118DA114BA14CA5AA1ECF261DBAE68B",
                Host = "hxehost",
                Port = 39015
            };

            var connFactory = new HanaDbConnectionFactory();
            _hanaConnection = connFactory.CreateConnection(_hanaConnInfo);
            repo1 = new HanaGenericRepository<Model1>(_hanaConnection, _preventSqlInjection);
        }

        [HttpGet]
        public List<Model1> GetModel1Objects()
        {
            var count = repo1.Count();
            if(count == 0)
            {
                var newRecord = new Model1()
                {
                    Active = true,
                    DateTimeValue = DateTime.Now,
                    Email = "test@test.com",
                    Id = Guid.NewGuid(),
                    IntValue = 1,
                    Name = "name",
                    NotMappedProperty = "does not persist",
                    NotMappedProperty2 = 2f
                };
                var ok = repo1.Create(newRecord);
            }

            var models = repo1.GetPaged();            
            return models;
        }
    }
}

Run the new API

• Build and Add missing using namespaces if needed (mouse over and Add)
• Run on IIS Express
• Open Browser with the route http://localhost:PORT/api/model

Sample code

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

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