public void TestWriteAliasIsNull() { const string expected = "ISNULL([Field1], 'x'), ISNULL([Field2], 0)"; var actual = new FieldSqlWriter(_fields).Alias("[", "]").IsNull().Write(); Assert.AreEqual(expected, actual); }
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 void TestWriteName() { const string expected = "[f1], [f2]"; var actual = new FieldSqlWriter(_fields).Name("[", "]").Write(); Assert.AreEqual(expected, actual); }
protected override void PrepareCommand(Row row, SqlCommand command) { var fields = _entity.OutputFields(); var writer = new FieldSqlWriter(fields).AddDeleted(_entity); var sets = writer.Alias(_connection.L, _connection.R).SetParam().Write(", ", false); command.CommandText = string.Format(@" UPDATE [{0}] SET {1}, TflBatchId = @TflBatchId WHERE TflKey = @TflKey; ", _entity.OutputName(), sets); foreach (var field in fields) { AddParameter(command, field.Identifier, row[field.Alias]); } if (_entity.Delete) { AddParameter(command, "TflDeleted", false); } AddParameter(command, "TflKey", row["TflKey"]); AddParameter(command, "TflBatchId", _entity.TflBatchId); Logger.EntityDebug(_entity.Alias, command.CommandText); }
public void TestWriteNothing() { const string expected = ", "; var actual = new FieldSqlWriter(_fields).Write(); Assert.AreEqual(expected, actual); }
public void TestWriteAlias() { const string expected = "[Field1], [Field2]"; var actual = new FieldSqlWriter(_fields).Alias("[", "]").Write(); Assert.AreEqual(expected, actual); }
public void TestWriteNameIsNullToAlias() { const string expected = "ISNULL([f1], 'x') AS [Field1], ISNULL([f2], 0) AS [Field2]"; var actual = new FieldSqlWriter(_fields).Name("[", "]").IsNull().ToAlias("[", "]").Write(); Assert.AreEqual(expected, actual); }
public void TestWriteNameDataTypeNull() { const string expected = "[f1] NVARCHAR(10) NULL, [f2] INT NULL"; var actual = new FieldSqlWriter(_fields).Name("[", "]").DataType(new SqlServerDataTypeService()).Append(" NULL").Write(); Assert.AreEqual(expected, actual); }
public SqlResultsLoad(Process process) { _process = process; _fields = _process.CalculatedFields; var sets = new FieldSqlWriter(_fields).Alias(_process.OutputConnection.L, _process.OutputConnection.R).SetParam().Write(); _sql = string.Format("UPDATE {0} SET {1} WHERE TflKey = @TflKey;", _process.MasterEntity.OutputName(), sets); }
private string PrepareSql() { const string sqlPattern = @" SELECT e.{0}, e.TflKey{1} FROM {2} e WITH (NOLOCK); "; var selectKeys = new FieldSqlWriter(_entity.PrimaryKey).AddDeleted(_entity).Alias(_connection.L, _connection.R).Write(", e.", false); return string.Format(sqlPattern, selectKeys, PrepareVersion(), _connection.Enclose(_entity.OutputName())); }
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 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)); }
private string PrepareSql() { const string sqlPattern = @" SELECT e.{0}, e.TflKey{1} FROM {2} e WITH (NOLOCK); "; var selectKeys = new FieldSqlWriter(_entity.PrimaryKey).AddDeleted(_entity).Alias(_connection.L, _connection.R).Write(", e.", false); return(string.Format(sqlPattern, selectKeys, PrepareVersion(), _connection.Enclose(_entity.OutputName()))); }
private string PrepareSql() { var sqlPattern = "SELECT {0}, TflKey FROM {1}"; if (Connection.NoLock) { sqlPattern += " WITH (NOLOCK);"; } else { sqlPattern = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; " + sqlPattern + "; COMMIT;"; } var selectKeys = new FieldSqlWriter(_entity.PrimaryKey).AddDeleted(_entity).Alias(Connection.L, Connection.R).Write(", ", false); return string.Format(sqlPattern, selectKeys, Connection.Enclose(_entity.OutputName())); }
protected override void PrepareCommand(Row row, SqlCommand command) { var sets = new FieldSqlWriter(_process.CalculatedFields.WithOutput()) .Alias(_process.OutputConnection.L, _process.OutputConnection.R) .SetParam() .Write(); command.CommandText = string.Format("UPDATE {0} SET {1} WHERE TflKey = @TflKey;", _process.MasterEntity.OutputName(), sets); foreach (var field in _process.CalculatedFields) { AddParameter(command, field.Identifier, row[field.Alias]); } AddParameter(command, "TflKey", row["TflKey"]); }
private string PrepareSql() { var sqlPattern = "SELECT {0}, TflKey FROM {1}"; if (Connection.NoLock) { sqlPattern += " WITH (NOLOCK);"; } else { sqlPattern = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; " + sqlPattern + "; COMMIT;"; } var selectKeys = new FieldSqlWriter(_entity.PrimaryKey).AddDeleted(_entity).Alias(Connection.L, Connection.R).Write(", ", false); return(string.Format(sqlPattern, selectKeys, Connection.Enclose(_entity.OutputName()))); }
private string PrepareSqlWithInputKeys() { const string sqlPattern = @" {0} SELECT e.{1}, e.TflKey{2} FROM {3} e WITH (NOLOCK) INNER JOIN @KEYS k ON ({4}); "; var builder = new StringBuilder(); builder.AppendLine(_connection.WriteTemporaryTable("@KEYS", _key.WithInput())); builder.AppendLine(SqlTemplates.BatchInsertValues(50, "@KEYS", _key.WithInput(), _entity.InputKeys, _connection)); var selectKeys = new FieldSqlWriter(_entity.PrimaryKey).AddDeleted(_entity).Alias(_connection.L, _connection.R).Write(", e.", false); var joinKeys = new FieldSqlWriter(_entity.PrimaryKey).Input().Alias(_connection.L, _connection.R).Set("e", "k").Write(" AND "); return(string.Format(sqlPattern, builder, selectKeys, PrepareVersion(), _connection.Enclose(_entity.OutputName()), joinKeys)); }
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; }
private string PrepareSqlWithInputKeys() { const string sqlPattern = @" {0} SELECT e.{1}, e.TflKey{2} FROM {3} e WITH (NOLOCK) INNER JOIN @KEYS k ON ({4}); "; var builder = new StringBuilder(); builder.AppendLine(_connection.WriteTemporaryTable("@KEYS", _key.WithInput())); builder.AppendLine(SqlTemplates.BatchInsertValues(50, "@KEYS", _key.WithInput(), _entity.InputKeys, _connection)); var selectKeys = new FieldSqlWriter(_entity.PrimaryKey).AddDeleted(_entity).Alias(_connection.L, _connection.R).Write(", e.", false); var joinKeys = new FieldSqlWriter(_entity.PrimaryKey).Input().Alias(_connection.L, _connection.R).Set("e", "k").Write(" AND "); return string.Format(sqlPattern, builder, selectKeys, PrepareVersion(), _connection.Enclose(_entity.OutputName()), joinKeys); }
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; }