private void ScriptTableData(Database db, Table table, bool verbose, string dataDirectory, DataScriptingFormat dataScriptingFormat, Server server)
{
if ((dataScriptingFormat & DataScriptingFormat.Sql) == DataScriptingFormat.Sql)
{
ScriptTableDataNative(table, dataDirectory, server);
}
if ((dataScriptingFormat & DataScriptingFormat.Csv) == DataScriptingFormat.Csv)
{
ScriptTableDataToCsv(db, table, dataDirectory);
}
if ((dataScriptingFormat & DataScriptingFormat.Bcp) == DataScriptingFormat.Bcp)
{
ScriptTableDataWithBcp(db, table, verbose, dataDirectory);
}
}
// TODO: maybe pass in the databaseOutputDirectory instead of calculating it in here?
private void GenerateDatabaseScript(Database db, string outputDirectory, bool purgeDirectory,
DataScriptingFormat dataScriptingFormat, bool verbose, bool scriptProperties, Server server)
{
Properties = scriptProperties;
// Output folder
var databaseOutputDirectory = string.Empty;
if (outputDirectory != null)
{
databaseOutputDirectory = Path.Combine(outputDirectory, FixUpFileName(db.Name));
if (Directory.Exists(databaseOutputDirectory))
{
if (purgeDirectory)
{
if (verbose) Console.Error.Write("Purging database directory...");
PurgeDirectory(databaseOutputDirectory, "*.sql");
if (verbose) Console.Error.WriteLine("done.");
}
}
else
{
Directory.CreateDirectory(databaseOutputDirectory);
}
}
var so = new ScriptingOptions
{
Default = true,
DriDefaults = true,
DriUniqueKeys = true,
Bindings = true,
Permissions = Permissions,
NoCollation = NoCollation,
Statistics = Statistics,
IncludeDatabaseContext = IncludeDatabase
};
ScriptTables(verbose, db, so, databaseOutputDirectory, dataScriptingFormat, server);
ScriptDefaults(verbose, db, so, databaseOutputDirectory);
ScriptRules(verbose, db, so, databaseOutputDirectory);
ScriptUddts(verbose, db, so, databaseOutputDirectory);
ScriptUdfs(verbose, db, so, databaseOutputDirectory);
ScriptViews(verbose, db, so, databaseOutputDirectory);
ScriptSprocs(verbose, db, so, databaseOutputDirectory);
if (db.Version >= 9 &&
db.CompatibilityLevel >= CompatibilityLevel.Version90)
{
ScriptUdts(verbose, db, so, databaseOutputDirectory);
ScriptSchemas(verbose, db, so, databaseOutputDirectory);
ScriptDdlTriggers(verbose, db, so, databaseOutputDirectory);
//ScriptAssemblies(verbose, db, so, databaseOutputDirectory);
}
}
/// <summary>
/// does all the work.
/// </summary>
/// <param name="connStr"></param>
/// <param name="outputDirectory"></param>
/// <param name="dataScriptingFormat"></param>
/// <param name="verbose"></param>
/// <param name="scriptAllDatabases"></param>
/// <param name="purgeDirectory"></param>
/// <param name="scriptProperties"></param>
public void GenerateScripts(string outputDirectory,
bool scriptAllDatabases, bool purgeDirectory,
DataScriptingFormat dataScriptingFormat, bool verbose, bool scriptProperties)
{
var connection = new SqlConnection(ConnectionString);
var sc = new ServerConnection(connection);
var s = new Server(sc);
s.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject", "IsEncrypted");
s.SetDefaultInitFields(typeof(Table), "IsSystemObject");
s.SetDefaultInitFields(typeof(View), "IsSystemObject", "IsEncrypted");
s.SetDefaultInitFields(typeof(UserDefinedFunction), "IsSystemObject", "IsEncrypted");
s.SetDefaultInitFields(typeof(DatabaseDdlTrigger), "IsSystemObject", "IsEncrypted");
s.SetDefaultInitFields(typeof(Schema), "IsSystemObject");
s.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;
RunCommand(StartCommand, verbose, outputDirectory, s.Name, null);
// Purge at the Server level only when we're doing all databases
if (purgeDirectory && scriptAllDatabases && outputDirectory != null && Directory.Exists(outputDirectory))
{
if (verbose) Console.Error.Write("Purging directory...");
PurgeDirectory(outputDirectory, "*.sql");
if (verbose) Console.Error.WriteLine("Done");
}
if (!string.IsNullOrEmpty(OutputFileName) && File.Exists(OutputFileName))
{
try
{
File.Delete(OutputFileName);
}
catch (Exception e)
{
Console.Error.Write("Error deleting output file {0}: {1}", OutputFileName, e.Message);
}
}
if (scriptAllDatabases)
{
foreach (Database db in s.Databases)
{
try
{
RunCommand(PreScriptingCommand, verbose, outputDirectory, s.Name, db.Name);
GenerateDatabaseScript(db, outputDirectory, purgeDirectory, dataScriptingFormat, verbose, scriptProperties, s);
RunCommand(PostScriptingCommand, verbose, outputDirectory, s.Name, db.Name);
}
catch (Exception e)
{
Console.Error.WriteLine("Exception: {0}", e.Message);
}
}
}
else
{
//var db = s.Databases[connection.Database]; // Doesn't fix the case of the database name.
var db = s.Databases.Cast<Database>().Single(d => String.Equals(d.Name, connection.Database, StringComparison.InvariantCultureIgnoreCase));
if(db == null)
{
throw new Exception(string.Format("Database '{0}' was not found", connection.Database));
}
RunCommand(PreScriptingCommand, verbose, outputDirectory, s.Name, db.Name);
GenerateDatabaseScript(db, outputDirectory, purgeDirectory,
dataScriptingFormat, verbose, scriptProperties, s);
RunCommand(PostScriptingCommand, verbose, outputDirectory, s.Name, db.Name);
}
RunCommand(FinishCommand, verbose, outputDirectory, s.Name, null);
}
private void ScriptTables(bool verbose, Database db, ScriptingOptions so, string outputDirectory, DataScriptingFormat dataScriptingFormat, Server server)
{
string data = Path.Combine(outputDirectory, "Data");
string tables = Path.Combine(outputDirectory, "Tables");
string constraints = Path.Combine(tables, "Constraints");
string foreignKeys = Path.Combine(tables, "ForeignKeys");
string fullTextIndexes = Path.Combine(tables, "FullTextIndexes");
string triggers = Path.Combine(tables, "Triggers");
foreach (Table table in db.Tables)
{
if (IncludeSystemObjects || !table.IsSystemObject)
{
if (!FilterExists() || MatchesFilter(FilterType.Table, table.Name))
{
ScriptTable(verbose, db, so, tables, table, triggers, fullTextIndexes, foreignKeys, constraints);
}
#region Script Data
if (MatchesTableDataFilters(db.Name, table.Name))
{
ScriptTableData(db, table, verbose, data, dataScriptingFormat, server);
}
#endregion
}
}
}
private void ScriptTables(bool verbose, Database db, ScriptingOptions so, string outputDirectory, DataScriptingFormat dataScriptingFormat, Server server)
{
string data = Path.Combine(outputDirectory, "Data");
string tables = Path.Combine(outputDirectory, "Tables");
string programmability = Path.Combine(outputDirectory, "Programmability");
string constraints = Path.Combine(tables, "Constraints");
string foreignKeys = Path.Combine(tables, "ForeignKeys");
string fullTextIndexes = Path.Combine(tables, "FullTextIndexes");
string triggers = Path.Combine(programmability, "Triggers");
foreach (Table table in db.Tables)
{
if (!table.IsSystemObject)
{
if (!FilterExists() || MatchesFilter(TableFilter, table.Name))
{
string fileName = Path.Combine(tables, GetScriptFileName(table));
#region Table Definition
using (StreamWriter sw = GetStreamWriter(fileName, false))
{
if (verbose) Console.WriteLine("{0} Scripting {1}", db.Name, table.Name);
if (!CreateOnly)
{
so.ScriptDrops = so.IncludeIfNotExists = true;
WriteScript(table.Script(so), sw);
}
so.ScriptDrops = so.IncludeIfNotExists = false;
WriteScript(table.Script(so), sw);
if (Properties)
{
ScriptProperties(table, sw);
}
}
#endregion
#region Triggers
foreach (Trigger smo in table.Triggers)
{
if (!smo.IsSystemObject && !smo.IsEncrypted)
{
if (!TableOneFile)
fileName = Path.Combine(triggers, GetScriptFileName(table, smo));
using (StreamWriter sw = GetStreamWriter(fileName, TableOneFile))
{
if (verbose) Console.WriteLine("{0} Scripting {1}.{2}", db.Name, table.Name, smo.Name);
if (!CreateOnly)
{
so.ScriptDrops = so.IncludeIfNotExists = true;
WriteScript(smo.Script(so), sw);
}
so.ScriptDrops = so.IncludeIfNotExists = false;
WriteScript(smo.Script(so), sw);
if (Properties)
{
ScriptProperties(smo, sw);
}
}
}
}
#endregion
ScriptIndexes(table, verbose, db, so, tables);
#region Full Text Indexes
if (table.FullTextIndex != null)
{
if (!TableOneFile)
fileName = Path.Combine(fullTextIndexes, GetScriptFileName(table));
using (StreamWriter sw = GetStreamWriter(fileName, TableOneFile))
{
if (verbose) Console.WriteLine("{0} Scripting full-text index for {1}", db.Name, table.Name);
if (!CreateOnly)
{
so.ScriptDrops = so.IncludeIfNotExists = true;
WriteScript(table.FullTextIndex.Script(so), sw);
}
so.ScriptDrops = so.IncludeIfNotExists = false;
WriteScript(table.FullTextIndex.Script(so), sw);
}
}
#endregion
#region Foreign Keys
foreach (ForeignKey smo in table.ForeignKeys)
{
if (!TableOneFile)
fileName = Path.Combine(foreignKeys, GetScriptFileName(table, smo));
using (StreamWriter sw = GetStreamWriter(fileName, TableOneFile))
{
if (verbose) Console.WriteLine("{0} Scripting {1}.{2}", db.Name, table.Name, smo.Name);
if (!CreateOnly)
{
so.ScriptDrops = so.IncludeIfNotExists = true;
}
WriteScript(smo.Script(), sw);
if (Properties)
{
ScriptProperties(smo, sw);
}
}
}
#endregion
#region Constraints
foreach (Check smo in table.Checks)
{
if (!TableOneFile)
fileName = Path.Combine(constraints, GetScriptFileName(table, smo));
using (StreamWriter sw = GetStreamWriter(fileName, TableOneFile))
{
if (verbose) Console.WriteLine("{0} Scripting {1}.{2}", db.Name, table.Name, smo.Name);
WriteScript(smo.Script(), sw);
if (Properties)
{
ScriptProperties(smo, sw);
}
}
}
#endregion
}
#region Script Data
if (MatchesTableDataFilters(db.Name, table.Name))
{
ScriptTableData(db, table, verbose, data, dataScriptingFormat, server);
}
#endregion
}
}
}
/// <summary>
/// does all the work.
/// </summary>
/// <param name="connStr"></param>
/// <param name="outputDirectory"></param>
/// <param name="dataScriptingFormat"></param>
/// <param name="verbose"></param>
/// <param name="scriptAllDatabases"></param>
/// <param name="purgeDirectory"></param>
/// <param name="scriptProperties"></param>
public void GenerateScripts(string outputDirectory,
bool scriptAllDatabases, bool purgeDirectory,
DataScriptingFormat dataScriptingFormat, bool verbose, bool scriptProperties)
{
var connection = new SqlConnection(ConnectionString);
var sc = new ServerConnection(connection);
var s = new Server(sc);
s.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject", "IsEncrypted");
s.SetDefaultInitFields(typeof(Table), "IsSystemObject");
s.SetDefaultInitFields(typeof(View), "IsSystemObject", "IsEncrypted");
s.SetDefaultInitFields(typeof(UserDefinedFunction), "IsSystemObject", "IsEncrypted");
s.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;
RunCommand(StartCommand, verbose, outputDirectory, s.Name, null);
// Purge at the Server level only when we're doing all databases
if (purgeDirectory && scriptAllDatabases && outputDirectory != null && Directory.Exists(outputDirectory))
{
if (verbose) Console.Error.Write("Purging directory...");
PurgeDirectory(outputDirectory, "*.sql");
if (verbose) Console.Error.WriteLine("Done");
}
if (scriptAllDatabases)
{
foreach (Database db in s.Databases)
{
try
{
RunCommand(PreScriptingCommand, verbose, outputDirectory, s.Name, db.Name);
GenerateDatabaseScript(db, outputDirectory, purgeDirectory, dataScriptingFormat, verbose, scriptProperties, s);
RunCommand(PostScriptingCommand, verbose, outputDirectory, s.Name, db.Name);
}
catch (Exception e)
{
Console.WriteLine("Exception: {0}", e.Message);
}
}
}
else
{
var db = s.Databases[connection.Database];
if(db == null)
{
throw new Exception(string.Format("Database '{0}' was not found", connection.Database));
}
RunCommand(PreScriptingCommand, verbose, outputDirectory, s.Name, db.Name);
GenerateDatabaseScript(db, outputDirectory, purgeDirectory,
dataScriptingFormat, verbose, scriptProperties, s);
RunCommand(PostScriptingCommand, verbose, outputDirectory, s.Name, db.Name);
}
RunCommand(FinishCommand, verbose, outputDirectory, s.Name, null);
}
