public void ParameterConversion()
{
var context = new XrmFakedContext();
context.InitializeMetadata(Assembly.GetExecutingAssembly());
var org = context.GetOrganizationService();
var metadata = new AttributeMetadataCache(org);
var fetch = @"
<fetch>
<entity name='contact'>
<attribute name='firstname' />
<attribute name='lastname' />
<filter>
<condition attribute='firstname' operator='eq' value='Mark' />
</filter>
</entity>
</fetch>";
var converted = FetchXml2Sql.Convert(org, metadata, fetch, new FetchXml2SqlOptions {
UseParametersForLiterals = true
}, out var parameters);
Assert.AreEqual("SELECT firstname, lastname FROM contact WHERE firstname = @firstname", NormalizeWhitespace(converted));
Assert.AreEqual("Mark", parameters["@firstname"]);
}
public void AndOr()
{
var context = new XrmFakedContext();
context.InitializeMetadata(Assembly.GetExecutingAssembly());
var org = context.GetOrganizationService();
var metadata = new AttributeMetadataCache(org);
var fetch = @"
<fetch>
<entity name='contact'>
<attribute name='firstname' />
<attribute name='lastname' />
<filter>
<condition attribute='firstname' operator='eq' value='Mark' />
<filter type='or'>
<condition attribute='lastname' operator='eq' value='Carrington' />
<condition attribute='lastname' operator='eq' value='Twain' />
</filter>
</filter>
</entity>
</fetch>";
var converted = FetchXml2Sql.Convert(org, metadata, fetch, new FetchXml2SqlOptions(), out _);
Assert.AreEqual("SELECT firstname, lastname FROM contact WHERE firstname = 'Mark' AND (lastname = 'Carrington' OR lastname = 'Twain')", NormalizeWhitespace(converted));
}
public void NextXYearsConversion()
{
var context = new XrmFakedContext();
context.InitializeMetadata(Assembly.GetExecutingAssembly());
var org = context.GetOrganizationService();
var metadata = new AttributeMetadataCache(org);
var fetch = @"
<fetch>
<entity name='contact'>
<attribute name='firstname' />
<attribute name='lastname' />
<filter>
<condition attribute='createdon' operator='next-x-years' value='2' />
</filter>
</entity>
</fetch>";
var converted = FetchXml2Sql.Convert(org, metadata, fetch, new FetchXml2SqlOptions {
PreserveFetchXmlOperatorsAsFunctions = false
}, out _);
Assert.AreEqual($"SELECT firstname, lastname FROM contact WHERE createdon >= '{DateTime.Now:s}' AND createdon < '{DateTime.Today.AddDays(1).AddYears(2):s}'", NormalizeWhitespace(converted));
}
public void EqBusinessId()
{
var context = new XrmFakedContext();
context.InitializeMetadata(Assembly.GetExecutingAssembly());
context.AddFakeMessageExecutor <WhoAmIRequest>(new WhoAmIHandler());
var org = context.GetOrganizationService();
var metadata = new AttributeMetadataCache(org);
var fetch = @"
<fetch>
<entity name='contact'>
<attribute name='firstname' />
<attribute name='lastname' />
<filter>
<condition attribute='parentcustomerid' operator='eq-businessid' />
</filter>
</entity>
</fetch>";
var converted = FetchXml2Sql.Convert(org, metadata, fetch, new FetchXml2SqlOptions {
PreserveFetchXmlOperatorsAsFunctions = false
}, out _);
Assert.AreEqual($"SELECT firstname, lastname FROM contact WHERE parentcustomerid = '{WhoAmIHandler.BusinessUnitId:D}'", NormalizeWhitespace(converted));
}
public void JoinFilter()
{
var context = new XrmFakedContext();
context.InitializeMetadata(Assembly.GetExecutingAssembly());
var org = context.GetOrganizationService();
var metadata = new AttributeMetadataCache(org);
var fetch = @"
<fetch>
<entity name='contact'>
<attribute name='firstname' />
<attribute name='lastname' />
<link-entity name='account' from='accountid' to='parentcustomerid'>
<attribute name='name' />
<filter>
<condition attribute='name' operator='eq' value='data8' />
</filter>
</link-entity>
<filter>
<condition attribute='firstname' operator='eq' value='Mark' />
</filter>
</entity>
</fetch>";
var converted = FetchXml2Sql.Convert(org, metadata, fetch, new FetchXml2SqlOptions(), out _);
Assert.AreEqual("SELECT contact.firstname, contact.lastname, account.name FROM contact INNER JOIN account ON contact.parentcustomerid = account.accountid AND account.name = 'data8' WHERE contact.firstname = 'Mark'", NormalizeWhitespace(converted));
}
public void Distinct()
{
var metadata = new AttributeMetadataCache(_service);
var fetch = @"
<fetch distinct='true'>
<entity name='contact'>
<attribute name='firstname' />
<attribute name='lastname' />
</entity>
</fetch>";
var converted = FetchXml2Sql.Convert(_service, metadata, fetch, new FetchXml2SqlOptions(), out _);
Assert.AreEqual("SELECT DISTINCT firstname, lastname FROM contact", NormalizeWhitespace(converted));
}
public void Order()
{
var metadata = new AttributeMetadataCache(_service);
var fetch = @"
<fetch>
<entity name='contact'>
<attribute name='firstname' />
<attribute name='lastname' />
<order attribute='firstname' />
</entity>
</fetch>";
var converted = FetchXml2Sql.Convert(_service, metadata, fetch, new FetchXml2SqlOptions(), out _);
Assert.AreEqual("SELECT firstname, lastname FROM contact ORDER BY firstname ASC", NormalizeWhitespace(converted));
}
public void OnIncomingMessage(MessageBusEventArgs message)
{
_ai.TrackEvent("Incoming message", new Dictionary <string, string> {
["SourcePlugin"] = message.SourcePlugin
});
var param = message.TargetArgument as IDictionary <string, object>;
if (param == null)
{
var xml = message.TargetArgument as string;
param = new Dictionary <string, object>();
param["FetchXml"] = xml;
param["ConvertOnly"] = false;
}
if (_objectExplorer.SelectedConnection == null)
{
return;
}
var con = _objectExplorer.SelectedConnection;
var metadata = _metadata[con];
var fetch = DeserializeFetchXml((string)param["FetchXml"]);
var options = new FetchXml2SqlOptions();
if ((bool)param["ConvertOnly"])
{
options.PreserveFetchXmlOperatorsAsFunctions = false;
}
var sql = FetchXml2Sql.Convert(con.ServiceClient, metadata, fetch, options, out _);
if ((bool)param["ConvertOnly"])
{
param["Sql"] = sql;
OnOutgoingMessage(this, new MessageBusEventArgs(message.SourcePlugin)
{
TargetArgument = null
});
}
else
{
CreateQuery(con, "-- Imported from " + message.SourcePlugin + "\r\n\r\n" + sql, message.SourcePlugin == "FetchXML Builder" ? null : message.SourcePlugin);
}
}
public void Filter()
{
var metadata = new AttributeMetadataCache(_service);
var fetch = @"
<fetch>
<entity name='contact'>
<attribute name='firstname' />
<attribute name='lastname' />
<filter>
<condition attribute='firstname' operator='eq' value='Mark' />
</filter>
</entity>
</fetch>";
var converted = FetchXml2Sql.Convert(_service, metadata, fetch, new FetchXml2SqlOptions(), out _);
Assert.AreEqual("SELECT firstname, lastname FROM contact WHERE firstname = 'Mark'", NormalizeWhitespace(converted));
}
public void Joins()
{
var metadata = new AttributeMetadataCache(_service);
var fetch = @"
<fetch>
<entity name='contact'>
<attribute name='firstname' />
<attribute name='lastname' />
<link-entity name='account' from='accountid' to='parentcustomerid'>
<attribute name='name' />
</link-entity>
</entity>
</fetch>";
var converted = FetchXml2Sql.Convert(_service, metadata, fetch, new FetchXml2SqlOptions(), out _);
Assert.AreEqual("SELECT contact.firstname, contact.lastname, account.name FROM contact INNER JOIN account ON contact.parentcustomerid = account.accountid", NormalizeWhitespace(converted));
}
public void EqUserId()
{
var metadata = new AttributeMetadataCache(_service);
var fetch = @"
<fetch>
<entity name='account'>
<attribute name='name' />
<filter>
<condition attribute='ownerid' operator='eq-userid' />
</filter>
</entity>
</fetch>";
var converted = FetchXml2Sql.Convert(_service, metadata, fetch, new FetchXml2SqlOptions {
ConvertFetchXmlOperatorsTo = FetchXmlOperatorConversion.SqlCalculations
}, out _);
Assert.AreEqual("SELECT name FROM account WHERE ownerid = CURRENT_USER", NormalizeWhitespace(converted));
}
public void EqBusinessId()
{
var metadata = new AttributeMetadataCache(_service);
var fetch = @"
<fetch>
<entity name='contact'>
<attribute name='firstname' />
<attribute name='lastname' />
<filter>
<condition attribute='parentcustomerid' operator='eq-businessid' />
</filter>
</entity>
</fetch>";
var converted = FetchXml2Sql.Convert(_service, metadata, fetch, new FetchXml2SqlOptions {
ConvertFetchXmlOperatorsTo = FetchXmlOperatorConversion.Literals
}, out _);
Assert.AreEqual($"SELECT firstname, lastname FROM contact WHERE parentcustomerid = '{WhoAmIHandler.BusinessUnitId:D}'", NormalizeWhitespace(converted));
}
public void Distinct()
{
var context = new XrmFakedContext();
context.InitializeMetadata(Assembly.GetExecutingAssembly());
var org = context.GetOrganizationService();
var metadata = new AttributeMetadataCache(org);
var fetch = @"
<fetch distinct='true'>
<entity name='contact'>
<attribute name='firstname' />
<attribute name='lastname' />
</entity>
</fetch>";
var converted = FetchXml2Sql.Convert(org, metadata, fetch, new FetchXml2SqlOptions(), out _);
Assert.AreEqual("SELECT DISTINCT firstname, lastname FROM contact", NormalizeWhitespace(converted));
}
public void NextXYearsConversion()
{
var metadata = new AttributeMetadataCache(_service);
var fetch = @"
<fetch>
<entity name='contact'>
<attribute name='firstname' />
<attribute name='lastname' />
<filter>
<condition attribute='createdon' operator='next-x-years' value='2' />
</filter>
</entity>
</fetch>";
var converted = FetchXml2Sql.Convert(_service, metadata, fetch, new FetchXml2SqlOptions {
ConvertFetchXmlOperatorsTo = FetchXmlOperatorConversion.Literals
}, out _);
Assert.AreEqual($"SELECT firstname, lastname FROM contact WHERE createdon >= '{DateTime.Now:s}' AND createdon < '{DateTime.Today.AddDays(1).AddYears(2):s}'", NormalizeWhitespace(converted));
}
public void ParameterConversion()
{
var metadata = new AttributeMetadataCache(_service);
var fetch = @"
<fetch>
<entity name='contact'>
<attribute name='firstname' />
<attribute name='lastname' />
<filter>
<condition attribute='firstname' operator='eq' value='Mark' />
</filter>
</entity>
</fetch>";
var converted = FetchXml2Sql.Convert(_service, metadata, fetch, new FetchXml2SqlOptions {
UseParametersForLiterals = true
}, out var parameters);
Assert.AreEqual("SELECT firstname, lastname FROM contact WHERE firstname = @firstname", NormalizeWhitespace(converted));
Assert.AreEqual("Mark", parameters["@firstname"]);
}
public void Disconnected()
{
var fetch = @"
<fetch>
<entity name='contact'>
<attribute name='firstname' />
<attribute name='lastname' />
<filter>
<condition attribute='firstname' operator='eq' value='Mark' />
<filter type='or'>
<condition attribute='lastname' operator='eq' value='Carrington' />
<condition attribute='lastname' operator='eq' value='Twain' />
</filter>
</filter>
</entity>
</fetch>";
var converted = FetchXml2Sql.Convert(null, null, fetch, new FetchXml2SqlOptions(), out _);
Assert.AreEqual("SELECT firstname, lastname FROM contact WHERE firstname = 'Mark' AND (lastname = 'Carrington' OR lastname = 'Twain')", NormalizeWhitespace(converted));
}
public void JoinFilterOr()
{
var metadata = new AttributeMetadataCache(_service);
var fetch = @"
<fetch>
<entity name='contact'>
<attribute name='firstname' />
<attribute name='lastname' />
<link-entity name='account' from='accountid' to='parentcustomerid'>
<filter type='or'>
<condition attribute='name' operator='eq' value='Data8' />
<condition attribute='name' operator='eq' value='Microsoft' />
</filter>
</link-entity>
</entity>
</fetch>";
var converted = FetchXml2Sql.Convert(_service, metadata, fetch, new FetchXml2SqlOptions(), out _);
Assert.AreEqual("SELECT contact.firstname, contact.lastname FROM contact INNER JOIN account ON contact.parentcustomerid = account.accountid AND (account.name = 'Data8' OR account.name = 'Microsoft')", NormalizeWhitespace(converted));
}
public void CustomOperator()
{
var context = new XrmFakedContext();
context.InitializeMetadata(Assembly.GetExecutingAssembly());
var org = context.GetOrganizationService();
var metadata = new AttributeMetadataCache(org);
var fetch = @"
<fetch>
<entity name='contact'>
<attribute name='firstname' />
<attribute name='lastname' />
<filter>
<condition attribute='createdon' operator='last-x-days' value='2' />
</filter>
</entity>
</fetch>";
var converted = FetchXml2Sql.Convert(org, metadata, fetch, new FetchXml2SqlOptions(), out _);
Assert.AreEqual("SELECT firstname, lastname FROM contact WHERE createdon = lastxdays(2)", NormalizeWhitespace(converted));
}
public void OnIncomingMessage(MessageBusEventArgs message)
{
_ai.TrackEvent("Incoming message", new Dictionary <string, string> {
["SourcePlugin"] = message.SourcePlugin, ["Source"] = "XrmToolBox"
});
var param = message.TargetArgument as IDictionary <string, object>;
if (param == null)
{
var str = message.TargetArgument as string;
param = new Dictionary <string, object>();
if (str.StartsWith("<"))
{
param["FetchXml"] = str;
}
else
{
param["SQL"] = str;
}
param["ConvertOnly"] = false;
}
if (_objectExplorer.SelectedConnection == null)
{
return;
}
var con = _objectExplorer.SelectedConnection;
var metadata = _dataSources[con.ConnectionName].Metadata;
if (param.TryGetValue("FetchXml", out var xml) && xml is string xmlStr && !String.IsNullOrEmpty(xmlStr))
{
var fetch = DeserializeFetchXml(xmlStr);
var options = new FetchXml2SqlOptions();
if ((bool)param["ConvertOnly"])
{
options.ConvertFetchXmlOperatorsTo = FetchXmlOperatorConversion.SqlCalculations;
}
_ai.TrackEvent("Convert", new Dictionary <string, string> {
["QueryType"] = "FetchXML", ["Source"] = "XrmToolBox"
});
string sql;
if (Settings.Instance.UseNativeSqlConversion)
{
try
{
var convertReq = new OrganizationRequest("FetchXMLToSQL")
{
["FetchXml"] = xmlStr,
["SubqueryCompatible"] = true
};
var convertResp = con.ServiceClient.Execute(convertReq);
sql = (string)convertResp["Response"];
}
catch
{
sql = FetchXml2Sql.Convert(con.ServiceClient, metadata, fetch, options, out _);
}
}
else
{
sql = FetchXml2Sql.Convert(con.ServiceClient, metadata, fetch, options, out _);
}
if ((bool)param["ConvertOnly"])
{
param["Sql"] = sql;
OnOutgoingMessage(this, new MessageBusEventArgs(message.SourcePlugin)
{
TargetArgument = null
});
}
else
{
CreateQuery(con, "-- Imported from " + message.SourcePlugin + "\r\n\r\n" + sql);
}
}
/// <summary>
/// This function is the callback used to execute the command when the menu item is clicked.
/// See the constructor to see how the menu item is associated with this function using
/// OleMenuCommandService service and MenuCommand class.
/// </summary>
/// <param name="sender">Event sender.</param>
/// <param name="e">Event args.</param>
private void Execute(object sender, EventArgs e)
{
ThreadHelper.ThrowIfNotOnUIThread();
try
{
_objectExplorer.GetSelectedNodes(out var size, out var nodes);
var conStr = new SqlConnectionStringBuilder(nodes[0].Connection.ConnectionString);
var start = ActiveDocument.StartPoint.CreateEditPoint();
var fetch = start.GetText(ActiveDocument.EndPoint);
_ai.TrackEvent("Convert", new Dictionary <string, string> {
["QueryType"] = "FetchXML", ["Source"] = "SSMS"
});
string sql;
IDictionary <string, object> paramValues;
if (Package.Settings.UseNativeSqlConversion)
{
var convertReq = new OrganizationRequest("FetchXMLToSQL")
{
["FetchXml"] = fetch,
["SubqueryCompatible"] = true
};
var convertResp = ConnectCDS(conStr).Execute(convertReq);
sql = (string)convertResp["Response"];
paramValues = new Dictionary <string, object>();
}
else
{
sql = FetchXml2Sql.Convert(ConnectCDS(conStr), GetMetadataCache(conStr), fetch, new FetchXml2SqlOptions
{
ConvertFetchXmlOperatorsTo = FetchXmlOperatorConversion.SqlCalculations,
UseParametersForLiterals = true,
ConvertDateTimeToUtc = true
}, out paramValues);
}
ServiceCache.ScriptFactory.CreateNewBlankScript(ScriptType.Sql, ServiceCache.ScriptFactory.CurrentlyActiveWndConnectionInfo.UIConnectionInfo, null);
var editPoint = ActiveDocument.EndPoint.CreateEditPoint();
editPoint.Insert("/*\r\nCreated from query:\r\n\r\n");
editPoint.Insert(fetch);
editPoint.Insert("\r\n\r\n*/\r\n\r\n");
foreach (var param in paramValues)
{
string paramType;
var quoteValues = false;
switch (param.Value.GetType().Name)
{
case "Int32":
paramType = "int";
break;
case "Decimal":
paramType = "numeric";
break;
case "DateTime":
paramType = "datetime";
quoteValues = true;
break;
default:
paramType = "nvarchar(max)";
quoteValues = true;
break;
}
editPoint.Insert($"DECLARE {param.Key} {paramType} = ");
var value = param.Value.ToString();
if (param.Value is DateTime dt)
{
value = dt.ToString("s");
}
if (quoteValues)
{
value = "'" + value.Replace("'", "''") + "'";
}
editPoint.Insert(value + "\r\n");
}
if (paramValues.Count > 0)
{
editPoint.Insert("\r\n");
}
editPoint.Insert(sql);
}
catch (Exception ex)
{
VsShellUtilities.LogError("SQL 4 CDS", ex.ToString());
}
}
/// <summary>
/// Handles the entity retrieve multiple message.
/// </summary>
/// <param name="context">The context.</param>
public override void HandleRetrieveMultipleMessage(PluginExecutionContext context)
{
base.HandleRetrieveMultipleMessage(context);
var query = context.PluginContext.InputParameters["Query"];
if (query != null)
{
var mapper = new GenericMapper(context);
EntityCollection collection = new EntityCollection();
string fetchXml = string.Empty;
if (query is QueryExpression qe)
{
var convertRequest = new QueryExpressionToFetchXmlRequest();
convertRequest.Query = (QueryExpression)qe;
var response = (QueryExpressionToFetchXmlResponse)context.Service.Execute(convertRequest);
fetchXml = response.FetchXml;
}
else if (query is FetchExpression fe)
{
fetchXml = fe.Query;
}
if (!string.IsNullOrEmpty(fetchXml))
{
context.Trace($"Pre FetchXML: {fetchXml}");
var metadata = new AttributeMetadataCache(context.Service);
var fetch = Deserialize(fetchXml);
mapper.MapFetchXml(fetch);
//Store page info before converting
int page = -1;
int count = -1;
if (!string.IsNullOrEmpty(fetch.page))
{
page = Int32.Parse(fetch.page);
fetch.page = string.Empty;
}
if (!string.IsNullOrEmpty(fetch.count))
{
count = Int32.Parse(fetch.count);
fetch.count = string.Empty;
}
var sql = FetchXml2Sql.Convert(context.Service, metadata, fetch, new FetchXml2SqlOptions {
PreserveFetchXmlOperatorsAsFunctions = false
}, out _);
sql = mapper.MapVirtualEntityAttributes(sql);
context.Trace($"SQL: {sql}");
if (page != -1 && count != -1)
{
collection = this.GetEntitiesFromSql(context, mapper, sql, count, page);
}
else
{
collection = this.GetEntitiesFromSql(context, mapper, sql, -1, 1);
}
}
context.Trace($"Records Returned: {collection.Entities.Count}");
context.PluginContext.OutputParameters["BusinessEntityCollection"] = collection;
}
}
