/// <summary>
/// 数据库操作类
/// </summary>
/// <param name="server"></param>
/// <param name="userId"></param>
/// <param name="password"></param>
/// <param name="database"></param>
public GenerateScripts(string server, string userId, string password, string database)
{
_Connection = new Microsoft.SqlServer.Management.Common.ServerConnection(server, userId, password);
_Server = new Microsoft.SqlServer.Management.Smo.Server(_Connection);
_Scripter = GetScripter(_Server);
_Database = _Server.Databases[database];
}
public static void ScriptViews(
Database database,
string scriptDirectory,
Microsoft.SqlServer.Management.Smo.Scripter scripter)
{
ScriptViews(database, scriptDirectory, scripter, false, "");
}
public static void ScriptTables(
Database database,
string scriptDirectory,
Microsoft.SqlServer.Management.Smo.Scripter scripter,
bool matchOnNameContains,
string textToMatchOnNameContains)
{
string tableDirectory = Path.Combine(scriptDirectory, "tables");
System.IO.Directory.CreateDirectory(tableDirectory);
Stopwatch blockStart = new Stopwatch();
blockStart.Start();
TableCollection allTables = database.Tables;
List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn> allTableObjects = new List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn>();
int tableIndex = 0;
ScriptMove.WriteToLog("Scripting tables...");
foreach (Table oneTable in database.Tables)
{
if (!oneTable.IsSystemObject)
{
if (matchOnNameContains == false || (matchOnNameContains == true && oneTable.Name.ToUpper().Contains(textToMatchOnNameContains)))
{
List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn> oneTableObject = new List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn>();
SqlSmoObject[] objectArray = { oneTable };
int depCount = CountTableDependancies(scripter, objectArray);
if (depCount > 0)
{
ScriptMove.WriteToLog(string.Format("table {0} has {1} dependancies", oneTable.Name, depCount));
}
oneTableObject.Add(oneTable.Urn);
allTableObjects.Add(oneTable.Urn);
string fileName = string.Format("{0}.{1}.{2}.sql",
string.Format("0000{0}", tableIndex).Right(4),
oneTable.Schema,
oneTable.Name);
string fullFileName = Path.Combine(tableDirectory, fileName);
try
{
WriteScriptToFile(fullFileName, oneTable.Urn, ref scripter);
} catch (Exception ex)
{
ScriptMove.WriteToLog(String.Format(" Unable to script {0} due to error {1}", oneTable.Name, ex.Message));
}
tableIndex++;
}
}
}
ScriptMove.WriteToLog(String.Format("{0} tables scripted. Elapsed seconds: {1}", tableIndex, blockStart.Elapsed.TotalSeconds));
}
public static void ScriptDatabaseTriggers(
Database database,
string scriptDirectory,
Microsoft.SqlServer.Management.Smo.Scripter scripter,
bool matchOnNameContains,
string textToMatchOnNameContains)
{
string triggerDirectory = Path.Combine(scriptDirectory, "ddltriggers");
System.IO.Directory.CreateDirectory(triggerDirectory);
Stopwatch blockStart = new Stopwatch();
blockStart.Start();
DatabaseDdlTriggerCollection allTriggers = database.Triggers;
List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn> allTriggerObjects = new List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn>();
int triggerIndex = 0;
ScriptMove.WriteToLog("Scripting DDL triggers...");
foreach (DatabaseDdlTrigger oneTrigger in allTriggers)
{
if (!oneTrigger.IsSystemObject)
{
if (matchOnNameContains == false || (matchOnNameContains == true && oneTrigger.Name.ToUpper().Contains(textToMatchOnNameContains)))
{
List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn> oneTriggerObject = new List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn>();
oneTriggerObject.Add(oneTrigger.Urn);
string fileName = string.Format("{0}.{1}.{2}.sql",
string.Format("0000{0}", triggerIndex).Right(4),
"dbo",
oneTrigger.Name);
string fullFileName = Path.Combine(triggerDirectory, fileName);
try
{
WriteScriptToFile(fullFileName, oneTrigger.Urn, ref scripter);
}
catch (Exception ex)
{
ScriptMove.WriteToLog(String.Format(" Unable to script {0} due to error {1}", oneTrigger.Name, ex.Message));
}
triggerIndex++;
}
}
}
ScriptMove.WriteToLog(String.Format("{0} DDL triggers scripted. Elapsed seconds: {1}", triggerIndex, blockStart.Elapsed.TotalSeconds));
}
private void ScriptToFile(
string scriptDirectory)
{
Stopwatch totalTime = new Stopwatch();
totalTime.Start();
string nowDir = string.Format("{0}_{1}_{2}_{3}_{4}",
DateTime.Now.Year,
string.Format("00{0}", DateTime.Now.Month).Right(2),
string.Format("00{0}", DateTime.Now.Day).Right(2),
string.Format("00{0}", DateTime.Now.Hour).Right(2),
string.Format("00{0}", DateTime.Now.Minute).Right(2));
string instanceScriptPath = Path.Combine(scriptDirectory, nowDir);
Directory.CreateDirectory(instanceScriptPath);
_logPath = Path.Combine(instanceScriptPath, "log.txt");
Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(_serverName);
if (_useIntegratedAuthentication)
{
server.ConnectionContext.LoginSecure = true;
}
else
{
server.ConnectionContext.LoginSecure = false;
server.ConnectionContext.Login = _userId;
server.ConnectionContext.Password = _password;
}
Microsoft.SqlServer.Management.Smo.Database database = server.Databases[_databaseName];
/* define scripting options */
ScriptingOptions options = new ScriptingOptions();
options.AllowSystemObjects = false;
options.AllowSystemObjects = false;
options.IncludeDatabaseContext = false;
options.IncludeIfNotExists = false;
options.ClusteredIndexes = true;
options.Default = true;
options.DriAll = true;
options.Indexes = true;
options.NonClusteredIndexes = true;
options.IncludeHeaders = true;
options.ToFileOnly = true;
options.AppendToFile = true;
options.AnsiPadding = false;
// Set scripter options to ensure only schema is scripted
options.ScriptSchema = true;
options.ScriptData = false;
//Exclude GOs after every line
options.NoCommandTerminator = false;
Microsoft.SqlServer.Management.Smo.Scripter scripter = new Microsoft.SqlServer.Management.Smo.Scripter();
scripter.Options = options;
scripter.Server = server;
if (_includeTables)
{
ExportScript.ScriptTables(database, instanceScriptPath, scripter, _matchOnNameContains, _textToMatchOnNameContains);
}
if (_includeViews)
{
ExportScript.ScriptViews(database, instanceScriptPath, scripter, _matchOnNameContains, _textToMatchOnNameContains);
}
if (_includeFunctions)
{
ExportScript.ScriptFunctions(database, instanceScriptPath, scripter, _matchOnNameContains, _textToMatchOnNameContains);
}
if (_includeProcedures)
{
ExportScript.ScriptProcedures(database, instanceScriptPath, scripter, _matchOnNameContains, _textToMatchOnNameContains);
}
if (_includeTriggers)
{
ExportScript.ScriptTableTriggers(database, instanceScriptPath, scripter, _matchOnNameContains, _textToMatchOnNameContains);
}
if (_includeDDLTriggers)
{
ExportScript.ScriptDatabaseTriggers(database, instanceScriptPath, scripter, _matchOnNameContains, _textToMatchOnNameContains);
}
WriteToLog(String.Format("Scripting completed. Elapsed seconds: {0}", totalTime.Elapsed.TotalSeconds));
}
