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 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);
}
}));
}
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 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);
}
}));
}
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();
}
});
}
// 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 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();
}
});
}
// 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)
{
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(() => { }));
}
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 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(() => { }));
}
// 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();
}
});
}
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(() => { });
}
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(() => { });
}