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);