Data import through external API services - integrating with Dynamics 365

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.CSharp;
using System.Data;
using OneStream.Shared.Common;
using OneStream.Shared.Wcf;
using OneStream.Shared.Engine;
using OneStream.Shared.Database;
using OneStream.Stage.Engine;
using OneStream.Stage.Database;
using OneStream.Finance.Engine;
using OneStream.Finance.Database;
using System.Net.Http;
using System.Net.Http.Headers;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

namespace OneStream.BusinessRule.DashboardDataSet.D365_HelperQueries
{
	public class MainClass
	{
		// As of 8.0, CAT (Cloud Administration Tools) can be used to setup secrets in an Azure Key Vault
		// https://documentation.onestream.com/release_8.2/Content/CAT/Key%20Management.html?Highlight=key%20vault
		// You can use BRApi.Utilities.GetSecretValue(SessionInfo si, string secretName) instead of putting client ID and Secret in plain code 
		private const string m_ClientId = "yourclientid";
        private const string m_ClientSecret = "yourclientsecret";
        private const string m_Resource = "https://yourtenant.sandbox.operations.dynamics.com";
        private const string m_AuthUrl = "https://login.microsoftonline.com/yourguid/oauth2/token";
		
		public object Main(SessionInfo si, BRGlobals globals, object api, DashboardDataSetArgs args)
		{
			try
			{
				switch (args.FunctionType)
				{
					case DashboardDataSetFunctionType.GetDataSetNames:
						break;
						
					case DashboardDataSetFunctionType.GetDataSet:	
	                    // Usage will be:
	                    // {D365_HelperQueries}{GetDataFromEndpoint}{Endpoint=[/data/....]}
	                    if (args.DataSetName.XFEqualsIgnoreCase("GetDataFromEndpoint"))
	                    {
	                        // get the parameterized endpoint, 
	                        // and pass it to our data-retrieval method
	                        string endpoint = args.NameValuePairs.XFGetValue("Endpoint");
	                        return GetData(si, endpoint, false).Result;
	                    }
                    	break;
				}
				return null;
			}
			catch (Exception ex)
			{
				throw ErrorHandler.LogWrite(si, new XFException(si, ex));
			}
		}
		
		/* Utility to handle security details */
		public async Task<string> GetBearerToken(SessionInfo si)
        {
            try
            {
                string bearerToken;
				
                using (HttpClient hc = new HttpClient())
                {
                    HttpRequestMessage request = new HttpRequestMessage(HttpMethod.Post, m_AuthUrl);
                    MultipartFormDataContent content = new MultipartFormDataContent();
                    content.Add(new StringContent("client_credentials"), "grant_type");
                    content.Add(new StringContent(m_ClientId), "client_id");
                    content.Add(new StringContent(m_ClientSecret), "client_secret");
                    content.Add(new StringContent(m_Resource), "resource");
                    request.Content = content;
                    // response
                    HttpResponseMessage response = await hc.SendAsync(request);
                    response.EnsureSuccessStatusCode();
                    string result = await response.Content.ReadAsStringAsync();
                    var responseJson = JObject.Parse(result);
                    bearerToken = responseJson["access_token"].ToString();
                }
				
                return bearerToken;
            }
            catch (Exception ex)
            {
                throw ErrorHandler.LogWrite(si, new XFException(si, ex));
            }
        }

		/* entry point for our data-retrieval logic */
		public async Task<DataTable> GetData(SessionInfo si, string endpoint, bool LimitRecords = false)
        {
            string bearerToken = GetBearerToken(si).Result;
			DataTable dt = null;
			
            if (bearerToken != string.Empty)
            {
                try
                {
                    using (HttpClient hc = new HttpClient())
                    {

                        // set up our request...
                        string limitOpt = string.Empty;
                        if (LimitRecords)
                            limitOpt = "&$top=1";
                        HttpRequestMessage request = new HttpRequestMessage(HttpMethod.Get, m_Resource + endpoint + limitOpt);
                        request.Headers.Authorization = new AuthenticationHeaderValue("Bearer", bearerToken);

                        // ... then handle the response
                        HttpResponseMessage response = await hc.SendAsync(request);
                        string result = await response.Content.ReadAsStringAsync();

                        // APIs typically return JSON, so we parse that as a DataTable
                        var resultValues = JObject.Parse(result)["value"];
                        dt = JsonConvert.DeserializeObject<DataTable>(resultValues.ToString());
                    }
                }
                catch (Exception ex)
                {
                    throw ErrorHandler.LogWrite(si, new XFException(si, ex));
                }
            }
			
            return dt;
        }
	}
}

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Windows.Forms;
using Microsoft.CSharp;
using OneStream.Finance.Database;
using OneStream.Finance.Engine;
using OneStream.Shared.Common;
using OneStream.Shared.Database;
using OneStream.Shared.Engine;
using OneStream.Shared.Wcf;
using OneStream.Stage.Database;
using OneStream.Stage.Engine;
// import our custom DDS and name it HelperQueries
using HelperQueries = OneStream.BusinessRule.DashboardDataSet.D365_HelperQueries;

namespace OneStream.BusinessRule.Connector.D365_DataSource
{
	public class MainClass
	{
		
		public object Main(SessionInfo si, BRGlobals globals, Transformer api, ConnectorArgs args)
		{
			// Workflow varies by Scenario Type, so let's find that first
			var scenType = BRApi.Finance.Scenario.GetScenarioType(si, si.WorkflowClusterPk.ScenarioKey);
			// get Text1 property on the current Workflow Profile
			string endpoint = BRApi.Workflow.Metadata.GetProfile(
				si, si.WorkflowClusterPk 
			).GetAttributeValue(
				scenType.Id, 
				SharedConstants.WorkflowProfileAttributeIndexes.Text1
			);
			
			try
			{
				switch (args.ActionType)
				{
					case ConnectorActionTypes.GetFieldList:
					{
						// use our DDS rule to fetch data,
						// then return column names
						DataTable dt = HelperQueries.MainClass.GetData(si, endpoint, true).Result;
						List<string> columnNames = dt.Columns.Cast<DataColumn>().Select(x => x.ColumnName).ToList();
						return columnNames;
					}
					case ConnectorActionTypes.GetData:
					{
						// use our DDS rule to fetch data,
						// then pass it to transformation
						DataTable dt = HelperQueries.MainClass.GetData(si, endpoint).Result;
						api.Parser.ProcessDataTable(si, 
							dt, false, api.ProcessInfo);
						break;
					}
				}
				return null;
			}
			catch (Exception ex)
			{
				throw ErrorHandler.LogWrite(si, new XFException(si, ex));
			}
		}
	}
}