Esempio n. 1
0
        /// <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);
        }
Esempio n. 3
0
        /// <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;
        }
Esempio n. 4
0
        /// <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);
        }
Esempio n. 5
0
        /// <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()));
        }
Esempio n. 6
0
 /// <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);
 }
Esempio n. 7
0
        /// <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);
        }
Esempio n. 8
0
        /// <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();
        }
Esempio n. 9
0
        /// <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;
        }
Esempio n. 10
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);
        }
Esempio n. 11
0
 /// <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;
 }