public AdoFlattenTwoPartUpdateAction(
OutputContext output,
IConnectionFactory cf,
AdoSqlModel model,
string sql
)
{
_output = output;
_cf = cf;
_model = model;
_sql = sql;
}
private static IEnumerable <ExpandoObject> Read(IDbConnection cn, string sql, AdoSqlModel model)
{
using (var reader = cn.ExecuteReader(sql, new { model.Threshold }, null, 0, CommandType.Text)) {
while (reader.Read())
{
var obj = new ExpandoObject();
var dict = (IDictionary <string, object>)obj;
for (var i = 0; i < model.FieldNames.Length; i++)
{
dict[model.FieldNames[i]] = reader.GetValue(i);
}
yield return(obj);
}
}
}
public ActionResponse Execute()
{
if (_output.Process.Entities.Sum(e => e.Inserts + e.Updates + e.Deletes) == 0)
{
return(new ActionResponse(200, "nothing to flatten"));
}
var model = new AdoSqlModel(_output, _cf);
if (model.MasterEntity.Inserts > 0)
{
if (_output.Process.IsFirstRun())
{
return(new AdoFlattenFirstRunAction(_output, _cf, model).Execute());
}
if (_cf.AdoProvider == AdoProvider.SqlCe)
{
var insertAction = new AdoFlattenInsertBySelectAction(_output, _cf, model);
var insertResponse = insertAction.Execute();
insertResponse.Action = new Action {
Type = "internal",
Description = "Flatten Action",
ErrorMode = "abort"
};
if (insertResponse.Code != 200)
{
return(insertResponse);
}
}
else
{
var insertAction = new AdoFlattenInsertByViewAction(_output, _cf, model);
var insertResponse = insertAction.Execute();
insertResponse.Action = new Action {
Type = "internal",
Description = "Flatten Action",
ErrorMode = "abort"
};
if (insertResponse.Code != 200)
{
return(insertResponse);
}
}
}
switch (_cf.AdoProvider)
{
case AdoProvider.SqlCe:
// this provider does NOT support views, and does NOT support UPDATE ... SET ... FROM ... syntax
var masterAlias = Utility.GetExcelName(model.MasterEntity.Index);
var builder = new StringBuilder();
builder.AppendLine($"SELECT {_output.SqlStarFields(_cf)}");
foreach (var from in _output.SqlStarFroms(_cf))
{
builder.AppendLine(@from);
}
builder.AppendFormat("INNER JOIN {0} flat ON (flat.{1} = {2}.{3})", _cf.Enclose(_output.Process.Flat), model.EnclosedKeyLongName, masterAlias, model.EnclosedKeyShortName);
builder.AppendLine($" WHERE {masterAlias}.{model.Batch} > @Threshold; ");
return(new AdoFlattenTwoPartUpdateAction(_output, _cf, model, builder.ToString()).Execute());
case AdoProvider.SqLite:
// this provider supports views, but does NOT support UPDATE ... SET ... FROM ... syntax
var sql = $@"
SELECT s.{string.Join(",s.", model.Aliases)}
FROM {model.Master} m
INNER JOIN {model.Flat} f ON (f.{model.EnclosedKeyLongName} = m.{model.EnclosedKeyShortName})
INNER JOIN {model.Star} s ON (s.{model.EnclosedKeyLongName} = m.{model.EnclosedKeyShortName})
WHERE m.{model.Batch} > @Threshold;";
return(new AdoFlattenTwoPartUpdateAction(_output, _cf, model, sql).Execute());
default:
// these providers support views and UPDATE ... SET ... FROM ... syntax (server based)
return(new AdoFlattenUpdateByViewAction(_output, _cf, model).Execute());
}
}
public AdoFlattenInsertBySelectAction(OutputContext output, IConnectionFactory cf, AdoSqlModel model)
{
_output = output;
_cf = cf;
_model = model;
}
public AdoFlattenFirstRunAction(OutputContext context, IConnectionFactory cf, AdoSqlModel model)
{
_context = context;
_cf = cf;
_model = model;
}
public AdoFlattenUpdateByViewAction(OutputContext output, IConnectionFactory cf, AdoSqlModel model)
{
_output = output;
_cf = cf;
_model = model;
}