protected object InvokeMethod(object target, string methodName, params object[] args)
{
var type = target.GetType();
while (type != null)
{
var methods = type.GetMethods(BindingFlags.InvokeMethod | BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance);
var method = methods
.Where(m => m.Name == methodName && m.GetParameters().Length == args.Length)
.ToArray();
if (method.Length == 1)
{
return(method[0].Invoke(target, args));
}
if (method.Length > 1)
{
VsShellUtilities.LogError("SQL 4 CDS", $"Ambiguous method {methodName} on type {target.GetType()}");
throw new ArgumentOutOfRangeException(nameof(methodName));
}
type = type.BaseType;
}
VsShellUtilities.LogError("SQL 4 CDS", $"Missing method {methodName} on type {target.GetType()}");
throw new ArgumentOutOfRangeException(nameof(methodName));
}
internal static void LogAndForget(this Task task, string source) =>
task.ContinueWith(
(t, s) => VsShellUtilities.LogError(s as string, t.Exception?.ToString()),
source,
CancellationToken.None,
TaskContinuationOptions.OnlyOnFaulted,
TaskScheduler.Default);
internal static void LogAndForget(this Task task, string source) =>
task.ContinueWith(
(t, s) => VsShellUtilities.LogError(s as string, t.Exception.ToString()),
source,
CancellationToken.None,
TaskContinuationOptions.OnlyOnFaulted,
VsTaskLibraryHelper.GetTaskScheduler(VsTaskRunContext.UIThreadNormalPriority));
protected void SetField(object target, string fieldName, object value)
{
var field = target.GetType().GetField(fieldName, BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance);
if (field == null)
{
VsShellUtilities.LogError("SQL 4 CDS", $"Missing field {fieldName} on type {target.GetType()}");
}
field.SetValue(target, value);
}
protected void SetProperty(object target, string propName, object value)
{
var prop = target.GetType().GetProperty(propName, BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance);
if (prop == null)
{
VsShellUtilities.LogError("SQL 4 CDS", $"Missing property {propName} on type {target.GetType()}");
}
prop.SetValue(target, value);
}
protected static Type GetType(string typeName)
{
var type = Type.GetType(typeName);
if (type == null)
{
VsShellUtilities.LogError("SQL 4 CDS", $"Missing type {typeName}");
}
return(type);
}
internal static void ShowErrorMessage(this IServiceProvider provider, string message,
string title = "Error during Cake installation")
{
VsShellUtilities.LogError("Cake.VisualStudio", message);
VsShellUtilities.ShowMessageBox(
provider,
message,
title,
OLEMSGICON.OLEMSGICON_CRITICAL,
OLEMSGBUTTON.OLEMSGBUTTON_OK,
OLEMSGDEFBUTTON.OLEMSGDEFBUTTON_FIRST);
}
private static async Task <object> GetSelectedItemAsync()
{
await ThreadHelper.JoinableTaskFactory.SwitchToMainThreadAsync();
IVsMonitorSelection vsMonitorSelection = await ServiceProvider.GetGlobalServiceAsync <SVsShellMonitorSelection, IVsMonitorSelection>();
vsMonitorSelection.GetCurrentSelection(out IntPtr ppHier, out uint pitemid, out _, out IntPtr ppSC);
try
{
if (ppHier == IntPtr.Zero)
{
return(null);
}
// multiple items are selected.
if (pitemid == (uint)VSConstants.VSITEMID.Selection)
{
return(null);
}
if (Marshal.GetTypedObjectForIUnknown(ppHier, typeof(IVsHierarchy)) is IVsHierarchy hierarchy)
{
ErrorHandler.ThrowOnFailure(hierarchy.GetProperty(pitemid, (int)__VSHPROPID.VSHPROPID_ExtObject, out object item));
return(item);
}
}
catch (Exception ex)
{
VsShellUtilities.LogError(ex.Source, ex.ToString());
}
finally
{
if (ppHier != IntPtr.Zero)
{
Marshal.Release(ppHier);
}
if (ppSC != IntPtr.Zero)
{
Marshal.Release(ppSC);
}
}
return(null);
}
/// <summary>Gets the current text from the status bar.</summary>
public async Task <string?> GetMessageAsync()
{
await ThreadHelper.JoinableTaskFactory.SwitchToMainThreadAsync();
try
{
IVsStatusbar statusBar = await VS.Services.GetStatusBarAsync();
statusBar.GetText(out string pszText);
return(pszText);
}
catch (Exception ex)
{
VsShellUtilities.LogError(ex.Source, ex.ToString());
return(null);
}
}
internal static bool DownloadFileToProject(string downloadPath, string targetFileName,
Action <string> installCallback)
{
var dte = CakePackage.Dte;
try
{
var slnFilePath = new FileInfo(dte.Solution.FullName).FullName;
var cakeScriptPath = dte.Solution.FindProjectItem(Constants.ScriptFileName)?.FileNames[1];
var targetPath = Path.Combine(new FileInfo(string.IsNullOrWhiteSpace(cakeScriptPath) ? slnFilePath : cakeScriptPath).Directory.FullName, targetFileName);
bool confirm = true;
if (File.Exists(targetPath))
{
confirm = VsShellUtilities.PromptYesNo("File already exists. Overwrite?", $"Downloading {targetFileName}",
OLEMSGICON.OLEMSGICON_QUERY, CakePackage.Shell);
}
if (!confirm)
{
return(true);
}
try
{
ProjectHelpers.CheckFileOutOfSourceControl(targetPath);
}
catch (Exception)
{
// ignored
}
using (var wc = new WebClient())
{
wc.DownloadFile(downloadPath, targetPath);
VsShellUtilities.LogMessage(Constants.PackageName,
$"File downloaded from '{downloadPath}' to '{targetPath}'",
__ACTIVITYLOG_ENTRYTYPE.ALE_INFORMATION);
installCallback.Invoke(targetPath);
}
return(true);
}
catch
{
VsShellUtilities.LogError(Constants.PackageName, $"There was an error downloading the requested file: '{downloadPath}'");
return(false);
}
}
protected override int QueryClose(out bool canClose)
{
ThreadHelper.ThrowIfNotOnUIThread();
try
{
if (GeneralOptions.Instance.EnableAutoHide)
{
var dte = GetService(typeof(DTE)) as DTE2;
DockHelpers.DockToolWindows(dte);
}
}
catch (Exception ex)
{
VsShellUtilities.LogError(ex.Source, ex.ToString());
}
return(base.QueryClose(out canClose));
}
/// <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());
}
}
/// <summary>
/// Gets the selected item of the <see cref="IVsMonitorSelection"/>.
/// </summary>
/// <param name="monitorSelection">The monitor selection.</param>
/// <returns></returns>
/// <exception cref="System.ArgumentNullException">monitorSelection</exception>
public static object GetSelectedItem(this IVsMonitorSelection monitorSelection)
{
if (monitorSelection == null)
{
throw new ArgumentNullException(nameof(monitorSelection));
}
var selectedObject = default(object);
try
{
monitorSelection.GetCurrentSelection(
out IntPtr hierarchyPointer,
out uint itemId,
out IVsMultiItemSelect multiItemSelect,
out IntPtr selectionContianerPointer);
var selectedHierarchy = Marshal.GetTypedObjectForIUnknown(
hierarchyPointer,
typeof(IVsHierarchy))
as IVsHierarchy;
if (selectedHierarchy != null)
{
ErrorHandler.ThrowOnFailure(selectedHierarchy.GetProperty(
itemId,
(int)__VSHPROPID.VSHPROPID_ExtObject,
out selectedObject));
}
Marshal.Release(hierarchyPointer);
Marshal.Release(selectionContianerPointer);
}
catch (Exception exception)
{
VsShellUtilities.LogError(exception.Source, exception.ToString());
}
return(selectedObject);
}
public void Switch()
{
ThreadHelper.ThrowIfNotOnUIThread();
try
{
var toolWindowFrames = _package
.GetToolWindowFrames()
.Select(fr => fr as WindowFrame);
ApplySwitch(
toolWindowFrames.Where(IsDocked),
toolWindowFrames.Where(IsBookmarked));
}
catch (Exception e)
{
VsShellUtilities.LogError(e.Source, e.ToString());
#if DEBUG
throw;
#endif
}
}
/// <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());
}
}
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());
}
}
