/// <summary>
/// Creates the <see cref="EditConnectionInfo"/> or restores the saved one.
/// </summary>
/// <param name="mySqlTable">The <see cref="MySqlDataTable"/> used for the Edit Data session.</param>
/// <param name="currentWorksheet">The current worksheet.</param>
/// <returns>A new or restored <see cref="EditConnectionInfo"/> object.</returns>
private EditConnectionInfo GetEditConnectionInfo(MySqlDataTable mySqlTable, ExcelInterop.Worksheet currentWorksheet)
{
if (mySqlTable == null || currentWorksheet == null)
{
return(null);
}
var atCell = currentWorksheet.Range["A1", Type.Missing];
var editingRange = mySqlTable.ImportDataIntoExcelRange(atCell);
EditConnectionInfo connectionInfo = null;
var workbookEditConnectionInfos = WorkbookConnectionInfos.GetWorkbookEditConnectionInfos(Globals.ThisAddIn.ActiveWorkbook);
if (workbookEditConnectionInfos.Count > 0)
{
connectionInfo = workbookEditConnectionInfos.GetActiveEditConnectionInfo(mySqlTable.TableName);
}
// The EditConnectionInfo is new and has to be created from scratch.
if (connectionInfo == null)
{
var activeWorkbook = Globals.ThisAddIn.ActiveWorkbook;
connectionInfo = new EditConnectionInfo(activeWorkbook.GetOrCreateId(), activeWorkbook.FullName, WbConnection.Id, WbConnection.Schema, mySqlTable.TableName);
}
if (connectionInfo.EditDialog != null)
{
return(connectionInfo);
}
// The EditConnectionInfo is being either restored from the settings file or created for the newborn object.
connectionInfo.EditDialog = new EditDataDialog(this, new NativeWindowWrapper(Globals.ThisAddIn.Application.Hwnd), WbConnection, editingRange, mySqlTable, currentWorksheet);
currentWorksheet.StoreProtectionKey(connectionInfo.EditDialog.WorksheetProtectionKey);
return(connectionInfo);
}
/// <summary>
/// Initializes a new instance of the <see cref="ImportConnectionInfo" /> class.
/// </summary>
/// <param name="mySqlTable">MySqlDataTable object related to the <see cref="ImportConnectionInfo" />.</param>
/// <param name="atCell">The top left Excel cell of the new <see cref="ExcelInterop.ListObject"/>.</param>
/// <param name="addSummaryRow">Flag indicating whether to include a row with summary fields at the end of the data rows.</param>
public ImportConnectionInfo(MySqlDataTable mySqlTable, ExcelInterop.Range atCell, bool addSummaryRow)
: this()
{
if (mySqlTable == null)
{
throw new ArgumentNullException(nameof(mySqlTable));
}
_connection = mySqlTable.WbConnection;
MySqlTable = mySqlTable;
SchemaName = mySqlTable.SchemaName;
TableName = mySqlTable.TableName;
ConnectionId = mySqlTable.WbConnection.Id;
ImportColumnNames = mySqlTable.ImportColumnNames;
OperationType = mySqlTable.OperationType;
ProcedureResultSetIndex = mySqlTable.ProcedureResultSetIndex;
SelectQuery = mySqlTable.SelectQuery;
var activeWorkbook = Globals.ThisAddIn.ActiveWorkbook;
WorkbookGuid = activeWorkbook.GetOrCreateId();
WorkbookName = activeWorkbook.Name;
WorkbookFilePath = activeWorkbook.FullName;
ExcelInterop.Worksheet worksheet = activeWorkbook.ActiveSheet;
WorksheetName = worksheet.Name;
InitializeConnectionObjects(atCell, addSummaryRow);
}
/// <summary>
/// Initializes a new instance of the <see cref="EditDataDialog"/> class.
/// </summary>
/// <param name="parentTaskPane">The <see cref="ExcelAddInPane"/> from which the <see cref="EditDataDialog"/> is called.</param>
/// <param name="parentWindow">The parent window assigned to the <see cref="EditDataDialog"/> to be opened as a dialog.</param>
/// <param name="wbConnection">The connection to a MySQL server instance selected by users.</param>
/// <param name="originalEditDataRange">The Excel cells range containing the MySQL table's data being edited.</param>
/// <param name="importTable">The table containing the data imported from the MySQL table that will be edited.</param>
/// <param name="editingWorksheet">The Excel worksheet tied to the current editing session.</param>
public EditDataDialog(ExcelAddInPane parentTaskPane, IWin32Window parentWindow, MySqlWorkbenchConnection wbConnection, ExcelInterop.Range originalEditDataRange, MySqlDataTable importTable, ExcelInterop.Worksheet editingWorksheet)
{
_mouseDownPoint = Point.Empty;
_neverBeenShown = true;
_updatingUSeOptimisticUpdateSetting = false;
InitializeComponent();
var existingProtectionKey = editingWorksheet.GetProtectionKey();
WorksheetProtectionKey = string.IsNullOrEmpty(existingProtectionKey) ? Guid.NewGuid().ToString() : existingProtectionKey;
_parentTaskPane = parentTaskPane;
_parentWindow = parentWindow;
_wbConnection = wbConnection;
_editDataRange = originalEditDataRange;
_mySqlTable = importTable;
EditingWorksheet = editingWorksheet;
EditingWorksheet.SelectionChange += EditingWorksheet_SelectionChange;
ResetToolTip();
Opacity = 0.60;
AddNewRowToEditingRange(false);
_useOptimisticUpdateForThisSession = Settings.Default.EditUseOptimisticUpdate;
ForThisSessionToolStripMenuItem.Checked = _useOptimisticUpdateForThisSession;
ForAllSessionsToolStripMenuItem.Checked = _useOptimisticUpdateForThisSession;
UseOptimisticUpdateToolStripMenuItem.Checked = _useOptimisticUpdateForThisSession;
Settings.Default.PropertyChanged += SettingsPropertyValueChanged;
}
/// <summary>
/// Executes the given procedure and returns its result sets in tables within a <see cref="DataSet"/> object.
/// </summary>
/// <remarks>Only works against Procedures, but not with Tables or Views.</remarks>
/// <returns><see cref="DataSet"/> where each table within it represents each of the result sets returned by the stored procedure.</returns>
public DataSet Execute()
{
if (Parameters == null)
{
InitializeParameters();
}
if (Parameters == null)
{
return(null);
}
var sql = $"`{Connection.Schema}`.`{Name}`";
var resultSetDs = Connection.ExecuteRoutine(sql, Parameters.Select(tuple => tuple.Item2).ToArray());
if (resultSetDs == null || resultSetDs.Tables.Count == 0)
{
return(null);
}
// Create result set data set and MySqlDataTable tables for each table in the result sets
var returnDataSet = new DataSet(Name + "ResultSet");
var procedureSql = GetSql();
var resultIndex = 1;
foreach (DataTable table in resultSetDs.Tables)
{
table.TableName = $"Result{resultIndex}";
var mySqlDataTable = new MySqlDataTable(Connection, table, procedureSql, resultIndex - 1);
returnDataSet.Tables.Add(mySqlDataTable);
resultIndex++;
}
if (ReadOnlyParameters == null || ReadOnlyParameters.Count <= 0)
{
return(returnDataSet);
}
// Create a table containing output parameters and return values
var outParamsTable = new DataTable(OUT_AND_RETURN_VALUES_TABLE_NAME);
foreach (var readOnlyTuple in ReadOnlyParameters)
{
var dataType = readOnlyTuple.Item1;
var parameter = readOnlyTuple.Item2;
outParamsTable.Columns.Add(new MySqlDataColumn(parameter.ParameterName, dataType, true));
}
// Add output/return parameter values to OutAndReturnValues table
var valuesRow = outParamsTable.NewRow();
valuesRow.ItemArray = ReadOnlyParameters.Select(tuple => tuple.Item2.Value).ToArray();
outParamsTable.Rows.Add(valuesRow);
var outParamsMySqlTable = new MySqlDataTable(Connection, outParamsTable, procedureSql, resultIndex - 1);
returnDataSet.Tables.Add(outParamsMySqlTable);
return(returnDataSet);
}
/// <summary>
/// Checks if the <see cref="ExcelInterop.Range"/> where the data of this <see cref="DbObject"/> is imported would collide with another Excel object.
/// </summary>
/// <param name="mySqlTable">A <see cref="MySqlDataTable"/> filled with data for this <see cref="DbObject"/>.</param>
/// <returns><c>true</c> if the <see cref="ExcelInterop.Range"/> where the data of this <see cref="DbObject"/> is imported would collide with another Excel object, <c>false</c> otherwise.</returns>
private bool DetectDataForImportPossibleCollisions(MySqlDataTable mySqlTable)
{
if (mySqlTable == null)
{
return(false);
}
var atCell = Globals.ThisAddIn.Application.ActiveCell;
var ranges = mySqlTable.GetExcelRangesToOccupy(atCell, ImportParameters.AddSummaryRow, ImportParameters.CreatePivotTable);
return(ranges != null && ranges.Aggregate(false, (current, range) => current || range.IntersectsWithAnyExcelObject()));
}
/// <summary>
/// Initializes a new instance of the DataRow. Constructs a row from the builder.
/// </summary>
/// <remarks>Only for internal usage.</remarks>
/// <param name="builder">A <see cref="DataRowBuilder"/> to construct the row.</param>
protected internal MySqlDataRow(DataRowBuilder builder) : base(builder)
{
_excelRange = null;
_mySqlTable = null;
_refreshingData = false;
_setVariablesSql = null;
_sqlQuery = null;
ChangedColumnNames = new List <string>(Table.Columns.Count);
IsBeingDeleted = false;
IsHeadersRow = false;
ExcelModifiedRangesList = new List <ExcelInterop.Range>(Table.Columns.Count);
Statement = new MySqlStatement(this);
}
/// <summary>
/// Creates the import my SQL table.
/// </summary>
/// <param name="wbConnection">The wb connection.</param>
/// <param name="operationType">The <see cref="MySqlDataTable.DataOperationType"/> intended for the new <see cref="MySqlDataTable"/>.</param>
/// <param name="tableOrViewName">The name of the MySQL table or view to import data from..</param>
/// <param name="importColumnNames">Flag indicating if column names will be imported as the first row of imported data.</param>
/// <param name="selectQuery">A SELECT query against a database object to fill the [MySqlDataTable] return object with.</param>
/// <param name="procedureResultSetIndex">The index of the result set of a stored procedure this table contains data for.</param>
/// <returns>MySql Table created from the selectQuery.</returns>
public static MySqlDataTable CreateImportMySqlTable(this MySqlWorkbenchConnection wbConnection, MySqlDataTable.DataOperationType operationType, string tableOrViewName, bool importColumnNames, string selectQuery, int procedureResultSetIndex = 0)
{
DataTable dt = GetDataFromSelectQuery(wbConnection, selectQuery);
if (dt == null)
{
MySqlSourceTrace.WriteToLog(string.Format(Resources.SelectQueryReturnedNothing, selectQuery));
return(null);
}
var importMySqlDataTable = new MySqlDataTable(wbConnection, tableOrViewName, dt, operationType, selectQuery)
{
ImportColumnNames = importColumnNames,
ProcedureResultSetIndex = procedureResultSetIndex
};
return(importMySqlDataTable);
}
/// <summary>
/// Initializes a new instance of the <see cref="MySqlScriptDialog"/> class.
/// </summary>
private MySqlScriptDialog()
{
_createdTable = false;
_errorDialogSummary = null;
_isUserInput = true;
_lockedTable = false;
_mySqlMaxAllowedPacket = 0;
_mySqlTable = null;
_originalStatementRowsList = null;
_refreshRowsDataAfterPush = false;
_showOriginalOperationsInformation = false;
_useOptimisticUpdate = false;
_userChangedOriginalQuery = false;
_wbConnection = null;
ActualStatementRowsList = null;
ErroredOutDataRow = null;
OriginalSqlScript = null;
ScriptResult = MySqlStatement.StatementResultType.NotApplied;
InitializeComponent();
OriginalQueryButton.Enabled = false;
ResetTextZoom();
}
/// <summary>
/// Initializes a new instance of the <see cref="MySqlScriptDialog"/> class.
/// </summary>
/// <param name="mySqlTable">The <see cref="MySqlDataTable"/> object containing data changes to be committed to the database.</param>
/// <param name="refreshRowsDataAfterPush">Flag indicating whether rows data is refreshed after their push operation is executed.</param>
public MySqlScriptDialog(MySqlDataTable mySqlTable, bool refreshRowsDataAfterPush)
: this()
{
if (mySqlTable != null)
{
switch (mySqlTable.OperationType)
{
case MySqlDataTable.DataOperationType.Export:
_errorDialogSummary = string.Format(Resources.ExportDataGenericErrorText, mySqlTable.TableName);
break;
case MySqlDataTable.DataOperationType.Append:
_errorDialogSummary = string.Format(Resources.AppendDataDetailsDoneErrorText, mySqlTable.TableName);
break;
case MySqlDataTable.DataOperationType.Edit:
_errorDialogSummary = string.Format(Resources.EditedDataForTable, mySqlTable.TableName) + Resources.EditedDataCommittedError;
break;
default:
_errorDialogSummary = Resources.ScriptErrorThrownSummary;
break;
}
_mySqlTable = mySqlTable;
_refreshRowsDataAfterPush = refreshRowsDataAfterPush;
_showOriginalOperationsInformation = true;
_useOptimisticUpdate = _mySqlTable.UseOptimisticUpdate;
_wbConnection = _mySqlTable.WbConnection;
CreateOriginalStatementsList();
SetOriginalOperationsInfoAvailability();
}
SqlScript = OriginalSqlScript;
ApplyButton.Enabled = SqlScript.Trim().Length > 0;
}
/// <summary>
/// Imports the result sets of this stored procedure to a <see cref="ExcelInterop.Worksheet"/>.
/// </summary>
/// <param name="importType">The <see cref="ProcedureResultSetsImportType"/> defining what result sets are imported and how they are laid out in the Excel worksheet. </param>
/// <param name="selectedResultSetIndex">The index of the result set selected for import in case the <see cref="importType"/> is <see cref="ProcedureResultSetsImportType.SelectedResultSet"/>.</param>
/// <param name="resultSetsDataSet">The <see cref="DataSet"/> containing all result sets returned by the stored procedure's execution.</param>
/// <returns><c>true</c> if the import is successful, <c>false</c> otherwise.</returns>
public bool ImportData(ProcedureResultSetsImportType importType, int selectedResultSetIndex, DataSet resultSetsDataSet = null)
{
if (resultSetsDataSet == null)
{
resultSetsDataSet = Execute();
}
var success = true;
try
{
var activeWorkbook = Globals.ThisAddIn.ActiveWorkbook;
// Check if the data being imported does not overlap with the data of an existing Excel table.
if (DetectDataForImportPossibleCollisions(importType, selectedResultSetIndex, resultSetsDataSet))
{
var infoProperties = InfoDialogProperties.GetYesNoDialogProperties(
InfoDialog.InfoType.Warning,
Resources.ImportOverExcelObjectErrorTitle,
Resources.ImportOverExcelObjectErrorDetail,
Resources.ImportOverExcelObjectErrorSubDetail);
if (InfoDialog.ShowDialog(infoProperties).DialogResult == DialogResult.No)
{
return(false);
}
var newWorkSheet = activeWorkbook.CreateWorksheet(Name, true);
if (newWorkSheet == null)
{
return(false);
}
}
var tableIdx = 0;
var createPivotTable = ImportParameters.CreatePivotTable;
var addSummaryRow = ImportParameters.AddSummaryRow;
var nextTopLeftCell = Globals.ThisAddIn.Application.ActiveCell;
foreach (var mySqlTable in resultSetsDataSet.Tables.Cast <MySqlDataTable>().Where(mySqlTable => importType != ProcedureResultSetsImportType.SelectedResultSet || selectedResultSetIndex == tableIdx++))
{
var importingMySqlTable = mySqlTable;
var excludedColumnIndexes = mySqlTable.Columns.Cast <MySqlDataColumn>().Where(mySqlColumn => mySqlColumn.ExcludeColumn).Select(mySqlColumn => mySqlColumn.Ordinal).ToList();
if (excludedColumnIndexes.Count > 0)
{
importingMySqlTable = new MySqlDataTable(Connection, mySqlTable, mySqlTable.SelectQuery, mySqlTable.ProcedureResultSetIndex);
excludedColumnIndexes.Reverse();
foreach (var excludedColumnIndex in excludedColumnIndexes)
{
importingMySqlTable.Columns.RemoveAt(excludedColumnIndex);
}
}
Globals.ThisAddIn.Application.Goto(nextTopLeftCell, false);
importingMySqlTable.ImportColumnNames = ImportParameters.IncludeColumnNames;
importingMySqlTable.TableName = Name + "." + mySqlTable.TableName;
var excelObj = Settings.Default.ImportCreateExcelTable
? importingMySqlTable.ImportDataIntoExcelTable(createPivotTable, ExcelUtilities.PivotTablePosition.Right, addSummaryRow)
: importingMySqlTable.ImportDataIntoExcelRange(createPivotTable, ExcelUtilities.PivotTablePosition.Right, addSummaryRow);
if (excelObj == null)
{
continue;
}
var fillingRange = excelObj is ExcelInterop.ListObject listObject
? listObject.Range
: excelObj as ExcelInterop.Range;
nextTopLeftCell = fillingRange.GetNextResultSetTopLeftCell(importType, createPivotTable);
}
}
catch (Exception ex)
{
success = false;
Logger.LogException(ex, true, string.Format(Resources.UnableToRetrieveData, "procedure", Name));
}
return(success);
}
/// <summary>
/// Initializes a new instance of the <see cref="TableWarningsChangedArgs"/> class.
/// </summary>
/// <param name="table">The <see cref="MySqlDataTable"/> object that contains changes in its warning texts.</param>
/// <param name="autoPkWarning">Flag indicating if the warning is related to the auto-generated primary key or to the table.</param>
public TableWarningsChangedArgs(MySqlDataTable table, bool autoPkWarning)
{
CurrentWarning = autoPkWarning ? table.CurrentAutoPkWarningText : table.CurrentTableWarningText;
WarningsType = autoPkWarning ? TableWarningsType.AutoPrimaryKeyWarnings : TableWarningsType.TableNameWarnings;
}
