public void ChangeCollation(string collation)
{
string filename;
FileStream fileStream = GetTempFileStreamWriter(out filename);
fileStream.Close();
Server server = new Server(new ServerConnection(Database.Instance.Connection));
Microsoft.SqlServer.Management.Smo.Database smoDatabase = server.Databases[Database.Instance.DatabaseName];
ScriptingOptions scriptingOptionDBStructure = new ScriptingOptions
{
ToFileOnly = true,
NoCollation = true,
DriAll = true,
ExtendedProperties = true,
Indexes = true,
Triggers = true,
AppendToFile = true,
FileName = filename
};
ScriptingOptions scriptingOptionData = new ScriptingOptions
{
ToFileOnly = true,
NoCollation = true,
ScriptData = true,
ScriptSchema = false,
AppendToFile = true,
FileName = filename
};
Transfer transfer = new Transfer(smoDatabase)
{
CopyAllObjects = false,
CopyAllTables = true,
CopyAllViews = true,
CopyAllUserDefinedFunctions = true,
Options = scriptingOptionDBStructure
};
transfer.ScriptTransfer();
StringBuilder noCheckConstraints = new StringBuilder();
StringBuilder witchCheckConstraints = new StringBuilder();
foreach (DatabaseObject constraint in _databaseOperations.GetActiveForeignKeys())
{
noCheckConstraints.AppendLine($"ALTER TABLE {constraint.Schema} NOCHECK CONSTRAINT {constraint.Name};");
witchCheckConstraints.AppendLine($"ALTER TABLE {constraint.Schema} WITH CHECK CHECK CONSTRAINT {constraint.Name};");
}
using (StreamWriter writer = new StreamWriter(filename, true, Encoding.Unicode))
{
writer.Write(noCheckConstraints.ToString());
writer.WriteLine("GO");
}
Scripter scripter = new Scripter(server);
scripter.Options = scriptingOptionData;
foreach (Table table in smoDatabase.Tables)
{
scripter.EnumScript(new[] { table.Urn });
}
using (StreamWriter writer = new StreamWriter(filename, true, Encoding.Unicode))
{
writer.Write(witchCheckConstraints.ToString());
}
Database database = Database.Instance;
foreach (DatabaseObject index in _databaseOperations.GetIndexes())
{
database.ExecuteNonResultQuery($"DROP INDEX [{index.Name}] ON {index.Schema};");
}
foreach (DatabaseObject constraint in _databaseOperations.GetConstraints())
{
database.ExecuteNonResultQuery($"ALTER TABLE {constraint.Schema} DROP CONSTRAINT [{constraint.Name}];");
}
foreach (DatabaseObject view in _databaseOperations.GetViews())
{
database.ExecuteNonResultQuery($"DROP VIEW {view.NameWithSchemaBrackets};");
}
foreach (DatabaseObject table in _databaseOperations.GetTables())
{
database.ExecuteNonResultQuery($"DROP TABLE {table.NameWithSchemaBrackets};");
}
foreach (DatabaseObject function in _databaseOperations.GetFunctions())
{
database.ExecuteNonResultQuery($"DROP FUNCTION {function.NameWithSchemaBrackets};");
}
database.ExecuteNonResultQuery($"ALTER DATABASE {database.DatabaseName} SET SINGLE_USER WITH ROLLBACK IMMEDIATE;");
database.ExecuteNonResultQuery($"ALTER DATABASE {database.DatabaseName} COLLATE {collation};");
database.ExecuteNonResultQuery($"ALTER DATABASE {database.DatabaseName} SET MULTI_USER;");
using (StreamReader file = File.OpenText(filename))
{
string line;
StringBuilder buffer = new StringBuilder();
while ((line = file.ReadLine()) != null)
{
if (line == "GO")
{
database.ExecuteNonResultQuery(buffer.ToString());
buffer.Clear();
}
else
{
buffer.AppendLine(line);
}
}
if (buffer.Length > 0)
{
database.ExecuteNonResultQuery(buffer.ToString());
buffer.Clear();
}
}
File.Delete(filename);
}
public bool ArchiveTable(DatabaseObject tableToArchive, IDictionary <string, string> convertorTablenames, bool createTable, string whereConditions, bool useInsertIntoSelect, bool archiveAllEntries, StreamWriter streamToSave)
{
bool exportIsNotEmpty = false;
ISet <DependencyEdge> cycleEdges;
IList <DependencyNode> orderedTables = DependencyGraph.GetTopologicallySortedNodes(tableToArchive, true, out cycleEdges);
if (createTable)
{
Server server = new Server(new ServerConnection(Database.Instance.Connection));
Microsoft.SqlServer.Management.Smo.Database database = server.Databases[Database.Instance.DatabaseName];
Transfer transfer = new Transfer(database)
{
CopyAllObjects = false,
CopyAllUserDefinedDataTypes = true,
CopyAllSchemas = true,
CopyAllXmlSchemaCollections = true,
Options = new ScriptingOptions
{
FullTextCatalogs = true,
FullTextStopLists = true,
FullTextIndexes = true,
DriAll = true,
ExtendedProperties = true,
Indexes = true,
NonClusteredIndexes = true,
Triggers = true,
IncludeIfNotExists = true
}
};
foreach (DependencyNode table in orderedTables)
{
transfer.ObjectList.Add(database.Tables[table.DbObject.Name, table.DbObject.Schema]);
}
StringBuilder archiveCreateTable = new StringBuilder();
foreach (string line in transfer.ScriptTransfer())
{
archiveCreateTable.AppendLine(line);
archiveCreateTable.AppendLine("GO");
}
foreach (DependencyNode table in orderedTables)
{
archiveCreateTable.Replace(table.DbObject.NameWithSchemaBrackets, table.DbObject.NameWithSchema.Replace(table.DbObject.NameWithSchemaBrackets, $"[{convertorTablenames[table.DbObject.NameWithSchema.ToLower()].Replace(".", "].[")}]"));
archiveCreateTable.Replace($"TABLE {table.DbObject.NameWithSchema}", $"TABLE {convertorTablenames[table.DbObject.NameWithSchema.ToLower()]}");
archiveCreateTable.Replace($"ON {table.DbObject.NameWithSchema}", $"ON {convertorTablenames[table.DbObject.NameWithSchema.ToLower()]}");
string oldTablename = table.DbObject.Name;
string newTablename = convertorTablenames[table.DbObject.NameWithSchema.ToLower()];
newTablename = newTablename.Substring(Math.Min(newTablename.LastIndexOf(".") + 1, newTablename.Length - 1)).Replace("[", "").Replace("]", "");
archiveCreateTable.Replace($"_{oldTablename}_", $"_{newTablename}_");
archiveCreateTable.Replace($"name=N'{oldTablename}'", $"name=N'{newTablename}'");
}
streamToSave.Write(archiveCreateTable.ToString());
exportIsNotEmpty = true;
archiveCreateTable.Clear();
}
IDictionary <DependencyNode, string> tableSubSelects = new Dictionary <DependencyNode, string>(orderedTables.Count);
if (!archiveAllEntries)
{
foreach (DependencyNode table in orderedTables.Reverse())
{
if (table.DbObject.Equals(tableToArchive))
{
tableSubSelects.Add(table, $"FROM {table.DbObject.NameWithSchemaBrackets}{(whereConditions.Trim().Length > 0 ? $" WHERE {whereConditions}" : "")}");
}
foreach (DependencyEdge edge in table.ParentEdges)
{
DependencyNode parent = edge.Parent;
if (parent.DbObject.Equals(tableToArchive))
{
continue;
}
if (orderedTables.Contains(parent))
{
IList <string> parentColumnsConditions = new List <string>();
foreach (DependencyColumn column in edge.Columns)
{
parentColumnsConditions.Add($"{parent.DbObject.NameWithSchemaBrackets}.[{column.ParentColumn}] IN (SELECT DISTINCT {table.DbObject.NameWithSchemaBrackets}.[{column.ChildColumn}] {tableSubSelects[edge.Child]})");
}
if (!tableSubSelects.ContainsKey(parent))
{
tableSubSelects[parent] = $"FROM {parent.DbObject.NameWithSchemaBrackets} WHERE ({string.Join(" AND ", parentColumnsConditions)})";
}
else
{
tableSubSelects[parent] = tableSubSelects[parent] + $" OR ({string.Join(" AND ", parentColumnsConditions)})";
}
}
}
}
}
else
{
foreach (DependencyNode table in orderedTables)
{
if (table.DbObject.Equals(tableToArchive) && whereConditions.Trim().Length > 0)
{
tableSubSelects.Add(table, $"FROM {table.DbObject.NameWithSchemaBrackets} WHERE {whereConditions}");
}
else
{
tableSubSelects.Add(table, $"FROM {table.DbObject.NameWithSchemaBrackets}");
}
}
}
IDictionary <DependencyNode, bool> valuesInTables = new Dictionary <DependencyNode, bool>(orderedTables.Count);
bool valuesGenerated = false;
foreach (DependencyNode table in orderedTables)
{
valuesInTables[table] = (Database.Instance.ExecuteScalarSelect($"SELECT COUNT(*) {tableSubSelects[table]}") ?? 0) > 0;
if (valuesInTables[table])
{
valuesGenerated = true;
}
}
StringBuilder noCheckConstraints = new StringBuilder();
StringBuilder witchCheckConstraints = new StringBuilder();
foreach (DependencyEdge edge in cycleEdges)
{
noCheckConstraints.AppendLine($"ALTER TABLE {edge.Child.DbObject.NameWithSchemaBrackets} NOCHECK CONSTRAINT {edge.Name};");
witchCheckConstraints.AppendLine($"ALTER TABLE {edge.Child.DbObject.NameWithSchemaBrackets} WITH CHECK CHECK CONSTRAINT {edge.Name};");
}
if (valuesGenerated || useInsertIntoSelect)
{
streamToSave.Write(noCheckConstraints.ToString());
}
foreach (DependencyNode table in orderedTables)
{
string tableName = convertorTablenames[table.DbObject.NameWithSchema.ToLower()];
if (!tableName.Contains("[") && !tableName.Contains("]"))
{
tableName = "[" + tableName.Replace(".", "].[") + "]";
}
IList <string> columnsList = _databaseOperations.GetColumnNames(table.DbObject, false);
string columns = "[" + string.Join("], [", columnsList) + "]";
if (!useInsertIntoSelect || table.DbObject.NameWithSchema == convertorTablenames[table.DbObject.NameWithSchema.ToLower()])
{
if (!valuesInTables[table])
{
continue;
}
if (_databaseOperations.HasTableIdentityColumn(table.DbObject))
{
streamToSave.WriteLine($"SET IDENTITY_INSERT {tableName} ON;");
}
int values = 0;
foreach (string insert in new DatabaseOperations.ValueInsertGetter($"SELECT {columns} {tableSubSelects[table]}"))
{
if (values % MAX_VALUES_IN_INSERT == 0)
{
if (values > 0)
{
streamToSave.WriteLine(";");
streamToSave.WriteLine("GO");
}
streamToSave.Write($"INSERT INTO {tableName} ({columns}) VALUES ");
}
else
{
streamToSave.Write(", ");
}
streamToSave.Write(insert);
values++;
}
if (values > 0)
{
streamToSave.WriteLine(";");
streamToSave.WriteLine("GO");
}
if (_databaseOperations.HasTableIdentityColumn(table.DbObject))
{
streamToSave.WriteLine($"SET IDENTITY_INSERT {tableName} OFF;");
}
}
else
{
exportIsNotEmpty = true;
if (_databaseOperations.HasTableIdentityColumn(table.DbObject))
{
streamToSave.WriteLine($"SET IDENTITY_INSERT {tableName} ON;");
}
streamToSave.WriteLine($"INSERT INTO {tableName} ({columns}) SELECT * {tableSubSelects[table]};");
}
}
if (valuesGenerated || useInsertIntoSelect)
{
exportIsNotEmpty = true;
streamToSave.Write(witchCheckConstraints.ToString());
}
streamToSave.Flush();
return(exportIsNotEmpty);
}
