Is Sql4CDS availble to integrated into our own c# plugin? Possible method for parallel execution in plugin?

[emibanana]

Hi I have come across this amazing tool / library and I am shocked! This tool is so useful and i can't wait to integrated into my own plugin to use to quary my dataverse environment!

But some technical details aren't clear and I am wondering if I may ask here:

1, can this be integrated into our own c# plugin for dataverse? I am image i just need to include NuGet package into my solution and I can reference this library's method using keyword new Sql4CdsConnection(connectionString) directly in my plugin, and once i register the plugin it will work as intended right? In terms of details code i believe it can be work out by intellisense...which isn't hard.

2, the hard part is, as i need to write a plugin in post operation synchronous mode, I wonder if this will be conflict with sql4CDS's parallel feature? Below is the implementation i would like to do:

• I have a Purchase Invoice entity that is looking up to Project entity
• I need to write a plugin to sum Purchase Invoice's amount field to some Project's fields (total cost to date, cost this period, etc)
• I have wrote a plugin to be triggered on UPDATE/CREATE/DELETE of Purchase Invoice's amount field
• my plugin will examinate the event for validation against some buisness logic, once everything is ok to go, I want to call Sql4Cds plugin class, passing some guid information so that i can be used in SQL quary
• NOW, since my logic need to run a few different query with different conditions, and write all the results into corresponding Project fields (because cost to date and cost this period has different query condition, i have no choice but to put them into different query)
• BECAUSE of this reason, I sort of want to let these query to run in parallel,
• SO that i can speed up plugin execution and release freezing time to the user's client ASAP.
• AND I do not want to make this plugin to be asynchronous, as Dataverse's Async server is SOOOOOO slow in responding, it's always take at least 30s+ to start executing!

So the key question is, how can i configure BatchSize and MaxDegreeOfParallelism properties to let Sql4CDS to run each quary in parallel? so to improve plugin run time? As far as I know parallelism isn't allowed inside the context of one plugin? My plugin won't need to process anything after the SQL4CDS plugin, once the SQL4CDS plugin start running, I would like it to execute in parallel, and once complete, the whole plugin process is ready to be ended and exit. Below is a short implementation i would like to do:

• Purchase invoice entity is updated
• MyPlugin is triggered, evaluating if the data is valid
• data is valid, now constructing my own SQL quary and calling Sql4CdsConnection(quaryString1) and Sql4CdsConnection(quaryString2) in parallel (some how, not sure how this can be achieve)
• Sql4Cds will do SELECT 'PurchaseInvoice'...FROM...WHERE...UPDATE 'Project'
• All done and terminated

The user do not need to see the result immediately, instead, release of the user's client interface ASAP is crucial, and then the user can manually refresh the page to see the result.

So, I would like to know how to implement this? any thoughts and how to make parallel call inside a plugin's context?

Thank you Mark! Amazing tool it is!

[emibanana]

I just found that there is a wiki about this plugin...let me sink in...

[MarkMpn]

You should use the Sql4CdsConnection constructor that accepts an IOrganizationService parameter and pass it the instance you get from the IOrganizationServiceFactory from the plugin context:

public void Execute(IServiceProvider serviceProvider)
{
  var orgServiceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
  var orgSvc = orgServiceFactory.CreateOrganizationService(Guid.Empty);

  using (var con = new Sql4CdsConnection(orgSvc))
  using (var cmd = con.CreateCommand())
  {
    cmd.CommandText ="...";
  }
}

As you say, parallel use of the organisation service is not permitted in a plugin and SQL 4 CDS will execute all commands without any parallelism in this case.

[emibanana]

Thank you! i have tried my first SQL plugin for dataverse:

using System;
using MarkMpn.Sql4Cds.Engine;

namespace PMPlugin
{
    //common libaray for child sum proces
    //General input: roll up target's guid, for example: timesheet to sales invoice, then sales invoice's guid
    //General output: void
    //General naming: <Childname>_<generic paramaters category for roll up>_<Parentname>, for example: 
    public class ChildProcessLibrary
    {

        public static void Timesheet_SalesInvoice(ILocalPluginContext LocalPluginContext,Guid projectGuid, Guid salesInvoiceGuid, int progressClaimNumber)
        {
            LocalPluginContext.TracingService.Trace($"[RestorialPMPlugin] Execute step: Entering Child process Timesheet_SalesInvoice, and start SQL operation.");

            var orgServiceFactory = (IOrganizationServiceFactory)LocalPluginContext.ServiceProvider.GetService(typeof(IOrganizationServiceFactory));
            var orgSvc = orgServiceFactory.CreateOrganizationService(Guid.Empty);

            using (var con = new Sql4CdsConnection(orgSvc))
            using (var cmd = con.CreateCommand())
            try
            {
                cmd.CommandText =   $@"UPDATE
                                        new_salesinvoice
                                    SET 
                                        new_managementgrossclaimed = (
                                            SELECT 
                                                SUM(new_subtotal) + SUM(new_margin)
                                            FROM 
                                                new_timesheet
                                            WHERE 
                                                new_project = '{projectGuid}' AND
                                                new_linktosalesinvoice = '{salesInvoiceGuid}' AND
                                                statecode = 0
                                        ),
                                        new_managementthisclaim = (
                                            SELECT 
                                                SUM(new_subtotal) + SUM(new_margin)
                                            FROM 
                                                new_timesheet
                                            INNER JOIN
                                                new_salesinvoice
                                            ON
                                                new_timesheet.new_linktosalesinvoice = new_salesinvoice.new_salesinvoiceid
                                            WHERE 
                                                new_timesheet.new_project = '{projectGuid}' AND
                                                new_salesinvoice.new_progressclaimno <= {progressClaimNumber} AND
                                                new_timesheet.statecode = 0
                                        )
                                    WHERE
                                        new_salesinvoiceid = '{salesInvoiceGuid}';";

                // Add the new account
                cmd.ExecuteNonQuery();

                LocalPluginContext.TracingService.Trace("Completed SQL quary"); //debug
            }
            catch (Exception exception) 
            {
                throw new InvalidPluginExecutionException($"Exception in SQL operation: {exception.Message}");
            }
        }
    }
}

and the above code will be executed in below parent class:

using Microsoft.Xrm.Sdk.Query;
using System;

namespace RestorialPMPlugin
{
    //plugin to rollup timesheet to sales invoice
    //Rollup logic target: salesinvoice's new_managementthisclaim, new_managementgrossclaimed
    //this plugin run on post operation async only!
    public class Rollup_Timesheet_SalesInvoice : PluginBase
    {
        public Rollup_Timesheet_SalesInvoice(string unsecureConfiguration, string secureConfiguration): base(typeof(Rollup_Timesheet_SalesInvoice)){}

        protected override void ExecuteDataversePlugin(ILocalPluginContext localPluginContext)
        {
            localPluginContext.TracingService.Trace("Plugin Execution Started."); //debug

            if (localPluginContext == null){throw new ArgumentNullException(nameof(localPluginContext));}

            var context = localPluginContext.PluginExecutionContext;
            var service = localPluginContext.PluginUserService;

            if (context.InputParameters.Contains("Target") && context.InputParameters["Target"] is Entity triggeredEntityCollection)
            {
                if (triggeredEntityCollection.LogicalName != "new_timesheet"){
                    return;
                }

                //main process input validation logic here

                ChildProcessLibrary.Timesheet_SalesInvoice(localPluginContext, operationProjectGUID, operationSalesInvoiceGUID, progressClaimNumber);

            }

        }

    }
}

I tried run it and it says below error:

Exception in SQL operation: Method not found: 'System.Nullable`1<Boolean> Microsoft.Xrm.Sdk.Metadata.EntityMetadata.get_IsRetentionEnabled()'.

i realise when i build my plugin, there suddenly have many many .dll appear in the publish folder, I am not sure what's going on, do I need to register them all to the dataverse? that's a lot to register...It used to just have 1 dll that is my main execution process dll

[MarkMpn]

I see you've opened an issue on this as well, so I'll close this and answer on #379