private void DmView_Filter() { // first filter var filter = new Predicate <DmRow>((row) => { if (row.RowState == DmRowState.Deleted) { return(false); } if ((int)row["CustomerID"] == 1) { return(true); } return(false); }); var view = new DmView(set.Tables["ServiceTickets"], filter); Assert.Equal(5, view.Count); // Second Filter view = view.Filter((r) => (int)r["StatusValue"] == 2); Assert.Equal(3, view.Count); }
/// <summary> /// Try to apply changes on the server. /// Internally will call ApplyInsert / ApplyUpdate or ApplyDelete /// </summary> /// <param name="dmChanges">Changes from remote</param> /// <returns>every lines not updated on the server side</returns> internal int ApplyChanges(DmView dmChanges, ScopeInfo scope, List <SyncConflict> conflicts) { int appliedRows = 0; foreach (var dmRow in dmChanges) { bool operationComplete = false; if (applyType == DmRowState.Added) { operationComplete = this.ApplyInsert(dmRow); } else if (applyType == DmRowState.Modified) { operationComplete = this.ApplyUpdate(dmRow, scope, false); } else if (applyType == DmRowState.Deleted) { operationComplete = this.ApplyDelete(dmRow, scope, false); } if (operationComplete) { // if no pb, increment then go to next row appliedRows++; } else { // Generate a conflict and add it conflicts.Add(GetConflict(dmRow)); } } return(appliedRows); }
private void DmView_Constructor() { var t = set.Tables["ServiceTickets"]; var view = new DmView(t); Assert.Equal(17, view.Count); var view2 = new DmView(t, DmRowState.Modified); Assert.Equal(0, view2.Count); // Set one row as Modified view[0].SetModified(); var view22 = new DmView(t, DmRowState.Modified); Assert.Equal(1, view22.Count); var view3 = new DmView(t, (r) => (int)r["CustomerID"] == 1); Assert.Equal(5, view3.Count); var view4 = new DmView(view3, (r) => (int)r["StatusValue"] == 2); Assert.Equal(3, view4.Count); }
/// <summary> /// Try to apply changes on the server. /// Internally will call ApplyInsert / ApplyUpdate or ApplyDelete /// </summary> /// <param name="dmChanges">Changes from remote</param> /// <returns>every lines not updated on the server side</returns> internal int ApplyChanges(DmView dmChanges, ScopeInfo scope, List <SyncConflict> conflicts) { int appliedRows = 0; DbCommand dbCommand; foreach (var dmRow in dmChanges) { bool operationComplete = false; if (applyType == DmRowState.Added) { operationComplete = this.ApplyInsert(dmRow, scope, false); if (operationComplete) { dbCommand = this.GetCommand(DbCommandType.InsertMetadata); this.SetCommandParameters(DbCommandType.InsertMetadata, dbCommand); var lastTimestamp = (long)dmRow["create_timestamp"]; this.InsertOrUpdateMetadatas(dbCommand, dmRow, scope.Id); } } else if (applyType == DmRowState.Modified) { operationComplete = this.ApplyUpdate(dmRow, scope, false); if (operationComplete) { dbCommand = this.GetCommand(DbCommandType.UpdateMetadata); var lastTimestamp = (long)dmRow["update_timestamp"]; this.SetCommandParameters(DbCommandType.UpdateMetadata, dbCommand); this.InsertOrUpdateMetadatas(dbCommand, dmRow, scope.Id); } } else if (applyType == DmRowState.Deleted) { operationComplete = this.ApplyDelete(dmRow, scope, false); if (operationComplete) { dbCommand = this.GetCommand(DbCommandType.UpdateMetadata); this.SetCommandParameters(DbCommandType.UpdateMetadata, dbCommand); this.InsertOrUpdateMetadatas(dbCommand, dmRow, scope.Id); } } if (operationComplete) { // if no pb, increment then go to next row appliedRows++; } else { // Generate a conflict and add it conflicts.Add(GetConflict(dmRow)); } } return(appliedRows); }
public AppliedChangesEventArgs(DmView changes, DmRowState state, DbConnection connection, DbTransaction transaction) { this.Changes = changes; this.State = state; this.Connection = connection; this.Transaction = transaction; this.Action = ChangeApplicationAction.Continue; }
private void DmView_Order() { var view = new DmView(set.Tables["ServiceTickets"]); view = view.Order((r1, r2) => string.Compare(((string)r1["Title"]), (string)r2["Title"], StringComparison.Ordinal)); Assert.Equal(17, view.Count); Assert.Equal("Titre AC", (string)view[0]["Title"]); Assert.Equal("Titre ADFVB", (string)view[1]["Title"]); Assert.Equal("Titre AEEE", (string)view[2]["Title"]); Assert.Equal("Titre AER", (string)view[3]["Title"]); Assert.Equal("Titre AFBBB", (string)view[4]["Title"]); }
private void DmView_Skip() { var view = new DmView(set.Tables["ServiceTickets"]); view = view.Order((r1, r2) => string.Compare(((string)r1["Title"]), (string)r2["Title"], StringComparison.Ordinal)); Assert.Equal(17, view.Count); var view2 = view.Take(0, 2); Assert.Equal(2, view2.Count); Assert.Equal("Titre AC", (string)view2[0]["Title"]); Assert.Equal("Titre ADFVB", (string)view2[1]["Title"]); var view3 = view.Take(2, 2); Assert.Equal(2, view3.Count); Assert.Equal("Titre AEEE", (string)view3[0]["Title"]); Assert.Equal("Titre AER", (string)view3[1]["Title"]); var view4 = view.Take(4, 1); Assert.Single(view4); Assert.Equal("Titre AFBBB", (string)view4[0]["Title"]); var view5 = view.Take(5, 12); Assert.Equal(12, view5.Count); var view6 = view.Take(5, 13); Assert.Equal(12, view6.Count); var view7 = view.Take(12, 0); Assert.Empty(view7); var view8 = view.Take(17, 0); Assert.Empty(view8); Assert.Throws <Exception>(() => { var view9 = view.Take(18, 0); }); Assert.Throws <Exception>(() => { var view10 = view.Take(18, 2); }); }
public override void ExecuteBatchCommand(DbCommand cmd, DmView applyTable, DmTable failedRows, ScopeInfo scope) { if (applyTable.Count <= 0) { return; } // Insert data in bulk table BulkInsertTemporyTable(BulkInsertTemporyTableText(applyTable)); ((OracleParameterCollection)cmd.Parameters)["sync_scope_id"].Value = scope.Id; ((OracleParameterCollection)cmd.Parameters)["sync_min_timestamp"].Value = scope.LastTimestamp; bool alreadyOpened = this._connection.State == ConnectionState.Open; try { if (!alreadyOpened) { this._connection.Open(); } if (this._transaction != null) { cmd.Transaction = this._transaction; } using (DbDataReader dataReader = cmd.ExecuteReader()) { failedRows.Fill(dataReader); } } catch (DbException ex) { Debug.WriteLine(ex.Message); throw; } finally { if (!alreadyOpened && this._connection.State != ConnectionState.Closed) { this._connection.Close(); } } }
/// <summary> /// Launch apply bulk changes /// </summary> /// <returns></returns> public int ApplyBulkChanges(DmView dmChanges, ScopeInfo fromScope, List <SyncConflict> conflicts) { DbCommand bulkCommand = null; if (this.applyType == DmRowState.Added) { bulkCommand = this.GetCommand(DbObjectType.BulkInsertProcName); } else if (this.applyType == DmRowState.Modified) { bulkCommand = this.GetCommand(DbObjectType.BulkUpdateProcName); } else if (this.applyType == DmRowState.Deleted) { bulkCommand = this.GetCommand(DbObjectType.BulkDeleteProcName); } else { throw new Exception("DmRowState not valid during ApplyBulkChanges operation"); } if (Transaction != null && Transaction.Connection != null) { bulkCommand.Transaction = Transaction; } DmTable batchDmTable = dmChanges.Table.Clone(); DmTable failedDmtable = new DmTable { Culture = CultureInfo.InvariantCulture }; // Create the schema for failed rows (just add the Primary keys) this.AddSchemaForFailedRowsTable(batchDmTable, failedDmtable); int batchCount = 0; int rowCount = 0; foreach (var dmRow in dmChanges) { // Cancel the delete state to be able to get the row, more simplier if (applyType == DmRowState.Deleted) { dmRow.RejectChanges(); } // Load the datarow DmRow dataRow = batchDmTable.LoadDataRow(dmRow.ItemArray, false); // Apply the delete // is it mandatory ? if (applyType == DmRowState.Deleted) { dmRow.Delete(); } batchCount++; rowCount++; if (batchCount != 500 && rowCount != dmChanges.Count) { continue; } // Since the update and create timestamp come from remote, change name for the bulk operations batchDmTable.Columns["update_timestamp"].ColumnName = "update_timestamp"; batchDmTable.Columns["create_timestamp"].ColumnName = "create_timestamp"; // execute the batch, through the provider ExecuteBatchCommand(bulkCommand, batchDmTable, failedDmtable, fromScope); // Clear the batch batchDmTable.Clear(); // Recreate a Clone // TODO : Evaluate if it's necessary batchDmTable = dmChanges.Table.Clone(); batchCount = 0; } // Update table progress //tableProgress.ChangesApplied = dmChanges.Count - failedDmtable.Rows.Count; if (failedDmtable.Rows.Count == 0) { return(dmChanges.Count); } // Check all conflicts raised var failedFilter = new Predicate <DmRow>(row => { if (row.RowState == DmRowState.Deleted) { return(failedDmtable.FindByKey(row.GetKeyValues(DmRowVersion.Original)) != null); } else { return(failedDmtable.FindByKey(row.GetKeyValues()) != null); } }); // New View var dmFailedRows = new DmView(dmChanges, failedFilter); // Generate a conflict and add it foreach (var dmFailedRow in dmFailedRows) { conflicts.Add(GetConflict(dmFailedRow)); } int failedRows = dmFailedRows.Count; // Dispose the failed view dmFailedRows.Dispose(); // return applied rows - failed rows (generating a conflict) return(dmChanges.Count - failedRows); }
/// <summary> /// Apply changes internal method for one Insert or Update or Delete for every dbSyncAdapter /// </summary> internal async Task <ChangeApplicationAction> ApplyChangesInternalAsync( DmTable table, SyncContext context, MessageApplyChanges message, DbConnection connection, DbTransaction transaction, DmRowState applyType, DatabaseChangesApplied changesApplied) { var changeApplicationAction = ChangeApplicationAction.Continue; // if we are in upload stage, so check if table is not download only if (context.SyncWay == SyncWay.Upload && table.SyncDirection == SyncDirection.DownloadOnly) { return(ChangeApplicationAction.Continue); } // if we are in download stage, so check if table is not download only if (context.SyncWay == SyncWay.Download && table.SyncDirection == SyncDirection.UploadOnly) { return(ChangeApplicationAction.Continue); } var builder = this.GetDatabaseBuilder(table); var syncAdapter = builder.CreateSyncAdapter(connection, transaction); syncAdapter.ApplyType = applyType; if (message.Changes.BatchPartsInfo != null && message.Changes.BatchPartsInfo.Count > 0) { // getting the table to be applied // we may have multiple batch files, so we can have multipe dmTable with the same Name // We can say that dmTable may be contained in several files foreach (var dmTablePart in message.Changes.GetTable(table.TableName)) { if (dmTablePart == null || dmTablePart.Rows.Count == 0) { continue; } // check and filter var dmChangesView = new DmView(dmTablePart, (r) => r.RowState == applyType); if (dmChangesView.Count == 0) { dmChangesView.Dispose(); dmChangesView = null; continue; } // Conflicts occured when trying to apply rows var conflicts = new List <SyncConflict>(); context.SyncStage = SyncStage.TableChangesApplying; // Launch any interceptor if available await this.InterceptAsync(new TableChangesApplyingArgs(context, table, applyType, connection, transaction)); int rowsApplied; // applying the bulkchanges command if (this.Options.UseBulkOperations && this.SupportBulkOperations) { rowsApplied = syncAdapter.ApplyBulkChanges(dmChangesView, message.FromScope, conflicts); } else { rowsApplied = syncAdapter.ApplyChanges(dmChangesView, message.FromScope, conflicts); } // If conflicts occured // Eventuall, conflicts are resolved on server side. if (conflicts != null && conflicts.Count > 0) { foreach (var conflict in conflicts) { //var scopeBuilder = this.GetScopeBuilder(); //var scopeInfoBuilder = scopeBuilder.CreateScopeInfoBuilder(message.ScopeInfoTableName, connection, transaction); //var localTimeStamp = scopeInfoBuilder.GetLocalTimestamp(); var fromScopeLocalTimeStamp = message.FromScope.Timestamp; var conflictCount = 0; DmRow resolvedRow = null; (changeApplicationAction, conflictCount, resolvedRow) = await this.HandleConflictAsync(syncAdapter, context, conflict, message.Policy, message.FromScope, fromScopeLocalTimeStamp, connection, transaction); if (changeApplicationAction == ChangeApplicationAction.Continue) { // row resolved if (resolvedRow != null) { context.TotalSyncConflicts += conflictCount; rowsApplied++; } } else { context.TotalSyncErrors++; // TODO : Should we break at the first error ? return(ChangeApplicationAction.Rollback); } } } // Handle sync progress for this syncadapter (so this table) var changedFailed = dmChangesView.Count - rowsApplied; // raise SyncProgress Event var existAppliedChanges = changesApplied.TableChangesApplied.FirstOrDefault( sc => string.Equals(sc.Table.TableName, table.TableName) && sc.State == applyType); if (existAppliedChanges == null) { existAppliedChanges = new TableChangesApplied { Table = new DmTableSurrogate(table), Applied = rowsApplied, Failed = changedFailed, State = applyType }; changesApplied.TableChangesApplied.Add(existAppliedChanges); } else { existAppliedChanges.Applied += rowsApplied; existAppliedChanges.Failed += changedFailed; } // Progress & Interceptor context.SyncStage = SyncStage.TableChangesApplied; var tableChangesAppliedArgs = new TableChangesAppliedArgs(context, existAppliedChanges, connection, transaction); this.ReportProgress(context, tableChangesAppliedArgs, connection, transaction); await this.InterceptAsync(tableChangesAppliedArgs); } } return(ChangeApplicationAction.Continue); }
/// <summary> /// Execute a batch command /// </summary> public abstract void ExecuteBatchCommand(DbCommand cmd, DmView applyTable, DmTable failedRows, ScopeInfo scope);
/// <summary> /// Try to apply changes on the server. /// Internally will call ApplyInsert / ApplyUpdate or ApplyDelete /// </summary> /// <param name="dmChanges">Changes from remote</param> /// <returns>every lines not updated on the server side</returns> internal int ApplyChanges(DmView dmChanges, ScopeInfo scope, List <SyncConflict> conflicts) { int appliedRows = 0; foreach (var dmRow in dmChanges) { bool operationComplete = false; try { if (ApplyType == DmRowState.Added) { operationComplete = this.ApplyInsert(dmRow, scope, false); if (operationComplete) { UpdateMetadatas(DbCommandType.InsertMetadata, dmRow, scope); } } else if (ApplyType == DmRowState.Modified) { operationComplete = this.ApplyUpdate(dmRow, scope, false); if (operationComplete) { UpdateMetadatas(DbCommandType.UpdateMetadata, dmRow, scope); } } else if (ApplyType == DmRowState.Deleted) { operationComplete = this.ApplyDelete(dmRow, scope, false); if (operationComplete) { UpdateMetadatas(DbCommandType.UpdateMetadata, dmRow, scope); } } if (operationComplete) { // if no pb, increment then go to next row appliedRows++; } else { // Generate a conflict and add it conflicts.Add(GetConflict(dmRow)); } } catch (Exception ex) { if (this.IsUniqueKeyViolation(ex)) { // Generate the conflict var conflict = new SyncConflict(ConflictType.UniqueKeyConstraint); // Add the row as Remote row conflict.AddRemoteRow(dmRow); // Get the local row var localTable = GetRow(dmRow); if (localTable.Rows.Count > 0) { conflict.AddLocalRow(localTable.Rows[0]); } conflicts.Add(conflict); localTable.Clear(); } else { throw; } } } return(appliedRows); }
/// <summary> /// Launch apply bulk changes /// </summary> /// <returns></returns> public int ApplyBulkChanges(DmView dmChanges, ScopeInfo fromScope, List <SyncConflict> conflicts) { DbCommand bulkCommand = null; if (this.ApplyType == DmRowState.Added) { bulkCommand = this.GetCommand(DbCommandType.BulkInsertRows); this.SetCommandParameters(DbCommandType.BulkInsertRows, bulkCommand); } else if (this.ApplyType == DmRowState.Modified) { bulkCommand = this.GetCommand(DbCommandType.BulkUpdateRows); this.SetCommandParameters(DbCommandType.BulkUpdateRows, bulkCommand); } else if (this.ApplyType == DmRowState.Deleted) { bulkCommand = this.GetCommand(DbCommandType.BulkDeleteRows); this.SetCommandParameters(DbCommandType.BulkDeleteRows, bulkCommand); } else { throw new Exception("DmRowState not valid during ApplyBulkChanges operation"); } if (Transaction != null && Transaction.Connection != null) { bulkCommand.Transaction = Transaction; } //DmTable batchDmTable = dmChanges.Table.Clone(); DmTable failedDmtable = new DmTable { Culture = CultureInfo.InvariantCulture }; // Create the schema for failed rows (just add the Primary keys) this.AddSchemaForFailedRowsTable(failedDmtable); // Since the update and create timestamp come from remote, change name for the bulk operations var update_timestamp_column = dmChanges.Table.Columns["update_timestamp"].ColumnName; dmChanges.Table.Columns["update_timestamp"].ColumnName = "update_timestamp"; var create_timestamp_column = dmChanges.Table.Columns["create_timestamp"].ColumnName; dmChanges.Table.Columns["create_timestamp"].ColumnName = "create_timestamp"; // Make some parts of BATCH_SIZE for (int step = 0; step < dmChanges.Count; step += BATCH_SIZE) { // get upper bound max value var taken = step + BATCH_SIZE >= dmChanges.Count ? dmChanges.Count - step : BATCH_SIZE; using (var dmStepChanges = dmChanges.Take(step, taken)) { // execute the batch, through the provider ExecuteBatchCommand(bulkCommand, dmStepChanges, failedDmtable, fromScope); } } // Disposing command if (bulkCommand != null) { bulkCommand.Dispose(); bulkCommand = null; } // Since the update and create timestamp come from remote, change name for the bulk operations dmChanges.Table.Columns["update_timestamp"].ColumnName = update_timestamp_column; dmChanges.Table.Columns["create_timestamp"].ColumnName = create_timestamp_column; //foreach (var dmRow in dmChanges) //{ // // Cancel the delete state to be able to get the row, more simplier // if (applyType == DmRowState.Deleted) // dmRow.RejectChanges(); // // Load the datarow // DmRow dataRow = batchDmTable.LoadDataRow(dmRow.ItemArray, false); // // Apply the delete // // is it mandatory ? // if (applyType == DmRowState.Deleted) // dmRow.Delete(); // batchCount++; // rowCount++; // if (batchCount < BATCH_SIZE && rowCount < dmChanges.Count) // continue; // // Since the update and create timestamp come from remote, change name for the bulk operations // batchDmTable.Columns["update_timestamp"].ColumnName = "update_timestamp"; // batchDmTable.Columns["create_timestamp"].ColumnName = "create_timestamp"; // // execute the batch, through the provider // ExecuteBatchCommand(bulkCommand, batchDmTable, failedDmtable, fromScope); // // Clear the batch // batchDmTable.Clear(); // // Recreate a Clone // // TODO : Evaluate if it's necessary // batchDmTable = dmChanges.Table.Clone(); // batchCount = 0; //} // Update table progress //tableProgress.ChangesApplied = dmChanges.Count - failedDmtable.Rows.Count; if (failedDmtable.Rows.Count == 0) { return(dmChanges.Count); } // Check all conflicts raised var failedFilter = new Predicate <DmRow>(row => { if (row.RowState == DmRowState.Deleted) { return(failedDmtable.FindByKey(row.GetKeyValues(DmRowVersion.Original)) != null); } else { return(failedDmtable.FindByKey(row.GetKeyValues()) != null); } }); // New View var dmFailedRows = new DmView(dmChanges, failedFilter); // Generate a conflict and add it foreach (var dmFailedRow in dmFailedRows) { conflicts.Add(GetConflict(dmFailedRow)); } int failedRows = dmFailedRows.Count; // Dispose the failed view dmFailedRows.Dispose(); dmFailedRows = null; // return applied rows - failed rows (generating a conflict) return(dmChanges.Count - failedRows); }
/// <summary> /// Executing a batch command /// </summary> /// <param name="cmd">the DbCommand already prepared</param> /// <param name="applyTable">the table rows to apply</param> /// <param name="failedRows">the failed rows dmTable to store failed rows</param> /// <param name="scope">the current scope</param> public override void ExecuteBatchCommand(DbCommand cmd, DmView applyTable, DmTable failedRows, ScopeInfo scope) { if (applyTable.Count <= 0) { return; } var lstMutableColumns = applyTable.Table.Columns.Where(c => !c.IsReadOnly).ToList(); List <SqlDataRecord> records = new List <SqlDataRecord>(applyTable.Count); SqlMetaData[] metadatas = new SqlMetaData[lstMutableColumns.Count]; for (int i = 0; i < lstMutableColumns.Count; i++) { var column = lstMutableColumns[i]; SqlMetaData metadata = GetSqlMetadaFromType(column); metadatas[i] = metadata; } try { foreach (var dmRow in applyTable) { SqlDataRecord record = new SqlDataRecord(metadatas); int sqlMetadataIndex = 0; bool isDeleted = false; // Cancel the delete state to be able to get the row, more simplier if (dmRow.RowState == DmRowState.Deleted) { isDeleted = true; dmRow.RejectChanges(); } for (int i = 0; i < dmRow.ItemArray.Length; i++) { // check if it's readonly if (applyTable.Table.Columns[i].IsReadOnly) { continue; } // Get the default value // Since we have the readonly values in ItemArray, get the value from original column dynamic defaultValue = applyTable.Table.Columns[i].DefaultValue; dynamic rowValue = dmRow[i]; var columnType = applyTable.Table.Columns[i].DataType; // metadatas don't have readonly values, so get from sqlMetadataIndex var sqlMetadataType = metadatas[sqlMetadataIndex].SqlDbType; if (rowValue != null) { switch (sqlMetadataType) { case SqlDbType.BigInt: if (columnType != typeof(long)) { if (Int64.TryParse(rowValue.ToString(), out Int64 v)) { rowValue = v; } else { throw new InvalidCastException($"Can't convert value {rowValue} to Int64"); } } break; case SqlDbType.Bit: if (columnType != typeof(bool)) { if (Boolean.TryParse(rowValue.ToString(), out Boolean v)) { rowValue = v; } else { throw new InvalidCastException($"Can't convert value {rowValue} to Boolean"); } } break; case SqlDbType.Date: case SqlDbType.DateTime: case SqlDbType.DateTime2: case SqlDbType.SmallDateTime: if (columnType != typeof(DateTime)) { if (DateTime.TryParse(rowValue.ToString(), out DateTime v)) { rowValue = v; } else { throw new InvalidCastException($"Can't convert value {rowValue} to DateTime"); } } break; case SqlDbType.DateTimeOffset: if (columnType != typeof(DateTimeOffset)) { if (DateTimeOffset.TryParse(rowValue.ToString(), out DateTimeOffset dt)) { rowValue = dt; } else { throw new InvalidCastException($"Can't convert value {rowValue} to DateTimeOffset"); } } break; case SqlDbType.Decimal: if (columnType != typeof(Decimal)) { if (Decimal.TryParse(rowValue.ToString(), out decimal v)) { rowValue = v; } else { throw new InvalidCastException($"Can't convert value {rowValue} to Decimal"); } } break; case SqlDbType.Float: if (columnType != typeof(Double)) { if (Double.TryParse(rowValue.ToString(), out Double v)) { rowValue = v; } else { throw new InvalidCastException($"Can't convert value {rowValue} to Double"); } } break; case SqlDbType.Real: if (columnType != typeof(float)) { if (float.TryParse(rowValue.ToString(), out float v)) { rowValue = v; } else { throw new InvalidCastException($"Can't convert value {rowValue} to Double"); } } break; case SqlDbType.Image: case SqlDbType.Binary: case SqlDbType.VarBinary: if (columnType != typeof(Byte[])) { rowValue = BitConverter.GetBytes(rowValue); } break; case SqlDbType.Variant: break; case SqlDbType.Int: if (columnType != typeof(Int32)) { if (Int32.TryParse(rowValue.ToString(), out int v)) { rowValue = v; } else { throw new InvalidCastException($"Can't convert value {rowValue} to Int32"); } } break; case SqlDbType.Money: case SqlDbType.SmallMoney: if (columnType != typeof(Decimal)) { if (Decimal.TryParse(rowValue.ToString(), out Decimal v)) { rowValue = v; } else { throw new InvalidCastException($"Can't convert value {rowValue} to Decimal"); } } break; case SqlDbType.NChar: case SqlDbType.NText: case SqlDbType.VarChar: case SqlDbType.Xml: case SqlDbType.NVarChar: case SqlDbType.Text: case SqlDbType.Char: if (columnType != typeof(string)) { rowValue = rowValue.ToString(); } break; case SqlDbType.SmallInt: if (columnType != typeof(Int16)) { if (Int16.TryParse(rowValue.ToString(), out Int16 v)) { rowValue = v; } else { throw new InvalidCastException($"Can't convert value {rowValue} to Int16"); } } break; case SqlDbType.Time: if (columnType != typeof(TimeSpan)) { if (TimeSpan.TryParse(rowValue.ToString(), out TimeSpan v)) { rowValue = v; } else { throw new InvalidCastException($"Can't convert value {rowValue} to TimeSpan"); } } break; case SqlDbType.Timestamp: break; case SqlDbType.TinyInt: if (columnType != typeof(Byte)) { if (Byte.TryParse(rowValue.ToString(), out byte v)) { rowValue = v; } else { throw new InvalidCastException($"Can't convert value {rowValue} to Byte"); } } break; case SqlDbType.Udt: throw new ArgumentException($"Can't use UDT as SQL Type"); case SqlDbType.UniqueIdentifier: if (columnType != typeof(Guid)) { if (Guid.TryParse(rowValue.ToString(), out Guid v)) { rowValue = v; } else { throw new InvalidCastException($"Can't convert value {rowValue} to Guid"); } } break; } } if (applyTable.Table.Columns[i].AllowDBNull && rowValue == null) { rowValue = DBNull.Value; } else if (rowValue == null) { rowValue = defaultValue; } record.SetValue(sqlMetadataIndex, rowValue); sqlMetadataIndex++; } records.Add(record); // Apply the delete // is it mandatory ? if (isDeleted) { dmRow.Delete(); } } } catch (Exception ex) { throw new InvalidOperationException($"Can't create a SqlRecord based on the rows we have: {ex.Message}"); } ((SqlParameterCollection)cmd.Parameters)["@changeTable"].TypeName = string.Empty; ((SqlParameterCollection)cmd.Parameters)["@changeTable"].Value = records; ((SqlParameterCollection)cmd.Parameters)["@sync_scope_id"].Value = scope.Id; ((SqlParameterCollection)cmd.Parameters)["@sync_min_timestamp"].Value = scope.Timestamp; bool alreadyOpened = this.connection.State == ConnectionState.Open; try { if (!alreadyOpened) { this.connection.Open(); } if (this.transaction != null) { cmd.Transaction = this.transaction; } using (DbDataReader dataReader = cmd.ExecuteReader()) { failedRows.Fill(dataReader); } } catch (DbException ex) { Debug.WriteLine(ex.Message); //DbException dbException = dbException1; //Error = CheckZombieTransaction(tvpCommandNameForApplyType, Adapter.TableName, dbException); //this.AddFailedRowsAfterRIFailure(applyTable, failedRows); throw; } finally { records.Clear(); records = null; if (!alreadyOpened && this.connection.State != ConnectionState.Closed) { this.connection.Close(); } } }
private string BulkInsertTemporyTableText(DmView applyTable) { var bulkTableName = oracleObjectNames.GetCommandName(DbCommandType.BulkTable); StringBuilder sb = new StringBuilder(); StringBuilder sbInsert = new StringBuilder(); string str = "", exceptionMessage = "Can't convert value {0} to {1}"; var lstMutableColumns = applyTable.Table.Columns.Where(c => !c.ReadOnly).ToList(); sb.AppendLine("BEGIN"); sbInsert.Append($"INSERT INTO {bulkTableName}("); lstMutableColumns.ToList().ForEach(c => { sbInsert.Append($"{str} {c.ColumnName}"); str = ","; }); sbInsert.Append(") VALUES ("); foreach (var dmRow in applyTable) { str = ""; sb.Append(sbInsert.ToString()); bool isDeleted = false; // Cancel the delete state to be able to get the row, more simplier if (dmRow.RowState == DmRowState.Deleted) { isDeleted = true; dmRow.RejectChanges(); } for (int i = 0; i < dmRow.ItemArray.Length; i++) { dynamic rowValue = dmRow[i]; var columnType = applyTable.Table.Columns[i].DataType; OracleType type = oracleMetadata.ValidateOracleType(columnType); if (dmRow[i] != null) { switch (type) { case OracleType.Number: if (columnType == typeof(Int32)) { if (Int32.TryParse(rowValue.ToString(), out Int32 v0)) { rowValue = v0; } else { throw new InvalidCastException(string.Format(exceptionMessage, rowValue, columnType)); } } else if (columnType == typeof(Int16)) { if (Int16.TryParse(rowValue.ToString(), out Int16 v1)) { rowValue = v1; } else { throw new InvalidCastException(string.Format(exceptionMessage, rowValue, columnType)); } } else if (columnType == typeof(Int64)) { if (Int64.TryParse(rowValue.ToString(), out Int64 v2)) { rowValue = v2; } else { throw new InvalidCastException(string.Format(exceptionMessage, rowValue, columnType)); } } break; case OracleType.Blob: if (columnType == typeof(byte[])) { if (rowValue is byte[]) { rowValue = (byte[])rowValue; } else { throw new InvalidCastException(string.Format(exceptionMessage, rowValue, columnType)); } } break; case OracleType.NVarChar: rowValue = $"'{Convert.ToString(rowValue)}'"; break; case OracleType.Byte: if (columnType == typeof(Boolean)) { if (Int32.TryParse(rowValue.ToString(), out Int32 v4)) { rowValue = v4 == 1; } else { throw new InvalidCastException(string.Format(exceptionMessage, rowValue, columnType)); } } break; case OracleType.DateTime: if (columnType == typeof(DateTime)) { if (DateTime.TryParse(rowValue.ToString(), out DateTime v5)) { rowValue = v5; } else { throw new InvalidCastException(string.Format(exceptionMessage, rowValue, columnType)); } } break; case OracleType.Float: if (columnType == typeof(float)) { if (float.TryParse(rowValue.ToString(), out float v6)) { rowValue = v6; } else { throw new InvalidCastException(string.Format(exceptionMessage, rowValue, columnType)); } } break; case OracleType.Double: if (double.TryParse(rowValue.ToString(), out double v)) { rowValue = v; } else { throw new InvalidCastException(string.Format(exceptionMessage, rowValue, columnType)); } break; } sb.Append($"{str} {rowValue.ToString()}"); } else { sb.Append($"{str} null"); } str = ","; } sb.AppendLine(");"); if (isDeleted) { dmRow.Delete(); } } sb.AppendLine("END;"); return(sb.ToString()); }
/// <summary> /// Apply changes internal method for one Insert or Update or Delete for every dbSyncAdapter /// </summary> internal ChangeApplicationAction ApplyChangesInternal( SyncContext context, MessageApplyChanges message, DbConnection connection, DbTransaction transaction, DmRowState applyType, ChangesApplied changesApplied) { ChangeApplicationAction changeApplicationAction = ChangeApplicationAction.Continue; // for each adapters (Zero to End for Insert / Updates -- End to Zero for Deletes for (int i = 0; i < message.Schema.Tables.Count; i++) { // If we have a delete we must go from Up to Down, orthewise Dow to Up index var tableDescription = (applyType != DmRowState.Deleted ? message.Schema.Tables[i] : message.Schema.Tables[message.Schema.Tables.Count - i - 1]); // if we are in upload stage, so check if table is not download only if (context.SyncWay == SyncWay.Upload && tableDescription.SyncDirection == SyncDirection.DownloadOnly) { continue; } // if we are in download stage, so check if table is not download only if (context.SyncWay == SyncWay.Download && tableDescription.SyncDirection == SyncDirection.UploadOnly) { continue; } var builder = this.GetDatabaseBuilder(tableDescription); var syncAdapter = builder.CreateSyncAdapter(connection, transaction); syncAdapter.ConflictApplyAction = SyncConfiguration.GetApplyAction(message.Policy); // Set syncAdapter properties syncAdapter.applyType = applyType; // Get conflict handler resolver if (syncAdapter.ConflictActionInvoker == null && this.ApplyChangedFailed != null) { syncAdapter.ConflictActionInvoker = GetConflictAction; } if (message.Changes.BatchPartsInfo != null && message.Changes.BatchPartsInfo.Count > 0) { // getting the table to be applied // we may have multiple batch files, so we can have multipe dmTable with the same Name // We can say that dmTable may be contained in several files foreach (DmTable dmTablePart in message.Changes.GetTable(tableDescription.TableName)) { if (dmTablePart == null || dmTablePart.Rows.Count == 0) { continue; } // check and filter var dmChangesView = new DmView(dmTablePart, (r) => r.RowState == applyType); if (dmChangesView.Count == 0) { dmChangesView.Dispose(); dmChangesView = null; continue; } // Conflicts occured when trying to apply rows List <SyncConflict> conflicts = new List <SyncConflict>(); // Raise event progress only if there are rows to be applied context.SyncStage = SyncStage.TableChangesApplying; var args = new TableChangesApplyingEventArgs(this.ProviderTypeName, context.SyncStage, tableDescription.TableName, applyType); this.TryRaiseProgressEvent(args, this.TableChangesApplying); int rowsApplied; // applying the bulkchanges command if (message.UseBulkOperations && this.SupportBulkOperations) { rowsApplied = syncAdapter.ApplyBulkChanges(dmChangesView, message.FromScope, conflicts); } else { rowsApplied = syncAdapter.ApplyChanges(dmChangesView, message.FromScope, conflicts); } // If conflicts occured // Eventuall, conflicts are resolved on server side. if (conflicts != null && conflicts.Count > 0) { foreach (var conflict in conflicts) { //var scopeBuilder = this.GetScopeBuilder(); //var scopeInfoBuilder = scopeBuilder.CreateScopeInfoBuilder(message.ScopeInfoTableName, connection, transaction); //var localTimeStamp = scopeInfoBuilder.GetLocalTimestamp(); var fromScopeLocalTimeStamp = message.FromScope.Timestamp; changeApplicationAction = syncAdapter.HandleConflict(conflict, message.Policy, message.FromScope, fromScopeLocalTimeStamp, out DmRow resolvedRow); if (changeApplicationAction == ChangeApplicationAction.Continue) { // row resolved if (resolvedRow != null) { rowsApplied++; } } else { context.TotalSyncErrors++; // TODO : Should we break at the first error ? return(ChangeApplicationAction.Rollback); } } } // Get all conflicts resolved context.TotalSyncConflicts = conflicts.Where(c => c.Type != ConflictType.ErrorsOccurred).Sum(c => 1); // Handle sync progress for this syncadapter (so this table) var changedFailed = dmChangesView.Count - rowsApplied; // raise SyncProgress Event var existAppliedChanges = changesApplied.TableChangesApplied.FirstOrDefault( sc => string.Equals(sc.TableName, tableDescription.TableName) && sc.State == applyType); if (existAppliedChanges == null) { existAppliedChanges = new TableChangesApplied { TableName = tableDescription.TableName, Applied = rowsApplied, Failed = changedFailed, State = applyType }; changesApplied.TableChangesApplied.Add(existAppliedChanges); } else { existAppliedChanges.Applied += rowsApplied; existAppliedChanges.Failed += changedFailed; } // Event progress context.SyncStage = SyncStage.TableChangesApplied; var progressEventArgs = new TableChangesAppliedEventArgs(this.ProviderTypeName, context.SyncStage, existAppliedChanges); this.TryRaiseProgressEvent(progressEventArgs, this.TableChangesApplied); } } // Dispose conflict handler resolver if (syncAdapter.ConflictActionInvoker != null) { syncAdapter.ConflictActionInvoker = null; } } return(ChangeApplicationAction.Continue); }
public override void ExecuteBatchCommand(DbCommand cmd, DmView applyTable, DmTable failedRows, ScopeInfo scope) { throw new NotImplementedException(); }