public void Delete(IEnumerable <IRow> rows) { // Could probably do bulk updates with partition and bulk operation foreach (var row in rows) { var id = string.Concat(_context.OutputFields.Where(f => f.PrimaryKey).Select(f => row[f])); _client.Update <VoidResponse>(_index, _type, id, row.ToExpandoObject(_fields)); _context.Increment(); } }
public void Write(IEnumerable <Row> rows) { var sql = _context.SqlUpdateCalculatedFields(_original); var temp = new List <Field> { _context.Entity.Fields.First(f => f.Name == Constants.TflKey) }; temp.AddRange(_context.Entity.CalculatedFields.Where(f => f.Output && f.Name != Constants.TflHashCode)); var fields = temp.ToArray(); var count = 0; using (var cn = new SqlConnection(_context.Connection.GetConnectionString())) { cn.Open(); foreach (var batch in rows.Partition(_context.Entity.UpdateSize)) { var trans = cn.BeginTransaction(); var batchCount = cn.Execute( sql, batch.Select(r => r.ToExpandoObject(fields)), trans, _context.Connection.Timeout, CommandType.Text ); trans.Commit(); count += batchCount; _context.Increment(batchCount); } _context.Info("{0} to {1}", count, _context.Connection.Name); } _context.Entity.Updates += count; }
public void Write(IEnumerable <Row> rows) { var query = _output.SqlUpdateOutput(_output.Entity.BatchId); var count = 0; using (var cn = new SqlConnection(_output.Connection.GetConnectionString())) { cn.Open(); foreach (var batch in rows.Partition(_output.Entity.UpdateSize)) { var trans = cn.BeginTransaction(); var batchCount = cn.Execute( query, batch.Select(r => r.ToExpandoObject(_output.OutputFields)), trans, _output.Connection.Timeout, CommandType.Text ); trans.Commit(); count += batchCount; _output.Increment(batchCount); } _output.Info("{0} to {1}", count, _output.Connection.Name); } _output.Entity.Updates += count; }
public void Delete(IEnumerable <Row> rows) { var sql = _output.SqlDeleteOutput(_output.Entity.BatchId); var count = 0; using (var cn = new SqlConnection(_output.Connection.GetConnectionString())) { cn.Open(); foreach (var batch in rows.Partition(_output.Entity.DeleteSize)) { var trans = cn.BeginTransaction(); var batchCount = cn.Execute( sql, batch.Select(r => r.ToExpandoObject(_output.Entity.GetPrimaryKey())), trans, _output.Connection.Timeout, CommandType.Text ); trans.Commit(); count += batchCount; _output.Increment(batchCount); } _output.Entity.Deletes += count; _output.Info("{0} deletes from {1}", _output.Entity.Deletes, _output.Connection.Name); } }
public void Write(IEnumerable <IRow> rows) { var fullCount = 0; var batchCount = (uint)0; foreach (var part in rows.Partition(_context.Entity.InsertSize)) { var docs = new List <Dictionary <string, object> >(); foreach (var row in part) { batchCount++; fullCount++; docs.Add(_fields.ToDictionary(field => field.Alias.ToLower(), field => row[field])); } var response = _solr.AddRange(docs); _context.Increment(@by: batchCount); if (response.Status == 0) { _context.Debug(() => $"{batchCount} to output"); } else { _context.Error("ah!"); } batchCount = 0; } _solr.Commit(); if (fullCount > 0) { _context.Info($"{fullCount} to output"); } }
public void Write(IEnumerable <IRow> rows) { var fields = _context.Entity.GetAllOutputFields().Cast <IField>().ToArray(); var writer = new StreamWriter(_stream); if (!string.IsNullOrEmpty(_context.Connection.Header)) { writer.WriteLine(_context.Connection.Header); } foreach (var row in rows) { foreach (var field in fields) { writer.Write(row[field]); } writer.WriteLine(); _context.Increment(); } if (!string.IsNullOrEmpty(_context.Connection.Footer)) { writer.Write(_context.Connection.Footer); } writer.Flush(); }
public void Delete(IEnumerable <IRow> rows) { var criteria = string.Join(" AND ", _output.Entity.GetPrimaryKey().Select(f => f.FieldName()).Select(n => _cf.Enclose(n) + " = @" + n)); var sql = $"UPDATE {_cf.Enclose(_output.Entity.OutputTableName(_output.Process.Name))} SET {_output.Entity.TflDeleted().FieldName()} = CAST(1 AS BIT), {_output.Entity.TflBatchId().FieldName()} = {_output.Entity.BatchId} WHERE {criteria}"; _output.Debug(() => sql); var count = (uint)0; using (var cn = _cf.GetConnection()) { cn.Open(); foreach (var batch in rows.Partition(_output.Entity.DeleteSize)) { var trans = cn.BeginTransaction(); var batchCount = Convert.ToUInt32(cn.Execute( sql, batch.Select(r => r.ToExpandoObject(_fields)), trans, 0, CommandType.Text )); trans.Commit(); count += batchCount; _output.Increment(batchCount); } _output.Entity.Deletes += count; if (_output.Entity.Deletes > 0) { _output.Info("{0} deletes from {1}", _output.Entity.Deletes, _output.Connection.Name); } } }
public void Update() { var status = _c.GetEntityStatus(); if (!status.NeedsUpdate()) { return; } using (var cn = _cf.GetConnection()) { cn.Open(); var select = SelectStatement(status); var update = UpdateStatement(status); var trans = cn.BeginTransaction(); try { foreach (var batch in Read(cn, select).Partition(_master.UpdateSize)) { var expanded = batch.ToArray(); cn.Execute(update, expanded, trans); _c.Increment(Convert.ToUInt32(expanded.Length)); } trans.Commit(); } catch (Exception ex) { _c.Error("error executing: {0} {1}", select, ex.Message); _c.Error(ex, ex.Message); _c.Warn("rolling back"); trans.Rollback(); } } }
public void Write(IEnumerable <IRow> rows) { var sql = _output.SqlInsertIntoOutput(_cf); var count = 0; using (var cn = _cf.GetConnection()) { cn.Open(); var trans = cn.BeginTransaction(); try { foreach (var batch in rows.Partition(_output.Entity.InsertSize)) { var batchCount = cn.Execute( sql, batch.Select(r => r.ToExpandoObject(_output.OutputFields)), trans, 0, CommandType.Text ); count += batchCount; _output.Increment(batchCount); } trans.Commit(); } catch (Exception ex) { _output.Error(ex, ex.Message); trans.Rollback(); } _output.Debug(() => $"{count} to {_output.Connection.Name}"); } _output.Entity.Inserts += count; }
public void Write(IEnumerable <IRow> rows) { var sql = _context.SqlUpdateCalculatedFields(_parent, _cf); var temp = new List <Field> { _context.Entity.TflKey() }; temp.AddRange(_context.Entity.CalculatedFields.Where(f => f.Output)); var fields = temp.ToArray(); using (var cn = _cf.GetConnection()) { cn.Open(); var trans = cn.BeginTransaction(); try { foreach (var batch in rows.Partition(_context.Entity.UpdateSize)) { var data = batch.Select(r => r.ToExpandoObject(fields)); var batchCount = Convert.ToUInt32(cn.Execute(sql, data, trans, 0, CommandType.Text)); _context.Increment(batchCount); } trans.Commit(); } catch (Exception ex) { _context.Error(ex, ex.Message); trans.Rollback(); } } }
public void Write(IEnumerable <IRow> rows) { var query = _output.SqlUpdateOutput(_cf); var count = (uint)0; using (var cn = _cf.GetConnection()) { cn.Open(); _output.Debug(() => "begin transaction"); var trans = cn.BeginTransaction(); try { foreach (var batch in rows.Partition(_output.Entity.UpdateSize)) { var batchCount = Convert.ToUInt32(cn.Execute( query, batch.Select(r => r.ToExpandoObject(_output.OutputFields)), trans, 0, CommandType.Text )); count += batchCount; _output.Increment(batchCount); } _output.Debug(() => "commit transaction"); trans.Commit(); _output.Entity.Updates += count; _output.Info("{0} to {1}", count, _output.Connection.Name); } catch (Exception ex) { _output.Error(ex.Message); _output.Warn("rollback transaction"); trans.Rollback(); } } }
public void Write(IEnumerable <IRow> rows) { var tflHashCode = _output.Entity.TflHashCode(); var tflDeleted = _output.Entity.TflDeleted(); foreach (var part in rows.Partition(_output.Entity.InsertSize)) { var inserts = new List <IRow>(_output.Entity.InsertSize); var updates = new List <IRow>(_output.Entity.InsertSize); var batchCount = 0; if (_output.Entity.IsFirstRun) { foreach (var row in part) { inserts.Add(row); batchCount++; } } else { var batch = part.ToArray(); var matching = _matcher.Read(batch).ToArray(); for (int i = 0, batchLength = batch.Length; i < batchLength; i++) { var row = batch[i]; var match = matching.FirstOrDefault(f => f.Match(_keys, row)); if (match == null) { inserts.Add(row); } else { if (match[tflDeleted].Equals(true) || !match[tflHashCode].Equals(row[tflHashCode])) { updates.Add(row); } } batchCount++; } } if (inserts.Any()) { _inserter.Write(inserts); } if (updates.Any()) { _updater.Write(updates); } _output.Increment(batchCount); } _output.Info("{0} inserts into {1}", _output.Entity.Inserts, _output.Connection.Name); _output.Info("{0} updates to {1}", _output.Entity.Updates, _output.Connection.Name); }
public void Write(IEnumerable <IRow> rows) { var builder = new StringBuilder(); var fullCount = 0; var batchCount = (uint)0; foreach (var part in rows.Partition(_context.Entity.InsertSize)) { foreach (var row in part) { batchCount++; fullCount++; foreach (var field in _context.OutputFields) { switch (field.Type) { case "guid": row[field] = ((Guid)row[field]).ToString(); break; case "datetime": row[field] = ((DateTime)row[field]).ToString("o"); break; } if (field.SearchType == "geo_point") { row[field] = new Dictionary <string, string> { { "text", row[field].ToString() }, { "location", row[field].ToString() } }; } } builder.Append(_prefix); foreach (var key in _context.OutputFields.Where(f => f.PrimaryKey)) { builder.Append(row[key]); } builder.AppendLine("\"}}"); builder.AppendLine(JsonConvert.SerializeObject(_context.OutputFields.ToDictionary(f => f.Alias.ToLower(), f => row[f]))); } var response = _client.Bulk <DynamicResponse>(builder.ToString(), nv => nv .AddQueryString("refresh", @"true") ); _context.Increment(@by: batchCount); if (response.Success) { _context.Debug(() => $"{batchCount} to output"); } else { _context.Error(response.OriginalException.Message); } builder.Clear(); batchCount = 0; } _context.Info($"{fullCount} to output"); }
public IEnumerable <IRow> Read() { var batches = _parent.Entities.Select(e => e.BatchId).ToArray(); var minBatchId = batches.Min(); var maxBatchId = batches.Max(); _output.Info("Batch Range: {0} to {1}.", minBatchId, maxBatchId); var sql = $"SELECT {string.Join(",", _output.Entity.Fields.Where(f=>f.Output).Select(f => _cf.Enclose(f.Alias)))} FROM {_cf.Enclose(_output.Process.Star)} {(_cf.AdoProvider == AdoProvider.SqlServer ? "WITH (NOLOCK)" : string.Empty)} WHERE {_cf.Enclose(Constants.TflBatchId)} BETWEEN @MinBatchId AND @MaxBatchId;"; _output.Debug(() => sql); using (var cn = _cf.GetConnection()) { cn.Open(); var cmd = cn.CreateCommand(); cmd.CommandTimeout = 0; cmd.CommandType = CommandType.Text; cmd.CommandText = sql; var min = cmd.CreateParameter(); min.ParameterName = "@MinBatchId"; min.Value = minBatchId; min.Direction = ParameterDirection.Input; min.DbType = DbType.Int32; var max = cmd.CreateParameter(); max.ParameterName = "@MaxBatchId"; max.Value = maxBatchId; max.Direction = ParameterDirection.Input; max.DbType = DbType.Int32; cmd.Parameters.Add(min); cmd.Parameters.Add(max); var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess); var rowCount = 0; var fieldArray = _output.Entity.Fields.ToArray(); while (reader.Read()) { rowCount++; _output.Increment(); yield return(_rowCreator.Create(reader, fieldArray)); } _output.Info("{0} from {1}", rowCount, _output.Connection.Name); } }
public void Update() { var status = _c.GetEntityStatus(); if (!status.NeedsUpdate()) { return; } using (var cn = _cf.GetConnection()) { cn.Open(); var select = SelectStatement(status); var update = UpdateStatement(status); var trans = cn.BeginTransaction(); try { var rows = new List <ExpandoObject>(); using (var reader = cn.ExecuteReader(select, new { TflBatchId = _c.Entity.BatchId, MasterTflBatchId = _master.BatchId }, trans, 0, CommandType.Text)) { while (reader.Read()) { var obj = new ExpandoObject(); var dict = (IDictionary <string, object>)obj; for (var i = 0; i < reader.FieldCount; i++) { dict[reader.GetName(i)] = reader.GetValue(i); } dict[_master.TflBatchId().FieldName()] = _c.Entity.BatchId; rows.Add(obj); } } if (rows.Any()) { foreach (var batch in rows.Partition(_master.UpdateSize)) { var expanded = batch.ToArray(); cn.Execute(update, expanded, trans); _c.Increment(expanded.Length); } } trans.Commit(); } catch (Exception ex) { _c.Error("error executing: {0} {1}", select, ex.Message); _c.Error(ex, ex.Message); _c.Warn("rolling back"); trans.Rollback(); } } }
public void Write(IEnumerable <IRow> rows) { var fields = _context.Entity.GetAllOutputFields().Cast <IField>().ToArray(); var keys = fields.Select(f => f.Alias).ToArray(); _context.Entity.Rows.Clear(); foreach (var row in rows) { _context.Entity.Rows.Add(row.ToCfgRow(fields, keys)); _context.Increment(); } if (_context.Entity.Inserts > 0) { _context.Info("{0} inserts into {1} {2}", _context.Entity.Inserts, _context.Connection.Name, _context.Entity.Alias); } }
public ActionResponse Execute() { var updateVariables = string.Join(", ", _model.Fields.Where(f => f.Name != _model.KeyLongName).Select(f => $"{_cf.Enclose(f.Alias)} = @{f.FieldName()}")); var builder = new StringBuilder(); builder.AppendLine($"UPDATE {_output.Process.Flat}"); builder.AppendLine($"SET {updateVariables}"); builder.AppendLine($"WHERE {_model.EnclosedKeyLongName} = @{_model.KeyShortName};"); var command = builder.ToString(); using (var cn = _cf.GetConnection()) { cn.Open(); var count = 0; var trans = cn.BeginTransaction(); try { foreach (var batch in Read(cn, _sql, _model).Partition(_model.MasterEntity.UpdateSize)) { var expanded = batch.ToArray(); cn.Execute(command, expanded, trans); count += expanded.Length; _output.Increment(Convert.ToUInt32(expanded.Length)); } trans.Commit(); _output.Info($"{count} record{count.Plural()} updated in flat"); } catch (Exception ex) { _output.Error(ex.Message); _output.Error(_sql); _output.Warn("rolling back"); trans.Rollback(); return(new ActionResponse(500, ex.Message) { Action = new Configuration.Action { Type = "internal", Description = "Flatten Action", ErrorMode = "abort" } }); } } return(new ActionResponse(200, "Ok")); }
public void Write(IEnumerable <IRow> rows) { if (!string.IsNullOrEmpty(_context.Connection.Header)) { Builder.AppendLine(_context.Connection.Header); } var fields = _context.Entity.GetAllOutputFields().Cast <IField>().ToArray(); foreach (var row in rows) { foreach (var field in fields) { Builder.Append(row[field]); } Builder.AppendLine(); _context.Increment(); } if (!string.IsNullOrEmpty(_context.Connection.Footer)) { Builder.Append(_context.Connection.Footer); } }
public void Write(IEnumerable <IRow> rows) { var fields = _context.Entity.GetAllOutputFields().Cast <IField>().ToArray(); var keys = fields.Select(f => f.Alias).ToArray(); var cleared = false; foreach (var row in rows) { // only clear the output if rows need to be written, otherwise leave it alone if (!cleared) { _context.Entity.Rows.Clear(); cleared = true; } _context.Entity.Rows.Add(row.ToCfgRow(fields, keys)); _context.Increment(); } if (_context.Entity.Inserts > 0) { _context.Info("{0} inserts into {1} {2}", _context.Entity.Inserts, _context.Connection.Name, _context.Entity.Alias); } }
public void Write(IEnumerable <IRow> rows) { using (var cn = new SqlConnection(_cf.GetConnectionString())) { cn.Open(); SqlDataAdapter adapter; var dt = new DataTable(); using (adapter = new SqlDataAdapter(_output.SqlSelectOutputSchema(_cf), cn)) { adapter.Fill(dt); } var bulkCopy = new SqlBulkCopy(cn, _bulkCopyOptions, null) { BatchSize = _output.Entity.InsertSize, BulkCopyTimeout = 0, DestinationTableName = "[" + _output.Entity.OutputTableName(_output.Process.Name) + "]" }; for (var i = 0; i < _output.OutputFields.Length; i++) { bulkCopy.ColumnMappings.Add(i, i); } foreach (var part in rows.Partition(_output.Entity.InsertSize)) { var batch = part.ToArray(); if (_output.Entity.IsFirstRun || (_output.Entity.Insert && !_output.Entity.Update)) { var inserts = new List <IRow>(); inserts.AddRange(batch); Insert(bulkCopy, dt, inserts); } else { var inserts = new ConcurrentBag <IRow>(); var updates = new ConcurrentBag <IRow>(); var tflHashCode = _output.Entity.TflHashCode(); var tflDeleted = _output.Entity.TflDeleted(); var matching = _outputKeysReader.Read(batch).AsParallel().ToArray(); Parallel.ForEach(batch, (row) => { var match = matching.FirstOrDefault(f => f.Match(_keys, row)); if (match == null) { inserts.Add(row); } else { if (match[tflDeleted].Equals(true)) { updates.Add(row); } else { var destination = (int)match[tflHashCode]; var source = (int)row[tflHashCode]; if (source != destination) { updates.Add(row); } } } }); Insert(bulkCopy, dt, inserts); if (updates.Any()) { _sqlUpdater.Write(updates); } } _output.Increment(Convert.ToUInt32(batch.Length)); } if (_output.Entity.Inserts > 0) { _output.Info("{0} inserts into {1} {2}", _output.Entity.Inserts, _output.Connection.Name, _output.Entity.Alias); } if (_output.Entity.Updates > 0) { _output.Info("{0} updates to {1}", _output.Entity.Updates, _output.Connection.Name); } } }
public void Write(IEnumerable <IRow> rows) { var table = _output.Entity.OutputTableName(_output.Process.Name); using (var cn = new SqlCeConnection(_cf.GetConnectionString())) { cn.Open(); foreach (var part in rows.Partition(_output.Entity.InsertSize)) { var batch = part.ToArray(); if (_output.Entity.IsFirstRun) { var inserts = new List <IRow>(); inserts.AddRange(batch); Insert(inserts, cn, table); } else { var inserts = new ConcurrentBag <IRow>(); var updates = new ConcurrentBag <IRow>(); var tflHashCode = _output.Entity.TflHashCode(); var tflDeleted = _output.Entity.TflDeleted(); var matching = _outputKeysReader.Read(batch).AsParallel().ToArray(); Parallel.ForEach(batch, (row) => { var match = matching.FirstOrDefault(f => f.Match(_keys, row)); if (match == null) { inserts.Add(row); } else { if (match[tflDeleted].Equals(true)) { updates.Add(row); } else { var destination = (int)match[tflHashCode]; var source = (int)row[tflHashCode]; if (source != destination) { updates.Add(row); } } } }); Insert(inserts, cn, table); if (updates.Any()) { _output.Entity.OutputTableName(_output.Process.Name); _sqlUpdater.Write(updates); } } _output.Increment(Convert.ToUInt32(batch.Length)); } if (_output.Entity.Inserts > 0) { _output.Info("{0} inserts into {1} {2}", _output.Entity.Inserts, _output.Connection.Name, _output.Entity.Alias); } if (_output.Entity.Updates > 0) { _output.Info("{0} updates to {1}", _output.Entity.Updates, _output.Connection.Name); } } }
public void Write(IEnumerable <IRow> rows) { var table = _output.Entity.OutputTableName(_output.Process.Name); using (var cn = new SqlCeConnection(_cf.GetConnectionString())) { cn.Open(); foreach (var part in rows.Partition(_output.Entity.InsertSize)) { var batch = part.ToArray(); if (_output.Process.Mode == "init") { var inserts = new List <IRow>(); inserts.AddRange(batch); Insert(inserts, cn, table); } else { var inserts = new List <IRow>(); var updates = new List <IRow>(); var tflHashCode = _output.Entity.TflHashCode(); var tflDeleted = _output.Entity.TflDeleted(); var matching = _outputKeysReader.Read(batch); for (int i = 0, batchLength = batch.Length; i < batchLength; i++) { var row = batch[i]; if (matching.Contains(i)) { if (matching[i][tflDeleted].Equals(true) || !matching[i][tflHashCode].Equals(row[tflHashCode])) { updates.Add(row); } } else { inserts.Add(row); } } Insert(inserts, cn, table); if (updates.Any()) { _output.Entity.OutputTableName(_output.Process.Name); _sqlUpdater.Write(updates); } } _output.Increment(Convert.ToUInt32(batch.Length)); } if (_output.Entity.Inserts > 0) { _output.Info("{0} inserts into {1} {2}", _output.Entity.Inserts, _output.Connection.Name, _output.Entity.Alias); } if (_output.Entity.Updates > 0) { _output.Info("{0} updates to {1}", _output.Entity.Updates, _output.Connection.Name); } } }
public void Write(IEnumerable <Row> rows) { var firstRun = _output.Entity.IsFirstRun(); using (var cn = new SqlConnection(_output.Connection.GetConnectionString())) { cn.Open(); SqlDataAdapter adapter; var dt = new DataTable(); using (adapter = new SqlDataAdapter(_output.SqlSelectOutputSchema(), cn)) { adapter.Fill(dt); } var bulkCopy = new SqlBulkCopy(cn, _bulkCopyOptions, null) { BatchSize = _output.Entity.InsertSize, BulkCopyTimeout = _output.Connection.Timeout, DestinationTableName = "[" + _output.Entity.OutputTableName(_output.Process.Name) + "]", }; var counter = 0; for (int i = 0; i < _output.OutputFields.Length; i++) { bulkCopy.ColumnMappings.Add(i, i); counter++; } bulkCopy.ColumnMappings.Add(counter, counter); //TflBatchId foreach (var part in rows.Partition(_output.Entity.InsertSize)) { var inserts = new List <DataRow>(_output.Entity.InsertSize); var updates = new List <Row>(); var batchCount = 0; if (firstRun) { foreach (var row in part) { inserts.Add(GetDataRow(dt, row)); batchCount++; } } else { var matching = _outputKeysReader.Read(part); var batch = part.ToArray(); for (int i = 0, batchLength = batch.Length; i < batchLength; i++) { var row = batch[i]; var match = matching.FirstOrDefault(f => f.Match(_keys, row)); if (match == null) { inserts.Add(GetDataRow(dt, row)); } else { if (!match.TflHashCode.Equals(row.TflHashCode)) { updates.Add(row); } } batchCount++; } } if (inserts.Any()) { bulkCopy.WriteToServer(inserts.ToArray()); _output.Entity.Inserts += inserts.Count; } if (updates.Any()) { _sqlUpdater.Write(updates); _output.Entity.Updates += updates.Count; } _output.Increment(batchCount); } _output.Info("{0} inserts into {1}", _output.Entity.Inserts, _output.Connection.Name); _output.Info("{0} updates to {1}", _output.Entity.Updates, _output.Connection.Name); } }
public void Write(IEnumerable <IRow> rows) { var tflHashCode = _output.Entity.TflHashCode(); var tflDeleted = _output.Entity.TflDeleted(); foreach (var part in rows.Partition(_output.Entity.InsertSize)) { var inserts = new List <IRow>(_output.Entity.InsertSize); var updates = new List <IRow>(_output.Entity.InsertSize); var batchCount = (uint)0; if (_output.Process.Mode == "init" || (_output.Entity.Insert && !_output.Entity.Update)) { foreach (var row in part) { inserts.Add(row); batchCount++; } } else { var newRows = part.ToArray(); var oldRows = _matcher.Read(newRows); for (int i = 0, batchLength = newRows.Length; i < batchLength; i++) { var row = newRows[i]; if (oldRows.Contains(i)) { if (oldRows[i][tflDeleted].Equals(true) || !oldRows[i][tflHashCode].Equals(row[tflHashCode])) { updates.Add(row); } } else { inserts.Add(row); } batchCount++; } } if (inserts.Any()) { _inserter.Write(inserts); } if (updates.Any()) { _updater.Write(updates); } _output.Increment(batchCount); } if (_output.Entity.Inserts > 0) { _output.Info("{0} inserts into {1}", _output.Entity.Inserts, _output.Connection.Name); } if (_output.Entity.Updates > 0) { _output.Info("{0} updates to {1}", _output.Entity.Updates, _output.Connection.Name); } }
public void Write(IEnumerable <IRow> rows) { using (var cn = new SqlConnection(_cf.GetConnectionString())) { cn.Open(); SqlDataAdapter adapter; var dt = new DataTable(); using (adapter = new SqlDataAdapter(_output.SqlSelectOutputSchema(_cf), cn)) { adapter.Fill(dt); } var bulkCopy = new SqlBulkCopy(cn, _bulkCopyOptions, null) { BatchSize = _output.Entity.InsertSize, BulkCopyTimeout = 0, DestinationTableName = "[" + _output.Entity.OutputTableName(_output.Process.Name) + "]" }; for (var i = 0; i < _output.OutputFields.Length; i++) { bulkCopy.ColumnMappings.Add(i, i); } foreach (var part in rows.Partition(_output.Entity.InsertSize)) { var batch = part.ToArray(); if (_output.Process.Mode == "init" || (_output.Entity.Insert && !_output.Entity.Update)) { var inserts = new List <IRow>(); inserts.AddRange(batch); Insert(bulkCopy, dt, inserts); } else { var inserts = new List <IRow>(); var updates = new List <IRow>(); var tflHashCode = _output.Entity.TflHashCode(); var tflDeleted = _output.Entity.TflDeleted(); var matching = _outputKeysReader.Read(batch); for (int i = 0, batchLength = batch.Length; i < batchLength; i++) { var row = batch[i]; if (matching.Contains(i)) { if (matching[i][tflDeleted].Equals(true) || !matching[i][tflHashCode].Equals(row[tflHashCode])) { updates.Add(row); } } else { inserts.Add(row); } } Insert(bulkCopy, dt, inserts); if (updates.Any()) { _sqlUpdater.Write(updates); } } _output.Increment(Convert.ToUInt32(batch.Length)); } if (_output.Entity.Inserts > 0) { _output.Info("{0} inserts into {1} {2}", _output.Entity.Inserts, _output.Connection.Name, _output.Entity.Alias); } if (_output.Entity.Updates > 0) { _output.Info("{0} updates to {1}", _output.Entity.Updates, _output.Connection.Name); } } }
public void Write(IEnumerable <IRow> rows) { using (var cn = new SqlConnection(_cf.GetConnectionString())) { cn.Open(); SqlDataAdapter adapter; var dt = new DataTable(); using (adapter = new SqlDataAdapter(_output.SqlSelectOutputSchema(_cf), cn)) { adapter.Fill(dt); } var bulkCopy = new SqlBulkCopy(cn, _bulkCopyOptions, null) { BatchSize = _output.Entity.InsertSize, BulkCopyTimeout = 0, DestinationTableName = "[" + _output.Entity.OutputTableName(_output.Process.Name) + "]" }; for (var i = 0; i < _output.OutputFields.Length; i++) { bulkCopy.ColumnMappings.Add(i, i); } var tflHashCode = _output.Entity.TflHashCode(); var tflDeleted = _output.Entity.TflDeleted(); foreach (var part in rows.Partition(_output.Entity.InsertSize)) { var inserts = new List <DataRow>(_output.Entity.InsertSize); var updates = new List <IRow>(); var batchCount = 0; if (_output.Entity.IsFirstRun) { foreach (var row in part) { inserts.Add(GetDataRow(dt, row)); batchCount++; } } else { var batch = part.ToArray(); var matching = _outputKeysReader.Read(batch).ToArray(); for (int i = 0, batchLength = batch.Length; i < batchLength; i++) { var row = batch[i]; var match = matching.FirstOrDefault(f => f.Match(_keys, row)); if (match == null) { inserts.Add(GetDataRow(dt, row)); } else { if (match[tflDeleted].Equals(true)) { updates.Add(row); } else { var destination = (int)match[tflHashCode]; var source = (int)row[tflHashCode]; if (source != destination) { updates.Add(row); } } } batchCount++; } } if (inserts.Any()) { try { bulkCopy.WriteToServer(inserts.ToArray()); _output.Entity.Inserts += inserts.Count; } catch (Exception ex) { _output.Error(ex.Message); } } if (updates.Any()) { _sqlUpdater.Write(updates); } _output.Increment(batchCount); } if (_output.Entity.Inserts > 0) { _output.Info("{0} inserts into {1} {2}", _output.Entity.Inserts, _output.Connection.Name, _output.Entity.Alias); } if (_output.Entity.Updates > 0) { _output.Info("{0} updates to {1}", _output.Entity.Updates, _output.Connection.Name); } } }