public async Task OutputResultsAsync(IQueryRunner runner, IQueryTextProvider textProvider) { await Task.Run(() => { try { runner.OutputMessage("Query Started"); var sw = Stopwatch.StartNew(); var dq = textProvider.QueryText; // write results to Excel runner.Host.Proxy.OutputLinkedResultAsync(dq , runner.SelectedWorksheet , runner.ConnectedToPowerPivot?"":runner.ConnectionStringWithInitialCatalog).ContinueWith((ascendant) => { sw.Stop(); var durationMs = sw.ElapsedMilliseconds; runner.OutputMessage( string.Format("Query Completed - Query sent to Excel for execution)"), durationMs); runner.ActivateOutput(); runner.SetResultsMessage("Query sent to Excel for execution", OutputTarget.Linked); }, TaskScheduler.Default); } catch (Exception ex) { runner.ActivateOutput(); runner.OutputError(ex.Message); } finally { runner.QueryCompleted(); } }); }
public async Task OutputResultsAsync(IQueryRunner runner, IQueryTextProvider textProvider) { try { runner.OutputMessage("Query Started"); var sw = Stopwatch.StartNew(); var dq = textProvider.QueryText; var res = await runner.ExecuteDataTableQueryAsync(dq); sw.Stop(); var durationMs = sw.ElapsedMilliseconds; runner.OutputMessage(string.Format("Query Completed ({0:N0} row{1} returned)", res.Rows.Count, res.Rows.Count == 1 ? "" : "s"), durationMs); runner.RowCount = res.Rows.Count; runner.SetResultsMessage("Query timings sent to output tab", OutputTarget.Timer); //runner.QueryCompleted(); runner.ActivateOutput(); } catch (Exception ex) { Log.Error(ex, Common.Constants.LogMessageTemplate, nameof(ResultsTargetTimer), nameof(OutputResultsAsync), ex.Message); runner.ActivateOutput(); runner.OutputError(ex.Message); } finally { runner.QueryCompleted(); } }
/// <summary> /// Returns a string representation of the query /// </summary> /// <returns></returns> public override string ToString() { IQueryTextProvider queryText = this.Provider as IQueryTextProvider; if (queryText == null) { return(String.Empty); } return(queryText.GetQueryText(this.Expression)); }
public async Task OutputResultsAsync(IQueryRunner runner, IQueryTextProvider textProvider) { await Task.Run(async() => { try { runner.OutputMessage("Query Started"); var sw = Stopwatch.StartNew(); var dq = textProvider.QueryText; DataTable res = await runner.ExecuteDataTableQueryAsync(dq); if (res == null || res.Rows?.Count == 0) { Log.Warning("{class} {method} {message}", nameof(ResultsTargetExcelStatic), nameof(OutputResultsAsync), "Query Result DataTable has no rows"); runner.ActivateOutput(); runner.OutputWarning("Unable to send results to Excel as there are no rows in the result set"); return; } sw.Stop(); var durationMs = sw.ElapsedMilliseconds; // write results to Excel await runner.Host.Proxy.OutputStaticResultAsync(res, runner.SelectedWorksheet); //.ContinueWith((ascendant) => { runner.OutputMessage( string.Format("Query Completed ({0:N0} row{1} returned)", res.Rows.Count, res.Rows.Count == 1 ? "" : "s"), durationMs); runner.RowCount = res.Rows.Count; runner.ActivateOutput(); runner.SetResultsMessage("Static results sent to Excel", OutputTarget.Static); //},TaskScheduler.Default); } catch (Exception ex) { Log.Error(ex, "{class} {method} {message}", nameof(ResultsTargetExcelStatic), nameof(OutputResultsAsync), ex.Message); runner.ActivateOutput(); runner.OutputError(ex.Message); } finally { runner.QueryCompleted(); } }); }
public async Task OutputResultsAsync(IQueryRunner runner, IQueryTextProvider textProvider) { await Task.Run(async() => { try { runner.OutputMessage("Opening .odc file in Excel"); var sw = Stopwatch.StartNew(); var dq = textProvider.QueryText; // odc queries require 'mdx compatibility=1' var fixedConnStr = runner.ConnectionStringWithInitialCatalog.Replace("mdx compatibility=3", "mdx compatibility=1"); // create odc file var odcFile = OdcHelper.CreateOdcQueryFile(fixedConnStr, dq); Process.Start(odcFile); // write results to Excel sw.Stop(); var durationMs = sw.ElapsedMilliseconds; runner.OutputMessage( "Query Completed - Query sent to Excel for execution", durationMs); runner.OutputMessage("Note: odc files can only handle a query that returns a single result set. If you see an error try using one of the other output types to ensure your query is valid."); runner.ActivateOutput(); runner.SetResultsMessage("Query sent to Excel for execution", OutputTarget.Linked); await CleanUpOdcAsync(odcFile); } catch (Exception ex) { Log.Error(ex, Common.Constants.LogMessageTemplate, nameof(ResultsTargetExcelLinkedOdc), nameof(OutputResultsAsync), ex.Message); runner.ActivateOutput(); runner.OutputError(ex.Message); } finally { runner.QueryCompleted(); } }); }
public async Task OutputResultsAsync(IQueryRunner runner, IQueryTextProvider textProvider) { var dlg = new Microsoft.Win32.SaveFileDialog { DefaultExt = ".csv", Filter = "Comma separated text file - UTF8|*.csv|Tab separated text file|*.txt|Comma separated text file - Unicode|*.csv|Custom Export Format (Configure in Options)|*.csv" }; string fileName = ""; long durationMs = 0; // Show save file dialog box var result = dlg.ShowDialog(); // Process save file dialog box results if (result == true) { // Save document fileName = dlg.FileName; await Task.Run(() => { try { runner.OutputMessage("Query Started"); var sw = Stopwatch.StartNew(); string sep = "\t"; bool shouldQuoteStrings = true; //default to quoting all string fields string decimalSep = System.Globalization.CultureInfo.CurrentUICulture.NumberFormat.CurrencyDecimalSeparator; string isoDateFormat = string.Format(Constants.IsoDateMask, decimalSep); Encoding enc = new UTF8Encoding(false); switch (dlg.FilterIndex) { case 1: // utf-8 csv sep = System.Globalization.CultureInfo.CurrentUICulture.TextInfo.ListSeparator; break; case 2: // tab separated sep = "\t"; break; case 3: // unicode csv enc = new UnicodeEncoding(); sep = System.Globalization.CultureInfo.CurrentUICulture.TextInfo.ListSeparator; break; case 4: // custom export format sep = runner.Options.GetCustomCsvDelimiter(); shouldQuoteStrings = runner.Options.CustomCsvQuoteStringFields; break; } var daxQuery = textProvider.QueryText; var reader = runner.ExecuteDataReaderQuery(daxQuery, textProvider.ParameterCollection); using (var statusProgress = runner.NewStatusBarMessage("Starting Export")) { try { if (reader != null) { int iFileCnt = 1; runner.OutputMessage("Command Complete, writing output file"); bool moreResults = true; while (moreResults) { var outputFilename = fileName; int iRowCnt = 0; if (iFileCnt > 1) { outputFilename = AddFileCntSuffix(fileName, iFileCnt); } using (var textWriter = new System.IO.StreamWriter(outputFilename, false, enc)) { iRowCnt = reader.WriteToStream(textWriter, sep, shouldQuoteStrings, isoDateFormat, statusProgress); } runner.OutputMessage( string.Format("Query {2} Completed ({0:N0} row{1} returned)" , iRowCnt , iRowCnt == 1 ? "" : "s", iFileCnt) ); runner.RowCount = iRowCnt; moreResults = reader.NextResult(); iFileCnt++; } sw.Stop(); durationMs = sw.ElapsedMilliseconds; runner.SetResultsMessage("Query results written to file", OutputTarget.File); runner.ActivateOutput(); } else { runner.OutputError("Query Batch Completed with errors listed above (you may need to scroll up)", durationMs); } } finally { if (reader != null) { reader.Dispose(); } } } } catch (Exception ex) { Log.Error(ex, Common.Constants.LogMessageTemplate, nameof(ResultsTargetTextFile), nameof(OutputResultsAsync), ex.Message); runner.ActivateOutput(); runner.OutputError(ex.Message); #if DEBUG runner.OutputError(ex.StackTrace); #endif } finally { runner.OutputMessage("Query Batch Completed", durationMs); runner.QueryCompleted(); } }); } // else dialog was cancelled so return an empty task. await Task.Run(() => { }); }
// This is the core method that handles the output of the results public async Task OutputResultsAsync(IQueryRunner runner, IQueryTextProvider textProvider) { // Read the AutoFormat option from the options singleton bool autoFormat = _options.ResultAutoFormat; await Task.Run(() => { long durationMs = 0; int queryCnt = 1; try { runner.OutputMessage("Query Started"); var sw = Stopwatch.StartNew(); var dq = textProvider.QueryText; //var res = runner.ExecuteDataTableQuery(dq); var isSessionsDmv = dq.Contains(Common.Constants.SessionsDmv, StringComparison.OrdinalIgnoreCase); using (var dataReader = runner.ExecuteDataReaderQuery(dq)) { if (dataReader != null) { Log.Verbose("Start Processing Grid DataReader (Elapsed: {elapsed})", sw.ElapsedMilliseconds); runner.ResultsDataSet = dataReader.ConvertToDataSet(autoFormat, isSessionsDmv); Log.Verbose("End Processing Grid DataReader (Elapsed: {elapsed})", sw.ElapsedMilliseconds); sw.Stop(); // add extended properties to DataSet runner.ResultsDataSet.ExtendedProperties.Add("QueryText", dq); runner.ResultsDataSet.ExtendedProperties.Add("IsDiscoverSessions", isSessionsDmv); durationMs = sw.ElapsedMilliseconds; var rowCnt = runner.ResultsDataSet.Tables[0].Rows.Count; foreach (DataTable tbl in runner.ResultsDataSet.Tables) { runner.OutputMessage( string.Format("Query {2} Completed ({0:N0} row{1} returned)", tbl.Rows.Count, tbl.Rows.Count == 1 ? "" : "s", queryCnt)); queryCnt++; } runner.RowCount = rowCnt; // activate the result only when Counters are not selected... runner.ActivateResults(); runner.OutputMessage("Query Batch Completed", durationMs); } else { runner.OutputError("Query Batch Completed with errors listed above (you may need to scroll up)", durationMs); } } } catch (Exception ex) { Log.Error("{class} {method} {message} {stacktrace}", nameof(ResultsTargetGrid), nameof(OutputResultsAsync), ex.Message, ex.StackTrace); runner.ActivateOutput(); runner.OutputError(ex.Message); runner.OutputError("Query Batch Completed with errors listed above (you may need to scroll up)", durationMs); } finally { runner.QueryCompleted(); } }); }
// This is the core method that handles the output of the results public async Task OutputResultsAsync(IQueryRunner runner, IQueryTextProvider textProvider) { StringBuilder sb = new StringBuilder(); await Task.Run(() => { long durationMs = 0; try { runner.OutputMessage("Query Started"); var sw = Stopwatch.StartNew(); string sep = "\t"; bool shouldQuoteStrings = true; //default to quoting all string fields string decimalSep = System.Globalization.CultureInfo.CurrentUICulture.NumberFormat.CurrencyDecimalSeparator; string isoDateFormat = string.Format(Constants.IsoDateMask, decimalSep); Encoding enc = new UTF8Encoding(false); var daxQuery = textProvider.QueryText; var reader = runner.ExecuteDataReaderQuery(daxQuery); using (var statusProgress = runner.NewStatusBarMessage("Starting Export")) { try { if (reader != null) { runner.OutputMessage("Command Complete, writing output to clipboard"); bool moreResults = true; while (moreResults) { int iRowCnt = 0; using (StringWriter textWriter = new StringWriter(sb)) //using (var textWriter = new System.IO.StreamWriter( stringWriter, false, enc)) { iRowCnt = reader.WriteToStream(textWriter, sep, shouldQuoteStrings, isoDateFormat, statusProgress); } runner.OutputMessage( string.Format("Query Completed ({0:N0} row{1} returned)" , iRowCnt , iRowCnt == 1 ? "" : "s") ); runner.RowCount = iRowCnt; moreResults = reader.NextResult(); if (moreResults) { _eventAggregator.PublishOnUIThread(new OutputMessage(MessageType.Warning, "Output to Clipboard only copies the first table of results")); while (reader.NextResult()) { // loop thru } } } sw.Stop(); durationMs = sw.ElapsedMilliseconds; runner.SetResultsMessage("Query results written to file", OutputTarget.File); runner.ActivateOutput(); } else { runner.OutputError("Query Batch Completed with errors listed above (you may need to scroll up)", durationMs); } } finally { if (reader != null) { reader.Dispose(); } } } } catch (Exception ex) { runner.ActivateOutput(); runner.OutputError(ex.Message); #if DEBUG runner.OutputError(ex.StackTrace); #endif } finally { runner.OutputMessage("Query Batch Completed", durationMs); runner.QueryCompleted(); } }); // copy output to clipboard System.Windows.Threading.Dispatcher.CurrentDispatcher.Invoke(() => { System.Windows.Forms.Clipboard.SetText(sb.ToString()); }, System.Windows.Threading.DispatcherPriority.Normal); }
public async Task OutputResultsAsync(IQueryRunner runner, IQueryTextProvider textProvider) { var dlg = new Microsoft.Win32.SaveFileDialog { DefaultExt = ".xlsx", Filter = "Excel file (*.xlsx)|*.xlsx" }; string fileName = ""; long durationMs = 0; // Show save file dialog box var result = dlg.ShowDialog(); // Process save file dialog box results if (result == true) { // Save document fileName = dlg.FileName; await Task.Run(() => { try { runner.OutputMessage("Query Started"); var sw = Stopwatch.StartNew(); var daxQuery = textProvider.QueryText; var reader = runner.ExecuteDataReaderQuery(daxQuery); using (var statusProgress = runner.NewStatusBarMessage("Starting Export")) { try { if (reader != null) { int iFileCnt = 1; runner.OutputMessage("Command Complete, writing output file"); bool moreResults = true; using (var stream = new FileStream(fileName, FileMode.Create, FileAccess.Write)) using (var xlsxWriter = new XlsxWriter(stream)) { while (moreResults) { // create a worksheet for the current resultset xlsxWriter.BeginWorksheet($"Query{iFileCnt}", 1); // write out the current resultset var iRowCnt = WriteToWorksheet(reader, xlsxWriter, statusProgress, runner); // setup Excel Autofilters xlsxWriter.SetAutoFilter(1, 1, xlsxWriter.CurrentRowNumber, reader.FieldCount); runner.OutputMessage( string.Format("Query {2} Completed ({0:N0} row{1} returned)" , iRowCnt , iRowCnt == 1 ? "" : "s", iFileCnt) ); runner.RowCount = iRowCnt; moreResults = reader.NextResult(); iFileCnt++; } } sw.Stop(); durationMs = sw.ElapsedMilliseconds; runner.SetResultsMessage("Query results written to file", OutputTarget.File); runner.ActivateOutput(); } else { runner.OutputError("Query Batch Completed with errors listed above (you may need to scroll up)", durationMs); } } finally { reader?.Dispose(); } } } catch (Exception ex) { runner.ActivateOutput(); runner.OutputError(ex.Message); #if DEBUG runner.OutputError(ex.StackTrace); #endif } finally { runner.OutputMessage("Query Batch Completed", durationMs); runner.QueryCompleted(); } }); } // else dialog was cancelled so return an empty task. await Task.Run(() => { }); }