public static void ScriptDatabaseToFilePath(string connectionString, string outputFilePath, Action <string> announcer = null, bool generateDocument = false, SqlInfoMessageEventHandler infoMessageCallback = null) { void NullAnnouncer(string message) { /* no-op */ } var localAnnouncer = announcer ?? NullAnnouncer; void Logic(Database database) { var documentPath = Path.Combine(outputFilePath, database.Name); var databasePath = Path.Combine(documentPath, "schema"); IDocumentGenerator documentGenerator = new NullDocumentGenerator(); if (generateDocument) { if (!Directory.Exists(documentPath)) { Directory.CreateDirectory(documentPath); } documentGenerator = new DocumentGenerator(Path.Combine(documentPath, database.Name + "-Documentation.doc")); } using (documentGenerator) { ScriptObjects(database, documentGenerator, databasePath, localAnnouncer); documentGenerator.Close(); } } SqlServerSmoDatabaseManager.RunOperationOnSmoDatabase(Logic, connectionString, infoMessageCallback); }
/// <summary> /// Script the objects matching the provided list of names. /// </summary> /// <param name="connectionString">Connection to database to find objects in.</param> /// <param name="objectNames">Names of objects to script.</param> /// <param name="infoMessageCallback">Optional callback to capture information messages sent on connection.</param> /// <param name="scrubScript">Value indicating whether or not to scrub the script to make it more readable and remove issues that prvent running.</param> /// <returns>Collection of scripted objects matching the provided names.</returns> public static System.Collections.Generic.IReadOnlyList <ScriptedObject> ScriptObjectsFromDatabase(string connectionString, System.Collections.Generic.IReadOnlyCollection <string> objectNames, SqlInfoMessageEventHandler infoMessageCallback = null, bool scrubScript = true) { var ret = new List <ScriptedObject>(); void Logic(Database database) { var allScriptableObjects = database.GetAllScriptableObjects(); var filtered = allScriptableObjects.Where(_ => objectNames.Contains(_.Name)).ToList(); ret = filtered.Select(_ => SqlObjectScripter.ScriptToObject(_, scrubScript)).ToList(); } SqlServerSmoDatabaseManager.RunOperationOnSmoDatabase(Logic, connectionString, infoMessageCallback); return(ret); }
/// <summary> /// Copies objects from one database to another. /// </summary> /// <param name="orderedObjectNamesToCopy">Names of objects to copy in order of expected execution.</param> /// <param name="sourceDatabaseConnectionString">Connection string to the source database.</param> /// <param name="targetDatabaseConnectionString">Connection string to the target database.</param> /// <param name="announcer">Optional announcer to log messages about what is happening.</param> /// <returns>Task for async.</returns> public static async Task CopyObjects( IReadOnlyList <string> orderedObjectNamesToCopy, string sourceDatabaseConnectionString, string targetDatabaseConnectionString, Action <Func <object> > announcer = null) { new { orderedObjectNamesToCopy }.AsArg().Must().NotBeNullNorEmptyEnumerableNorContainAnyNulls(); new { sourceDatabaseConnectionString }.AsArg().Must().NotBeNullNorWhiteSpace(); new { targetDatabaseConnectionString }.AsArg().Must().NotBeNullNorWhiteSpace(); void NullAnnounce(Func <object> announcement) { /* no-op */ } var localAnnouncer = announcer ?? NullAnnounce; void SqlMessagesToAnnouncerAdapter(object sender, SqlInfoMessageEventArgs args) { localAnnouncer(() => args); } var scriptedObjects = Scripter.ScriptObjectsFromDatabase(sourceDatabaseConnectionString, orderedObjectNamesToCopy); using (var targetConnection = await targetDatabaseConnectionString.OpenSqlConnectionAsync(SqlMessagesToAnnouncerAdapter)) { async Task RunScriptOnServer(ScriptedObject scriptedObject, string scriptToRun) { localAnnouncer(() => Invariant($"Applying create script for '{scriptedObject.Name}' of type '{scriptedObject.ScriptableObjectType}'")); try { async Task ServerAction(Server server) { // because it might contain "GO" statements most likely this needs to be executed via the SMO connection. server.ConnectionContext.ExecuteNonQuery(scriptToRun); await Task.Run(() => { }); } await SqlServerSmoDatabaseManager.RunOperationOnSmoServerAsync(ServerAction, targetConnection); } catch (Exception ex) { throw new FailedOperationException( Invariant($"Failed to run script on database {targetDatabaseConnectionString.ObfuscateCredentialsInConnectionString()}; {scriptToRun}"), ex); } } foreach (var scriptedObject in scriptedObjects.Reverse()) { await RunScriptOnServer(scriptedObject, scriptedObject.DropScript); } foreach (var scriptedObject in scriptedObjects) { await RunScriptOnServer(scriptedObject, scriptedObject.CreateScript); } var tables = scriptedObjects.Where(_ => _.ScriptableObjectType == ScriptableObjectType.Table).ToList(); if (tables.Any()) { var copyOptions = SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.KeepNulls | SqlBulkCopyOptions.TableLock; using (var sourceConnection = await sourceDatabaseConnectionString.OpenSqlConnectionAsync(SqlMessagesToAnnouncerAdapter)) { foreach (var table in tables) { table.Name.MustForArg(Invariant($"{nameof(table)}.{nameof(ScriptedObject.Name)}")).NotBeNullNorWhiteSpace().And().BeAlphanumeric(TableDefinition.TableNameAlphanumericOtherAllowedCharacters); using (var transaction = targetConnection.BeginTransaction("BcpTable-" + table.Name)) { using (var bcp = new SqlBulkCopy(targetConnection, copyOptions, transaction) { DestinationTableName = table.Name }) { using (var command = sourceConnection.CreateCommand()) { command.CommandType = CommandType.Text; command.CommandText = "SELECT * FROM " + table.Name; using (var reader = await command.ExecuteReaderAsync()) { await bcp.WriteToServerAsync(reader); } } } transaction.Commit(); } } } } } }