Esempio n. 1
0
        // This is the core method that handles the output of the results
        public Task OutputResultsAsync(IQueryRunner runner)
        {
            // Read the AutoFormat option from the options singleton
            bool autoFormat = _options.ResultAutoFormat;

            return(Task.Run(() =>
            {
                long durationMs = 0;
                int queryCnt = 1;
                try
                {
                    runner.OutputMessage("Query Started");
                    var sw = Stopwatch.StartNew();

                    var dq = runner.QueryText;
                    //var res = runner.ExecuteDataTableQuery(dq);
                    using (var dataReader = runner.ExecuteDataReaderQuery(dq))
                    {
                        if (dataReader != null)
                        {
                            Log.Verbose("Start Processing Grid DataReader (Elapsed: {elapsed})", sw.ElapsedMilliseconds);
                            runner.ResultsDataSet = dataReader.ConvertToDataSet(autoFormat);
                            Log.Verbose("End Processing Grid DataReader (Elapsed: {elapsed})", sw.ElapsedMilliseconds);

                            sw.Stop();
                            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", durationMs);
                        }
                    }
                }
                catch (Exception ex)
                {
                    Log.Error("{class} {method} {message} {stacktrace}", "ResultsTargetGrid", "OutputQueryResultsAsync", ex.Message, ex.StackTrace);
                    runner.ActivateOutput();
                    runner.OutputError(ex.Message);
                    runner.OutputError("Query Batch Completed with erros", durationMs);
                }
                finally
                {
                    runner.QueryCompleted();
                }
            }));
        }
        public Task OutputResultsAsync(IQueryRunner runner)
        {
            return(Task.Run(() =>
            {
                try
                {
                    runner.OutputMessage("Query Started");
                    var sw = Stopwatch.StartNew();

                    var dq = runner.QueryText;
                    var res = runner.ExecuteDataTableQuery(dq);

                    sw.Stop();
                    var durationMs = sw.ElapsedMilliseconds;


                    // write results to Excel
                    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", OutputTargets.Static);
                        runner.QueryCompleted();
                    }, TaskScheduler.Default);
                }
                catch (Exception ex)
                {
                    runner.ActivateOutput();
                    runner.OutputError(ex.Message);
                }
            }));
        }
Esempio n. 3
0
 private void OutputResults(IQueryRunner runner)
 {
     try
     {
         runner.OutputMessage("Query Started");
         var sw  = Stopwatch.StartNew();
         var dq  = runner.QueryText;
         var res = runner.ExecuteDataTableQuery(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", OutputTargets.Timer);
         //runner.QueryCompleted();
         runner.ActivateOutput();
     }
     catch (Exception ex)
     {
         runner.ActivateOutput();
         runner.OutputError(ex.Message);
     }
     finally
     {
         runner.QueryCompleted();
     }
 }
        public void OutputResults(IQueryRunner runner)
        {
            try
            {
                runner.OutputMessage("Query Started");
                var start = DateTime.Now;

                var dq = runner.QueryText;
                runner.ExecuteQueryAsync(dq).ContinueWith((antecendant) =>
                {
                    var end        = DateTime.Now;
                    var durationMs = (end - start).TotalMilliseconds;
                    var res        = antecendant.Result;

                    // TODO write results to Excel
                    runner.Host.OutputStaticResult(res, runner.SelectedWorksheet);
                    //runner.ResultsTable = res;

                    runner.OutputMessage(
                        string.Format("Query Completed ({0} row{1} returned)", res.Rows.Count,
                                      res.Rows.Count == 1 ? "" : "s"), durationMs);
                    runner.ActivateResults();
                    runner.QueryCompleted();
                }, TaskScheduler.FromCurrentSynchronizationContext());
            }
            catch (Exception ex)
            {
                runner.ActivateOutput();
                runner.OutputError(ex.Message);
            }
        }
        public Task OutputResultsAsync(IQueryRunner runner)
        {
            return(Task.Factory.StartNew(() =>
            {
                try
                {
                    runner.OutputMessage("Query Started");
                    var start = DateTime.Now;

                    var dq = runner.QueryText;
                    var res = runner.ExecuteQuery(dq);

                    var end = DateTime.Now;
                    var durationMs = (end - start).TotalMilliseconds;


                    // TODO write results to Excel
                    runner.Host.OutputStaticResult(res, runner.SelectedWorksheet);
                    //runner.ResultsTable = res;

                    runner.OutputMessage(
                        string.Format("Query Completed ({0} row{1} returned)", res.Rows.Count,
                                      res.Rows.Count == 1 ? "" : "s"), durationMs);
                    runner.ActivateResults();
                    runner.QueryCompleted();
                }
                catch (Exception ex)
                {
                    runner.ActivateOutput();
                    runner.OutputError(ex.Message);
                }
            }));
        }
        public void OutputResults(IQueryRunner runner)
        {
            try
            {
                runner.OutputMessage("Query Started");
                var start = DateTime.Now;

                var dq = runner.QueryText;
                runner.ExecuteQueryAsync(dq).ContinueWith((antecendant) =>
                    {
                        var end = DateTime.Now;
                        var durationMs = (end - start).TotalMilliseconds;
                        var res = antecendant.Result;

                        // TODO write results to Excel
                        runner.Host.OutputStaticResult(res, runner.SelectedWorksheet);
                        //runner.ResultsTable = res;

                        runner.OutputMessage(
                            string.Format("Query Completed ({0} row{1} returned)", res.Rows.Count,
                                          res.Rows.Count == 1 ? "" : "s"), durationMs);
                        runner.ActivateResults();
                        runner.QueryCompleted();
                    },TaskScheduler.FromCurrentSynchronizationContext());
            }
            catch (Exception ex)
            {
                runner.ActivateOutput();
                runner.OutputError(ex.Message);
            }
        }
        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();
                }
            });
        }
Esempio n. 8
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();
            }
        }
        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();
                }
            });
        }
Esempio n. 10
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();
                }
            });
        }
Esempio n. 11
0
 private void OutputResults(IQueryRunner runner)
 {
     try
     {
         runner.OutputMessage("Query Started");
         var sw = Stopwatch.StartNew();
         var dq = runner.QueryText;
         var res = runner.ExecuteQuery(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", OutputTargets.Timer);
         //runner.QueryCompleted();
         runner.ActivateOutput();
     }
     catch (Exception ex)
     {
         runner.ActivateOutput();
         runner.OutputError(ex.Message);
     }
 }
Esempio n. 12
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);
        }
        public Task OutputResultsAsync(IQueryRunner runner)
        {
            return Task.Factory.StartNew(() =>
                {
                    try
                    {
                        runner.OutputMessage("Query Started");
                        var start = DateTime.Now;

                        var dq = runner.QueryText;
                        var res = runner.ExecuteQuery(dq);

                        var end = DateTime.Now;
                        var durationMs = (end - start).TotalMilliseconds;

                        // TODO write results to Excel
                        runner.Host.OutputStaticResult(res, runner.SelectedWorksheet);
                        //runner.ResultsTable = res;

                        runner.OutputMessage(
                            string.Format("Query Completed ({0} row{1} returned)", res.Rows.Count,
                                          res.Rows.Count == 1 ? "" : "s"), durationMs);
                        runner.ActivateResults();
                        runner.QueryCompleted();

                    }
                    catch (Exception ex)
                    {
                        runner.ActivateOutput();
                        runner.OutputError(ex.Message);
                    }
                });
        }
Esempio n. 14
0
        public Task OutputResultsAsync(IQueryRunner runner)
        {
            return Task.Factory.StartNew(() =>
                {
                    try
                    {
                        runner.OutputMessage("Query Started");
                        var sw = Stopwatch.StartNew();

                        var dq = runner.QueryText;
                        var res = runner.ExecuteQuery(dq);
                        if (res != null)
                        {
                            sw.Stop();
                            var durationMs =sw.ElapsedMilliseconds;
                            runner.ResultsTable = res;
                            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;
                            // activate the result only when Counters are not selected...
                            runner.ActivateResults();
                            //runner.QueryCompleted();
                        }

                    }
                    catch (Exception ex)
                    {
                        runner.ActivateOutput();
                        runner.OutputError(ex.Message);
                    }
                });
        }
Esempio n. 15
0
        public Task OutputResultsAsync(IQueryRunner runner)
        {
            var dlg = new Microsoft.Win32.SaveFileDialog
            {
                DefaultExt = ".txt",
                Filter     = "Tab separated text file|*.txt|Comma separated text file - UTF8|*.csv|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;
                return(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);
                        var enc = Encoding.UTF8;

                        switch (dlg.FilterIndex)
                        {
                        case 1:     // tab separated
                            sep = "\t";
                            break;

                        case 2:     // utf-8 csv
                            sep = System.Globalization.CultureInfo.CurrentUICulture.TextInfo.ListSeparator;
                            break;

                        case 3:     //unicode csv
                            enc = Encoding.Unicode;
                            sep = System.Globalization.CultureInfo.CurrentUICulture.TextInfo.ListSeparator;
                            break;

                        case 4:
                            // TODO - custom export format
                            sep = runner.Options.GetCustomCsvDelimiter();
                            shouldQuoteStrings = runner.Options.CustomCsvQuoteStringFields;
                            break;
                        }

                        var daxQuery = runner.QueryText;
                        var reader = runner.ExecuteDataReaderQuery(daxQuery);

                        try
                        {
                            if (reader != null)
                            {
                                int iFileCnt = 1;
                                var outputFilename = fileName;

                                runner.OutputMessage("Command Complete, writing output file");

                                bool moreResults = true;

                                while (moreResults)
                                {
                                    int iMaxCol = reader.FieldCount - 1;
                                    int iRowCnt = 0;
                                    if (iFileCnt > 1)
                                    {
                                        outputFilename = AddFileCntSuffix(fileName, iFileCnt);
                                    }

                                    using (var textWriter = new System.IO.StreamWriter(outputFilename, false, enc))
                                    {
                                        using (var csvWriter = new CsvHelper.CsvWriter(textWriter))
                                        {
                                            // CSV Writer config

                                            csvWriter.Configuration.Delimiter = sep;

                                            // Datetime as ISOFormat

                                            csvWriter.Configuration.TypeConverterOptionsCache.AddOptions(
                                                typeof(DateTime),
                                                new CsvHelper.TypeConversion.TypeConverterOptions()
                                            {
                                                Formats = new string[] { isoDateFormat }
                                            });

                                            // write out clean column names

                                            foreach (var colName in reader.CleanColumnNames())
                                            {
                                                csvWriter.WriteField(colName);
                                            }

                                            csvWriter.NextRecord();

                                            while (reader.Read())
                                            {
                                                iRowCnt++;

                                                for (int iCol = 0; iCol < reader.FieldCount; iCol++)
                                                {
                                                    var fieldValue = reader[iCol];

                                                    // quote all string fields
                                                    if (reader.GetFieldType(iCol) == typeof(string))
                                                    {
                                                        if (reader.IsDBNull(iCol))
                                                        {
                                                            csvWriter.WriteField("", shouldQuoteStrings);
                                                        }
                                                        else
                                                        {
                                                            csvWriter.WriteField(fieldValue.ToString(), shouldQuoteStrings);
                                                        }
                                                    }
                                                    else
                                                    {
                                                        csvWriter.WriteField(fieldValue);
                                                    }
                                                }

                                                csvWriter.NextRecord();

                                                if (iRowCnt % 1000 == 0)
                                                {
                                                    runner.NewStatusBarMessage(string.Format("Written {0:n0} rows to the file output", iRowCnt));
                                                }
                                            }
                                        }
                                    }

                                    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", OutputTargets.File);
                                runner.ActivateOutput();
                            }
                            else
                            {
                                runner.OutputError("Query Batch Completed with errors", 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();
                    }
                }));
            }
            // else dialog was cancelled so return an empty task.
            return(Task.Run(() => { }));
        }
Esempio n. 16
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(() => { });
        }
Esempio n. 17
0
        // 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();
                }
            });
        }
Esempio n. 18
0
        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(() => { });
        }
Esempio n. 19
0
        public Task OutputResultsAsync(IQueryRunner runner)
        {
            var dlg = new Microsoft.Win32.SaveFileDialog
            {
                DefaultExt = ".txt",
                Filter     = "Tab separated text file|*.txt|Comma separated text file - UTF8|*.csv|Comma separated text file - Unicode|*.csv"
            };

            string fileName = "";

            // Show save file dialog box
            var result = dlg.ShowDialog();

            // Process save file dialog box results
            if (result == true)
            {
                // Save document
                fileName = dlg.FileName;
                return(Task.Run(() =>
                {
                    try
                    {
                        runner.OutputMessage("Query Started");
                        var sw = Stopwatch.StartNew();
                        string sep = "\t";
                        string decimalSep = System.Globalization.CultureInfo.CurrentUICulture.NumberFormat.CurrencyDecimalSeparator;
                        string isoDateFormat = string.Format("yyyy-MM-dd HH:mm:ss{0}000", decimalSep);
                        Encoding enc = Encoding.UTF8;
                        switch (dlg.FilterIndex)
                        {
                        case 1:     // tab separated
                            sep = "\t";
                            break;

                        case 2:     // utf-8 csv
                            sep = System.Globalization.CultureInfo.CurrentUICulture.TextInfo.ListSeparator;
                            break;

                        case 3:     //unicode csv
                            enc = Encoding.Unicode;
                            sep = System.Globalization.CultureInfo.CurrentUICulture.TextInfo.ListSeparator;
                            break;
                        }

                        var dq = runner.QueryText;
                        //var res = runner.ExecuteDataTableQuery(dq);
                        AdomdDataReader res = runner.ExecuteDataReaderQuery(dq);

                        if (res != null)
                        {
                            sw.Stop();
                            var durationMs = sw.ElapsedMilliseconds;
                            //runner.ResultsTable = res;
                            runner.OutputMessage("Command Complete, writing output file");

                            var sbLine = new StringBuilder();
                            bool moreResults = true;
                            int iFileCnt = 1;
                            while (moreResults)
                            {
                                int iMaxCol = res.FieldCount - 1;
                                int iRowCnt = 0;
                                if (iFileCnt > 1)
                                {
                                    fileName = AddFileCntSuffix(fileName, iFileCnt);
                                }
                                using (var writer = new StreamWriter(File.Open(fileName, FileMode.Create), enc))
                                {
                                    // write out clean column names
                                    writer.WriteLine(string.Join(sep, res.CleanColumnNames()));

                                    // write out data
                                    while (res.Read())
                                    {
                                        iRowCnt++;
                                        for (int iCol = 0; iCol < res.FieldCount; iCol++)
                                        {
                                            switch (res.GetDataTypeName(iCol))
                                            {
                                            case "Decimal":
                                            case "Int64":
                                                if (!res.IsDBNull(iCol))
                                                {
                                                    sbLine.Append(res.GetString(iCol));
                                                }
                                                break;

                                            case "DateTime":
                                                if (res.IsDBNull(iCol))
                                                {
                                                    sbLine.Append("\"\"");
                                                }
                                                else
                                                {
                                                    sbLine.Append(res.GetDateTime(iCol).ToString(isoDateFormat));
                                                }                                                                          // ISO date format
                                                break;

                                            default:
                                                sbLine.Append("\"");
                                                if (!res.IsDBNull(iCol))
                                                {
                                                    sbLine.Append(res.GetString(iCol).Replace("\"", "\"\"").Replace("\n", " "));
                                                }
                                                sbLine.Append("\"");
                                                break;
                                            }

                                            if (iCol < iMaxCol)
                                            {
                                                sbLine.Append(sep);
                                            }
                                        }
                                        writer.WriteLine(sbLine);
                                        sbLine.Clear();
                                        if (iRowCnt % 1000 == 0)
                                        {
                                            runner.NewStatusBarMessage(string.Format("Written {0:n0} rows to the file output", iRowCnt));
                                        }
                                    }
                                }
                                runner.OutputMessage(
                                    string.Format("Query Completed ({0:N0} row{1} returned)"
                                                  , iRowCnt
                                                  , iRowCnt == 1 ? "" : "s"), durationMs);
                                runner.RowCount = iRowCnt;
                                moreResults = res.NextResult();
                                iFileCnt++;
                            }
                            runner.SetResultsMessage("Query results written to file", OutputTargets.Grid);
                            //runner.QueryCompleted();
                            runner.ActivateOutput();
                        }
                        res.Close();
                    }
                    catch (Exception ex)
                    {
                        runner.ActivateOutput();
                        runner.OutputError(ex.Message);
#if DEBUG
                        runner.OutputError(ex.StackTrace);
#endif
                    }
                    finally
                    {
                        runner.QueryCompleted();
                    }
                }));
            }
            // else dialog was cancelled so return an empty task.
            return(Task.Run(() => { }));
        }