public string GetConnectionString(AbstractConnection connection) { if (string.IsNullOrEmpty(ServerProperty)) return string.Empty; var builder = new DbConnectionStringBuilder { { ServerProperty, connection.Server } }; if (!string.IsNullOrEmpty(connection.Database)) { builder.Add(DatabaseProperty, connection.Database); } if (!String.IsNullOrEmpty(connection.User)) { builder.Add(UserProperty, connection.User); builder.Add(PasswordProperty, connection.Password); } else { if (!String.IsNullOrEmpty(TrustedProperty)) { builder.Add(TrustedProperty, true); } } if (PersistSecurityInfoProperty != string.Empty && connection.PersistSecurityInfo != string.Empty) { builder.Add(PersistSecurityInfoProperty, connection.PersistSecurityInfo); } if (connection.Port <= 0) return builder.ConnectionString; if (PortProperty == string.Empty) { builder[ServerProperty] += "," + connection.Port; } else { builder.Add("Port", connection.Port); } return builder.ConnectionString; }
public static string Select(Entity entity, AbstractConnection connection) { var maxDop = connection.MaxDop ? " OPTION (MAXDOP 2)" : string.Empty; var withNoLock = entity.NoLock && connection.NoLock ? " WITH(NOLOCK)" : string.Empty; var tableSample = string.Empty; if (entity.Sample > 0m && entity.Sample < 100m && connection.TableSample) { connection.Logger.EntityInfo(entity.Name, "Sample enforced at query level: {0:##} percent.", entity.Sample); tableSample = string.Format(" TABLESAMPLE ({0:##} PERCENT)", entity.Sample); } var where = string.Empty; if (entity.Filters.Any()) { where = " WHERE " + entity.Filters.ResolveExpression(connection.TextQualifier); } var sqlPattern = "\r\nSELECT\r\n {0}\r\nFROM {1}" + tableSample + withNoLock + where + maxDop + ";"; var columns = new FieldSqlWriter(entity.Fields.WithInput()).Select(connection).Write(",\r\n "); var sql = string.Format(sqlPattern, columns, SafeTable(entity.Name, connection, entity.Schema)); if (entity.Sample > 0m && entity.Sample < 100m && connection.TableSample) { entity.Sampled = true; } return sql; }
public FileLoadOperation(AbstractConnection connection, Entity entity) { FileInfo = new FileInfo(connection.File); Headers = new List<string>(); HeaderText = string.Empty; FooterText = string.Empty; _connection = connection; _entity = entity; _isCsv = _connection.File.ToLower().EndsWith(".csv"); _fileFields.Add(_entity.Fields.WithFileOutput()); _fileFields.Add(_entity.CalculatedFields.WithFileOutput()); _stringFields = _fileFields.WithString().Aliases().ToArray(); _mapFields = _fileFields.WithIdentifiers().ToArray(); if (FileInfo.DirectoryName != null && !Directory.Exists(FileInfo.DirectoryName)) { Logger.Info("Creating Output Folder(s)."); Directory.CreateDirectory(FileInfo.DirectoryName); } if (FileInfo.Exists) return; Logger.EntityWarn(entity.Alias, "Output file already exists. Deleting..."); FileInfo.Delete(); }
private IOperation ComposeInputOperation(Process process, AbstractConnection connection) { if (connection.Schemas && _entity.Schema.Equals(string.Empty)) { _entity.Schema = connection.DefaultSchema; } if (_entity.HasSqlKeysOverride()) { return new SqlKeysOverrideOperation(_entity, connection); } if (!_entity.PrimaryKey.WithInput().Any()) { return new EmptyOperation(); } if (process.IsFirstRun || !_entity.CanDetectChanges(connection.IsDatabase)) { return connection.ExtractAllKeysFromInput(_process, _entity); } var operation = new EntityInputKeysExtractDelta(process, _entity, connection); if (operation.NeedsToRun()) { return operation; } return new EmptyOperation(); }
public FileContentsExtract(AbstractConnection fileConnection, Entity entity) { _fileInfo = new FileInfo(fileConnection.File); _output = entity.Fields.First().Alias; if (!_fileInfo.Exists) { throw new TransformalizeException(Logger, entity.Alias, "File {0} does not exist.", fileConnection.Name); } }
protected bool CheckConnection(AbstractConnection connection) { var result = false; try { using (var cn = connection.GetConnection()) { if (connection.Type.Equals(ProviderType.SqlServer)) { cn.ConnectionString = connection.GetConnectionString().TrimEnd(";".ToCharArray()) + string.Format(";Connection Timeout={0};", _timeOut); } else { cn.ConnectionString = connection.GetConnectionString(); } try { cn.Open(); result = cn.State == ConnectionState.Open; if (result) { _logger.Debug("{0} connection is ready.", connection.Name); } else { _logger.Warn("{0} connection is not responding.", connection.Name); } } catch (Exception e) { _logger.Error("{0} connection caused error message: {1}", connection.Name, e.Message); } } } catch (Exception ex) { throw new TransformalizeException(_logger, "{0} connection type '{1}' is unavailable. Make sure the assembly (*.dll) is in the same folder as your executable. Error Message: {2}", connection.Name, connection.Type, ex.Message); } CachedResults[connection.Name] = result; return result; }
public EntityInputKeysExtractDelta(Process process, Entity entity, AbstractConnection connection) : base(connection) { _entity = entity; _fields = _entity.PrimaryKey.WithInput().Aliases().ToArray(); _length = _fields.Length; EntityName = entity.Name; _entity.CheckForChanges(process, connection); if (!_entity.HasRows) { Debug("No data detected in {0}.", _entity.Alias); } if (_entity.BeginAndEndAreEqual()) { Debug("No changes detected in {0}.", _entity.Alias); } var keyQuery = _entity.CanDetectChanges(connection.IsDatabase) ? connection.KeyQuery(_entity) : connection.KeyAllQuery(_entity); var sql = _entity.HasRange ? connection.KeyRangeQuery(_entity) : keyQuery; Debug(sql); _sql = sql; }
public bool Check(AbstractConnection connection) { if (CachedResults.ContainsKey(connection.Name)) { return CachedResults[connection.Name]; } return CheckConnection(connection); }
public static string Select(Entity entity, AbstractConnection connection) { var withNoLock = entity.NoLock && connection.NoLock ? " WITH(NOLOCK)" : string.Empty; var tableSample = string.Empty; if (entity.Sample > 0m && entity.Sample < 100m && connection.TableSample) { connection.Logger.EntityInfo(entity.Name, "Sample enforced at query level: {0:##} percent.", entity.Sample); tableSample = $" TABLESAMPLE ({entity.Sample:##} PERCENT)"; } var where = string.Empty; if (entity.Filters.Any()) { where = " WHERE " + entity.Filters.ResolveExpression(connection.TextQualifier); } var order = string.Empty; if (entity.Order.Any()) { var orderBy = string.Join(", ", entity.Order.Select(o => $"[{o.Field}] {o.Sort.ToUpper()}")); order = " ORDER BY " + orderBy; } var sqlPattern = "\r\nSELECT\r\n {0}\r\nFROM {1}" + tableSample + withNoLock + where + order + ";"; var columns = new FieldSqlWriter(entity.Fields.WithInput()).Select(connection).Write(",\r\n "); var sql = string.Format(sqlPattern, columns, SafeTable(entity.Name, connection, entity.Schema)); if (entity.Sample > 0m && entity.Sample < 100m && connection.TableSample) { entity.Sampled = true; } return sql; }
public EntityKeysToOperations(ref Entity entity, AbstractConnection connection, bool firstRun, string operationColumn = "operation") { _entity = entity; _connection = connection; _firstRun = firstRun; _operationColumn = operationColumn; _key = _entity.PrimaryKey.WithInput(); _fields = _entity.Fields.WithInput(); }
public EntityInputKeysExtractAllForDelete(Entity entity, AbstractConnection connection) : base(connection) { _entity = entity; _connection = connection; _fields = _entity.PrimaryKey.WithInput().Aliases().ToArray(); _length = _fields.Length; EntityName = entity.Name; }
public static string Select(Fields fields, string leftTable, string rightTable, AbstractConnection connection, string leftSchema, string rightSchema) { var maxDop = connection.MaxDop ? "OPTION (MAXDOP 2);" : ";"; var sqlPattern = "\r\nSELECT\r\n {0}\r\nFROM {1} l\r\nINNER JOIN {2} r ON ({3})\r\n" + maxDop; var columns = new FieldSqlWriter(fields).Input().Select(connection).Prepend("l.").ToAlias(connection.L, connection.R, true).Write(",\r\n "); var join = new FieldSqlWriter(fields).FieldType(FieldType.MasterKey, FieldType.PrimaryKey).Name(connection.L, connection.R).Input().Set("l", "r").Write(" AND "); return string.Format(sqlPattern, columns, SafeTable(leftTable, connection, leftSchema), SafeTable(rightTable, connection, rightSchema), @join); }
public DapperBulkUpdateOperation(AbstractConnection connection, Entity entity) { _connection = connection; _tflBatchId = entity.TflBatchId; _fields = entity.OutputFields(); var writer = new FieldSqlWriter(_fields); var sets = writer.Alias(_connection.L, _connection.R).SetParam().Write(", ", false); _sql = string.Format(@"UPDATE [{0}] SET {1}, TflBatchId = @TflBatchId WHERE TflKey = @TflKey;", entity.OutputName(), sets); }
public FileExcelExtract(AbstractConnection connection, Entity entity, int top = 0) { _fields = entity.Fields.WithInput().ToArray(); _fileInfo = new FileInfo(connection.File); _start = connection.Start; _end = connection.End; if (top > 0) { _end = _start + top; } }
public EntityDataExtract(Fields fields, string sql, AbstractConnection connection) : base(connection) { _fields = fields.ToArray(); _length = _fields.Length; _sql = sql; UseTransaction = false; Name = "EntityDataExtract"; EntityName = fields[0].Entity; }
public void Drop(AbstractConnection connection, Entity entity) { if (!EntityExists.Exists(connection, entity)) return; var sql = string.Format(FORMAT, connection.Enclose(entity.OutputName())); using (var cn = connection.GetConnection()) { cn.Open(); cn.Execute(sql); connection.Logger.EntityDebug(entity.Name, "Dropped Output {0}", entity.OutputName()); } }
public FileDelimitedExtract(AbstractConnection connection, Entity entity, int top = 0) { var fileInfo = new FileInfo(connection.File); _entity = entity; _top = top; _fields = entity.Fields.WithInput().ToArray(); _delimiter = connection.Delimiter; _fullName = fileInfo.FullName; _name = fileInfo.Name; _ignoreFirstLines = connection.Start - 1; _errorMode = connection.ErrorMode; }
public IScriptReponse Execute(AbstractConnection connection, string script, int timeOut) { var response = new ScriptResponse(); using (var cn = connection.GetConnection()) { try { cn.Open(); var cmd = cn.CreateCommand(); cmd.CommandTimeout = timeOut; cmd.CommandText = script; cmd.CommandType = CommandType.Text; response.RowsAffected = cmd.ExecuteNonQuery(); response.Success = true; } catch (Exception e) { response.Messages.Add(e.Message); if (e.InnerException != null) { response.Messages.Add(e.InnerException.Message); } } } return response; }
public void Modify(ref AbstractConnection connection) { if (connection.Schemas) { switch (NameElements.Length) { case 4: connection.Server = Clean(NameElements[0]); connection.Database = Clean(NameElements[1]); Schema = Clean(NameElements[2]); Name = Clean(NameElements[3]); break; case 3: connection.Database = Clean(NameElements[0]); Schema = Clean(NameElements[1]); Name = Clean(NameElements[2]); break; default: throw new DataProfilerException("Can't parse {0}. Expecting 3 to 4 elements delimited by dots (e.g. Server.Database.Schema.Table or Database.Schema.Table).", FullyQualifiedName); } TflLogger.Info("DataProfiler", Name, "Connection modified, Server:{0}, Database:{1}, Schema:{2}, Name:{3}.", connection.Server, connection.Database, Schema, Name); } else { switch (NameElements.Length) { case 3: connection.Server = Clean(NameElements[0]); connection.Database = Clean(NameElements[1]); Name = Clean(NameElements[2]); break; case 2: connection.Database = Clean(NameElements[0]); Name = Clean(NameElements[1]); break; default: throw new DataProfilerException("Can't parse {0}. Expecting 2 to 3 elements delimited by dots (e.g. Server.Database.Table or Database.Table).", FullyQualifiedName); } TflLogger.Info("DataPofiler", Name, "Connection modified, Server:{0}, Database:{1}, Name:{2}.", connection.Server, connection.Database, Name); } }
public FileFixedExtract(AbstractConnection connection, Entity entity, int top = 0) { var fileInfo = new FileInfo(connection.File); _entity = entity; _top = top; _fields = _entity.Fields.WithInput().ToArray(); _fullName = fileInfo.FullName; _name = fileInfo.Name; _errorMode = connection.ErrorMode; int ignoreFirstLines = connection.Start - 1; _classBuilder = new FixedLengthClassBuilder("Tfl" + _entity.Alias) { IgnoreEmptyLines = true, IgnoreFirstLines = ignoreFirstLines }; foreach (var field in _fields) { var length = field.Length.Equals("max", IC) ? int.MaxValue : Convert.ToInt32(field.Length.Equals(string.Empty) ? "64" : field.Length); var builder = new FixedFieldBuilder(field.Alias, length, typeof(string)) { FieldNullValue = new String(' ', length) }; _classBuilder.AddField(builder); } }
/// <summary> /// Complete the process by writing a batch record to the output. Record the max date or rowversion read from input. /// </summary> /// <param name="process"></param> /// <param name="input"></param> /// <param name="entity"></param> /// <param name="force"></param> public abstract void WriteEndVersion(Process process, AbstractConnection input, Entity entity, bool force = false);
protected OutputCommandOperation(AbstractConnection connection) : base(connection) { }
/// <summary> /// Presents the field for a select. /// </summary> /// <returns>field's name</returns> public FieldSqlWriter Select(AbstractConnection connection) { foreach (var key in CopyOutputKeys()) { var field = _original[key]; _output[key] = SafeColumn(field.Name, connection.L, connection.R); } return this; }
public FieldSqlWriter PrependEntityOutput(AbstractConnection connection, string entityName = null) { foreach (var key in CopyOutputKeys()) { var field = _original[key]; var table = SafeColumn(entityName ?? field.EntityOutputName, connection.L, connection.R); _output[key] = string.Concat(table, ".", _output[key]); } return this; }
public ConventionInputCommandOperation(AbstractConnection connection) : base(connection) { UseTransaction = false; Timeout = 0; }
protected SqlBulkInsertOperation(AbstractConnection connection, string targetTable, int timeout = 0) : base(connection) { Guard.Against(string.IsNullOrEmpty(targetTable), "TargetTable was not set, but it is mandatory"); TargetTable = targetTable; _timeout = timeout; }
public static string BatchInsertValues(int size, string name, Fields fields, IEnumerable<Row> rows, AbstractConnection connection) { return connection.InsertMultipleRows ? InsertMultipleValues(size, name, fields, rows, connection) : InsertUnionedValues(size, name, fields, rows, connection); }
public ConnectionIs(AbstractConnection connection) { _connection = connection; }
public ConventionSqlBulkInsertOperation(AbstractConnection connection, string targetTable) : base(connection, targetTable) { }
private static string SafeTable(string name, AbstractConnection connection, string schema) { if (name.StartsWith("@")) return name; return connection.Schemas && !schema.Equals(string.Empty) ? string.Concat(connection.L, schema, string.Format("{0}.{1}", connection.R, connection.L), name, connection.R) : string.Concat(connection.L, name, connection.R); }
/// <summary> /// Complete the process by writing a batch record to the output. Record the max date or rowversion read from input. /// </summary> /// <param name="process"></param> /// <param name="input"></param> /// <param name="entity"></param> /// <param name="force"></param> public abstract void WriteEndVersion(Process process, AbstractConnection input, Entity entity, bool force = false);