SQL 4 CDS 1.0.9 Released!

I’ve just released SQL 4 CDS 1.0.9. Please update it when XrmToolBox prompts you to take advantage of these latest features:

Open Source!

I’m very pleased to be able to release SQL 4 CDS as open source, thanks to the support of Data8. You can now grab all the code on GitHub, submit your bug reports or suggestions as issues or even open a pull request with your own changes!

As well as the XrmToolBox tool, I’ve also separated out the core SQL ⇔ FetchXML conversions into the MarkMpn.Sql4Cds.Engine NuGet package you can include in your own projects. A simple code example to convert from SQL to FetchXML would look like:

public string ConvertSqlToFetchXml(IOrganizationService org, string selectStatement)
{
  var metadataCache = new AttributeMetadataCache(org);
  var converter = new Sql2FetchXml(metadataCache, true);
  var queries = converter.Convert(selectStatement);

  // queries could contain many queries, including SELECT, INSERT, UPDATE or DELETE
  // We'll assume it's just a single SELECT statement for now
  var select = (SelectQuery) queries[0];

  // The FetchXML is returned as an object model, convert it to a string using XmlSerializer
  using (var writer = new StringWriter())
  {
    var serializer = new XmlSerializer(typeof(FetchType));
    serializer.Serialize(select.FetchXml);

    return writer.ToString();
  }
}

XrmToolBox 1.2020.2.36

If you get an error when opening SQL 4 CDS:

An error occured when trying to display this tool: Method not found: 'McTools.Xrm.Connection.ConnectionDetail ConnectionUpdatedEventArgs.get_ConnectionDetail()'.

after installing the latest version of XrmToolBox, please update SQL 4 CDS to 1.0.9 as well to fix it.

Cancel running queries

If you need to stop a query while it’s running, just click the 🟥 button in the toolbar to stop it.

Improved results display

The grid view showing the results of a query is improved with:

• total record count at the bottom
• record numbers on the left
• right-click options to copy data with or without headers
• click on lookup guid values to open the related record or quickly create a SELECT query to get the details of the record
• columns in a SELECT * query are sorted by name to make it easier to find what you’re looking for
• option to show lookup values as the name of the related record instead of the GUID
• option to show times as your local time zone instead of UTC

Improved query support

You will now get the expected results when running queries that combine * and a field name in the SELECT clause, e.g. SELECT name, * FROM account, and when ordering a SELECT * query, e.g. SELECT * FROM account ORDER BY name

Double quotes

By default, SQL treats double quotes as enclosing the name of a table or column, and single quotes for strings, so you can’t write:

SELECT name
FROM   account
WHERE  telephone1 = "01513554555"

as the account entity doesn’t contain a field called 01513554555

You can now switch this behaviour so double quotes and single quotes are both used to enclose strings by unticking the new “Quoted Identifiers” option in the settings window.

Feedback

Please open an issue in the GitHub repository to let me know of any problems you encounter or with any suggestions you might have for improvement.

Please also use the rating feature in XrmToolBox to let me know what you think!