public override void Execute()
{
using var cmd = DbConnection.CreateCommand();
cmd.CommandText = Query;
cmd.CommandTimeout = CommandTimeout;
try
{
using var rs = cmd.ExecuteReader();
int counter = 0;
while (rs.Read())
{
counter++;
var row = new Row();
for (int i = 0; i < rs.VisibleFieldCount; i++)
{
row.Add(rs.GetName(i), rs.GetValue(i));
}
Output.AddRow(row);
if (counter % 5000 == 0)
{
Log.Debug("Got {counter} rows", counter);
}
}
Log.Information("Retrieved a total of {counter} rows", counter);
}
catch (SqlException ex)
{
throw new StepException($"Failed to run query: {ex.Message}", ex);
}
}
/// <summary>
/// Adds all JsonObjects to the Output.
/// </summary>
public override void Execute()
{
foreach (var row in JsonObjects.Select(o => Row.FromJson(o)))
{
Output.AddRow(row);
}
}
public override void Execute()
{
bool needsPublishing = false;
while (Input.HasRowsAvailable)
{
var row = Input.RemoveRow();
if (!row.ContainsKey(LabelKey))
{
throw new StepException($"The current row does not contain a field '{LabelKey}'");
}
var label = row[LabelKey] as string;
if (string.IsNullOrEmpty(label))
{
continue;
}
if (!options.ContainsValue(label))
{
// create option set value
int?optionValue = null;
if (OptionValuePrefix != 0)
{
optionValue = GetFirstAvailableOptionSetValue();
}
optionValue = InsertOptionSetValue(label, optionValue);
options.Add(optionValue.Value, label);
row[LabelKey + "$value"] = optionValue;
Output.AddRow(row);
needsPublishing = true;
}
else
{
// add existing option set value to output
var optionValue = options.FirstOrDefault(kvp => kvp.Value == label).Key;
row[LabelKey + "$value"] = optionValue;
Output.AddRow(row);
}
}
if (needsPublishing)
{
if (isGlobalOptionSet)
{
Log.Information("Publishing option set {optionSetName}", optionSetName);
CrmServiceClient.Execute(new PublishXmlRequest()
{
ParameterXml =
$@"<importexportxml>
<optionsets>
<optionset>{optionSetName}</optionset>
</optionsets>
</importexportxml>"
});
}
else
{
Log.Information("Publishing entity {EntityLogicalName}", EntityLogicalName);
CrmServiceClient.PublishEntity(EntityLogicalName);
}
}
}
public override void Execute()
{
while (Input.HasRowsAvailable)
{
var inRow = Input.RemoveRow();
var outRow = new Row();
foreach (var column in inRow.Keys)
{
var rename = RenameMappings.FirstOrDefault(m => string.Equals(m.From, column, StringComparison.OrdinalIgnoreCase));
if (rename != null)
{
outRow[rename.To] = inRow[rename.From];
}
else if (!Remove.Contains(column))
{
outRow[column] = inRow[column];
}
}
foreach (var mapping in Add)
{
outRow[mapping.Field] = mapping.Value;
}
Output.AddRow(outRow);
}
}
public override void Execute()
{
var ctx = new MNISModel.MNISEntities(new Uri("http://data.parliament.uk/membersdataplatform/open/OData.svc"));
var constituencies = ctx.Constituencies
.Expand("ConstituencyAreas/Area")
.Where(c => c.EndDate == null);
foreach (var c in constituencies)
{
const int Country = 1;
const int Region = 8;
const int DistrictNI = 3;
const int FormerMetCounty = 4;
const int ShireCountyEngland = 9;
const int UnitaryAuthorityScotland = 12;
const int UnitaryAuthorityWales = 13;
var areas = c.ConstituencyAreas.Where(ca => ca.EndDate == null && ca.Area != null).
Select(ca => new { ca.Area.Name, Type = ca.Area.AreaType_Id }).Distinct().ToList();
var row = new Row
{
["Id"] = c.Constituency_Id,
["Name"] = c.Name,
["Country"] = areas.FirstOrDefault(a => a.Type == Country).Name,
["Region"] = areas.FirstOrDefault(a => a.Type == Region).Name,
["Counties"] = areas.Where(a => a.Type == DistrictNI || a.Type == FormerMetCounty ||
a.Type == ShireCountyEngland || a.Type == UnitaryAuthorityScotland ||
a.Type == UnitaryAuthorityWales).Select(a => a.Name).ToArray()
};
Output.AddRow(row);
}
}
public override void Execute()
{
var count = 0;
while (Input.HasRowsAvailable)
{
var row = Input.RemoveRow();
if (lastRow != null)
{
var(isDuplicate, master, subordinate) = DetectDuplicates(lastRow, row);
if (isDuplicate)
{
count++;
Log.Verbose("Found a duplicate\n{master}\n{subordinate}", master, subordinate);
var duplicateRow = new Row();
AddKeys(duplicateRow, master, "master$");
AddKeys(duplicateRow, subordinate, "subordinate$");
Output.AddRow(duplicateRow);
lastRow = master;
}
else
{
lastRow = row;
}
}
else
{
lastRow = row;
}
}
Log.Information("Found {count} duplicates.", count);
}
protected override void ProcessReponse(OrganizationResponse response, Row processedRow, OrganizationRequest request)
{
if (response is null)
{
throw new ArgumentNullException(nameof(response));
}
if (processedRow is null)
{
throw new ArgumentNullException(nameof(processedRow));
}
switch (response)
{
case CreateResponse cr:
processedRow[PrimaryKeyField] = cr.id;
Output.AddRow(processedRow);
break;
case UpsertResponse usr:
processedRow[PrimaryKeyField] = usr.Target.Id;
Output.AddRow(processedRow);
break;
case UpdateResponse _:
Output.AddRow(processedRow);
break;
case DeleteResponse _:
break;
}
}
public override void Execute()
{
while (Input.HasRowsAvailable)
{
recordCounter++;
var row = Input.RemoveRow();
Log.Write(LogLevel, "{recordCounter} {row}", recordCounter, row);
Output.AddRow(row);
}
}
protected override void ProcessReponse(OrganizationResponse response, Row processedRow, OrganizationRequest _)
{
if (response is MergeResponse)
{
Output.AddRow(processedRow);
}
else
{
Log.Error("Response {response} is not a MergeResponse.", response);
throw new StepException("Unexpected response received.");
}
}
public override void Execute()
{
while (Input.HasRowsAvailable)
{
var row = Input.RemoveRow();
rowCounter++;
if (rowCounter <= Top)
{
Output.AddRow(row);
}
}
}
public override void Execute()
{
using var cmd = DbConnection.CreateCommand();
cmd.CommandText = Query;
cmd.CommandTimeout = CommandTimeout;
int counter = 0;
while (Input.HasRowsAvailable)
{
var row = Input.RemoveRow();
// clear parameter values...
foreach (SqlParameter p in cmd.Parameters)
{
p.Value = null;
}
// create parameters
foreach (var column in row.Columns)
{
var parameter = "@" + column;
if (!cmd.Parameters.Contains(parameter))
{
cmd.Parameters.AddWithValue(parameter, row[column]);
}
else
{
cmd.Parameters[parameter].Value = row[column];
}
}
try
{
int affectedRows = cmd.ExecuteNonQuery();
Output.AddRow(row);
Log.Verbose("Command {cmd} affected {affectedRows} rows.", cmd, affectedRows);
if (++counter % 10000 == 0)
{
Log.Debug("Pushed {counter} rows", counter);
}
}
catch (Exception ex)
{
Log.Error("'{ex.Message}' for row {row}", ex.Message, row);
if (!ContinueOnError)
{
throw new StepException("Failed to run query.");
}
}
}
Log.Information("Pushed a total of {counter} rows", counter);
}
public override void Execute()
{
int counter = 0;
while (Input.HasRowsAvailable)
{
var row = Input.RemoveRow();
var key = row[KeyField];
if (key is null)
{
throw new StepException("Key must not be null");
}
if (!recordsAlreadyAdded.Contains(key))
{
recordsAlreadyAdded.Add(key);
Output.AddRow(row);
counter++;
}
}
}
/// <summary>
/// Maps all values for all columns specified in ColumnMappings.
/// </summary>
/// <remarks>
/// Null values are removed. Values not specified in Mappings are removed.
/// </remarks>
public override void Execute()
{
while (Input.HasRowsAvailable)
{
var row = Input.RemoveRow();
foreach (var columnMapping in ColumnMappings)
{
if (row.ContainsKey(columnMapping.Column))
{
var originalValue = row[columnMapping.Column];
var originalValueAsString = originalValue?.ToString();
row.Remove(columnMapping.Column);
if (mappings[columnMapping.Column].ContainsKey(originalValueAsString))
{
row.Add(columnMapping.Column, mappings[columnMapping.Column][originalValueAsString]);
}
}
}
Output.AddRow(row);
}
}
