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];