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