internal static SqlCommandAdapter GetTruncateTableCommand(
SqlConnection connection,
string tableName)
{
var command = SqlCommandAdapter.FromConnection(connection);
command.CommandText = $"TRUNCATE TABLE {Quote(tableName)};";
return(command);
}
internal static SqlCommandAdapter GetSelectIntoTemporaryTableCommand(
SqlConnection connection,
string baseTableName,
string tempTableName)
{
var command = SqlCommandAdapter.FromConnection(connection);
command.CommandText = $@"SELECT TOP 0 *
INTO {Quote(tempTableName)}
FROM {Quote(baseTableName)};";
return(command);
}
internal static SqlCommandAdapter GetGetReferencedTableNameCommand(
SqlConnection connection,
string schemaName,
string viewName)
{
var command = SqlCommandAdapter.FromConnection(connection);
command.CommandText = @"SELECT referenced_entity_name
FROM sys.dm_sql_referenced_entities (@viewName, 'OBJECT')
WHERE referenced_minor_name IS NULL;";
command.Parameters.Add("@viewName", SqlDbType.NVarChar).Value = $"{Quote(schemaName)}.{Quote(viewName)}";
return(command);
}
internal static SqlCommandAdapter GetSwitchTableCommand(
SqlConnection connection,
string viewName,
string tableName,
IEnumerable <string> columns)
{
var command = SqlCommandAdapter.FromConnection(connection);
command.CommandText = $@"ALTER VIEW {Quote(viewName)}
AS
SELECT {string.Join(", ", columns.Select(Quote))}
FROM {Quote(tableName)};";
return(command);
}
internal static SqlCommandAdapter GetAlterNonclusteredIndexesCommand(
SqlConnection connection,
string tableName,
string action)
{
var command = SqlCommandAdapter.FromConnection(connection);
command.CommandText = @"DECLARE @sql AS nvarchar(max) = '';
SELECT @sql = @sql + 'ALTER INDEX ' + QUOTENAME(sys.indexes.name) + ' ON ' + QUOTENAME(sys.objects.name) + ' ' + @action + ';' + CHAR(13) + CHAR(10)
FROM sys.indexes
JOIN sys.objects ON sys.indexes.object_id = sys.objects.object_id
WHERE sys.indexes.type_desc = 'NONCLUSTERED'
AND sys.objects.type_desc = 'USER_TABLE'
AND sys.objects.name = @tableName;
EXEC(@sql);";
command.Parameters.Add("@action", SqlDbType.VarChar).Value = action;
command.Parameters.Add("@tableName", SqlDbType.VarChar).Value = tableName;
return(command);
}
internal static SqlCommandAdapter GetMergeCommand(
SqlConnection connection,
string targetTable,
string tableSource,
IEnumerable <string> columns,
IEnumerable <string> primaryKeyColumns,
bool updateWhenMatched)
{
const string Target = "[Target]";
const string Source = "[Source]";
var command = SqlCommandAdapter.FromConnection(connection);
var mergeSearchCondition = GetMergeSearchCondition();
var valuesList = GetValuesList();
var sb = new StringBuilder();
sb.AppendLine($"MERGE INTO {Quote(targetTable)} AS {Target}");
sb.AppendLine($"USING {Quote(tableSource)} AS {Source}");
sb.AppendLine($" ON ({mergeSearchCondition})");
if (updateWhenMatched)
{
sb.AppendLine($"WHEN MATCHED");
sb.AppendLine($" THEN");
sb.AppendLine($" UPDATE");
sb.AppendLine($" SET {GetSetClause()}");
}
sb.AppendLine($"WHEN NOT MATCHED");
sb.AppendLine($" THEN");
sb.AppendLine($" INSERT ({valuesList})");
sb.AppendLine($" VALUES ({valuesList});");
command.CommandText = sb.ToString();
return(command);
string GetMergeSearchCondition()
{
var cols = from c in primaryKeyColumns
select $"{Target}.{Quote(c)} = {Source}.{Quote(c)}";
return(string.Join(" AND ", cols));
}
string GetSetClause()
{
// Exclude primary key columns
var cols = from c in columns.Except(primaryKeyColumns)
select $"{Target}.{Quote(c)} = {Source}.{Quote(c)}";
return(string.Join(",\r\n ", cols));
}
string GetValuesList()
{
return(string.Join(", ", columns.Select(Quote)));
}
}