Get Sql ConnectionString from CRM server code

Sometime, your business logic need access direct SQL Server, and you want to know the ConnectionString of CRM Database.

This code will help you get Sql ConnectionString from CRM Plugin/Custom Action/Custom Workflow (before that I saved the ConnectionString to the custom Options entity)

public void Execute(IServiceProvider serviceProvider)
{
    var context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
    var platformContext = context.GetType().InvokeMember("PlatformContext", BindingFlags.GetProperty, null, context, null);
    var transaction = (SqlTransaction)platformContext.GetType().InvokeMember("SqlTransaction", BindingFlags.GetProperty, null, platformContext, null);
    throw new InvalidPluginExecutionException(transaction.Connection.ConnectionString);
}
  • Remember to add reference System.Data.dll to your project

Limitation

  • CRM On-Premises only
  • Plugin/Custom Action/Custom Workflow when register the option Isolation Mode should selected None
  • Access SQL code should out side the Plugin/Custom Action/Custom Workflow pipe line transaction. (E.g: use at Pre-Validation)

Actual project experience

  • Requirement: End user want change exchange rate at the end of month after a lot of records created/locked on this month with current temporary exchange rate.
  • Analysis: You cannot use the SDK code because a lot of records locked by CRM
  • Solution:
    • Create a custom action, get SQL ConnectionString, and then use the ConnectionString to update records
    • Create a custom ribbon button, then call custom action to update data.