/// <summary>
/// Imports the selected MySQL procedure's result sets into the active <see cref="ExcelInterop.Worksheet"/>.
/// </summary>
private bool ImportData()
{
if (_importDataSet == null)
{
MiscUtilities.ShowCustomizedErrorDialog(string.Format(Resources.UnableToRetrieveData, "procedure", _dbProcedure.Name));
return(false);
}
if (_sumOfResultSetsExceedsMaxCompatibilityRows && ProcedureResultSetsImportType == DbProcedure.ProcedureResultSetsImportType.AllResultSetsVertically && _importDataSet.Tables.Count > 1)
{
InfoDialog.ShowDialog(InfoDialogProperties.GetWarningDialogProperties(
Resources.ImportVerticallyExceedsMaxRowsTitleWarning,
Resources.ImportVerticallyExceedsMaxRowsDetailWarning));
}
Cursor = Cursors.WaitCursor;
// Refresh import parameter values
_dbProcedure.ImportParameters.AddSummaryRow = AddSummaryFieldsCheckBox.Checked;
_dbProcedure.ImportParameters.CreatePivotTable = CreatePivotTableCheckBox.Checked;
_dbProcedure.ImportParameters.IncludeColumnNames = IncludeHeadersCheckBox.Checked;
_dbProcedure.ImportParameters.IntoNewWorksheet = false;
// Import the result sets into Excel
bool success = _dbProcedure.ImportData(ProcedureResultSetsImportType, _selectedResultSetIndex, _importDataSet);
Cursor = Cursors.Default;
return(success);
}
/// <summary>
/// Event delegate method fired when the <see cref="PreviewDataGridView"/> detects a data error in one of its cells.
/// </summary>
/// <param name="sender">Sender object.</param>
/// <param name="e">Event arguments.</param>
private void PreviewDataGridView_DataError(object sender, DataGridViewDataErrorEventArgs e)
{
if (PreviewDataGridView.Rows[e.RowIndex].Cells[e.ColumnIndex].ValueType != Type.GetType("System.Byte[]"))
{
return;
}
try
{
var img = (byte[])(PreviewDataGridView.Rows[e.RowIndex].Cells[e.ColumnIndex]).Value;
using (var ms = new MemoryStream(img))
{
Image.FromStream(ms);
}
}
catch (ArgumentException argEx)
{
MySqlSourceTrace.WriteAppErrorToLog(argEx);
}
catch (Exception ex)
{
MiscUtilities.ShowCustomizedErrorDialog(Resources.DataLoadingError, ex.Message);
MySqlSourceTrace.WriteAppErrorToLog(ex);
}
}
/// <summary>
/// Reverts the changes done to Excel cell values after the last commit.
/// </summary>
/// <param name="refreshFromDb">Flag indicating if instead of reverting the data back to the way it was when the editing session started, it is pulled to have the most recent version of it.</param>
private void RevertDataChanges(bool refreshFromDb)
{
try
{
if (!refreshFromDb)
{
_mySqlTable.RejectChanges();
}
else
{
_mySqlTable.RefreshData();
}
}
catch (Exception ex)
{
MiscUtilities.ShowCustomizedErrorDialog(Resources.EditDataRefreshErrorText, ex.Message);
}
Globals.ThisAddIn.SkipSelectedDataContentsDetection = true;
EditingWorksheet.UnprotectEditingWorksheet(EditingWorksheet_Change, WorksheetProtectionKey);
_editDataRange.Clear();
ExcelInterop.Range topLeftCell = _editDataRange.Cells[1, 1];
topLeftCell.Select();
_editDataRange = _mySqlTable.ImportDataIntoExcelRange(topLeftCell);
CommitChangesButton.Enabled = false;
AddNewRowToEditingRange(false);
}
/// <summary>
/// Event delegate method fired when <see cref="EditDataHotLabel"/> is clicked.
/// </summary>
/// <param name="sender">Sender object.</param>
/// <param name="e">Event arguments.</param>
private void EditDataHotLabel_Click(object sender, EventArgs e)
{
var selectedNode = DBObjectList.SelectedNode;
var selectedTable = selectedNode.DbObject as DbTable;
if (selectedNode == null || selectedNode.Type != MySqlListViewNode.MySqlNodeType.DbObject || selectedTable == null || _wbConnection == null)
{
return;
}
var passwordFlags = _wbConnection.TestConnectionAndRetryOnWrongPassword();
if (!passwordFlags.ConnectionSuccess)
{
return;
}
try
{
var excelAddInPane = Parent as ExcelAddInPane;
EditDataHotLabel.Enabled = excelAddInPane != null && !excelAddInPane.EditTableData(selectedTable, false, null);
}
catch (Exception ex)
{
MiscUtilities.ShowCustomizedErrorDialog(Resources.EditDataErrorTitle, ex.Message, true);
MySqlSourceTrace.WriteAppErrorToLog(ex);
}
}
/// <summary>
/// Event delegate method fired when <see cref="AppendDataHotLabel"/> is clicked.
/// </summary>
/// <param name="sender">Sender object.</param>
/// <param name="e">Event arguments.</param>
private void AppendDataHotLabel_Click(object sender, EventArgs e)
{
var selectedNode = DBObjectList.SelectedNode;
if (selectedNode == null || selectedNode.Type != MySqlListViewNode.MySqlNodeType.DbObject || !(selectedNode.DbObject is DbTable) || _wbConnection == null)
{
return;
}
var passwordFlags = _wbConnection.TestConnectionAndRetryOnWrongPassword();
if (!passwordFlags.ConnectionSuccess)
{
return;
}
try
{
ExportDataToTable(selectedNode.DbObject as DbTable);
}
catch (Exception ex)
{
MiscUtilities.ShowCustomizedErrorDialog(Resources.AppendDataErrorTitle, ex.Message, true);
MySqlSourceTrace.WriteAppErrorToLog(ex);
}
finally
{
if (Cursor == Cursors.WaitCursor)
{
Cursor = Cursors.Default;
}
}
}
/// <summary>
/// Event delegate method fired when <see cref="NextButton"/> is clicked.
/// </summary>
/// <param name="sender">Sender object.</param>
/// <param name="e">Event arguments.</param>
private void NextButton_Click(object sender, EventArgs e)
{
var selectedNode = SchemasList.SelectedNode;
if (selectedNode == null || selectedNode.Type == MySqlListViewNode.MySqlNodeType.Header || string.IsNullOrEmpty(selectedNode.DbObject.Name))
{
return;
}
var passwordFlags = _wbConnection.TestConnectionAndRetryOnWrongPassword();
if (!passwordFlags.ConnectionSuccess)
{
return;
}
try
{
var excelAddInPane = Parent as ExcelAddInPane;
if (excelAddInPane != null)
{
excelAddInPane.OpenSchema(selectedNode.DbObject.Name, true);
}
}
catch (Exception ex)
{
MiscUtilities.ShowCustomizedErrorDialog(Resources.SchemaOpeningErrorTitle, ex.Message, true);
MySqlSourceTrace.WriteAppErrorToLog(ex);
}
}
/// <summary>
/// Fetches all schema names from the current connection and loads them in the <see cref="SchemasList"/> tree.
/// </summary>
/// <returns><c>true</c> if schemas were loaded successfully, <c>false</c> otherwise.</returns>
private bool LoadSchemas()
{
if (SchemasList.HeaderNodes.Count < 2)
{
return(false);
}
try
{
// Avoids flickering of schemas list while adding the items to it.
SchemasList.BeginUpdate();
LoadedSchemas.ForEach(schema => schema.Dispose());
LoadedSchemas.Clear();
foreach (TreeNode node in SchemasList.Nodes)
{
node.Nodes.Clear();
}
DataTable databases = _wbConnection.GetSchemaCollection("Databases", null);
foreach (DataRow row in databases.Rows)
{
string schemaName = row["DATABASE_NAME"].ToString();
// If the user has specified a filter then check it
if (!string.IsNullOrEmpty(_filter) && !schemaName.ToUpper().Contains(_filter))
{
continue;
}
// Create the DbSchema and MySqlListViewNode objects
var schemaObject = new DbSchema(_wbConnection, schemaName, row["DEFAULT_CHARACTER_SET_NAME"].ToString(), row["DEFAULT_COLLATION_NAME"].ToString(), DisplaySchemaCollationsToolStripMenuItem.Checked);
string lcSchemaName = schemaName.ToLowerInvariant();
var headerNode = SchemasList.HeaderNodes[_systemSchemasListValues.Contains(lcSchemaName) ? 1 : 0];
LoadedSchemas.Add(schemaObject);
var node = SchemasList.AddDbObjectNode(headerNode, schemaObject);
node.ImageIndex = DisplaySchemaCollationsToolStripMenuItem.Checked ? 1 : 0;
}
if (SchemasList.Nodes[0].GetNodeCount(true) > 0)
{
SchemasList.Nodes[0].Expand();
}
// Avoids flickering of schemas list while adding the items to it.
SchemasList.EndUpdate();
return(true);
}
catch (Exception ex)
{
MiscUtilities.ShowCustomizedErrorDialog(Resources.SchemasLoadingErrorTitle, ex.Message, true);
MySqlSourceTrace.WriteAppErrorToLog(ex);
return(false);
}
}
/// <summary>
/// Event delegate method fired when the <see cref="RenameMappingButton"/> button is clicked.
/// </summary>
/// <param name="sender">Sender object.</param>
/// <param name="e">Event arguments.</param>
private void RenameMappingButton_Click(object sender, EventArgs e)
{
if (_selectedMapping == null)
{
return;
}
int indexForName = 1;
string proposedMappingName;
do
{
proposedMappingName = _selectedMapping.TableName + "Mapping" + (indexForName > 1 ? indexForName.ToString(CultureInfo.InvariantCulture) : string.Empty);
indexForName++;
}while (Mappings.Any(mapping => mapping.Name == proposedMappingName));
string newName;
using (var newColumnMappingDialog = new AppendNewColumnMappingDialog(proposedMappingName))
{
DialogResult dr = newColumnMappingDialog.ShowDialog();
if (dr == DialogResult.Cancel)
{
return;
}
MappingsChanged = true;
newName = newColumnMappingDialog.ColumnMappingName;
}
// Show error if name already exists
if (Mappings.Count(t => string.Compare(t.Name, newName, StringComparison.InvariantCultureIgnoreCase) == 0) > 0)
{
MiscUtilities.ShowCustomizedErrorDialog(Resources.MappingNameAlreadyExistsTitle, Resources.MappingNameAlreadyExistsDetail);
return;
}
_selectedMapping.Name = newName;
RefreshMappingList();
ListViewItem item = MappingsListView.FindItemWithText(string.Format("{0} ({1}.{2})", newName, _selectedMapping.SchemaName, _selectedMapping.TableName));
if (item != null)
{
MappingsListView.Items[item.Index].Selected = true;
}
MappingsListView.Focus();
}
/// <summary>
/// Event delegate method fired when the <see cref="RenameMappingButton"/> button is clicked.
/// </summary>
/// <param name="sender">Sender object.</param>
/// <param name="e">Event arguments.</param>
private void RenameMappingButton_Click(object sender, EventArgs e)
{
if (_selectedMapping == null)
{
return;
}
string newName;
using (var newColumnMappingDialog = new AppendNewColumnMappingDialog(Mappings, _selectedMapping.TableName))
{
var dr = newColumnMappingDialog.ShowDialog();
if (dr == DialogResult.Cancel)
{
return;
}
newName = newColumnMappingDialog.ColumnMappingName;
if (string.IsNullOrEmpty(newName))
{
// Should not get to this part of the code, if this is reached it means there is an issue in the logic above generating a mapping name.
MiscUtilities.ShowCustomizedErrorDialog("Error", Resources.AppendColumnMappingEmptyError);
return;
}
if (string.Equals(_selectedMapping.Name, newName, StringComparison.OrdinalIgnoreCase))
{
// User input exactly the same name, so no renaming was actually done.
return;
}
MappingsChanged = true;
}
_selectedMapping.Name = newName;
RefreshMappingList();
var item = MappingsListView.FindItemWithText($"{newName} ({_selectedMapping.SchemaName}.{_selectedMapping.TableName})");
if (item != null)
{
MappingsListView.Items[item.Index].Selected = true;
}
MappingsListView.Focus();
}
/// <summary>
/// Event delegate method fired when a key is pressed that triggers the search.
/// </summary>
/// <param name="sender">Sender object.</param>
/// <param name="e">Event arguments.</param>
private void DBObjectsFilter_SearchFired(object sender, EventArgs e)
{
var searchBox = sender as SearchEdit;
if (searchBox == null)
{
return;
}
Filter = DBObjectsFilter.Text.ToUpper();
try
{
RefreshDbObjectsList(false);
}
catch (Exception ex)
{
MiscUtilities.ShowCustomizedErrorDialog(ex.Message);
MySqlSourceTrace.WriteAppErrorToLog(ex);
}
}
/// <summary>
/// Creates the list of original statements this script contains before the user makes any changes.
/// </summary>
private void CreateOriginalStatementsList()
{
if (_originalStatementRowsList == null)
{
_originalStatementRowsList = new List <IMySqlDataRow>();
}
if (_mySqlTable != null)
{
_createdTable = false;
_lockedTable = false;
_originalStatementRowsList.Clear();
bool isForExport = _mySqlTable.OperationType.IsForExport();
bool isForAppend = _mySqlTable.OperationType.IsForAppend();
bool createTableOnly = isForExport && _mySqlTable.CreateTableWithoutData;
if (!createTableOnly && _mySqlTable.ChangedOrDeletedRows == 0)
{
return;
}
try
{
// Calculate the StringBuilder initial length to avoid its size to be internally doubled each time an Append to it is done to increase performance.
int builderLength = createTableOnly ? MiscUtilities.STRING_BUILDER_DEFAULT_CAPACITY : _mySqlTable.MaxQueryLength;
var sqlScript = new StringBuilder(builderLength);
IList <MySqlDummyRow> dummyRows;
bool createTableDummyRows = isForExport || isForAppend;
if (createTableDummyRows)
{
// Create optimization statements for INSERTS that disable key constraints and lock table.
// Also incluldes a CREATE statement if table on Export mode.
dummyRows = _mySqlTable.GetDummyRowsForTableCreationAndIndexOptimization(true);
if (dummyRows != null)
{
foreach (var dummyRow in dummyRows)
{
if (dummyRow.Statement.StatementType == MySqlStatement.SqlStatementType.CreateTable)
{
_createdTable = true;
}
if (dummyRow.Statement.StatementType == MySqlStatement.SqlStatementType.LockTables)
{
_lockedTable = true;
}
_originalStatementRowsList.Add(dummyRow);
sqlScript.AppendFormat("{0};{1}", dummyRow.Statement.SqlQuery, Environment.NewLine);
}
}
}
// Create DELETE, INSERT and UPDATE statements for table rows
// Do not change this code to get changed rows via the GetChanges method since the references to the MySqlDataTable and MySqlDataTable objects will be broken.
if (!createTableOnly)
{
bool isBulkInsert = (isForExport && !Settings.Default.ExportGenerateMultipleInserts) ||
(isForAppend && !Settings.Default.AppendGenerateMultipleInserts);
if (isBulkInsert)
{
// Generate INSERT statements as a bulk operations for all rows being processed
// Maybe more than 1 INSERT statement is generated depending on the size of the data and the value of the max allowed packet.
dummyRows = _mySqlTable.GetBulkInsertDummyRows();
foreach (var dummyRow in dummyRows)
{
_originalStatementRowsList.Add(dummyRow);
sqlScript.AppendFormat("{0};{1}", dummyRow.Statement.SqlQuery, Environment.NewLine);
}
}
else
{
// Generate a statement for each data row being processed
DataRowState[] rowStatesWithChanges = { DataRowState.Deleted, DataRowState.Added, DataRowState.Modified };
foreach (var mySqlRow in rowStatesWithChanges.SelectMany(rowState => _mySqlTable.Rows.Cast <MySqlDataRow>().Where(dr => !dr.IsHeadersRow && dr.RowState == rowState)))
{
_originalStatementRowsList.Add(mySqlRow);
string mainSqlQuery = mySqlRow.Statement.SqlQuery;
if (!string.IsNullOrEmpty(mySqlRow.Statement.SetVariablesSqlQuery))
{
sqlScript.AppendFormat("{0};{1}", mySqlRow.Statement.SetVariablesSqlQuery, Environment.NewLine);
}
sqlScript.AppendFormat("{0};{1}", mainSqlQuery, Environment.NewLine);
}
}
}
// Create optimization statements for INSERTS that re-enable key constraints and unlock table.
if (createTableDummyRows)
{
dummyRows = _mySqlTable.GetDummyRowsForTableCreationAndIndexOptimization(false);
if (dummyRows != null)
{
foreach (var dummyRow in dummyRows)
{
_originalStatementRowsList.Add(dummyRow);
sqlScript.AppendFormat("{0};{1}", dummyRow.Statement.SqlQuery, Environment.NewLine);
}
}
}
OriginalSqlScript = sqlScript.ToString();
}
catch (OutOfMemoryException oomEx)
{
MySqlSourceTrace.WriteAppErrorToLog(oomEx);
MiscUtilities.ShowCustomizedErrorDialog(Resources.SqlScriptTooBigErrorDetail, oomEx.Message, true);
}
}
else if (!string.IsNullOrEmpty(OriginalSqlScript) && _originalStatementRowsList.Count == 0)
{
var statementsList = SqlScript.SplitInSqlStatements();
if (statementsList == null)
{
return;
}
foreach (var statementText in statementsList.Where(statementText => !string.IsNullOrEmpty(statementText)))
{
_originalStatementRowsList.Add(new MySqlDummyRow(statementText));
}
}
}
/// <summary>
/// Event delegate method fired when the <see cref="CallButton"/> button is clicked.
/// </summary>
/// <param name="sender">Sender object.</param>
/// <param name="e">Event arguments.</param>
private void CallButton_Click(object sender, EventArgs e)
{
Cursor = Cursors.WaitCursor;
try
{
if (_dbProcedure.Parameters == null)
{
_dbProcedure.InitializeParameters();
}
// Fill procedure parameter values
for (int paramIdx = 0; paramIdx < _procedureParamsProperties.Count; paramIdx++)
{
var parameter = _dbProcedure.Parameters[paramIdx].Item2;
parameter.Value = _procedureParamsProperties[paramIdx].Value;
}
// Call stored procedure
_importDataSet = _dbProcedure.Execute();
if (_importDataSet == null || _importDataSet.Tables.Count == 0)
{
ImportButton.Enabled = false;
return;
}
// Refresh output/return parameter values in PropertyGrid
for (int paramIdx = 0; paramIdx < _procedureParamsProperties.Count; paramIdx++)
{
var parameter = _dbProcedure.Parameters[paramIdx].Item2;
if (!parameter.IsReadOnly())
{
continue;
}
_procedureParamsProperties[paramIdx].Value = parameter.Value;
}
ParametersPropertyGrid.Refresh();
// Prepare Preview DataSet to show it on Grids
_previewDataSet = _importDataSet.Clone();
int resultSetsRowSum = 0;
for (int tableIdx = 0; tableIdx < _importDataSet.Tables.Count; tableIdx++)
{
resultSetsRowSum += _importDataSet.Tables[tableIdx].Rows.Count;
if (_workbookInCompatibilityMode)
{
_sumOfResultSetsExceedsMaxCompatibilityRows = _sumOfResultSetsExceedsMaxCompatibilityRows || resultSetsRowSum > UInt16.MaxValue;
}
int limitRows = Math.Min(_importDataSet.Tables[tableIdx].Rows.Count, Settings.Default.ImportPreviewRowsQuantity);
for (int rowIdx = 0; rowIdx < limitRows; rowIdx++)
{
_previewDataSet.Tables[tableIdx].ImportRow(_importDataSet.Tables[tableIdx].Rows[rowIdx]);
}
}
// Refresh ResultSets in Tab Control
ResultSetsDataGridView.DataSource = null;
ResultSetsTabControl.TabPages.Clear();
for (int dtIdx = 0; dtIdx < _importDataSet.Tables.Count; dtIdx++)
{
ResultSetsTabControl.TabPages.Add(_importDataSet.Tables[dtIdx].TableName);
}
if (ResultSetsTabControl.TabPages.Count > 0)
{
_selectedResultSetIndex = ResultSetsTabControl.SelectedIndex = 0;
ResultSetsTabControl_SelectedIndexChanged(ResultSetsTabControl, EventArgs.Empty);
}
}
catch (Exception ex)
{
MiscUtilities.ShowCustomizedErrorDialog(Resources.ImportProcedureErrorTitle, ex.Message, true);
MySqlSourceTrace.WriteAppErrorToLog(ex);
}
finally
{
ImportButton.Enabled = true;
Cursor = Cursors.Default;
}
}
/// <summary>
/// Event delegate method fired when any value in a cell within the <see cref="EditingWorksheet"/> changes.
/// </summary>
/// <remarks>
/// This method is used to record any changes done by users to the data and prepare corresponding changes within a data table object
/// that later will generate queries to commit the data changes to the MySQL server.
/// </remarks>
/// <param name="target"></param>
private void EditingWorksheet_Change(ExcelInterop.Range target)
{
if (Globals.ThisAddIn.SkipWorksheetChangeEvent)
{
return;
}
var rowWasDeleted = EditingWorksheet.UsedRange.Rows.Count < _editingRowsQuantity && target.Columns.Count == EditingWorksheet.Columns.Count;
var undoChanges = false;
string operationSummary = null;
string operationDetails = null;
var intersectRange = _editDataRange.IntersectWith(target);
if (intersectRange == null || intersectRange.CountLarge == 0)
{
undoChanges = true;
if (rowWasDeleted)
{
// The row for insertions is attempted to be deleted, we need to undo
operationSummary = Resources.EditDataDeleteLastRowNotPermittedErrorTitle;
operationDetails = Resources.EditDataDeleteLastRowNotPermittedErrorDetail;
}
else
{
// It is a modification and outside the permitted range
operationSummary = Resources.EditDataOutsideEditingRangeNotPermittedErrorTitle;
operationDetails = Resources.EditDataOutsideEditingRangeNotPermittedErrorDetail;
}
}
if (undoChanges)
{
MiscUtilities.ShowCustomizedErrorDialog(operationSummary, operationDetails, true);
UndoChanges();
if (!rowWasDeleted)
{
return;
}
_editDataRange = EditingWorksheet.UsedRange;
return;
}
if (intersectRange.Item[1, 1] is ExcelInterop.Range startCell)
{
// Subtract from the Excel indexes since they start at 1, ExcelRow is subtracted by 2 if we imported headers.
var startDataTableRow = startCell.Row - 2;
var startDataTableCol = startCell.Column - 1;
// Detect if a row was deleted and if so flag it for deletion
if (rowWasDeleted)
{
var skipDeletedRowsList = new List <int>();
foreach (ExcelInterop.Range deletedRow in target.Rows)
{
startDataTableRow = deletedRow.Row - 2;
startDataTableRow = _mySqlTable.SearchRowIndexNotDeleted(startDataTableRow, skipDeletedRowsList, _editDataRange.Rows.Count);
var dr = _mySqlTable.Rows[startDataTableRow];
dr.Delete();
skipDeletedRowsList.Add(startDataTableRow);
}
_editingRowsQuantity = _editDataRange.Rows.Count;
}
else
{
// The change was a modification of cell values
MySqlDataColumn currCol = null;
try
{
for (var rowIdx = 1; rowIdx <= intersectRange.Rows.Count; rowIdx++)
{
for (var colIdx = 1; colIdx <= intersectRange.Columns.Count; colIdx++)
{
ExcelInterop.Range cell = intersectRange.Cells[rowIdx, colIdx];
if (cell == null)
{
continue;
}
// Detect if a data row has been added by the user and if so flag it for addition
if (cell.Row == _editDataRange.Rows.Count)
{
if (cell.Value == null)
{
continue;
}
var insertingRowRange = AddNewRowToEditingRange(true);
if (_mySqlTable.NewRow() is MySqlDataRow newRow)
{
newRow.ExcelRange = insertingRowRange;
_mySqlTable.Rows.Add(newRow);
}
}
var absRow = startDataTableRow + rowIdx - 1;
absRow = _mySqlTable.SearchRowIndexNotDeleted(absRow, null, _editDataRange.Rows.Count);
var absCol = startDataTableCol + colIdx - 1;
currCol = _mySqlTable.GetColumnAtIndex(absCol);
var cellValue = cell.GetCellPackedValue(true);
object insertingValue = DBNull.Value;
if (cellValue != null)
{
insertingValue = currCol.GetInsertingValueForType(cellValue, false);
}
if (insertingValue == null)
{
_mySqlTable.Rows[absRow][absCol] = DBNull.Value;
if (!(cellValue is DateTime))
{
continue;
}
Globals.ThisAddIn.SkipWorksheetChangeEvent = true;
cellValue = null;
Globals.ThisAddIn.SkipWorksheetChangeEvent = false;
}
else
{
_mySqlTable.Rows[absRow][absCol] = insertingValue;
}
}
}
}
catch (ArgumentException argEx)
{
undoChanges = true;
operationSummary = string.Format(Resources.EditDataInvalidValueError, currCol != null ? currCol.MySqlDataType.FullType : "Unknown");
operationDetails = argEx.Message;
}
catch (Exception ex)
{
undoChanges = true;
operationSummary = Resources.EditDataCellModificationError;
operationDetails = ex.Message;
Logger.LogException(ex);
}
finally
{
if (undoChanges)
{
MiscUtilities.ShowCustomizedErrorDialog(operationSummary, operationDetails, true);
UndoChanges();
}
}
}
}
CommitChangesButton.Enabled = !AutoCommitCheckBox.Checked && UncommittedDataExists;
if (AutoCommitCheckBox.Checked && UncommittedDataExists)
{
PushDataChanges();
}
}
/// <summary>
/// Opens an <see cref="EditConnectionInfo"/> for a MySQL table.
/// </summary>
/// <param name="tableObject">Table to start an editing for.</param>
/// <param name="fromSavedConnectionInfo">Flag indicating whether the <see cref="EditConnectionInfo"/> to be opened corresponds.</param>
/// <param name="workbook">The workbook.</param>
/// <returns><c>true</c> If the export/append action was executed, <c>false</c> otherwise.</returns>
public bool EditTableData(DbTable tableObject, bool fromSavedConnectionInfo, ExcelInterop.Workbook workbook)
{
if (tableObject == null)
{
return(false);
}
var schemaAndTableNames = WbConnection.Schema + "." + tableObject.Name;
// Check if the current DB object has an edit ongoing
if (TableHasEditOnGoing(tableObject.Name))
{
// Display an error since there is an ongoing Editing operation and return
InfoDialog.ShowDialog(InfoDialogProperties.GetErrorDialogProperties(Resources.TaskPaneEditingNotPossibleTitleText, string.Format(Resources.TableWithOperationOngoingError, schemaAndTableNames)));
return(false);
}
// Preview the table's data in case the user option for that is on
if (!fromSavedConnectionInfo && Settings.Default.EditPreviewMySqlData)
{
using (var previewDataDialog = new PreviewTableViewDialog(tableObject, true))
{
if (previewDataDialog.ShowDialog() == DialogResult.Cancel)
{
return(false);
}
}
}
// Check if selected Table has a Primary Key, it it does not we prompt an error and exit since Editing on such table is not permitted
if (!WbConnection.TableHasPrimaryKey(tableObject.Name))
{
InfoDialog.ShowDialog(InfoDialogProperties.GetErrorDialogProperties(Resources.EditOpenSatusError, Resources.EditOpenSummaryError, Resources.EditOpenDetailsError));
return(false);
}
// Attempt to Import Data unless the user cancels the import operation
var proposedWorksheetName = fromSavedConnectionInfo ? tableObject.Name : Globals.ThisAddIn.ActiveWorkbook.GetWorksheetNameAvoidingDuplicates(tableObject.Name);
tableObject.ImportParameters.ForEditDataOperation = true;
MySqlDataTable mySqlTable;
using (var importForm = new ImportTableViewForm(tableObject, proposedWorksheetName))
{
if (importForm.ImportHidingDialog() == DialogResult.Cancel)
{
return(false);
}
mySqlTable = importForm.MySqlTable;
}
if (mySqlTable == null || mySqlTable.Columns.Count == 0)
{
MiscUtilities.ShowCustomizedErrorDialog(string.Format(Resources.UnableToRetrieveData, tableObject.Name));
return(false);
}
var activeWorkbookEditConnectionInfos = WorkbookConnectionInfos.GetWorkbookEditConnectionInfos(Globals.ThisAddIn.ActiveWorkbook);
// Hide all other open EditDataDialog forms before opening a new one.
if (!fromSavedConnectionInfo)
{
foreach (var connectionInfo in activeWorkbookEditConnectionInfos.Where(connectionInfo => connectionInfo.EditDialog != null && connectionInfo.EditDialog.Visible))
{
connectionInfo.EditDialog.Hide();
}
}
// Create the new Excel Worksheet and import the editing data there
var editWorkbook = fromSavedConnectionInfo && workbook != null ? workbook : Globals.ThisAddIn.ActiveWorkbook;
var currentWorksheet = fromSavedConnectionInfo && Settings.Default.EditSessionsReuseWorksheets
? editWorkbook.GetOrCreateWorksheet(proposedWorksheetName, true)
: editWorkbook.CreateWorksheet(proposedWorksheetName, true);
if (currentWorksheet == null)
{
return(false);
}
// Clear the contents of the worksheet if we are restoring a saved <see cref="EditConnectionInfo"/> since the user may have input data into it.
if (fromSavedConnectionInfo)
{
currentWorksheet.UsedRange.Clear();
}
// Create and show the Edit Data Dialog
var editConnectionInfo = GetEditConnectionInfo(mySqlTable, currentWorksheet);
ActiveEditDialog = editConnectionInfo.EditDialog;
if (fromSavedConnectionInfo)
{
// If restoring EditConnectionInfo objects we need to create and link their corresponding EditDialog to it.
var editConnectionInfoBeingRestored = activeWorkbookEditConnectionInfos.FirstOrDefault(connectionInfo => connectionInfo.TableName.Equals(editConnectionInfo.TableName));
if (editConnectionInfoBeingRestored != null)
{
editConnectionInfoBeingRestored.EditDialog = editConnectionInfo.EditDialog;
}
}
else
{
ActiveEditDialog.ShowDialog();
// If not restoring EditConnectionInfo objects we need to add the manually triggered EditConnectionInfo to the list of the active workbook.
activeWorkbookEditConnectionInfos.Add(editConnectionInfo);
}
return(true);
}
/// <summary>
/// Refreshes the DB objects list control with current objects in the connected schema.
/// </summary>
/// <param name="reloadFromServer">Flag indicating whether DB objects must be reloaded from the connected MySQL server.</param>
private bool RefreshDbObjectsList(bool reloadFromServer)
{
if (DBObjectList.HeaderNodes.Count < 3)
{
return(false);
}
bool success = true;
try
{
// Avoids flickering of DB Objects lists while adding the items to it.
DBObjectList.BeginUpdate();
DBObjectList.ClearChildNodes();
if (reloadFromServer)
{
LoadTables();
LoadViews();
LoadProcedures();
}
// Refresh Tables
foreach (var dbTable in LoadedTables.Where(table => string.IsNullOrEmpty(Filter) || table.Name.ToUpper().Contains(Filter)))
{
var node = DBObjectList.AddDbObjectNode(DBObjectList.HeaderNodes[0], dbTable);
dbTable.Selected = false;
node.ImageIndex = 0;
}
// Refresh Views
foreach (var dbView in LoadedViews.Where(view => string.IsNullOrEmpty(Filter) || view.Name.ToUpper().Contains(Filter)))
{
var node = DBObjectList.AddDbObjectNode(DBObjectList.HeaderNodes[1], dbView);
dbView.Selected = false;
node.ImageIndex = 1;
}
// Refresh Procedures
foreach (var dbProcedure in LoadedProcedures.Where(procedure => string.IsNullOrEmpty(Filter) || procedure.Name.ToUpper().Contains(Filter)))
{
var node = DBObjectList.AddDbObjectNode(DBObjectList.HeaderNodes[2], dbProcedure);
dbProcedure.Selected = false;
node.ImageIndex = 2;
}
DBObjectList.ExpandAll();
DBObjectList.Nodes[0].EnsureVisible();
// Avoids flickering of DB Objects lists while adding the items to it.
DBObjectList.EndUpdate();
DBObjectList_AfterSelect(null, null);
}
catch (Exception ex)
{
success = false;
MiscUtilities.ShowCustomizedErrorDialog(Resources.RefreshDBObjectsErrorTitle, ex.Message, true);
MySqlSourceTrace.WriteAppErrorToLog(ex);
}
return(success);
}
/// <summary>
/// Event delegate method fired when <see cref="ImportDataHotLabel"/> is clicked.
/// </summary>
/// <param name="sender">Sender object.</param>
/// <param name="e">Event arguments.</param>
private void ImportDataHotLabel_Click(object sender, EventArgs e)
{
var selectedNode = DBObjectList.SelectedNode;
var parentTaskPane = Parent as ExcelAddInPane;
if (selectedNode == null || parentTaskPane == null || selectedNode.Type != MySqlListViewNode.MySqlNodeType.DbObject || selectedNode.DbObject == null || _wbConnection == null)
{
return;
}
var passwordFlags = _wbConnection.TestConnectionAndRetryOnWrongPassword();
if (!passwordFlags.ConnectionSuccess)
{
return;
}
if (parentTaskPane.ActiveWorksheetInEditMode)
{
var dr = MiscUtilities.ShowCustomizedWarningDialog(Resources.WorksheetInEditModeWarningTitle, Resources.WorksheetInEditModeWarningDetail);
if (dr != DialogResult.Yes)
{
return;
}
var newWorksheet = Globals.ThisAddIn.ActiveWorkbook.CreateWorksheet(selectedNode.DbObject.Name, true);
if (newWorksheet == null)
{
return;
}
}
try
{
DialogResult dr = DialogResult.Cancel;
Cursor = Cursors.WaitCursor;
var activeWorkbook = Globals.ThisAddIn.ActiveWorkbook;
if (selectedNode.DbObject is DbTable)
{
var dbTable = selectedNode.DbObject as DbTable;
dbTable.ImportParameters.ForEditDataOperation = false;
using (var importForm = new ImportTableViewForm(dbTable, activeWorkbook.ActiveSheet.Name))
{
dr = importForm.ShowDialog();
}
}
else if (selectedNode.DbObject is DbView)
{
var dbView = selectedNode.DbObject as DbView;
dbView.ImportParameters.ForEditDataOperation = false;
using (var importForm = new ImportTableViewForm(dbView, activeWorkbook.ActiveSheet.Name))
{
dr = importForm.ShowDialog();
}
}
else if (selectedNode.DbObject is DbProcedure)
{
using (var importProcedureForm = new ImportProcedureForm(selectedNode.DbObject as DbProcedure, parentTaskPane.ActiveWorksheet.Name))
{
dr = importProcedureForm.ShowDialog();
}
}
if (dr == DialogResult.OK)
{
RefreshActionLabelsEnabledStatus(null, false);
}
}
catch (Exception ex)
{
MiscUtilities.ShowCustomizedErrorDialog(Resources.ImportDataErrorTitle, ex.Message, true);
MySqlSourceTrace.WriteAppErrorToLog(ex);
}
finally
{
Cursor = Cursors.Default;
}
}
