/// <summary>
/// Gets the query text that would be executed based on the current selection
/// </summary>
/// <returns></returns>
protected string GetQuery()
{
var scriptFactory = new ScriptFactoryWrapper(ServiceCache.ScriptFactory);
var sqlScriptEditorControl = scriptFactory.GetCurrentlyActiveFrameDocView(ServiceCache.VSMonitorSelection, false, out _);
var textSpan = sqlScriptEditorControl.GetSelectedTextSpan();
return(textSpan.Text);
}
/// <summary>
/// Gets the details of the currently active connection
/// </summary>
/// <returns></returns>
protected SqlConnectionStringBuilder GetConnectionInfo(bool log)
{
var scriptFactory = new ScriptFactoryWrapper(ServiceCache.ScriptFactory);
var sqlScriptEditorControl = scriptFactory.GetCurrentlyActiveFrameDocView(ServiceCache.VSMonitorSelection, false, out _);
if (sqlScriptEditorControl?.ConnectionString == null)
{
if (log)
{
VsShellUtilities.LogMessage("SQL 4 CDS", "No currently active window or connection", Microsoft.VisualStudio.Shell.Interop.__ACTIVITYLOG_ENTRYTYPE.ALE_INFORMATION);
}
return(null);
}
return(new SqlConnectionStringBuilder(sqlScriptEditorControl.ConnectionString));
}
/// <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
{
var scriptFactory = new ScriptFactoryWrapper(ServiceCache.ScriptFactory);
var sqlScriptEditorControl = scriptFactory.GetCurrentlyActiveFrameDocView(ServiceCache.VSMonitorSelection, false, out _);
var constr = GetConnectionInfo(true);
var server = constr.DataSource.Split(',')[0];
_ai.TrackEvent("Convert", new Dictionary <string, string> {
["QueryType"] = "M", ["Source"] = "SSMS"
});
var sql = GetQuery();
var m = $@"/*
Query converted to M format by SQL 4 CDS
To use in Power BI:
1. Click New Source
2. Click Blank Query
3. Click Advanced Editor
4. Copy & paste in this query
*/
let
Source = CommonDataService.Database(""{server}""),
DataverseSQL = Value.NativeQuery(Source, ""{sql.Replace("\"", "\"\"").Replace("\r\n", " ").Trim()}"", null, [EnableFolding=true])
in
DataverseSQL";
var window = Dte.ItemOperations.NewFile("General\\Text File");
var editPoint = ActiveDocument.EndPoint.CreateEditPoint();
editPoint.Insert(m);
}
catch (Exception ex)
{
VsShellUtilities.LogError("SQL 4 CDS", ex.ToString());
}
}
private void OnExecuteQuery(string Guid, int ID, object CustomIn, object CustomOut, ref bool CancelDefault)
{
ThreadHelper.ThrowIfNotOnUIThread();
try
{
if (ActiveDocument == null)
{
return;
}
if (!IsDataverse())
{
return;
}
// We are running a query against the Dataverse TDS endpoint, so check if there are any DML statements in the query
// Get the SQL editor object
var scriptFactory = new ScriptFactoryWrapper(ServiceCache.ScriptFactory);
var sqlScriptEditorControl = scriptFactory.GetCurrentlyActiveFrameDocView(ServiceCache.VSMonitorSelection, false, out _);
var textSpan = sqlScriptEditorControl.GetSelectedTextSpan();
var sql = textSpan.Text;
// Quick check first so we don't spend a long time connecting to CDS just to find there's a simple SELECT query
if (sql.IndexOf("INSERT", StringComparison.OrdinalIgnoreCase) == -1 &&
sql.IndexOf("UPDATE", StringComparison.OrdinalIgnoreCase) == -1 &&
sql.IndexOf("DELETE", StringComparison.OrdinalIgnoreCase) == -1)
{
return;
}
// Allow user to bypass SQL 4 CDS logic in case of problematic queries
if (sql.IndexOf("Bypass SQL 4 CDS", StringComparison.OrdinalIgnoreCase) != -1 ||
sql.IndexOf("Bypass SQL4CDS", StringComparison.OrdinalIgnoreCase) != -1)
{
return;
}
// Store the options being used for these queries so we can cancel them later
var options = new QueryExecutionOptions(sqlScriptEditorControl, Package.Settings);
var metadata = GetMetadataCache();
var org = ConnectCDS();
var dataSource = new DataSource {
Name = "local", Metadata = metadata, TableSizeCache = new TableSizeCache(org, metadata), Connection = org
};
// We've possibly got a DML statement, so parse the query properly to get the details
var converter = new ExecutionPlanBuilder(new[] { dataSource }, options)
{
TDSEndpointAvailable = true,
QuotedIdentifiers = sqlScriptEditorControl.QuotedIdentifiers
};
IRootExecutionPlanNode[] queries;
try
{
queries = converter.Build(sql);
}
catch (Exception ex)
{
CancelDefault = true;
ShowError(sqlScriptEditorControl, textSpan, ex);
return;
}
var dmlQueries = queries.OfType <IDmlQueryExecutionPlanNode>().ToArray();
var hasSelect = queries.Length > dmlQueries.Length;
var hasDml = dmlQueries.Length > 0;
if (hasSelect && hasDml)
{
// Can't mix SELECT and DML queries as we can't show results in the grid and SSMS can't execute the DML queries
CancelDefault = true;
ShowError(sqlScriptEditorControl, textSpan, new ApplicationException("Cannot mix SELECT queries with DML queries. Execute SELECT statements in a separate batch to INSERT/UPDATE/DELETE"));
return;
}
if (hasSelect)
{
return;
}
// We need to execute the DML statements directly
CancelDefault = true;
// Show the queries starting to run
sqlScriptEditorControl.StandardPrepareBeforeExecute();
sqlScriptEditorControl.OnExecutionStarted(sqlScriptEditorControl, EventArgs.Empty);
sqlScriptEditorControl.ToggleResultsControl(true);
sqlScriptEditorControl.Results.StartExecution();
_options[ActiveDocument] = options;
var doc = ActiveDocument;
// Run the queries in a background thread
var task = new System.Threading.Tasks.Task(async() =>
{
var resultFlag = 0;
foreach (var query in dmlQueries)
{
if (options.Cancelled)
{
break;
}
try
{
_ai.TrackEvent("Execute", new Dictionary <string, string> {
["QueryType"] = query.GetType().Name, ["Source"] = "SSMS"
});
var msg = query.Execute(new Dictionary <string, DataSource>(StringComparer.OrdinalIgnoreCase)
{
[dataSource.Name] = dataSource
}, options, null, null);
sqlScriptEditorControl.Results.AddStringToMessages(msg + "\r\n\r\n");
resultFlag |= 1; // Success
}
catch (Exception ex)
{
var error = ex;
if (ex is PartialSuccessException partial)
{
error = partial.InnerException;
if (partial.Result is string msg)
{
sqlScriptEditorControl.Results.AddStringToMessages(msg + "\r\n\r\n");
resultFlag |= 1; // Success
}
}
_ai.TrackException(error, new Dictionary <string, string> {
["Sql"] = sql, ["Source"] = "SSMS"
});
AddException(sqlScriptEditorControl, textSpan, error);
resultFlag |= 2; // Failure
}
}
if (options.Cancelled)
{
resultFlag = 4; // Cancel
}
await Package.JoinableTaskFactory.SwitchToMainThreadAsync();
sqlScriptEditorControl.Results.OnSqlExecutionCompletedInt(resultFlag);
_options.Remove(doc);
});
options.Task = task;
task.Start();
}
catch (Exception ex)
{
VsShellUtilities.LogError("SQL 4 CDS", ex.ToString());
}
}
/// <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
{
var sql = GetQuery();
var scriptFactory = new ScriptFactoryWrapper(ServiceCache.ScriptFactory);
var sqlScriptEditorControl = scriptFactory.GetCurrentlyActiveFrameDocView(ServiceCache.VSMonitorSelection, false, out _);
var options = new QueryExecutionOptions(sqlScriptEditorControl, Package.Settings);
var metadata = GetMetadataCache();
var org = ConnectCDS();
var converter = new ExecutionPlanBuilder(metadata, new TableSizeCache(org, metadata), options)
{
TDSEndpointAvailable = false,
QuotedIdentifiers = sqlScriptEditorControl.QuotedIdentifiers
};
try
{
var queries = converter.Build(sql);
foreach (var query in queries)
{
_ai.TrackEvent("Convert", new Dictionary <string, string> {
["QueryType"] = query.GetType().Name, ["Source"] = "SSMS"
});
var window = Dte.ItemOperations.NewFile("General\\XML File");
var editPoint = ActiveDocument.EndPoint.CreateEditPoint();
editPoint.Insert("<!--\r\nCreated from query:\r\n\r\n");
editPoint.Insert(query.Sql);
var nodes = GetAllNodes(query).ToList();
if (nodes.Any(node => !(node is IFetchXmlExecutionPlanNode)))
{
editPoint.Insert("\r\n‼ WARNING ‼\r\n");
editPoint.Insert("This query requires additional processing. This FetchXML gives the required data, but needs additional processing to format it in the same way as returned by the TDS Endpoint or SQL 4 CDS.\r\n\r\n");
editPoint.Insert("Learn more at https://markcarrington.dev/sql-4-cds/additional-processing/\r\n\r\n");
}
editPoint.Insert("\r\n\r\n-->");
foreach (var fetchXml in nodes.OfType <IFetchXmlExecutionPlanNode>())
{
editPoint.Insert("\r\n\r\n");
editPoint.Insert(fetchXml.FetchXmlString);
}
}
}
catch (NotSupportedQueryFragmentException ex)
{
VsShellUtilities.LogError("SQL 4 CDS", ex.ToString());
VsShellUtilities.ShowMessageBox(Package, "The query could not be converted to FetchXML: " + ex.Message, "Query Not Supported", OLEMSGICON.OLEMSGICON_CRITICAL, OLEMSGBUTTON.OLEMSGBUTTON_OK, OLEMSGDEFBUTTON.OLEMSGDEFBUTTON_FIRST);
}
catch (QueryParseException ex)
{
VsShellUtilities.LogError("SQL 4 CDS", ex.ToString());
VsShellUtilities.ShowMessageBox(Package, "The query could not be parsed: " + ex.Message, "Query Parsing Error", OLEMSGICON.OLEMSGICON_CRITICAL, OLEMSGBUTTON.OLEMSGBUTTON_OK, OLEMSGDEFBUTTON.OLEMSGDEFBUTTON_FIRST);
}
}
catch (Exception ex)
{
VsShellUtilities.LogError("SQL 4 CDS", ex.ToString());
}
}