コード例 #1
0
        static string overwriteSql(JobSettings settings, string target, string source, string primaryKey, IEnumerable <string> restOfColumns)
        {
            string sql         = $"UPDATE t SET ";
            var    firstColumn = true;

            foreach (var column in restOfColumns)
            {
                if (firstColumn)
                {
                    firstColumn = false;
                }
                else
                {
                    sql += ",\n";
                }
                sql += $"[{column}] = ISNULL(s.[{column}],t.[{column}])";
            }
            if (settings.UseAuditColumnsOnImport.Value)
            {
                sql += @",
@modifiedUser = SUSER_NAME(),
@modifiedDate = GETDATE()";
            }
            sql += $"\nFROM {target} t\nINNER JOIN {source} s ON t.{primaryKey} = s.{primaryKey}";
            return(sql);
        }
コード例 #2
0
 public bool Initialize <T>(JobSettings settings, IErrorHandler errorHandler)
 {
     Fields.AddRange(typeof(T).GetProperties().Select(p => new Core.Table.Field {
         Name = p.Name, IsNullable = TypeCanBeNull(p.PropertyType)
     }));
     return(Initialize(settings, errorHandler));
 }
コード例 #3
0
        public bool Initialize(SqlConnection connection, JobSettings settings, IErrorHandler errorHandler)
        {
            Fields.AddRange(connection.Query <Field>(@"
SELECT c.Name, c.is_identity as IsPrimaryKey, c.is_nullable AS IsNullable, df.definition AS DefaultValue
FROM sys.all_objects o
INNER JOIN sys.all_columns c ON o.object_id = c.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
LEFT JOIN sys.default_constraints df ON c.default_object_id =  df.object_id
WHERE o.Name = @table AND s.Name = @schema
ORDER BY column_id
", new { table = BasicName, schema = SchemaName }));
            return(Initialize(settings, errorHandler));
        }
コード例 #4
0
        public static string GetSqlForMergeStrategy(JobSettings settings, Table table)
        {
            var target        = table.QualifiedName;
            var source        = "##" + table.BasicName;
            var primaryKey    = table.PrimaryKey;
            var restOfColumns = table.DataFields;
            var configObject  = new
            {
                target           = target,
                id               = primaryKey,
                columns          = string.Join(",", restOfColumns.Select(c => $"[{c}]")),
                source           = source,
                columnUpdateList = string.Join(",", restOfColumns.Select(r => $"[{r}]=s.[{r}]")),
                modifiedUser     = settings.AuditColumns?.ModifiedUser,
                modifiedDate     = settings.AuditColumns?.ModifiedDate,
                createdUser      = settings.AuditColumns?.CreatedUser,
                createdDate      = settings.AuditColumns?.CreatedDate
            };
            string sqlToUse = null;

            switch (table.MergeStrategy ?? settings.MergeStrategy)
            {
            case Strategy.MergeWithoutDelete:
                sqlToUse = getInsert(settings) + "\n" + getUpdate(settings);
                break;

            case Strategy.MergeWithDelete:
                sqlToUse = getInsert(settings) + "\n" + getUpdate(settings) + "\n" + delete;
                break;

            case Strategy.AddOnly:
                sqlToUse = getInsert(settings);
                break;

            case Strategy.Override:
                sqlToUse = getInsert(settings) + "\n" + overwriteSql(settings, target, source, primaryKey, restOfColumns);
                break;

            case Strategy.DropReadd:
                sqlToUse = dropReadd;
                break;

            default:
                throw new NotImplementedException("That merge strategy is not yet supported");
            }
            return(sqlToUse.FormatWith(configObject));
        }
コード例 #5
0
 public SqlSimpleDataWriter(StreamWriter file, Table table, JobSettings settings)
 {
     outputFormatter = new FileOutputFormatter(file);
     this.table      = table;
     this.settings   = settings;
 }
コード例 #6
0
 public SqlSimpleDataWriter(string connectionString, Table table, JobSettings settings)
 {
     outputFormatter = new SqlOutputFormatter(connectionString);
     this.table      = table;
     this.settings   = settings;
 }
コード例 #7
0
 static string getInsert(JobSettings settings) => settings.UseAuditColumnsOnImport.Value ? insertWithAudit : insert;
コード例 #8
0
 static string getUpdate(JobSettings settings) => settings.UseAuditColumnsOnImport.Value ? updateWithAudit : update;
コード例 #9
0
        bool Initialize(JobSettings settings, IErrorHandler errorHandler)
        {
            this.settings = settings;
            var auditColumns = settings.AuditColumns.AuditColumnNames().Select(c => c.ToLowerInvariant()).ToList();

            if (settings.UseAuditColumnsOnImport ?? false)
            {
                foreach (var field in Fields)
                {
                    if (auditColumns.Contains(field.CanonicalName))
                    {
                        field.IsAuditingColumn = true;
                    }
                }
            }

            if (!Fields.Any())
            {
                errorHandler.Error($"Could not find any information for table {Name}. Make sure it exists in the target database");
                return(false);
            }

            var data = Fields.Select(f => f.Name.ToLowerInvariant());

            if (PrimaryKey == null)
            {
                var primaryKeys = Fields.Where(f => f.IsPrimaryKey).ToList();
                if (primaryKeys.Count == 0)
                {
                    //errorHandler.Warning($"No primary key set for table {Name}, trying to infer from name");
                    primaryKeys = Fields.Where(f => f.Name.ToLowerInvariant() == "id" || f.Name.ToLowerInvariant() == BasicName.ToLowerInvariant() + "id").ToList();
                }
                if (primaryKeys.Count > 1)
                {
                    errorHandler.Error($"Multiple primary keys found for table {Name} ({string.Join(", ", primaryKeys.Select(pk => pk.Name))}). Please specify one manually.");
                    return(false);
                }
                if (!primaryKeys.Any())
                {
                    errorHandler.Error($"No primary key could be found for table {Name}. Please specify one manually");
                    return(false);
                }

                PrimaryKey = primaryKeys.Single().Name;
            }
            PrimaryKey = PrimaryKey.ToLowerInvariant();

            data = data.Where(f => f != PrimaryKey);

            if (settings.UseAuditColumnsOnImport ?? false)
            {
                data = data.Where(f => !settings.AuditColumns.AuditColumnNames().Select(a => a.ToLowerInvariant()).Contains(f));
            }

            if (IsEnvironmentSpecific)
            {
                data = data.Where(f => f != "isenvironmentspecific");
            }

            DataFields = data.ToList();
            return(true);
        }