public async Task<Dictionary<string, List<ChangedViewData>>> GetChangedItemsAsync(Parameters parameters) { var response = new Dictionary<string, List<ChangedViewData>>(); var sqlQuery = $"select * from [{parameters.table1}] where [{parameters.primarykey}] not in (Select t1.[{parameters.primarykey}] from [{parameters.table1}] t1 left join [{parameters.table2}] t2 on t2.[{parameters.primarykey}] = t1.[{parameters.primarykey}] where t2.[{parameters.primarykey}] is null)"+ $" except select * from [{parameters.table2}]"; var originalDataFromTable1 = await _sqlCommandExecutor.ExecuteAsync(sqlQuery); if (!originalDataFromTable1.Any()) return response; var keys = originalDataFromTable1.Select(r => "'"+r[parameters.primarykey]+ "'").ToArray(); var sqlQuery1 = $"Select * from [{parameters.table2}] where [{parameters.primarykey}] in ({string.Join(", ", keys)})"; var distortedDataFromTable2 = await _sqlCommandExecutor.ExecuteAsync(sqlQuery1); foreach (var row in originalDataFromTable1) { var changedValues = new List<ChangedViewData>(); var distortedRow = distortedDataFromTable2.First(d => d[parameters.primarykey].Equals(row[parameters.primarykey])); foreach (var key in distortedRow.Keys) { if (distortedRow[key].Equals(row[key])) continue; changedValues.Add(new ChangedViewData() { ColumnName = key, OriginalValue = row[key].ToString(), ChangedValue = distortedRow[key].ToString() }); } response.Add(row[parameters.primarykey].ToString(), changedValues); } return response; }
public async Task<List<Dictionary<string, object>>> GetRemovedItemsAsync(Parameters parameters) { var sqlQuery = $"Select t1.* from [{parameters.table1}] t1 left join [{parameters.table2}] t2 on t2.[{parameters.primarykey}] = t1.[{parameters.primarykey}] where t2.[{parameters.primarykey}] is null"; return await _sqlCommandExecutor.ExecuteAsync(sqlQuery); }