public void Handle(TraceChangingEvent message)
{
_traceMessage = new StatusBarMessage(ActiveDocument, "Waiting for trace to update");
_traceStatus = message.TraceStatus;
NotifyOfPropertyChange(() => CanRunQuery);
NotifyOfPropertyChange(() => CanConnect);
}
private void ExportDataToSQLServer(string connStr, string schemaName, bool truncateTables)
{
var metadataPane = this.ActiveDocument.MetadataPane;
// TODO: Use async but to be well done need to apply async on the DBCommand & DBConnection
// TODO: Show warning message?
if (metadataPane.SelectedModel == null)
{
return;
}
using (var conn = new SqlConnection(connStr))
{
conn.Open();
currentTableIdx = 0;
totalTableCnt = metadataPane.SelectedModel.Tables.Count;
foreach (var table in metadataPane.SelectedModel.Tables)
{
currentTableIdx++;
var daxQuery = $"EVALUATE('{table.Name}')";
using (var statusMsg = new StatusBarMessage(this.ActiveDocument, $"Exporting {table.Name}"))
using (var reader = metadataPane.Connection.ExecuteReader(daxQuery))
{
sqlTableName = $"[{schemaName}].[{table.Name}]";
EnsureSQLTableExists(conn, sqlTableName, reader);
using (var transaction = conn.BeginTransaction())
{
if (truncateTables)
{
using (var cmd = new SqlCommand($"truncate table {sqlTableName}", conn))
{
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
}
}
using (var sqlBulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.TableLock, transaction))
{
sqlBulkCopy.DestinationTableName = sqlTableName;
sqlBulkCopy.BatchSize = 1000;
sqlBulkCopy.NotifyAfter = 1000;
sqlBulkCopy.SqlRowsCopied += SqlBulkCopy_SqlRowsCopied;
sqlBulkCopy.EnableStreaming = true;
sqlBulkCopy.WriteToServer(reader);
}
transaction.Commit();
}
}
_eventAggregator.PublishOnUIThread(new OutputMessage(MessageType.Information, $"Exported {table.Name} to {sqlTableName}"));
}
}
}
private void ShowReady()
{
if (SuppressReadyMessage)
{
return;
}
var app = Connection.Application;
string companyName = app.Company.Name;
var status = new StatusBarMessage(app, $@"Add-on ready at {companyName}.");
status.Warning();
}
private void ExportDataToSQLServer(string connStr, string schemaName, bool truncateTables)
{
var metadataPane = this.Document.MetadataPane;
var cancellationTokenSource = new CancellationTokenSource();
SqlConnectionStringBuilder builder;
try
{
builder = new SqlConnectionStringBuilder(connStr);
}
catch (ArgumentException ex)
{
// wrap this exception and include the connection string that we could not parse
throw new ArgumentException($"Error parsing connections string: {connStr} - {ex.Message}", ex);
}
builder.ApplicationName = "DAX Studio Table Export";
currentTableIdx = 0;
var selectedTables = Tables.Where(t => t.IsSelected);
totalTableCnt = selectedTables.Count();
var connRead = Document.Connection;
// no tables were selected so exit here
if (totalTableCnt == 0)
{
return;
}
// TODO: Use async but to be well done need to apply async on the DBCommand & DBConnection
// TODO: Show warning message?
if (metadataPane.SelectedModel == null)
{
return;
}
try
{
Document.QueryStopWatch.Start();
using (var conn = new SqlConnection(builder.ToString()))
{
conn.Open();
foreach (var table in selectedTables)
{
try
{
EventAggregator.PublishOnUIThread(new ExportStatusUpdateEvent(table));
currentTable = table;
currentTable.Status = ExportStatus.Exporting;
currentTableIdx++;
var daxRowCount = $"EVALUATE ROW(\"RowCount\", COUNTROWS( {table.DaxName} ) )";
// get a count of the total rows in the table
DataTable dtRows = connRead.ExecuteDaxQueryDataTable(daxRowCount);
var totalRows = dtRows.Rows[0].Field <long>(0);
currentTable.TotalRows = totalRows;
using (var statusMsg = new StatusBarMessage(Document, $"Exporting {table.Caption}"))
{
for (long batchRows = 0; batchRows < totalRows; batchRows += maxBatchSize)
{
var daxQuery = $"EVALUATE {table.DaxName}";
// if the connection supports TOPNSKIP then use that to query batches of rows
if (connRead.AllFunctions.Contains("TOPNSKIP"))
{
daxQuery = $"EVALUATE TOPNSKIP({maxBatchSize}, {batchRows}, {table.DaxName} )";
}
using (var reader = connRead.ExecuteReader(daxQuery))
{
sqlTableName = $"[{schemaName}].[{table.Caption}]";
sqlBatchRows = batchRows;
// if this is the first batch ensure the table exists
if (batchRows == 0)
{
EnsureSQLTableExists(conn, sqlTableName, reader);
}
using (var transaction = conn.BeginTransaction())
{
if (truncateTables && batchRows == 0)
{
using (var cmd = new SqlCommand($"truncate table {sqlTableName}", conn))
{
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
}
}
var sqlBulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.TableLock, transaction); //)//, transaction))
sqlBulkCopy.DestinationTableName = sqlTableName;
sqlBulkCopy.BatchSize = 5000;
sqlBulkCopy.NotifyAfter = 5000;
sqlBulkCopy.SqlRowsCopied += SqlBulkCopy_SqlRowsCopied;
sqlBulkCopy.EnableStreaming = true;
var task = sqlBulkCopy.WriteToServerAsync(reader, cancellationTokenSource.Token);
WaitForTaskPollingForCancellation(cancellationTokenSource, task);
// update the currentTable with the final rowcount
currentTable.RowCount = sqlBulkCopy.RowsCopiedCount() + batchRows;
if (CancelRequested)
{
transaction.Rollback();
currentTable.Status = ExportStatus.Cancelled;
}
else
{
transaction.Commit();
if (currentTable.RowCount >= currentTable.TotalRows)
{
currentTable.Status = ExportStatus.Done;
}
}
} // end transaction
} // end using reader
// exit the loop here if the connection does not support TOPNSKIP
if (!connRead.AllFunctions.Contains("TOPNSKIP"))
{
break;
}
} // end rowBatch
}
// jump out of table loop if we have been cancelled
if (CancelRequested)
{
EventAggregator.PublishOnUIThread(new OutputMessage(MessageType.Warning, "Data Export Cancelled"));
// mark an tables not yet exported as skipped
MarkWaitingTablesAsSkipped();
break;
}
EventAggregator.PublishOnUIThread(new OutputMessage(MessageType.Information, exportTableMsg.Format(table.RowCount, table.RowCount == 1?"":"s", sqlTableName)));
currentTable.Status = ExportStatus.Done;
}
catch (Exception ex)
{
currentTable.Status = ExportStatus.Error;
Log.Error(ex, "{class} {method} {message}", nameof(ExportDataWizardViewModel), nameof(ExportDataToSQLServer), ex.Message);
EventAggregator.PublishOnUIThread(new OutputMessage(MessageType.Error, $"Error exporting data to SQL Server Table: {ex.Message}"));
EventAggregator.PublishOnUIThread(new ExportStatusUpdateEvent(currentTable, true));
continue; // skip to next table on error
}
} // end foreach table
}
Document.QueryStopWatch.Stop();
EventAggregator.PublishOnUIThread(new OutputMessage(MessageType.Information, exportCompleteMsg.Format(currentTableIdx), Document.QueryStopWatch.ElapsedMilliseconds));
EventAggregator.PublishOnUIThread(new ExportStatusUpdateEvent(currentTable, true));
Document.QueryStopWatch.Reset();
}
catch (Exception ex)
{
Document.QueryStopWatch.Stop();
if (currentTable == null && totalTableCnt > 0)
{
currentTable = selectedTables.FirstOrDefault();
}
if (currentTable != null)
{
currentTable.Status = ExportStatus.Error;
}
Log.Error(ex, "{class} {method} {message}", nameof(ExportDataWizardViewModel), nameof(ExportDataToSQLServer), ex.Message);
EventAggregator.PublishOnUIThread(new OutputMessage(MessageType.Error, $"Error exporting data to SQL Server: {ex.Message}"));
EventAggregator.PublishOnUIThread(new ExportStatusUpdateEvent(currentTable, true));
}
}
private void ExportDataToCSV(string outputPath)
{
var exceptionFound = false;
// TODO: Use async but to be well done need to apply async on the DBCommand & DBConnection
// TODO: Show warning message?
if (string.IsNullOrEmpty(Document.Connection.SelectedModelName))
{
return;
}
if (!Directory.Exists(outputPath))
{
Directory.CreateDirectory(outputPath);
}
Document.QueryStopWatch.Start();
var selectedTables = Tables.Where(t => t.IsSelected);
var totalTables = selectedTables.Count();
var tableCnt = 0;
string decimalSep = System.Globalization.CultureInfo.CurrentUICulture.NumberFormat.CurrencyDecimalSeparator;
string isoDateFormat = string.Format(Constants.IsoDateMask, decimalSep);
var encoding = new UTF8Encoding(false);
foreach (var table in selectedTables)
{
EventAggregator.PublishOnUIThread(new ExportStatusUpdateEvent(table));
var rows = 0;
tableCnt++;
try
{
table.Status = ExportStatus.Exporting;
var fileName = CleanNameOfIllegalChars(table.Caption);
var csvFilePath = System.IO.Path.Combine(outputPath, $"{fileName}.csv");
var daxRowCount = $"EVALUATE ROW(\"RowCount\", COUNTROWS( {table.DaxName} ) )";
// get a count of the total rows in the table
var connRead = Document.Connection;
DataTable dtRows = connRead.ExecuteDaxQueryDataTable(daxRowCount);
var totalRows = dtRows.Rows[0].Field <long?>(0) ?? 0;
table.TotalRows = totalRows;
StreamWriter textWriter = null;
try {
textWriter = new StreamWriter(csvFilePath, false, encoding);
using (var csvWriter = new CsvHelper.CsvWriter(textWriter, CultureInfo.InvariantCulture))
using (var statusMsg = new StatusBarMessage(Document, $"Exporting {table.Caption}"))
{
for (long batchRows = 0; batchRows < totalRows; batchRows += maxBatchSize)
{
var daxQuery = $"EVALUATE {table.DaxName}";
// if the connection supports TOPNSKIP then use that to query batches of rows
if (connRead.AllFunctions.Contains("TOPNSKIP"))
{
daxQuery = $"EVALUATE TOPNSKIP({maxBatchSize}, {batchRows}, {table.DaxName} )";
}
using (var reader = connRead.ExecuteReader(daxQuery))
{
rows = 0;
// configure delimiter
csvWriter.Configuration.Delimiter = CsvDelimiter;
// output dates using ISO 8601 format
csvWriter.Configuration.TypeConverterOptionsCache.AddOptions(
typeof(DateTime),
new CsvHelper.TypeConversion.TypeConverterOptions()
{
Formats = new string[] { isoDateFormat }
});
// if this is the first batch of rows
if (batchRows == 0)
{
// Write Header
foreach (var colName in reader.CleanColumnNames())
{
csvWriter.WriteField(colName);
}
csvWriter.NextRecord();
}
// Write data
while (reader.Read())
{
for (var fieldOrdinal = 0; fieldOrdinal < reader.FieldCount; fieldOrdinal++)
{
var fieldValue = reader[fieldOrdinal];
// quote all string fields
if (reader.GetFieldType(fieldOrdinal) == typeof(string))
{
if (reader.IsDBNull(fieldOrdinal))
{
csvWriter.WriteField("", this.CsvQuoteStrings);
}
else
{
csvWriter.WriteField(fieldValue.ToString(), this.CsvQuoteStrings);
}
}
else
{
csvWriter.WriteField(fieldValue);
}
}
rows++;
if (rows % 5000 == 0)
{
table.RowCount = rows + batchRows;
statusMsg.Update($"Exporting Table {tableCnt} of {totalTables} : {table.DaxName} ({rows + batchRows:N0} rows)");
Document.RefreshElapsedTime();
// if cancel has been requested do not write any more records
if (CancelRequested)
{
table.Status = ExportStatus.Cancelled;
// break out of datareader.Read() loop
break;
}
}
csvWriter.NextRecord();
}
Document.RefreshElapsedTime();
table.RowCount = rows + batchRows;
// if cancel has been requested do not write any more files
if (CancelRequested)
{
EventAggregator.PublishOnUIThread(new OutputMessage(MessageType.Warning, "Data Export Cancelled"));
MarkWaitingTablesAsSkipped();
// break out of foreach table loop
break;
}
}
// do not loop around if the current connection does not support TOPNSKIP
if (!connRead.AllFunctions.Contains("TOPNSKIP"))
{
break;
}
} // end of batch
EventAggregator.PublishOnUIThread(new OutputMessage(MessageType.Information, exportTableMsg.Format(rows, rows == 1 ? "":"s", table.DaxName + ".csv")));;
}
}
finally
{
textWriter?.Dispose();
}
table.Status = ExportStatus.Done;
}
catch (Exception ex)
{
table.Status = ExportStatus.Error;
exceptionFound = true;
Log.Error(ex, "{class} {method} {message}", nameof(ExportDataWizardViewModel), nameof(ExportDataToCSV), "Error while exporting model to CSV");
EventAggregator.PublishOnUIThread(new OutputMessage(MessageType.Error, $"Error Exporting '{table.DaxName}': {ex.Message}"));
EventAggregator.PublishOnUIThread(new ExportStatusUpdateEvent(currentTable, true));
continue; // skip to the next table if we have caught an exception
}
}
Document.QueryStopWatch.Stop();
// export complete
if (!exceptionFound)
{
EventAggregator.PublishOnUIThread(new OutputMessage(MessageType.Information, exportCompleteMsg.Format(tableCnt), Document.QueryStopWatch.ElapsedMilliseconds));
}
EventAggregator.PublishOnUIThread(new ExportStatusUpdateEvent(currentTable, true));
Document.QueryStopWatch.Reset();
}
private void ExportDataToFolder(string outputPath)
{
var metadataPane = this.ActiveDocument.MetadataPane;
var exceptionFound = false;
// TODO: Use async but to be well done need to apply async on the DBCommand & DBConnection
// TODO: Show warning message?
if (metadataPane.SelectedModel == null)
{
return;
}
if (!Directory.Exists(outputPath))
{
Directory.CreateDirectory(outputPath);
}
ActiveDocument.QueryStopWatch.Start();
foreach (var table in metadataPane.SelectedModel.Tables)
{
var rows = 0;
var tableCnt = 0;
try
{
var csvFilePath = System.IO.Path.Combine(outputPath, $"{table.Name}.csv");
var daxQuery = $"EVALUATE {table.DaxName}";
var totalTables = metadataPane.SelectedModel.Tables.Count;
using (var textWriter = new StreamWriter(csvFilePath, false, Encoding.UTF8))
using (var csvWriter = new CsvHelper.CsvWriter(textWriter))
using (var statusMsg = new StatusBarMessage(this.ActiveDocument, $"Exporting {table.Name}"))
using (var reader = ActiveDocument.Connection.ExecuteReader(daxQuery))
{
rows = 0;
tableCnt++;
// Write Header
foreach (var colName in reader.CleanColumnNames())
{
csvWriter.WriteField(colName);
}
csvWriter.NextRecord();
// Write data
while (reader.Read())
{
for (var fieldOrdinal = 0; fieldOrdinal < reader.FieldCount; fieldOrdinal++)
{
var fieldValue = reader[fieldOrdinal];
csvWriter.WriteField(fieldValue);
}
rows++;
if (rows % 5000 == 0)
{
new StatusBarMessage(ActiveDocument, $"Exporting Table {tableCnt} of {totalTables} : {table.Name} ({rows:N0} rows)");
ActiveDocument.RefreshElapsedTime();
// if cancel has been requested do not write any more records
if (CancelRequested)
{
break;
}
}
csvWriter.NextRecord();
}
ActiveDocument.RefreshElapsedTime();
_eventAggregator.PublishOnUIThread(new OutputMessage(MessageType.Information, $"Exported {rows:N0} rows to {table.Name}.csv"));
// if cancel has been requested do not write any more files
if (CancelRequested)
{
break;
}
}
}
catch (Exception ex)
{
exceptionFound = true;
Log.Error(ex, "{class} {method} {message}", "ExportDataDialogViewModel", "ExportDataToFolder", "Error while exporting model to CSV");
_eventAggregator.PublishOnUIThread(new OutputMessage(MessageType.Error, $"Error Exporting '{table.Name}': {ex.Message}"));
break; // exit from the loop if we have caught an exception
}
}
ActiveDocument.QueryStopWatch.Stop();
// export complete
if (!exceptionFound)
{
_eventAggregator.PublishOnUIThread(new OutputMessage(MessageType.Information, $"Model Export Complete: {metadataPane.SelectedModel.Tables.Count} tables exported", ActiveDocument.QueryStopWatch.ElapsedMilliseconds));
}
ActiveDocument.QueryStopWatch.Reset();
}
private void ExportDataToSQLServer(string connStr, string schemaName, bool truncateTables)
{
var metadataPane = this.Document.MetadataPane;
// TODO: Use async but to be well done need to apply async on the DBCommand & DBConnection
// TODO: Show warning message?
if (metadataPane.SelectedModel == null)
{
return;
}
Document.QueryStopWatch.Start();
using (var conn = new SqlConnection(connStr))
{
conn.Open();
var currentTableIdx = 0;
var selectedTables = Tables.Where(t => t.IsSelected);
totalTableCnt = selectedTables.Count();
foreach (var table in selectedTables)
{
try
{
EventAggregator.PublishOnUIThread(new ExportStatusUpdateEvent(table));
currentTable = table;
currentTable.Status = ExportStatus.Exporting;
currentTableIdx++;
var daxQuery = $"EVALUATE {table.DaxName}";
using (var statusMsg = new StatusBarMessage(Document, $"Exporting {table.Caption}"))
using (var reader = metadataPane.Connection.ExecuteReader(daxQuery))
{
sqlTableName = $"[{schemaName}].[{table.Caption}]";
EnsureSQLTableExists(conn, sqlTableName, reader);
using (var transaction = conn.BeginTransaction())
{
if (truncateTables)
{
using (var cmd = new SqlCommand($"truncate table {sqlTableName}", conn))
{
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
}
}
using (var sqlBulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.TableLock, transaction))
{
sqlBulkCopy.DestinationTableName = sqlTableName;
sqlBulkCopy.BatchSize = 5000;
sqlBulkCopy.NotifyAfter = 5000;
sqlBulkCopy.SqlRowsCopied += SqlBulkCopy_SqlRowsCopied;
sqlBulkCopy.EnableStreaming = true;
sqlBulkCopy.WriteToServer(reader);
currentTable.RowCount = sqlBulkCopy.RowsCopiedCount();
}
transaction.Commit();
currentTable.Status = ExportStatus.Done;
}
}
// jump out of table loop if we have been cancelled
if (CancelRequested)
{
EventAggregator.PublishOnUIThread(new OutputMessage(MessageType.Warning, "Data Export Cancelled"));
break;
}
EventAggregator.PublishOnUIThread(new OutputMessage(MessageType.Information, $"Exported {table.Caption} to {sqlTableName}"));
currentTable.Status = ExportStatus.Done;
}
catch (Exception ex)
{
currentTable.Status = ExportStatus.Error;
Log.Error(ex, "{class} {method} {message}", "ExportDataWizardViewModel", "ExportDataToSQLServer", ex.Message);
EventAggregator.PublishOnUIThread(new OutputMessage(MessageType.Error, $"Error exporting data to SQL Server: {ex.Message}"));
EventAggregator.PublishOnUIThread(new ExportStatusUpdateEvent(currentTable, true));
continue; // skip to next table on error
}
}
}
Document.QueryStopWatch.Stop();
EventAggregator.PublishOnUIThread(new OutputMessage(MessageType.Information, $"Model Export Complete: {currentTableIdx} tables exported", Document.QueryStopWatch.ElapsedMilliseconds));
EventAggregator.PublishOnUIThread(new ExportStatusUpdateEvent(currentTable, true));
Document.QueryStopWatch.Reset();
}
private void ExportDataToFolder(string outputPath)
{
var metadataPane = Document.MetadataPane;
var exceptionFound = false;
// TODO: Use async but to be well done need to apply async on the DBCommand & DBConnection
// TODO: Show warning message?
if (metadataPane.SelectedModel == null)
{
return;
}
if (!Directory.Exists(outputPath))
{
Directory.CreateDirectory(outputPath);
}
Document.QueryStopWatch.Start();
var selectedTables = Tables.Where(t => t.IsSelected);
var totalTables = selectedTables.Count();
var tableCnt = 0;
string decimalSep = System.Globalization.CultureInfo.CurrentUICulture.NumberFormat.CurrencyDecimalSeparator;
string isoDateFormat = string.Format(Constants.IsoDateMask, decimalSep);
var encoding = new UTF8Encoding(false);
foreach (var table in selectedTables)
{
EventAggregator.PublishOnUIThread(new ExportStatusUpdateEvent(table));
var rows = 0;
try
{
table.Status = ExportStatus.Exporting;
var fileName = CleanNameOfIllegalChars(table.Caption);
var csvFilePath = System.IO.Path.Combine(outputPath, $"{fileName}.csv");
var daxQuery = $"EVALUATE {table.DaxName}";
StreamWriter textWriter = null;
try {
textWriter = new StreamWriter(csvFilePath, false, encoding);
using (var csvWriter = new CsvHelper.CsvWriter(textWriter))
using (var statusMsg = new StatusBarMessage(Document, $"Exporting {table.Caption}"))
using (var reader = Document.Connection.ExecuteReader(daxQuery))
{
rows = 0;
tableCnt++;
// configure delimiter
csvWriter.Configuration.Delimiter = CsvDelimiter;
// output dates using ISO 8601 format
csvWriter.Configuration.TypeConverterOptionsCache.AddOptions(
typeof(DateTime),
new CsvHelper.TypeConversion.TypeConverterOptions()
{
Formats = new string[] { isoDateFormat }
});
// Write Header
foreach (var colName in reader.CleanColumnNames())
{
csvWriter.WriteField(colName);
}
csvWriter.NextRecord();
// Write data
while (reader.Read())
{
for (var fieldOrdinal = 0; fieldOrdinal < reader.FieldCount; fieldOrdinal++)
{
var fieldValue = reader[fieldOrdinal];
csvWriter.WriteField(fieldValue);
}
rows++;
if (rows % 5000 == 0)
{
table.RowCount = rows;
new StatusBarMessage(Document, $"Exporting Table {tableCnt} of {totalTables} : {table.DaxName} ({rows:N0} rows)");
Document.RefreshElapsedTime();
// if cancel has been requested do not write any more records
if (CancelRequested)
{
table.Status = ExportStatus.Cancelled;
break;
}
}
csvWriter.NextRecord();
}
Document.RefreshElapsedTime();
table.RowCount = rows;
EventAggregator.PublishOnUIThread(new OutputMessage(MessageType.Information, $"Exported {rows:N0} rows to {table.DaxName}.csv"));
// if cancel has been requested do not write any more files
if (CancelRequested)
{
EventAggregator.PublishOnUIThread(new OutputMessage(MessageType.Warning, "Data Export Cancelled"));
break;
}
}
}
finally
{
textWriter?.Dispose();
}
table.Status = ExportStatus.Done;
}
catch (Exception ex)
{
table.Status = ExportStatus.Error;
exceptionFound = true;
Log.Error(ex, "{class} {method} {message}", "ExportDataDialogViewModel", "ExportDataToFolder", "Error while exporting model to CSV");
EventAggregator.PublishOnUIThread(new OutputMessage(MessageType.Error, $"Error Exporting '{table.DaxName}': {ex.Message}"));
EventAggregator.PublishOnUIThread(new ExportStatusUpdateEvent(currentTable, true));
continue; // skip to the next table if we have caught an exception
}
}
Document.QueryStopWatch.Stop();
// export complete
if (!exceptionFound)
{
EventAggregator.PublishOnUIThread(new OutputMessage(MessageType.Information, $"Model Export Complete: {tableCnt} tables exported", Document.QueryStopWatch.ElapsedMilliseconds));
}
EventAggregator.PublishOnUIThread(new ExportStatusUpdateEvent(currentTable, true));
Document.QueryStopWatch.Reset();
}
public string this[StatusBarMessage message] => msg[message];