MSCRMSAMA, D365 CE learn and Share

Hello,

I had a customer request recently where the timing parameters may change periodically for certain fields on the form, we love to have workflows for citizen developers who can configure anytime without the need of developers. But to make the execution quick and smooth we love our client scripts. Below is the scenario, where I have to use the JavaScript to call the configurable synchronous workflow to execute quickly from ribbon button.

Knowing the fact that we can’t call the Synchronous events on the Javascript Dynamics V9.1.x, but still can call the RTW synchronously, so for the quick execution of events I have called a RTW from ribbon button trigger event on the form.

This approach was quick and works smooth on latest Dynamics online version.

Code can be found form


//Global parameters
var workFlowId = "{85A87D41-2081-4B35-B5F5-FF0B0B7A99C5}";
var entityRecId = "{E4E53BF4-9AA2-4F7F-8509-4111EAFC38D8}";
var formContext = null;

}


Environment: Dynamics 365 v 9.1.x

Hope it helped

Coolsama.

Hi Geeks,

Sharing something I have recently refreshed myself on with D365 for apps

Data export service (DES)

This is a standard OoB plugin form Microsoft to Sync data between Common data service and Azure SQL database. This process is commonly used for customers who want to use copy of CDS data to Azure SQL, Azure SQL being Datawarehouse and build reports on top of it, also migrate data further down the line after doing some transformations on the Azure SQL. This plugin allows only delta changes to Sync from Dynamics to Azure SQL, after initial sync.

Prerequisite:

• Azure SQL database subscription on Azure and right permissions assigned.
• Enable the Change tracking option on the Entity settings for entities that requires for data sync in CDS
• Install the data export service form App source on the Dynamics instance
• Azure Key Vault subscription 

We have two stages on this Setup. 

1. Create the resources on Azure manually or by using powershell script to automate
2. Create the Export profile and sync the data in Dynamics

Stage 1: 

Manual setup

• Create profile schema on Azure SQL database -> give permissions required and copy the connection string
• Create a Key Vault on Azure
• Create a Secret within the Key Vault 
• Add a Tag to the Key Valut with Tag name -> CE Organisation GUID (get from developer resources in Dynamics), Tag value -> Connectionstring of SQL
• Copy the Secret Identifier generated from the Secret

 Or

Run Powershell script 

• Run the script as in this link on Azure https://docs.microsoft.com/en-us/power-platform/admin/replicate-data-microsoft-azure-sql-database#SetupAzureKV
• Copy the Secret Identifier from the powershell output

Stage 2:

Create of export profile 

• Create export profile in CRM by going to Settings -> Data export. Give names accordingly, Key Vault URL will be the Secret Identifier copied from above stage, validate and click next 

• Mange entities that are required to Sync to Azure SQL, then click Next.

• Finally Click Create & Activate

•  Do the initial sync and validate by querying in the Azure SQL database

FAQs

– error on creation of Export profile -> Give the CRM user the permissions on the Key vault as Get

– profile error on Creation of export profile -> Check the Secret value is having the right SQL connection string and resolve. Still issue arises, then drop the tables and profile schema in SQL and recreate

Format of parameters in powershell script:

Input:

$subscriptionId = ‘xxxx.xxxxx’

$keyvaultName = ‘Samplekey’

$secretName = ‘SecreteName’

$resourceGroupName = ‘sQLRG’

$location = ‘centralus’

$connectionString = ‘AzureSQLconnectionString’

$organizationIdList = ‘ContosoSalesOrg1_id, ContosoSalesOrg2_id‘

Output:

Secret Identifier = ‘https://<azureserver>.vault.azure.net/secrets/Secret-SS-CRM-082020/e0e112d2943344473a159bf2efddd46c1‘

Helpful links: 

https://docs.microsoft.com/en-us/power-platform/admin/replicate-data-microsoft-azure-sql-database

https://docs.microsoft.com/en-us/power-platform/admin/replicate-data-microsoft-azure-sql-database#SetupAzureKV

https://www.powerobjects.com/blog/2019/05/07/data-export-service-installation-configuration/

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Xrm.Sdk;
using Microsoft.Crm.Sdk.Messages;
using System.Runtime.Serialization;
using System.Security;
using System.ServiceModel;
using Microsoft.SharePoint.Client;
using Microsoft.Xrm.Sdk.Metadata;
using Microsoft.Xrm.Sdk.Query;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

namespace PreAnnotationPlugin
{
public class PreAnnotationCreate : IPlugin
{

public void Execute(IServiceProvider serviceProvider)
{

//Please make sure CRM and sharepoint integration Oob is completed
//This is plugin code to trigger when new annotation record has attachment created, then it uploads the file to sharepoint, even can create custom folder
//The below code reads the filename and attachment, converts to bytes, pass the data to Sharepoint and uploads via Json rest End point
//Env: CRM 2016 onpremise, Sharepoint 2013 onpremise, gac register on CRM server Microsoft.SharePoint.Client.dll, microsoft.sharepoint.client.runtime.dll, Newtonsoft.Json.dll
//make sure the Sharepoint link “http://siteurl&#8221; is working from CRM server locally

Guid contactId;
string SiteUrl = “http://siteurl&#8221;;
string spCRMDomain = “domain”;
string spCRMUsername = “username”;
string spCRMPassword = “pass”;
Guid regardingObjId = Guid.Empty;
Guid supplierid = Guid.Empty;
string supplierName = string.Empty;
Guid certificateGuid = Guid.Empty;
Guid spDocLocDefaultCertId = Guid.Empty;
string spsiteName = string.Empty;
string folderpath = “account”; //Default
string regardingObjLogicalName = string.Empty;
string CertificateName = string.Empty;
Guid spsiteId = Guid.Empty;
Guid spDocLocAccId = Guid.Empty;
Guid spDocLocCertId = Guid.Empty;
string ctfoldername = “”;
string filename = string.Empty;
string documentBody = string.Empty;

IPluginExecutionContext context = (IPluginExecutionContext)serviceProvider.GetService(typeof (IPluginExecutionContext));
IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof (IOrganizationServiceFactory));
IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);
ITracingService tracing = (ITracingService)serviceProvider.GetService(typeof(ITracingService));

if (context.InputParameters.Contains(“Target”) && context.InputParameters[“Target”] is Entity)
{
try
{

Entity postMessageImage = (Entity)context.PostEntityImages[“PostImage”];

//use post image and read the values of filename an document body

tracing.Trace(“Entered the PrenotePluginstep1”);
Entity entity = (Entity) context.InputParameters[“Target”];

//convert the document to bytes
byte[] documentBytes = Convert.FromBase64String(documentBody);

//Get the root spspite that is default
string spSiteFetchXml = string.Format(@”

“, SiteUrl);

EntityCollection SpSiteDefaultist = service.RetrieveMultiple(new FetchExpression(spSiteFetchXml));

if (SpSiteDefaultist.Entities.Count > 0)
{

//Default spsiteId for http://siteurl
spsiteId = SpSiteDefaultist[0].Id;

//Get the related Certificate record
if (postMessageImage.Attributes.Contains(“objectid”))
{
regardingObjId = ((EntityReference)(postMessageImage.Attributes[“objectid”])).Id;
}

if (postMessageImage.Attributes.Contains(“objectid”))
{
regardingObjLogicalName = ((EntityReference)postMessageImage.Attributes[“objectid”]).LogicalName;

if (regardingObjLogicalName == “new_customentity”)
{

string customEntityFetchXml = string.Format(@”

“, regardingObjId);

EntityCollection customEntityList =
service.RetrieveMultiple(new FetchExpression(customEntityFetchXml));

if (customEntityList.Entities.Count > 0)
{
//get the values from the querylist
}
}
}

string customEntityrecordUrl = string.Format(“{0}_{1}”, customEntityRecordName, cusotmEntityRecordId).Replace(“-“, “”).Replace(“/”, “”).Replace(“:”, “”).Replace(“.”, “”).Replace(” “, “”).ToUpper();

//Get Default Sharepoint document location for custom record
string splocationCertfetchXml = string.Format(@”

“, regardingObjId);

EntityCollection splocCertlist = service.RetrieveMultiple(new FetchExpression(splocationCertfetchXml));

if (splocCertlist.Entities.Count > 0)
{
//found the location
spDocLocCertId = splocCertlist[0].Id;
}
else
{//create the docloc for new_customEntity record

string certificateUrl = string.Format(“{0}_{1}”, customEntityRecordName, cusotmEntityRecordId).Replace(“-“, “”).Replace(“/”, “”).Replace(“:”, “”).Replace(“.”, “”).Replace(” “, “”).ToUpper();

//GetDefaultSplocation for new_customEntity record
string splocationaccountfetchXml = string.Format(@”

“, supplierid);

EntityCollection splocAccountlist = service.RetrieveMultiple(new FetchExpression(splocationaccountfetchXml));

if (splocAccountlist.Entities.Count > 0)
{
//if found get the folder name or library name
spDocLocAccId = splocAccountlist[0].Id;

//create the Sharepoint folder for the custom entity

using (ClientContext clientContext = new ClientContext(SiteUrl))
{
SecureString passWord = new SecureString();

foreach (char c in spCRMPassword.ToCharArray()) passWord.AppendChar(c);

clientContext.Credentials = new NetworkCredential(spCRMUsername, spCRMPassword, spCRMDomain); //new SharePointOnlineCredentials(crmusername, passWord);
var folder = CreateSharePointFolder(SiteUrl, spCRMUsername, spCRMPassword, (string)splocAccountlist[0].Attributes[“relativeurl”], certificateUrl);

}
}
else
{
//Create the SharePoint folder for account and new_customentity entity

string accountURL = string.Format(“{0}_{1}”, accountName, accountId).Replace(“-“, “”).Replace(“/”, “”).Replace(“:”, “”).Replace(“.”, “”).Replace(” “, “”).ToUpper();

using (clientcontext clientcontext = new clientcontext(siteurl))
{
securestring password = new securestring();

foreach (char c in spcrmpassword.tochararray()) password.appendchar(c);

clientcontext.credentials = new networkcredential(spcrmusername, spcrmpassword, spcrmdomain); //new sharepointonlinecredentials(crmusername, password);
var folder = createfolder(clientcontext.web, “organisation”, accounturl);
folderpath = folder.name;
folder = createfolder(clientcontext.web, folderpath, certificateurl);
ctfoldername = folder.name;

}
}
}
}

bool filestatus = UploadUsingRest(“http://siteurl&#8221;, “new_customentity”, documentBytes, filename);

tracing.Trace(“After UploadUsingRest”);

// throw new Exception();

}
catch (InvalidPluginExecutionException exception)
{

tracing.Trace(“Error exception” + exception.message);
throw new InvalidPluginExecutionException(exception.Message);
}

}
}

public static bool UploadUsingRest(string siteurl, string libraryName, byte[] documentBytes, string fileName)
{

// tracing.Trace(“Entered the PrenotePluginstep1”);
bool status = false;
// byte[] binary = System.IO.File.ReadAllBytes(filePath);
// string fileName = System.IO.Path.GetFileName(filePath);
string result = string.Empty;
//Url to upload file
string resourceUrl = siteurl + “/_api/web/GetFolderByServerRelativeUrl(‘” + libraryName + “‘)/Files/add(url='” + fileName + “‘,overwrite=true)”;
HttpWebRequest wreq = HttpWebRequest.Create(resourceUrl) as HttpWebRequest;
wreq.UseDefaultCredentials = false;
//credential who has edit access on document library
NetworkCredential credentials = new System.Net.NetworkCredential(“username”, “passWord”, “domain”);
wreq.Credentials = credentials;

//Get formdigest value from site
string formDigest = GetFormDigestValue(siteurl, credentials);
wreq.Headers.Add(“X-RequestDigest”, formDigest);
wreq.Method = “POST”;
wreq.Timeout = 1000000; //timeout should be large in order to upload file which are of large size
wreq.Accept = “application/json; odata=verbose”;
wreq.ContentLength = documentBytes.Length;
try
{
using (System.IO.Stream requestStream = wreq.GetRequestStream())
{
requestStream.Write(documentBytes, 0, documentBytes.Length);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}

try
{
WebResponse wresp = wreq.GetResponse();
using (System.IO.StreamReader sr = new System.IO.StreamReader(wresp.GetResponseStream()))
{
result = sr.ReadToEnd();
status = true;
return status;
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);

}
}

private static string GetFormDigestValue(string siteurl, NetworkCredential credentials)
{
string newFormDigest = “”;
HttpWebRequest endpointRequest = (HttpWebRequest)HttpWebRequest.Create(siteurl + “/_api/contextinfo”);
endpointRequest.Method = “POST”;
endpointRequest.ContentLength = 0;
endpointRequest.Credentials = credentials;
endpointRequest.Accept = “application/json;odata=verbose”;

try
{
HttpWebResponse endpointResponse = (HttpWebResponse)endpointRequest.GetResponse();
}
catch (Exception ex)
{
throw new Exception(ex.Message);

}

try
{
WebResponse webResp = endpointRequest.GetResponse();
Stream webStream = webResp.GetResponseStream();
StreamReader responseReader = new StreamReader(webStream);
string response = responseReader.ReadToEnd();
var j = JObject.Parse(response);
var jObj = (JObject)JsonConvert.DeserializeObject(response);
foreach (var item in jObj[“d”].Children())
{
newFormDigest = item.First()[“FormDigestValue”].ToString();
}
responseReader.Close();

}
catch (Exception ex)
{

throw new Exception(ex.Message);

}

return newFormDigest;
}

private static Folder CreateFolder(Web web, string listTitle, string fullFolderUrl)
{
if (string.IsNullOrEmpty(fullFolderUrl))
throw new ArgumentNullException(“fullFolderUrl”);

var list = web.Lists.GetByTitle(listTitle);
if (list == null)
{
return null;
}
var curFolder = list.RootFolder.Folders.Add(fullFolderUrl.ToString());
web.Context.Load(curFolder);
web.Context.ExecuteQuery();
return curFolder;
}

public static string CreateSharePointFolder(string sharepointsite, string crmusername, string crmpassword, string mainfolder, string subfolder)
{
string status = string.Empty;

if (string.IsNullOrEmpty(sharepointsite) || string.IsNullOrEmpty(crmusername) || string.IsNullOrEmpty(crmpassword) || string.IsNullOrEmpty(mainfolder) || string.IsNullOrEmpty(subfolder))
return string.Empty;

try
{
using (ClientContext clientContext = new ClientContext(sharepointsite))
{
SecureString passWord = new SecureString();

foreach (char c in crmpassword.ToCharArray()) passWord.AppendChar(c);

clientContext.Credentials = new NetworkCredential(“username”, “password”, “domain”); //new SharePointOnlineCredentials(crmusername, passWord);
var folder = CreateFolder(clientContext.Web, mainfolder, subfolder);
status = folder != null ? folder.Name : string.Empty;
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);

}
return status;

return null;
}

}
}

Hello there,

Below is the console app, add references ‘Microsoft.SharePoint.Client.dll’, ‘Microsoft.SharePoint.Client.Runtime.dll’. Code can be used in CRM 2016 plugin, which I tried and it is working.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading.Tasks;
using System.ServiceModel;
using System.Runtime.Serialization;
using System.Security;
using System.ServiceModel.Dispatcher;
using Microsoft.Xrm.Sdk;
using Microsoft.Crm.Sdk.Messages;
using Microsoft.SharePoint.Client;

namespace SampleSP1
{
class Program
{
static void Main(string[] args)
{

CreateSharePointFolder(“http://siteurl&#8221;, “username”, “password”, “account”, “Fourth Coffee”);

}

public static string CreateSharePointFolder(string sharepointsite, string crmusername, string crmpassword, string mainfolder, string subfolder)
{
string status = string.Empty;

if (string.IsNullOrEmpty(sharepointsite) || string.IsNullOrEmpty(crmusername) || string.IsNullOrEmpty(crmpassword) || string.IsNullOrEmpty(mainfolder) || string.IsNullOrEmpty(subfolder))
return string.Empty;

try
{
using (ClientContext clientContext = new ClientContext(sharepointsite))
{
SecureString passWord = new SecureString();

foreach (char c in crmpassword.ToCharArray()) passWord.AppendChar(c);

clientContext.Credentials = new NetworkCredential( crmusername, crmpassword, domain); //new SharePointOnlineCredentials(crmusername, passWord);
var folder = CreateFolder(clientContext.Web, mainfolder, subfolder);
status = folder != null ? folder.Name : string.Empty;
}
}
catch (Exception ex)
{
return ex.Message;
}
return status;

return null;
}

private static Folder CreateFolder(Web web, string listTitle, string fullFolderUrl)
{
if (string.IsNullOrEmpty(fullFolderUrl))
throw new ArgumentNullException(“fullFolderUrl”);

var list = web.Lists.GetByTitle(listTitle);
if (list == null)
{
return null;
}
var curFolder = list.RootFolder.Folders.Add(fullFolderUrl.ToString());
web.Context.Load(curFolder);
web.Context.ExecuteQuery();
return curFolder;
}
}
}

Cheers

Hi there,

Simple console app, took a while but found article

using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading.Tasks;

namespace SPSample2
{
class Program
{
static void Main(string[] args)
{

string filePath = @”C:\Running Notes.txt”; //File to be uploade
string siteurl = “http://siteurl&#8221;; //site on which file needs to be uploaded (don’t put / at end)
string documentlibrary = “account”; //Document library where file needs to be uploaded
bool filestatus = UploadUsingRest(siteurl, documentlibrary, filePath);
if (filestatus)
{
Console.WriteLine(“File uploaded successfully”);
}
else
{
Console.WriteLine(“Error uploading file”);
}
}

//Method to upload File
public static bool UploadUsingRest(string siteurl, string libraryName, string filePath)
{
bool status = false;
byte[] binary = System.IO.File.ReadAllBytes(filePath);
string fname = System.IO.Path.GetFileName(filePath);
string result = string.Empty;
//Url to upload file
string resourceUrl = siteurl + “/_api/web/GetFolderByServerRelativeUrl(‘” + libraryName + “‘)/Files/add(url='” + fname + “‘,overwrite=true)”;
HttpWebRequest wreq = HttpWebRequest.Create(resourceUrl) as HttpWebRequest;
wreq.UseDefaultCredentials = false;
//credential who has edit access on document library
NetworkCredential credentials = new System.Net.NetworkCredential(“username”, “password”, “domain”);
wreq.Credentials = credentials;

//Get formdigest value from site
string formDigest = GetFormDigestValue(siteurl, credentials);
wreq.Headers.Add(“X-RequestDigest”, formDigest);
wreq.Method = “POST”;
wreq.Timeout = 1000000; //timeout should be large in order to upload file which are of large size
wreq.Accept = “application/json; odata=verbose”;
wreq.ContentLength = binary.Length;
try
{
using (System.IO.Stream requestStream = wreq.GetRequestStream())
{
requestStream.Write(binary, 0, binary.Length);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}

try
{
WebResponse wresp = wreq.GetResponse();
using (System.IO.StreamReader sr = new System.IO.StreamReader(wresp.GetResponseStream()))
{
result = sr.ReadToEnd();
status = true;
return status;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return status;
throw;
}
}

//Method which return form digest value
private static string GetFormDigestValue(string siteurl, NetworkCredential credentials)
{
string newFormDigest = “”;
HttpWebRequest endpointRequest = (HttpWebRequest)HttpWebRequest.Create(siteurl + “/_api/contextinfo”);
endpointRequest.Method = “POST”;
endpointRequest.ContentLength = 0;
endpointRequest.Credentials = credentials;
endpointRequest.Accept = “application/json;odata=verbose”;

try
{
HttpWebResponse endpointResponse = (HttpWebResponse)endpointRequest.GetResponse();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}

try
{

WebResponse webResp = endpointRequest.GetResponse();
Stream webStream = webResp.GetResponseStream();
StreamReader responseReader = new StreamReader(webStream);
string response = responseReader.ReadToEnd();
var j = JObject.Parse(response);
var jObj = (JObject)JsonConvert.DeserializeObject(response);
foreach (var item in jObj[“d”].Children())
{
newFormDigest = item.First()[“FormDigestValue”].ToString();
}
responseReader.Close();

}
catch (Exception ex)
{

Console.WriteLine(ex.Message);
}

return newFormDigest;
}

}
}

Add the Newtonsoft.Json dll from nugget.

Cheers.