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