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();
                }
            });
        }
Exemple #2
0
        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();
            }
        }
Exemple #3
0
        /// <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();
                }
            });
        }
Exemple #5
0
        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();
                }
            });
        }
Exemple #8
0
        // 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);
        }
Exemple #9
0
        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(() => { });
        }