Integrating SQL 4 CDS with your own XrmToolBox tool

One for the XrmToolBox developers: if you want to make use of the power of SQL 4 CDS from your own tool, how can you do it?

Basics

XrmToolBox allows tools to communicate with each other via messages. Your plugin control needs to implement the IMessageBusHost interface . This gives you a method that XrmToolBox will call when another tool sends a message to your tool, and an event that you can raise to send a message to another tool.

Jonas Rapp did a great write-up of how this works for FetchXML Builder which is a good starting point.

Running a query

If your tool generates a query you want the user to be able to run in SQL 4 CDS you can send it across as:

private void sendQueryButton_Click(object sender, EventArgs e)
{
  var fetchXml = @"
    <fetch>
      <entity name='account'>
        <attribute name='name' />
        <filter>
          <condition attribute='ownerid' operator='eq-userid' />
        </filter>
        <order attribute='name' />
      </entity>
    </fetch>";

  OnOutgoingMessage(this, new MessageBusEventArgs("SQL 4 CDS")
  {
    TargetArgument = new Dictionary<string, object>
    {
      ["FetchXml"] = fetchXml,
      ["ConvertOnly"] = false
    }
  });
}

This will open up SQL 4 CDS with the query converted to SQL and ready to run:

If you have some SQL code already that you want to show to the user rather than converting from FetchXML you can do that too:

private void sendQueryButton_Click(object sender, EventArgs e)
{
  var sql = "UPDATE account SET telephone1 = NULL WHERE telephone1 = 'invalid'";

  OnOutgoingMessage(this, new MessageBusEventArgs("SQL 4 CDS")
  {
    TargetArgument = new Dictionary<string, object>
    {
      ["SQL"] = sql,
      ["ConvertOnly"] = false
    }
  });
}

Getting the SQL back to your tool

If you have a FetchXML query but want to show the SQL version you can do that too. This method involves sending a message to SQL 4 CDS and then receiving a message back with the converted results.

This is a bit awkward as XrmToolBox doesn’t support the idea of a result to a message, so you have to do a bit of work to tie the reply back to the message you sent:

private Dictionary<string, object> _message;

private void convertQueryButton_Click(object sender, EventArgs e)
{
  var fetchXml = @"
    <fetch>
      <entity name='account'>
        <attribute name='name' />
        <filter>
          <condition attribute='ownerid' operator='eq-userid' />
        </filter>
        <order attribute='name' />
      </entity>
    </fetch>";

  _message = new Dictionary<string, object>
  {
    ["FetchXml"] = fetchXml,
    ["ConvertOnly"] = true
  };

  OnOutgoingMessage(this, new MessageBuxEventArgs("SQL 4 CDS")
  {
    TargetArgument = _message;
  });
}

public void OnIncomingMessage(MessageBusEventArgs message)
{
  if (message.SourcePlugin == "SQL 4 CDS")
    sqlTextBox.Text = (string) _message["Sql"];
}

When SQL 4 CDS receives the message with the ConvertOnly key set to true, it will convert the query in the FetchXml entry and store the result in the Sql entry. It will then send a message back to your plugin to let you know the results are ready and to force your plugin back to the front.

This is the approach FetchXML Builder uses to show the SQL version of your query when you enable the SQL 4 CDS integration.

Happy integrating!