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