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);
         }
     }
 }
Ejemplo n.º 3
0
        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;
 }