public async Task <string> Value(IScriptVariables variables)
{
var scriptLines = await _scriptLineFactory.LinesFrom(
0,
$"ALTER PROCEDURE [{variables.SchemaName}].[{variables.ProcedureName()}]",
$" @{variables.TableTypeVariableName()} [{variables.SchemaName}].[{variables.TableTypeName()}] READONLY",
"AS",
"BEGIN",
"",
" SET NOCOUNT ON;",
" SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;"
);
return(await _scriptLineFactory.StringFrom(scriptLines));
}
public async Task <string> Value(IScriptVariables variables)
{
var columns = variables.Table.Columns.Where(c => !c.IdentityColumn);
var tableVariableLines = new List <string>
{
$"DECLARE @{variables.ProcedureName}Variable TABLE",
"("
};
foreach (var column in columns)
{
var line = $" [{column.Name}] {DataType(column)} {NullableColumn(column)}";
if (column != columns.Last())
{
line += ",";
}
tableVariableLines.Add(line);
}
tableVariableLines.AddRange(new List <string> {
")",
$"INSERT INTO @{variables.ProcedureName}Variable",
"SELECT"
});
foreach (var column in columns)
{
var line = $" [{column.Name}]";
if (column != columns.Last())
{
line += ",";
}
tableVariableLines.Add(line);
}
tableVariableLines.AddRange(new List <string> {
"FROM",
$" @{variables.TableTypeVariableName()} AS tt"
});
foreach (var table in variables.TablesToJoin)
{
var joinColumns = string.Join(" AND ", table.selectColumns.Select(c => $"tt.[{c.Name}]=alias_{table.joinTable.Name.ToLower()}.[{c.Name}]"));
var line = $" INNER JOIN [{variables.SchemaName}].[{table.joinTable.Name}] AS alias_{table.joinTable.Name.ToLower()} ON {joinColumns}";
tableVariableLines.Add(line);
}
var scriptLines = await _scriptLineFactory.LinesFrom(
1,
tableVariableLines.ToArray()
);
return(await _scriptLineFactory.StringFrom(scriptLines));
}
public async Task <string> Value(IScriptVariables variables)
{
var matchOn = variables.MergeIdentifierColumns.Select(c => c.Name);
var usingType = (variables.TablesToJoin != null && variables.TablesToJoin.Any()) ? $"{variables.ProcedureName}Variable" : $"{variables.TableTypeVariableName()}";
var mergeStatement = new List <string>
{
$"MERGE [{variables.SchemaName}].[{variables.Table.Name}] AS t",
$"USING @{usingType} AS s",
$"ON ("
};
foreach (var match in matchOn)
{
var line = $" t.[{match}]=s.[{match}]";
if (match != matchOn.Last())
{
line += " AND";
}
mergeStatement.Add(line);
}
mergeStatement.Add(")");
var scriptLines = await _scriptLineFactory.LinesFrom(
1,
mergeStatement.ToArray()
);
return(await _scriptLineFactory.StringFrom(scriptLines));
}