public AdoFlattenUpdateByViewAction(OutputContext output, AdoSqlModel model, IDbConnection cn, IDbTransaction trans) { _output = output; _model = model; _cn = cn; _trans = trans; }
public AdoFlattenInsertByViewAction(OutputContext output, IConnectionFactory cf, AdoSqlModel model, IDbConnection cn, IDbTransaction trans) { _output = output; _cf = cf; _model = model; _trans = trans; _cn = cn; }
public AdoFlattenTwoPartUpdateAction( OutputContext output, IConnectionFactory cf, AdoSqlModel model, string sql ) { _output = output; _cf = cf; _model = model; _sql = sql; }
public AdoFlattenTwoPartUpdateAction( OutputContext output, IConnectionFactory cf, AdoSqlModel model, string sql, IDbConnection cn, IDbTransaction trans ) { _output = output; _cf = cf; _model = model; _sql = sql; _trans = trans; _cn = cn; }
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 (_output.Process.Mode == "init") { return(new AdoFlattenFirstRunAction(_output, _cf, model).Execute()); } ActionResponse updateResponse = null; ActionResponse insertResponse = null; using (var cn = _cf.GetConnection(Constants.ApplicationName)) { cn.Open(); using (var trans = cn.BeginTransaction()) { _output.Info("Begin Flat Insert/Update Transaction"); if (model.MasterEntity.Inserts > 0) { if (_cf.AdoProvider == AdoProvider.SqlCe) { insertResponse = new AdoFlattenInsertBySelectAction(_output, _cf, model, cn, trans).Execute(); insertResponse.Action = new Action { Type = "internal", Description = "Flatten Action", ErrorMode = "abort" }; if (insertResponse.Code != 200) { trans.Rollback(); return(insertResponse); } } else { insertResponse = new AdoFlattenInsertByViewAction(_output, _cf, model, cn, trans).Execute(); insertResponse.Action = new Action { Type = "internal", Description = "Flatten Action", ErrorMode = "abort" }; if (insertResponse.Code != 200) { trans.Rollback(); 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.Name + _output.Process.FlatSuffix), model.EnclosedKeyLongName, masterAlias, model.EnclosedKeyShortName); builder.AppendLine($" WHERE {masterAlias}.{model.Batch} > @Threshold; "); //todo: add common cn and trans updateResponse = new AdoFlattenTwoPartUpdateAction(_output, _cf, model, builder.ToString(), cn, trans).Execute(); break; 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;"; //todo: add common cn and trans updateResponse = new AdoFlattenTwoPartUpdateAction(_output, _cf, model, sql, cn, trans).Execute(); break; default: // these providers support views and UPDATE ... SET ... FROM ... syntax (server based) updateResponse = new AdoFlattenUpdateByViewAction(_output, model, cn, trans).Execute(); break; } if (updateResponse.Code == 200) { trans.Commit(); _output.Info("Committed Flat Insert/Update Transaction"); } else { updateResponse.Action = new Action { Type = "internal", Description = "Flatten Action", ErrorMode = "abort" }; trans.Rollback(); _output.Warn("Rolled Back Flat Insert/Update Transaction"); } return(updateResponse); } } }
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; }
public AdoFlattenInsertBySelectAction(OutputContext output, IConnectionFactory cf, AdoSqlModel model) { _output = output; _cf = cf; _model = model; }
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.Mode == "init") { 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()); } }