/// <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());
}
