/// <summary> /// Reflects changes in Excel worksheet if this row has just been modified. /// </summary> private void ReflectChangesForModifiedRow() { if (RowState == DataRowState.Added && ExcelRange != null) { // A recently added row's value is being modified, we just need to re-paint the whole "added" row. ExcelRange.SetInteriorColor(ExcelUtilities.UncommittedCellsOleColor); } if (RowState != DataRowState.Modified) { return; } if (ExcelRange != null) { ExcelModifiedRangesList.Clear(); } ChangedColumnNames.Clear(); // Check column by column for data changes, set related Excel cells color accordingly. for (int colIndex = 0; colIndex < Table.Columns.Count; colIndex++) { ExcelInterop.Range columnCell = ExcelRange != null ? ExcelRange.Cells[1, colIndex + 1] : null; bool originalAndModifiedIdentical = this[colIndex].Equals(this[colIndex, DataRowVersion.Original]); if (!originalAndModifiedIdentical) { if (columnCell != null) { ExcelModifiedRangesList.Add(columnCell); } ChangedColumnNames.Add(Table.Columns[colIndex].ColumnName); } if (columnCell == null) { continue; } var cellColor = originalAndModifiedIdentical ? _excelRangePreviousColors[colIndex] : ExcelUtilities.UncommittedCellsOleColor; columnCell.SetInteriorColor(cellColor); } // If the row resulted with no modifications (maybe some values set back to their original values by the user) then undo changes. if (ChangedColumnNames.Count == 0) { RejectChanges(); } }
/// <summary> /// Reflects changes in Excel worksheet if this row has just been commited. /// </summary> private void ReflectChangesForCommittedRow() { if (!IsBeingDeleted && ExcelRange != null) { ExcelModifiedRangesList.SetInteriorColor(ExcelUtilities.CommitedCellsOleColor); SaveCurrentColor(ExcelUtilities.CommitedCellsOleColor); if (!HasErrors) { ExcelModifiedRangesList.Clear(); } } if (!HasErrors) { ChangedColumnNames.Clear(); } }
/// <summary> /// Reflects changes in Excel worksheet if this row has just been rolled back. /// </summary> private void ReflectChangesForRolledbackRow() { if (!IsBeingDeleted) { for (int colIndex = 0; colIndex < Table.Columns.Count; colIndex++) { ExcelInterop.Range columnCell = ExcelRange != null ? ExcelRange.Cells[1, colIndex + 1] : null; if (columnCell == null) { continue; } columnCell.SetInteriorColor(_excelRangePreviousColors[colIndex]); } ExcelModifiedRangesList.Clear(); } ChangedColumnNames.Clear(); IsBeingDeleted = false; }
/// <summary> /// Creates sET and UPDATE statements SQL text for a row being modified where an optimistic concurrency model is used. /// </summary> /// <returns>The UPDATE SQL query.</returns> private string GetSqlForModifiedRowUsingOptimisticConcurrency() { var parentTable = MySqlTable; if (parentTable == null || RowState != DataRowState.Modified) { return(string.Empty); } // Reuse builders instead of using new ones in order to save memory. var setVariablesBuilder = parentTable.SqlBuilderForDelete; var wClauseBuilder = parentTable.SqlBuilderForInsert; var sqlBuilderForUpdate = parentTable.SqlBuilderForUpdate; setVariablesBuilder.Clear(); wClauseBuilder.Clear(); sqlBuilderForUpdate.Clear(); string serverCollation = parentTable.WbConnection.ServerCollation; string setSeparator = "SET"; string colsSeparator = string.Empty; string wClauseColsSeparator = string.Empty; wClauseBuilder.Append(" WHERE "); sqlBuilderForUpdate.Append(MySqlStatement.STATEMENT_UPDATE); sqlBuilderForUpdate.AppendFormat(" `{0}`.`{1}` SET ", parentTable.SchemaName, parentTable.TableNameForSqlQueries); foreach (MySqlDataColumn column in parentTable.Columns) { bool updatingValueIsNull; bool columnRequiresQuotes = column.MySqlDataType.RequiresQuotesForValue; bool columnIsText = column.MySqlDataType.IsChar || column.MySqlDataType.IsText || column.MySqlDataType.IsSetOrEnum; bool columnIsJson = column.MySqlDataType.IsJson; string valueToDb = column.GetStringValue(this[column.ColumnName, DataRowVersion.Original], out updatingValueIsNull); string wrapValueCharacter = columnRequiresQuotes && !updatingValueIsNull ? "'" : string.Empty; if (column.AllowNull) { var columnCollation = column.AbsoluteCollation; bool needToCollateTextValue = columnIsText && !updatingValueIsNull && serverCollation != null && !serverCollation.Equals(columnCollation, StringComparison.InvariantCultureIgnoreCase) && columnCollation.IsUnicodeCharSetOrCollation(); // If the length of the string value * 2 is greater than the string it requires to declare a variable for it, then declare the variable to save query space. bool needToCreateVariable = (valueToDb.Length * 2) > (valueToDb.Length + 24 + (needToCollateTextValue ? columnCollation.Length + 9 : 0)); string valueForClause; if (needToCreateVariable) { valueForClause = "@OldCol" + (column.Ordinal + 1) + "Value"; setVariablesBuilder.AppendFormat("{0} {1} = {2}{3}{2}", setSeparator, valueForClause, wrapValueCharacter, valueToDb); setSeparator = ","; if (needToCollateTextValue) { setVariablesBuilder.Append(" COLLATE "); setVariablesBuilder.Append(columnCollation); } } else { valueForClause = string.Format("{0}{1}{0}", wrapValueCharacter, valueToDb); } wClauseBuilder.AppendFormat("{0}(({2} IS NULL AND `{1}` IS NULL) ", wClauseColsSeparator, column.ColumnNameForSqlQueries, valueForClause); wClauseBuilder.AppendFormat(columnIsJson && !updatingValueIsNull ? "OR `{0}`=CAST({1} AS JSON))" : "OR `{0}`={1})", column.ColumnNameForSqlQueries, valueForClause); } else { wClauseBuilder.AppendFormat(columnIsJson && !updatingValueIsNull ? "{0}`{1}`=CAST({2}{3}{2} AS JSON)" : "{0}`{1}`={2}{3}{2}", wClauseColsSeparator, column.ColumnNameForSqlQueries, wrapValueCharacter, valueToDb); } wClauseColsSeparator = " AND "; if (!ChangedColumnNames.Contains(column.ColumnName)) { continue; } valueToDb = column.GetStringValue(this[column.ColumnName], out updatingValueIsNull); wrapValueCharacter = columnRequiresQuotes && !updatingValueIsNull ? "'" : string.Empty; sqlBuilderForUpdate.AppendFormat("{0}`{1}`={2}{3}{2}", colsSeparator, column.ColumnNameForSqlQueries, wrapValueCharacter, valueToDb); colsSeparator = ","; } if (setVariablesBuilder.Length > 0) { _setVariablesSql = setVariablesBuilder.ToString(); } sqlBuilderForUpdate.Append(wClauseBuilder); return(sqlBuilderForUpdate.ToString()); }
/// <summary> /// Creates an UPDATE statement SQL text for a row being modified. /// </summary> /// <returns>The UPDATE SQL query.</returns> private string GetSqlForModifiedRow() { var parentTable = MySqlTable; if (parentTable == null || RowState != DataRowState.Modified) { return(string.Empty); } var sqlBuilderForUpdate = parentTable.SqlBuilderForUpdate; sqlBuilderForUpdate.Clear(); string colsSeparator = string.Empty; sqlBuilderForUpdate.Append(MySqlStatement.STATEMENT_UPDATE); sqlBuilderForUpdate.AppendFormat(" `{0}`.`{1}` SET ", parentTable.SchemaName, parentTable.TableNameForSqlQueries); foreach (var column in parentTable.Columns.Cast <MySqlDataColumn>().Where(col => ChangedColumnNames.Contains(col.ColumnName))) { bool updatingValueIsNull; var valueToDb = column.GetStringValue(this[column.ColumnName], out updatingValueIsNull); string wrapValueCharacter = column.MySqlDataType.RequiresQuotesForValue && !updatingValueIsNull ? "'" : string.Empty; sqlBuilderForUpdate.AppendFormat("{0}`{1}`={2}{3}{2}", colsSeparator, column.ColumnNameForSqlQueries, wrapValueCharacter, valueToDb); colsSeparator = ","; } sqlBuilderForUpdate.Append(GetWhereClauseFromPrimaryKey(true)); return(sqlBuilderForUpdate.ToString()); }