<?xml version="1.0" encoding="utf-8"?>
<AlvaoApplication xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" ModelVersion="1">
  <Applications>
    <Application id="23">
      <Name>Import M365 Licenses</Name>
      <Description>Regular import of user licenses that are part of Microsoft 365 product family. Existing licenses are assigned to users.</Description>
      <UniqueId>80a291ec-2730-4a95-a5e0-6d8a92ffd017</UniqueId>
      <Version>3</Version>
      <AdvancedSettings>
        <Setting>
          <Name>ImportM365Licenses.AtHours</Name>
          <Value>2</Value>
        </Setting>
        <Setting>
          <Name>ImportM365Licenses.ClientId</Name>
          <Value />
        </Setting>
        <Setting>
          <Name>ImportM365Licenses.ClientSecret</Name>
          <Value />
        </Setting>
        <Setting>
          <Name>ImportM365Licenses.TenantId</Name>
          <Value />
        </Setting>
      </AdvancedSettings>
      <Scripts>
        <Script id="47">
          <Name>PeriodicAction</Name>
          <Code>using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net.Http;
using System.Text;
using System.Threading.Tasks;
using System.Web;
using Alvao.API.Common.Model.Database;
using Alvao.Apps.API;
using Azure.Identity;
using Microsoft.Graph;
using Microsoft.Graph.Models;
using Microsoft.Kiota.Abstractions.Serialization;
using Dapper;
using Microsoft.Data.SqlClient;
using Alvao.API.Common;
using Alvao.API.AM;
using Alvao.API.Utils;

public class PeriodicAction : IPeriodicAction
{
    private const string ExceededLicenseMemoPrefix = "License is exceeded in M365: ";

    private Settings Settings = new();
    private int MicrosoftCompanyId;
    private readonly List&lt;string&gt; Warnings = [];
    private readonly List&lt;string&gt; Errors = [];
    private readonly Dictionary&lt;string, string&gt; ProductsById = new();

    private tPerson SystemPerson = new();
    private tblKind EmailProperty = new();
    private tblKind UserProperty = new();
    private tblKind LoginNameProperty = new();

    private List&lt;SubscribedSku&gt; SubscribedSkus = [];
    private List&lt;User&gt; Users = [];

    private enum LicMode
    {
        PerDevice = 1,
        PerUser = 2,
        PerConnection = 3,
        PerProcessor = 4,
        PerCore = 5,
        Err
    }

    private class DbLicense
    {
        public int Id { get; set; }
        public Guid? SkuId { get; set; }
        public string SkuPartNumber { get; set; } = "";
        public int KindId { get; set; }
        public bool AutoAssign { get; set; }
        public bool IsValid { get; set; }
        public int Count { get; set; }
        public Dictionary&lt;int, Microsoft.Graph.Models.User&gt; UsersById { get; } = new();
        public bool IsValidInM365 { get; set; }
    }

    public string Name
    {
        get =&gt; "PeriodicAction";
        set { }
    }

    public void OnPeriod(SqlConnection con)
    {
        Settings = Settings.Get(con);
        if (!Settings.AtHours.Contains(DateTime.Now.Hour)) return;
        LogInfo("OnPeriod AtHours: {0}", Settings.AtHours);

        Init();

        Task.Run(ReadDataAsync).GetAwaiter().GetResult();

        int importedCount = Import(con);
        LogInfo("OnPeriod End: {0}", importedCount);
        if (Warnings.Count &gt; 0 || Errors.Count &gt; 0)
        {
            string message = "There were " + Errors.Count + " error(s) and " + Warnings.Count + " warning(s)\r\n";
            foreach (string error in Errors)
            {
                message += error + "\r\n";
            }
            foreach (string warning in Warnings)
            {
                message += warning + "\r\n";
            }
            throw new InvalidOperationException(message);
        }
    }

    private void Init()
    {
        SystemPerson = Alvao.API.Common.Person.GetSystem();
        var properties = ObjectProperty.GetTemplateProperties(tblClass.ClassCode.User);
        EmailProperty = properties.FirstOrDefault(p =&gt; p.intKindCode == (int)tblKind.KindCode.Email);
        UserProperty = properties.FirstOrDefault(p =&gt; p.intKindCode == (int)tblKind.KindCode.User);
        LoginNameProperty = properties.FirstOrDefault(p =&gt; p.intKindCode == (int)tblKind.KindCode.LoginName);
        if (EmailProperty == null &amp;&amp; LoginNameProperty == null)
        {
            throw new InvalidOperationException(
                "The template of User object doesn't contain the E-mail or Login name property so user objects can't be paired with the users in Azure.");
        }
    }

    private async Task ReadDataAsync()
    {
        await ReadProductsAsync();

        var graphClient = GetGraphClient();
        var skuResponse = await graphClient.SubscribedSkus.GetAsync();
        if (skuResponse == null)
        {
            LogError("Subscribed SKUs could not be read");
            return;
        }

        SubscribedSkus = await GetPagedEntitiesAsync&lt;SubscribedSku, SubscribedSkuCollectionResponse&gt;(graphClient, skuResponse);
        await ReadUsersAsync(graphClient);
        LogInfo("Total users with any license: {0}", Users.Count);
    }

    private async Task ReadProductsAsync()
    {
        using (var client = new HttpClient())
        {
            var response = await client.GetAsync(Settings.ProductsUrl);
            response.EnsureSuccessStatusCode();
            var stream = await response.Content.ReadAsStreamAsync();
            using (var reader = new CsvReader(stream, Encoding.Default) { Separator = "," })
            {
                int lineNumber = 1;
                var values = reader.GetCsvLine();
                while (values != null)
                {
                    if (lineNumber &gt; 1 &amp;&amp; values?.Length &gt; 1)
                    {
                        ProductsById[values[1]] = values[0];
                    }
                    values = reader.GetCsvLine();
                    lineNumber++;
                }
            }
        }
    }

    private async Task&lt;List&lt;TEntity&gt;&gt; GetPagedEntitiesAsync&lt;TEntity, TCollectionPage&gt;(GraphServiceClient graphClient, TCollectionPage response) where TCollectionPage : IParsable,IAdditionalDataHolder,new()
    {
        var list = new List&lt;TEntity&gt;();
        var pageIterator = PageIterator&lt;TEntity, TCollectionPage&gt;.CreatePageIterator(graphClient, response, (item) =&gt;
        {
            list.Add(item);
            return true;
        });

        await pageIterator.IterateAsync();
        return list;
    }

    private async Task ReadUsersAsync(GraphServiceClient graphClient)
    {
        var users = await graphClient.Users.GetAsync(config =&gt;
        {
            config.QueryParameters.Top = 100;
            config.QueryParameters.Select = ["id", "userPrincipalName", "displayName", "mail", "assignedLicenses"];
        });

        if (users == null)
        {
            LogError("Users could not be read");
            return;
        }

        var allUsers = await GetPagedEntitiesAsync&lt;User, UserCollectionResponse&gt;(graphClient, users);
        Users = allUsers.Where(x =&gt; x.AssignedLicenses?.Count &gt; 0).ToList();
    }

    private int Import(SqlConnection connection)
    {
        int importedCount = 0;
        var licenses = GetLicenseData(connection);
        foreach (var license in licenses.Values)
        {
            try
            {
                UpdateLicense(license);
                importedCount++;
            }
            catch (Exception ex)
            {
                LogError("Error updating license " + license.SkuPartNumber + " Id: " + license.Id + " Exception: " + ex.Message);
            }
        }

        return importedCount;
    }

    static Dictionary&lt;int, tblLicHistItem&gt; GetLicenseValidAssignments(int licenseId)
    {
        return License.GetValidItems(licenseId)
            .Where(i =&gt; i.NodeId != null)
            .ToDictionary(x =&gt; x.NodeId == null ? 0 : x.NodeId.Value);
    }

    void ChangeLicenseValidity(DbLicense license, bool isValid)
    {
        var lic = License.GetById(license.Id);
        if (isValid)
            lic.dteDateExpire = null;
        else
        {
            var expireDate = DateTime.UtcNow.AddDays(-1);
            lic.dteDateExpire = lic.CreatedDate &gt; expireDate ? lic.CreatedDate : expireDate;
        }
        License.CreateOrUpdate(lic);
        if( isValid )
            LogInfo($"License '{license.SkuPartNumber}' was validated");
        else
            LogInfo($"License '{license.SkuPartNumber}' was invalidated because its 'Capability status' or license count ({license.Count}) &lt;= 0");
    }

    private void UpdateLicense(DbLicense license)
    {
        if (license.Count &lt;= 0 &amp;&amp; license.IsValid )
            ChangeLicenseValidity(license, false);
        else if( license.IsValid &amp;&amp; !license.IsValidInM365 )
            ChangeLicenseValidity(license, false);
        else if( !license.IsValid &amp;&amp; license.IsValidInM365)
            ChangeLicenseValidity(license, true);
        
        var lic = License.GetById(license.Id);
        if (license.Count &lt; license.UsersById.Count)
        {   //license is exceeded in M365
            lic.LicenseCount = license.UsersById.Count;
            lic.txtMemo = $"{ExceededLicenseMemoPrefix} {license.UsersById.Count - license.Count} users need valid licenses";
            License.CreateOrUpdate(lic);
        }
        else
        {
            if (lic.txtMemo is not null &amp;&amp; lic.txtMemo.StartsWith(ExceededLicenseMemoPrefix))
            {   //reset "exceeded" note if necessary
                lic.txtMemo = null;
                License.CreateOrUpdate(lic);
            }
            License.UpdateCount(license.Id, license.Count);
        }

        var previousItemsByUserId = GetLicenseValidAssignments(license.Id);

        // add or remove assignments for users
        var newItems = new List&lt;tblLicHistItem&gt;();
        foreach (var userId in license.UsersById.Keys)
        {
            if (previousItemsByUserId.ContainsKey(userId))
            {
                // License allready assigned
                previousItemsByUserId.Remove(userId);
                LogInfo($"License '{license.SkuPartNumber}' ID {license.Id} is already assigned to user with ID {userId}");
                continue;
            }

            // previous assignment does not exist - create new assignment
            newItems.Add(new tblLicHistItem()
            {
                lintLicHistId = license.Id,
                NodeId = userId
            });
        }

        // remove previous assignments to users that are no more used
        foreach (var item in previousItemsByUserId.Values)
        {
            License.Release(item.intLicHistItemId);
            LogInfo($"License '{license.SkuPartNumber}' ID {license.Id} was released from user with ID {item.NodeId}");
        }

        // add new assignments to users
        foreach (var item in newItems)
        {
            try
            {
                LogInfo($"Assigning license '{license.SkuPartNumber}' ID {license.Id} to user with ID {item.NodeId}");
                License.Assign(item);
            }
            catch (Alvao.API.AM.Exceptions.LicenseAlreadyAssignedException ex)
            {
                // ignore - license already assigned
                LogInfo($"License '{license.SkuPartNumber}' ID {license.Id}, User ID {item.NodeId} Exception: {ex.Message}");
            }
        }
    }

    private Dictionary&lt;Guid, DbLicense&gt; GetLicenseData(SqlConnection connection)
    {
        var licenses = GetLicenses(connection);
        foreach (var user in Users)
        {
            if (user is null)
                continue;

            if (user.AssignedLicenses == null)
            {
                LogWarning($"User {user.DisplayName} doesn't have any license, skipping...");
                continue;
            }

            int userNodeId = 0;
            foreach (var license in user.AssignedLicenses)
            {
                if (license is null || license.SkuId is null)
                    continue;

                if (!licenses.TryGetValue(license.SkuId.Value, out var dbLicense))
                    continue;   //invalid license

                if (userNodeId == 0)
                {
                    userNodeId = GetUserNodeId(connection, user.Mail ?? "", user.UserPrincipalName ?? "");
                    if (userNodeId == 0)
                    {
                        userNodeId = CreateUserNode(connection, user.DisplayName ?? "", user.Mail ?? "", user.UserPrincipalName ?? "");
                    }
                }
                dbLicense.UsersById.Add(userNodeId, user);
            }
        }

        return licenses;
    }

    private Dictionary&lt;Guid, DbLicense&gt; GetLicenses(SqlConnection connection)
    {
        var result = new Dictionary&lt;Guid, DbLicense&gt;();
        foreach (var license in SubscribedSkus)
        {
            if( license is null || license.SkuPartNumber is null || license.SkuId is null)
                continue;

            if( license.PrepaidUnits?.Enabled &lt;= 0 )
                continue;

            LogInfo($"Getting Db license for '{license.SkuPartNumber}', '{license.Id}'");
            var dbLicense = GetLicense(connection, license);
            if (dbLicense.Id == 0)
            {
                LogInfo($"Db license for '{license.SkuPartNumber}' is invalid");
                continue;
            }

            dbLicense.SkuId = license.SkuId;
            dbLicense.SkuPartNumber = license.SkuPartNumber;
            var totalCount = license.PrepaidUnits?.Enabled;
            dbLicense.Count = totalCount ?? 0;
            dbLicense.IsValidInM365 = license.CapabilityStatus is "Enabled" or "Warning";
            if (dbLicense.KindId != (int)LicMode.PerUser)
            {
                dbLicense.IsValid = false;
                LogWarning($"License '{license.SkuPartNumber}' can’t be processed because of its Licensing model. It has to be set to \"per user\".");
            }
            if (dbLicense.AutoAssign)
            {
                LogWarning($"License '{license.SkuPartNumber}' uses automatic assigning. For proper functioning, turn this feature off for this license.");
            }

            result.Add(license.SkuId.Value, dbLicense);
        }

        return result;
    }

    private int CreateUserNode(SqlConnection connection, string userName, string email, string loginName)
    {
        int folderId = Alvao.API.AM.Object.FindOrCreateImportedObjectsFolder();
        int userNodeId = Alvao.API.AM.Object.CreateByClass((int)tblClass.ClassCode.User, userName, folderId, SystemPerson.iPersonId);
        var properties = new Dictionary&lt;string, object&gt;();
        if (!string.IsNullOrEmpty(email) &amp;&amp; EmailProperty != null)
        {
            properties.Add(EmailProperty.txtName, email);
        }
        if (!string.IsNullOrEmpty(loginName) &amp;&amp; LoginNameProperty != null)
        {
            properties.Add(LoginNameProperty.txtName, loginName);
        }
        if (!string.IsNullOrEmpty(userName) &amp;&amp; UserProperty != null)
        {
            properties.Add(UserProperty.txtName, userName);
        }
        ObjectProperty.Update(SystemPerson.iPersonId, userNodeId, properties, false);
        connection.Execute("exec spCalcNodeName @nodeId", new { nodeId = userNodeId });
        return userNodeId;
    }

    private int GetUserNodeId(SqlConnection connection, string email, string loginName)
    {
        string sql = @"
select distinct n.intNodeId
from tblNode n
left join vNodeProperty p on p.NodeId = n.intNodeId and p.InheritedFromNodeId is null -- email value
    and p.KindCode = @emailCode
left join vNodeProperty p2 on p2.NodeId = n.intNodeId and p2.InheritedFromNodeId is null -- user name value
  and p2.KindCode = @loginCode -- user name kind
left join NodeCust v ON v.NodeId = n.intNodeId
where n.IsActive = 1
    and n.lintClassId = 7
    and (lower(v.Email) = lower(@email) or lower(v.UserName) = lower(@loginName))
";
        var ids = connection.Query&lt;int&gt;(sql, new {
            email,
            loginName,
            emailCode = (int)tblKind.KindCode.Email,
            loginCode = (int)tblKind.KindCode.LoginName
        });
        if (!ids.Any())
        {
            return 0;
        }
        if (ids.Count() &gt; 1)
        {
            LogWarning($"Found more than one user with email {email} or login name {loginName} in the database.");
        }

        return ids.Min();
    }

    private DbLicense GetLicense(SqlConnection connection, SubscribedSku skuPartNumberLic)
    {
        string sql = "select intLicHistId as Id, LicKindId as KindId, AutoAssign, IsValid from tblLicHist where lower(UId) = lower(@SkuPartNumber) and InvalidatedDate is null";
        var license = connection.Query&lt;DbLicense&gt;(sql, new { skuPartNumberLic.SkuPartNumber }).FirstOrDefault();

        if ((license is null || !license.IsValid) &amp;&amp; skuPartNumberLic.CapabilityStatus is not ("Enabled" or "Warning"))
            return new() { Id = 0 };

        if (license == null)
        {
            if (!ProductsById.TryGetValue(skuPartNumberLic.SkuPartNumber ?? "", out var productName))
            {
                LogWarning($"Product with Id '{skuPartNumberLic.SkuPartNumber}' not found in the CSV.");
                return new() { Id = 0 };
            }

            int productId = GetProductId(connection, productName);
            var licenseModel = new tblLicHist()
            {
                txtLicName = productName,
                lintProductId = productId,
                dteDate = DateTime.UtcNow.Date,
                CreatedDate = DateTime.UtcNow,
                LicKindId = (int)LicMode.PerUser,
                LicTypeId = 1, // Normal
                AutoAssign = false,
                UId = skuPartNumberLic.SkuPartNumber,
                CoverPackPartsWithoutPackageInstalled = true,
            };

            license = new DbLicense()
            {
                Id = License.CreateOrUpdate(licenseModel),
                KindId = licenseModel.LicKindId
            };

            LogInfo($"Created new license '{skuPartNumberLic.SkuPartNumber}' with ID {license.Id}");
        }
        return license;
    }

    private int GetProductId(SqlConnection connection, string productName)
    {
        string sql = "select intProductId from tblProduct where lower(txtName) = lower(@productName)";
        int productId = connection.Query&lt;int&gt;(sql, new { productName }).FirstOrDefault();
        if (productId == 0)
        {
            var productModel = new tblProduct()
            {
                txtName = productName,
                lintProducerCompanyId = GetMicrosoftCompanyId(connection),
                lintProductTypeId = 1, // Commercial
                SamTypeId = 1, // Product displays in Overview of licenses and installations
                ProductStateId = 1, // Unclassified
                bolValid = true,
                dteTimeStamp = DateTime.UtcNow,
            };
            productId = Product.CreateOrUpdate(productModel);
            Product.AddCustomProductToValidationQueueTable(productId);
            LogInfo($"Created new product '{productName}' with ID {productId}");
        }

        return productId;
    }

    private int GetMicrosoftCompanyId(SqlConnection connection)
    {
        if (MicrosoftCompanyId &gt; 0) return MicrosoftCompanyId;

        string sql = "select intCompanyId from tblCompany where intCompanyUID=147";
        MicrosoftCompanyId = connection.Query&lt;int&gt;(sql).FirstOrDefault();
        return MicrosoftCompanyId;
    }

    private GraphServiceClient GetGraphClient()
    {
        var scopes = new[] { "https://graph.microsoft.com/.default" };
        var options = new ClientSecretCredentialOptions
        {
            AuthorityHost = AzureAuthorityHosts.AzurePublicCloud,
        };

        // https://learn.microsoft.com/dotnet/api/azure.identity.clientsecretcredential
        var clientSecretCredential = new ClientSecretCredential(
            Settings.TenantId, Settings.ClientId, Settings.ClientSecret, options);

        return new GraphServiceClient(clientSecretCredential, scopes);
    }

    private void LogInfo(string message, params object[] args)
    {
        // add code to log Info messages
    }

    private void LogWarning(string message)
    {
        Warnings.Add(message);
    }

    private void LogError(string message)
    {
        Errors.Add(message);
    }
}

public class CsvReader : IDisposable
{
    private Stream stream;
    private StreamReader reader;
    private string separator = ",";

    public string Separator
    {
        get
        {
            return separator;
        }
        set
        {
            separator = value;
        }
    }

    public CsvReader(Stream s) : this(s, null) { }

    public CsvReader(Stream s, Encoding enc)
    {
        this.stream = s;
        if (!s.CanRead)
        {
            throw new CsvReaderException("Can't open CSV stream!");
        }
        reader = (enc != null) ? new StreamReader(s, enc) : new StreamReader(s);
    }

    public CsvReader(string filename) : this(filename, null) { }

    public CsvReader(string filename, Encoding enc)
        : this(new FileStream(filename, FileMode.Open), enc) { }

    private string ReadLine(StreamReader rdr)
    {
        string data=null;
        int ch;

        while ( (ch = rdr.Read()) != -1 )
        {
            if (ch == '\r')
            {
                //zkus \n
                ch = rdr.Read();
                if (ch == '\n')
                {
                    //konec radku
                    break;
                }
                else
                {
                    //mekky enter pokracuj
                    data += "\r" + (char)ch;
                }
            }
            else if (ch == '\n')
            {
                data += "\r\n"; //pretransformuj mekou mezeru na tvrdou
            }
            else
                data += (char)ch;
        }

        return data;
    }
    public string[] GetCsvLine()
    {
        string data = ReadLine(reader);
        if (data == null) return null;
        if (data.Length == 0) return new string[0];

        ArrayList result = new ArrayList();

        ParseCsvFields(result, data);

        return (string[])result.ToArray(typeof(string));
    }

    public void SeekToBegin()
    {
        stream.Seek(0, SeekOrigin.Begin);
    }

    private void ParseCsvFields(ArrayList result, string data)
    {
        int pos = -1;
        while (pos &lt; data.Length)
            result.Add(ParseCsvField(data, ref pos).Trim());
    }

    private string ParseCsvField(string data, ref int startSeparatorPosition)
    {
        if (startSeparatorPosition == data.Length - 1)
        {
            startSeparatorPosition++;
            return "";
        }

        int fromPos = startSeparatorPosition + 1;

        //je to pole s uvozovkama?
        if (data[fromPos] == '"')
        {
            if (fromPos == data.Length - 1)
            {
                fromPos++;
                return "\"";
            }
            int nextSingleQuote = FindSingleQuote(data, fromPos + 1);
            startSeparatorPosition = nextSingleQuote + 1;
            return data.Substring(fromPos + 1, nextSingleQuote - fromPos - 1).Replace("\"\"", "\"");
        }

        //pole konci oddelovacem nebo EOF
        int nextComma = data.IndexOf(separator, fromPos);
        if (nextComma == -1)
        {
            startSeparatorPosition = data.Length;
            return data.Substring(fromPos);
        }
        else
        {
            startSeparatorPosition = nextComma;
            return data.Substring(fromPos, nextComma - fromPos);
        }
    }

    private int FindSingleQuote(string data, int startFrom)
    {
        int i = startFrom - 1;
        while (++i &lt; data.Length)
            if (data[i] == '"')
            {
                //uvozovka - preskoc znaky
                if (i &lt; data.Length - 1 &amp;&amp; data[i + 1] == '"')
                {
                    i++;
                    continue;
                }
                else
                    return i;
            }
        return i;
    }

    public void Dispose()
    {
        if (reader != null) reader.Close();
        else if (stream != null)
            stream.Close(); // v pripade chyby pred konstrukci readeru
        GC.SuppressFinalize(this);
    }
}

[Serializable]
public class CsvReaderException : ApplicationException
{
    public CsvReaderException(string message) : base(message) { }

    public CsvReaderException()
    {
    }

    public CsvReaderException(string message, Exception innerException) : base(message, innerException)
    {
    }
}</Code>
          <IsLibCode>false</IsLibCode>
          <Codesign>QKIOwdDQlQbr+nNZhoZB/um6lLAJxwaE202bHbhmRYnhA8LTr9O3QW8If7svPk4vTisXnH2TK1OvcG0EtqUGTZ9yrjBUTdSb3P18l73n/voEjUgxE4z72qYTs7g4EFT1geezPQyf+3P4odbnhHODjiQmL26LWCT5a5ThutGC7+yenWdhkC4+L0OJR1XqOSSRVu5deZTrYvTx8xB6ZS/31eToa3IFmBj/OFqk447u15F9TQetHjpr5XM7mpxfgEvcWA3O6Ciu2/+Z7pxQUFXdKVMGO79OYAg+gOmDbldlkQ2TVTg0Ru2A8yIrGypkRizHpvg/2ASueRtpqFCgOlBngQ==</Codesign>
        </Script>
        <Script id="48">
          <Name>Settings</Name>
          <Code>using System;
using System.Collections.Generic;
using System.Linq;
using Dapper;
using Microsoft.Data.SqlClient;

public class Settings
{
    private const string DefaultProductsUrl = "https://download.microsoft.com/download/e/3/e/e3e9faf2-f28b-490a-9ada-c6089a1fc5b0/Product%20names%20and%20service%20plan%20identifiers%20for%20licensing.csv";

    public const string AppName = "ImportM365Licenses";
    public string ClientId { get; set; }
    public string ClientSecret { get; set; }
    public string TenantId { get; set; }
    public IList&lt;int&gt; AtHours { get; set; }
    public string ProductsUrl { get; set; }

    public static Settings Get(SqlConnection connection)
    {
        string tenantId = connection.ExecuteScalar&lt;string&gt;("SELECT TOP 1 AzureTenantId FROM AzureAdTenant");
        // set your tenantId here if it is not in database
        if (string.IsNullOrEmpty(tenantId)) tenantId = "&lt;tenantId&gt;";

        // return values from table tProperty or from default value presets (third parameter in GetPropertyValue method)
        return new Settings()
        {
            ClientId = GetPropertyValue(connection, nameof(ClientId), "&lt;clientId&gt;"),
            ClientSecret = GetPropertyValue(connection, nameof(ClientSecret), "&lt;clientSecret&gt;"),
            TenantId = GetPropertyValue(connection, nameof(TenantId), tenantId),
            AtHours = StringToIntList(GetPropertyValue(connection, nameof(AtHours), "2")),
            ProductsUrl = GetPropertyValue(connection, nameof(ProductsUrl), DefaultProductsUrl)
        };
    }

    private static T GetPropertyValue&lt;T&gt;(SqlConnection connection, string name, T defaultValue)
    {
        string type = typeof(T).Name;
        string typePrefix = "s";
        if (type.StartsWith("Int")) typePrefix = "i";
        else if (type.StartsWith("Bool")) typePrefix = "b";
        else if (type.StartsWith("Date")) typePrefix = "d";
        var values = connection.Query&lt;T&gt;(
            $"select {typePrefix}PropertyValue from tProperty WHERE sProperty = @propertyName",
            new { propertyName = AppName + "." + name });
        return values.Any() ? values.First() : defaultValue;
    }

    private static List&lt;int&gt; StringToIntList(string s)
    {
        if (string.IsNullOrEmpty(s)) return new List&lt;int&gt;();
        return s.Split(',').Select(x =&gt; int.Parse(x)).ToList();
    }
}</Code>
          <IsLibCode>true</IsLibCode>
          <Codesign>gFn3DBWa6y3AQhVdBbUkU3kmY2NV9o2tl9ayT/SyF/nJsCFzr0EOcZ4BM4iP5XnaDU0xwVTKYPnHHNLUqGE7KX+L9G8ps0hJcxv5yxUTjSrhcbcM3jy2gzr9WYSDz51Nr3HcYti7bMvzjl9NUvz6ICVG/HH0u5nwSYLETc1UoeWu2C3UBApG0t0lCqNQutr6bUGzt1Xpo+bZghmV+HOfuUFnCTRkmXapdYb8YUBoFzU7Qs1lL620wb1rEWxnrPnpmbYdiNNIyYYWBWvxezS0svYeeOAlRBefpPe5R4MFcALvP+4QFfFqc4ZyZ8zo0/dGdK0W27BsMtRHVNoZVfaqXg==</Codesign>
        </Script>
      </Scripts>
    </Application>
  </Applications>
</AlvaoApplication>