Load data from DB

Sample code for:

• Map.Functions.CountryCode('DE'), loads the country code from a user defined table/view from current eBiss DB.
• Map.Functions.CountryCodeFromDb('DE'), loads the country code from a user defined table/view from a different DB.

Referenced assemblies: eBiss.Api.dll und Pranke.Orm.TypeLib.dll

using eBiss.Api;
using eBiss.Api.Data;
using Pranke.Attributes;
using System.Linq;
using Pranke.Orm.Interfaces;
using System.Collections;

namespace Map
{
    [OrmClass(tableName: "my_country_codes")]
    public abstract class CountryCodes
    {
        [Key]
        public abstract string CountryCode { get; set; }

        public abstract string Description { get; set; }
    }


    /// <summary>
    /// 
    /// </summary>
    public class Functions : IMapFunctionExtender, ILoggingObject, IApplicationConsumer, ISystemVariableConsumer
    {
        private static string Db_Connection_Variable = "Db.CountryCodes.Connection";

        public IApplication Application { get; set; }

        public ILogContext Log { get; set; }

        public IDictionary Variables { get; set; }

        /// <summary>
        /// Select from my_country_codes using local DB
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        /// Sample Call: Map.Functions.CountryCode('DE')
        [eBiss.Api.MappingFunction]
        public object CountryCode(string code)
        {
            CountryCodes foundItem = Application.Entities.DbSet<CountryCodes>().Where(c => c.CountryCode == code).FirstOrDefault();

            return foundItem?.Description;
        }

        /// <summary>
        /// Select from my_country_codes using from diffrent DB, in this sample from MS-SQLServer DB eBiss3Demo
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        /// Sample Call: Map.Functions.CountryCodeFromDb('DE')
        [eBiss.Api.MappingFunction]
        public object CountryCodeFromDb(string code)
        {
            Pranke.Orm.Interfaces.IEntities entities = Pranke.Orm.EntitiesBase.Connect(Pranke.Orm.DatabaseType.MsSql, GetDbConnection());
            
            IQueryResult<CountryCodes> res = entities.DbSet<CountryCodes>();
            res.Query.Timeout = 100;

            CountryCodes foundItem = res.Where(c => c.CountryCode == code).FirstOrDefault();
           

            return foundItem?.Description;
        }

        /// <summary>
        /// the Connection string has the same format as the one in the eBiss.Config under 'add key="DbConnectionString"...', 
        /// e.g. with „…;uid=<user>;pwd=<password>“ and not "User=<user>; Password=<password>"
        /// </summary>
        /// <returns></returns>
        /// <exception cref="System.ApplicationException"></exception>
        private string GetDbConnection()
        {
            if (Variables.Contains(Db_Connection_Variable) && string.IsNullOrWhiteSpace(Variables[Db_Connection_Variable].ToString()) == false)
            {
                return Variables[Db_Connection_Variable].ToString();
            }
            else
            {
                throw new System.ApplicationException($"Missing Variable {Db_Connection_Variable}!");
            }
        }

    }
}