/// <summary>
/// Initializes a new instance of the StoredProcedure class.
/// </summary>
public StoredProcedure(SMO.StoredProcedure storedProcedure)
{
long startTime = Common.WriteToDebugWindow(string.Format("Enter SMOH.{0}({1})", "StoredProcedure", storedProcedure));
_storedProcedure = storedProcedure;
CreateDate = storedProcedure.CreateDate.ToString();
try
{
DateLastModified = storedProcedure.DateLastModified.ToString("yyyy-MM-dd hh:mm:ss");
}
catch (Exception)
{
DateLastModified = "Not Available";
}
ID = storedProcedure.ID.ToString();
IsSystemObject = storedProcedure.IsSystemObject.ToString();
try
{
MethodName = storedProcedure.MethodName;
}
catch (Exception)
{
MethodName = "Not Available";
}
Name = storedProcedure.Name;
Owner = storedProcedure.Owner;
try
{
TextHeader = storedProcedure.TextHeader;
}
catch (Exception)
{
TextHeader = "<No Access>";
}
try
{
TextBody = storedProcedure.TextBody;
}
catch (Exception)
{
TextBody = "<No Access>";
}
try
{
ExtendedProperties = storedProcedure.ExtendedProperties;
}
catch (Exception)
{
}
Common.WriteToDebugWindow(string.Format(" Exit SMOH.{0}({1}) Exit", "StoredProcedure", storedProcedure), startTime);
}
private Model.StoredProcedure GetNewStoredProcedure(Microsoft.SqlServer.Management.Smo.StoredProcedure smoStoredProcedure)
{
Model.StoredProcedure storedProcedure = new Model.StoredProcedure(smoStoredProcedure.Name, Script.GetSingluar(smoStoredProcedure.Name), false);
storedProcedure.Enabled = false;
// Columns
//int ordinalPosition = 0;
//Microsoft.SqlServer.Management.Smo.Column[] smoColumns = GetColumns(smoStoredProcedure);
//foreach (Microsoft.SqlServer.Management.Smo.Column smoColumn in smoColumns)
//{
/*if (UnsupportedDataTypes.ToLower().IndexOf("'" + smoColumn.PhysicalDatatype.ToLower() + "'") >= 0)
* {
* continue;
* }
*
* Column column = new Column(smoColumn.Name, Script.GetSingluar(smoColumn.Name), smoColumn.Name, storedProcedure, ordinalPosition, smoColumn.Nullable, smoColumn.PhysicalDatatype, smoColumn.Length,
* smoColumn.InPrimaryKey, false, false, smoColumn.Identity);
* storedProcedure.Columns.Add(column);
* ordinalPosition++;*/
//}
// Parameters
foreach (Microsoft.SqlServer.Management.Smo.Parameter smoParameter in smoStoredProcedure.Parameters)
{
//Model.StoredProcedure.Parameter parameter = new Model.StoredProcedure.Parameter(smoParameter.Name, smoParameter.Name.Replace("@", ""), smoParameter.DataType.Name);
//storedProcedure.AddParameter(parameter);
}
return(storedProcedure);
}
public static void UpdateDataSet(this MSMO.StoredProcedure storedProcedure, Data.ApplicationDataSet.DBStoredProceduresRow storedProcedureRow)
{
try
{
storedProcedureRow.Owner = storedProcedure.Owner;
storedProcedureRow.CreateDate = storedProcedure.CreateDate;
if (Data.Config.ADSLoad_DBStoredProcedureContent)
{
try
{
storedProcedureRow.MethodName = storedProcedure.MethodName;
}
catch (Exception ex)
{
PLLog.Warning(ex, PLLOG_APPNAME, CLASS_BASE_ERRORNUMBER + 1);
storedProcedureRow.MethodName = "<Exception>";
}
try
{
storedProcedureRow.TextHeader = storedProcedure.TextHeader;
}
catch (Exception ex)
{
PLLog.Warning(ex, PLLOG_APPNAME, CLASS_BASE_ERRORNUMBER + 1);
storedProcedureRow.TextHeader = "<Exception>";
}
try
{
storedProcedureRow.TextBody = storedProcedure.TextBody;
}
catch (Exception ex)
{
PLLog.Warning(ex, PLLOG_APPNAME, CLASS_BASE_ERRORNUMBER + 1);
storedProcedureRow.TextBody = "<Exception>";
}
}
try
{
storedProcedureRow.DateLastModified = storedProcedure.DateLastModified;
}
catch (Exception ex)
{
PLLog.Warning(ex, PLLOG_APPNAME, CLASS_BASE_ERRORNUMBER + 1);
}
}
catch (Exception ex)
{
// TODO(crhodes): Need to wrap anything above that throws an exception
// that we want to ignore, e.g. property not available because of
// SQL Edition.
PLLog.Error(ex, PLLOG_APPNAME, CLASS_BASE_ERRORNUMBER + 2);
throw ex;
}
}
public static void Test()
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
using (System.Data.SqlClient.SqlConnection con = (System.Data.SqlClient.SqlConnection)
//SqlFactory.GetConnection()
SqlFactory.LocalConntection
)
{
Microsoft.SqlServer.Management.Common.ServerConnection sc =
new Microsoft.SqlServer.Management.Common.ServerConnection(con);
lock (con)
{
sc.Connect();
Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(sc);
Microsoft.SqlServer.Management.Smo.Database database; // = new Microsoft.SqlServer.Management.Smo.Database();
//database = server.Databases["redmine"];
//string schemaName = @"dbo";
//string tableName = @"issues";
database = server.Databases["COR_Basic_Demo_V4"];
string schemaName = @"dbo";
string tableName = @"T_Benutzer";
Microsoft.SqlServer.Management.Smo.Table table = database.Tables[tableName, schemaName];
System.Collections.Specialized.StringCollection result = table.Script();
// table.Script(new ScriptingOptions() { ScriptForAlter = true });
Microsoft.SqlServer.Management.Smo.StoredProcedure proc = database.StoredProcedures["procname", "schema"];
// proc.Script(new ScriptingOptions() { ScriptForAlter = true });
// string alterText = proc.ScriptHeader(true) + proc.TextBody;
foreach (string line in result)
{
sb.AppendLine(line);
} // Next line
sc.Disconnect();
} // End Lock con
} // End Using con
using (System.IO.FileStream fs = System.IO.File.OpenWrite(@"d:\testScriptSAQl.sql"))
{
using (System.IO.TextWriter sw = new System.IO.StreamWriter(fs, System.Text.Encoding.UTF8))
{
sw.Write(sb.ToString());
sw.Flush();
fs.Flush();
} // End Using sw
} // End Using fs
} // End Sub
public MySmo.StoredProcedure GetStoredProcedure(Smo.StoredProcedure smo_sp, MySmo.Database parent = null)
{
#region implement
SetDataLimit();
var mysmo_sp = new MySmo.StoredProcedure();
mysmo_sp.ParentDatabase = parent;
mysmo_sp.Name = smo_sp.Name;
mysmo_sp.Schema = smo_sp.Schema;
mysmo_sp.CreateTime = smo_sp.CreateDate;
mysmo_sp.Owner = smo_sp.Owner;
mysmo_sp.ExtendedProperties = GetExtendProperties(mysmo_sp, smo_sp.ExtendedProperties);
var s = "";
if (mysmo_sp.ExtendedProperties.TryGetValue(K_MS_Description, out s))
{
mysmo_sp.Description = s;
mysmo_sp.ExtendedProperties.Remove(K_MS_Description);
}
mysmo_sp.Parameters = new List <MySmo.Parameter>();
foreach (Smo.StoredProcedureParameter smo_p in smo_sp.Parameters)
{
var mysmo_p = new MySmo.Parameter
{
ParentDatabase = parent,
ParentParameterBase = mysmo_sp,
Name = smo_p.Name.Substring(1),
DefaultValue = smo_p.DefaultValue,
IsOutputParameter = smo_p.IsOutputParameter,
IsReadOnly = smo_p.IsReadOnly,
DataType = new MySmo.DataType
{
Name = smo_p.DataType.Name,
Schema = smo_p.DataType.Schema,
MaximumLength = smo_p.DataType.MaximumLength,
NumericPrecision = smo_p.DataType.NumericPrecision,
NumericScale = smo_p.DataType.NumericScale,
SqlDataType = (MySmo.SqlDataType)(int) smo_p.DataType.SqlDataType
}
};
mysmo_sp.Parameters.Add(mysmo_p);
}
FormatExtendProperties(mysmo_sp);
return(mysmo_sp);
#endregion
}
private static void Update(MSMO.StoredProcedure storedProcedure, SQLInformation.Data.ApplicationDataSet.DBStoredProceduresRow dataRow)
{
try
{
storedProcedure.UpdateDataSet(dataRow);
UpdateDatabaseWithSnapShot(dataRow, "");
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 10);
UpdateDatabaseWithSnapShot(dataRow, ex.ToString().Substring(0, 256));
}
}
/// <summary>
/// Compare 2 ddl stored procedures
/// </summary>
/// <param name="sp1">your first smo procedure</param>
/// <param name="sp2">your second smo procedure</param>
/// <param name="checkComments">True if you want to compare comments</param>
/// <param name="checkBrackets">True if you want to compare scripts with brackets</param>
/// <param name="ignoreCaseSensitive">True if you want to ignore Case Sensitive. False if Case sensitive</param>
/// <returns></returns>
public static KMOCompareInfo CompareSchema(this smo.StoredProcedure sp1, smo.StoredProcedure sp2, bool checkComments = false, bool checkBrackets = false, bool ignoreCaseSensitive = true)
{
smo.ScriptingOptions so = new smo.ScriptingOptions();
so.DriAll = true;
string s1 = String.Join(Environment.NewLine, sp1.Script(so).Cast <String>().Select(s => s.ToString()).AsEnumerable());
string s2 = String.Join(Environment.NewLine, sp2.Script(so).Cast <String>().Select(s => s.ToString()).AsEnumerable());
string message = string.Empty;
if (KMOCompareHelper.CompareScript(s1, s2, ignoreCaseSensitive, checkComments, checkBrackets))
{
return(new KMOCompareInfo {
IsIdentical = true, Message = string.Empty, Script1 = s1, Script2 = s2
});
}
else
{
return(new KMOCompareInfo {
IsIdentical = false, Message = "Script difference", Script1 = s1, Script2 = s2
});
}
}
/// <summary>
/// Get StoredProcedures in SqlDataStore
/// </summary>
/// <param name="sqlDataStore">The Sql data store</param>
/// <returns>The stored procedures.</returns>
public static System.Collections.Generic.IReadOnlyList <StoredProcedure> GetStoredProcedures(SqlDataStoreTestsFixture sqlDataStore)
{
EnsureArg.IsNotNull(sqlDataStore, nameof(sqlDataStore));
using var sqlConnection = new SqlConnection(sqlDataStore.TestConnectionString);
ServerConnection connection = new ServerConnection(sqlConnection);
Server server = new Server(connection);
Database db = server.Databases[sqlDataStore.DatabaseName];
DataTable storedProcedureTable = db.EnumObjects(DatabaseObjectTypes.StoredProcedure);
List <StoredProcedure> result = new List <StoredProcedure>();
foreach (DataRow row in storedProcedureTable.Rows)
{
string schema = (string)row["Schema"];
if (schema == "sys")
{
continue;
}
StoredProcedure sp = (StoredProcedure)server.GetSmoObject(new Urn((string)row["Urn"]));
result.Add(sp);
}
return(result);
}
private static SQLInformation.Data.ApplicationDataSet.DBStoredProceduresRow Add(Guid databaseID, MSMO.StoredProcedure storedProcedure)
{
SQLInformation.Data.ApplicationDataSet.DBStoredProceduresRow dataRow = null;
try
{
dataRow = Common.ApplicationDataSet.DBStoredProcedures.NewDBStoredProceduresRow();
dataRow.ID = Guid.NewGuid(); // See if this is available from SP.
dataRow.Name_StoredProcedure = storedProcedure.Name;
dataRow.StoredProcedure_ID = storedProcedure.ID.ToString();;
dataRow.Database_ID = databaseID; // From above
dataRow.Owner = storedProcedure.Owner;
dataRow.CreateDate = storedProcedure.CreateDate;
dataRow.IsSystemObject = storedProcedure.IsSystemObject;
// Loading the body of the Stored Procedure is expensive. Optional.
if (Data.Config.ADSLoad_DBStoredProcedureContent)
{
try
{
dataRow.MethodName = storedProcedure.MethodName;
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 5);
#endif
dataRow.MethodName = "<Not Available>";
}
try
{
dataRow.TextHeader = storedProcedure.TextHeader;
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 6);
#endif
dataRow.TextHeader = "<No Access>";
}
try
{
dataRow.TextBody = storedProcedure.TextBody;
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 7);
#endif
dataRow.TextBody = "<No Access>";
}
}
try
{
dataRow.DateLastModified = storedProcedure.DateLastModified;
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 8);
#endif
}
dataRow.SnapShotDate = DateTime.Now;
dataRow.SnapShotError = "";
Common.ApplicationDataSet.DBStoredProcedures.AddDBStoredProceduresRow(dataRow);
Common.ApplicationDataSet.DBStoredProceduresTA.Update(Common.ApplicationDataSet.DBStoredProcedures);
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 9);
// TODO(crhodes):
// Wrap anything above that throws an exception that we want to ignore,
// e.g. property not available because of SQL Edition.
UpdateDatabaseWithSnapShot(dataRow, ex.ToString().Substring(0, 256));
}
return(dataRow);
}
private static SQLInformation.Data.ApplicationDataSet.DBStoredProceduresRow GetInfoFromSMO(MSMO.StoredProcedure storedProcedure, Guid databaseID)
{
#if TRACE
long startTicks = VNC.AppLog.Trace4("Enter", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 2);
#endif
SQLInformation.Data.ApplicationDataSet.DBStoredProceduresRow dataRow = null;
try
{
var dbs = from sp in Common.ApplicationDataSet.DBStoredProcedures
where sp.Database_ID == databaseID
select sp;
var dbs2 = from db2 in dbs
where db2.Name_StoredProcedure == storedProcedure.Name
select db2;
if (dbs2.Count() > 0)
{
dataRow = dbs2.First();
Update(storedProcedure, dataRow);
}
else
{
dataRow = Add(databaseID, storedProcedure);
}
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 3);
}
#if TRACE
VNC.AppLog.Trace4("Exit", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 4, startTicks);
#endif
return(dataRow);
}
public static void UpdateDataSet(this MSMO.StoredProcedure storedProcedure, Data.ApplicationDataSet.DBStoredProceduresRow dataRow)
{
try
{
dataRow.Owner = storedProcedure.Owner;
dataRow.CreateDate = storedProcedure.CreateDate;
if (Data.Config.ADSLoad_DBStoredProcedureContent)
{
try
{
dataRow.MethodName = storedProcedure.MethodName;
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 11);
#endif
dataRow.MethodName = "<Exception>";
}
try
{
dataRow.TextHeader = storedProcedure.TextHeader;
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 12);
#endif
dataRow.TextHeader = "<Exception>";
}
try
{
dataRow.TextBody = storedProcedure.TextBody;
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 13);
#endif
dataRow.TextBody = "<Exception>";
}
}
try
{
dataRow.DateLastModified = storedProcedure.DateLastModified;
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 14);
#endif
}
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 15);
// TODO(crhodes):
// Wrap anything above that throws an exception that we want to ignore,
// e.g. property not available because of SQL Edition.
}
}
public void ExtractScript(SQLObjectName oname, SQLScripts SQLScriptsCollection, bool Verbose)
{
// Store extracted scripts. Each extract may include multiple scripts.
StringCollection OutputScripts = new StringCollection();
string FinalScript = String.Empty;
switch (oname.ObjectType)
{
case SQLObjectType.Table:
Microsoft.SqlServer.Management.Smo.Table scriptTable = connDatabase.Tables[oname.Name, oname.Schema];
if (scriptTable != null)
{
StringCollection CheckScripts = new StringCollection(); // Store scripts to be checked
String TableScript = String.Empty; // Stores individual script for output collection.
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.DriAll = true;
scriptOptions.Statistics = true;
scriptOptions.ClusteredIndexes = true;
scriptOptions.NonClusteredIndexes = true;
scriptOptions.DriAllConstraints = true;
scriptOptions.WithDependencies = false;
// Get table and related scripts
CheckScripts = scriptTable.Script(scriptOptions);
// Check scripts so we can remove invalide SQL 2012 column store options from the script.
// (Why doesn't the target server version remove this?
// This is a crappy place to do this, and it's version specific.
// Need to implement the new versioning code to check target model.
foreach (string CheckCCI in CheckScripts)
{
if (CheckCCI.Contains(", DATA_COMPRESSION = COLUMNSTORE"))
{
TableScript = CheckCCI.Replace(", DATA_COMPRESSION = COLUMNSTORE", "");
}
else
{
TableScript = CheckCCI;
}
// Add the script into the OutputScripts collection.
OutputScripts.Add(TableScript);
}
}
break;
case SQLObjectType.View:
Microsoft.SqlServer.Management.Smo.View scriptView = connDatabase.Views[oname.Name, oname.Schema];
if (scriptView != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.DriAll = true;
scriptOptions.ClusteredIndexes = true;
scriptOptions.NonClusteredIndexes = true;
scriptOptions.WithDependencies = false;
// Must specify tables seperatly, but safer to do so
// to avoid having duplicate table names in the model.
OutputScripts = scriptView.Script(scriptOptions);
}
break;
case SQLObjectType.StoredProcedure:
Microsoft.SqlServer.Management.Smo.StoredProcedure scriptStoredProcedure = connDatabase.StoredProcedures[oname.Name, oname.Schema];
if (scriptStoredProcedure != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
OutputScripts = scriptStoredProcedure.Script(scriptOptions);
}
break;
case SQLObjectType.PartitionScheme:
{
Microsoft.SqlServer.Management.Smo.PartitionScheme scriptPScheme = connDatabase.PartitionSchemes[oname.Name];
if (scriptPScheme != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
OutputScripts = scriptPScheme.Script(scriptOptions);
}
}
break;
case SQLObjectType.PartitionFunction:
{
Microsoft.SqlServer.Management.Smo.PartitionFunction scriptPFunction = connDatabase.PartitionFunctions[oname.Name];
if (scriptPFunction != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
OutputScripts = scriptPFunction.Script(scriptOptions);
}
}
break;
case SQLObjectType.Schema:
{
Microsoft.SqlServer.Management.Smo.Schema scriptSchema = connDatabase.Schemas[oname.Name];
if (scriptSchema != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
scriptOptions.ScriptOwner = true; // This includes the "with authorize" part.
OutputScripts = scriptSchema.Script(scriptOptions);
}
}
break;
case SQLObjectType.FileGroup:
{
Microsoft.SqlServer.Management.Smo.FileGroup scriptFG = connDatabase.FileGroups[oname.Name];
if (scriptFG != null)
{
// Create manual script for FileGroups
OutputScripts.Add("ALTER DATABASE [$(DatabaseName)] ADD FILEGROUP " + scriptFG.Name);
}
}
break;
case SQLObjectType.User:
{
Microsoft.SqlServer.Management.Smo.User scriptUser = connDatabase.Users[oname.Name];
if (scriptUser != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
OutputScripts = scriptUser.Script(scriptOptions);
}
}
break;
case SQLObjectType.Function:
Microsoft.SqlServer.Management.Smo.UserDefinedFunction userDefinedFunction = connDatabase.UserDefinedFunctions[oname.Name, oname.Schema];
if (userDefinedFunction != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
OutputScripts = userDefinedFunction.Script(scriptOptions);
}
break;
}
if (OutputScripts.Count > 0)
{
Console.WriteLine("Extracted SQL script: (" + oname.ObjectType.ToString() + ") " + ((oname.Schema != String.Empty) ? oname.Schema + "." + oname.Name : oname.Name));
foreach (string script in OutputScripts)
{
// Add the script to the script collection.
FinalScript = FinalScript + script + Environment.NewLine + "GO" + Environment.NewLine;
}
}
else
{
Console.WriteLine("Warning - Could not retrieve: (" + oname.ObjectType.ToString() + ") " + ((oname.Schema != String.Empty) ? oname.Schema + "." + oname.Name : oname.Name));
FinalScript = String.Empty;
}
if (FinalScript != String.Empty)
{
SQLScriptsCollection.Scripts.Add(FinalScript);
}
else
{
SQLScriptsCollection.MissingScripts.Add("Missing SQL object: (" + oname.ObjectType.ToString() + ") " + ((oname.Schema != String.Empty) ? oname.Schema + "." + oname.Name : oname.Name));
}
// Print script(s) if verbose is on.
if (Verbose)
{
Console.WriteLine(FinalScript);
}
}
public void Script(string srvname, string dbName, string destination)
{
tbxOutput.Text = "Scripting the " + dbName + " database." + "\r\n";
if (destination == "")
{
destination = Environment.CurrentDirectory + "\\";
}
else
{
if (destination[destination.Length - 1] != Convert.ToChar("\\"))
{
destination += "\\";
}
}
tbxOutput.Text += "Output directory set to " + destination + "\r\n";
/* *************************** */
/* CHECK FOR VALID DESTINATION */
/* *************************** */
tbxOutput.Text += "Checking for valid destination directory...\r\n";
if (!Directory.Exists(destination))
{
throw new DirectoryNotFoundException("The specified destination directory does not exist.");
}
else
{
tbxOutput.Text += "Destination directory is valid.\r\n";
/* *********************** */
/* CREATE FOLDER STRUCTURE */
/* *********************** */
tbxOutput.Text += "Establishing folder structure...\r\n";
newFolders.Clear();
try
{
if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd")))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd"));
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd"));
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + "\r\n";
}
if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\"))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\");
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\");
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\" + "\r\n";
}
if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\"))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\");
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\");
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\" + "\r\n";
}
if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\"))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\");
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\");
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\" + "\r\n";
}
if (!Directory.Exists(destination + dbName + @"\Programmability\Functions\"))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\");
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\");
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\" + "\r\n";
}
}
catch
{
throw new UnauthorizedAccessException("The program failed to create the backup folders in the specified directory. Please check security settings.");
}
tbxOutput.Text += "Folder structure established \r\n";
}
/* *************** */
/* Generate Script */
/* *************** */
try //Wrap in try statement to catch incorrect server errors
{
tbxOutput.Text += "Connecting to server " + srvname + "...\r\n";
Server srv;
srv = new Server(srvname);
srv.ConnectionContext.LoginSecure = true;
if (!srv.Databases.Contains(dbName))
{
RemoveFolders();//Clean out folders creating during this run
throw new ArgumentException("The specified database could not be found.");
}
Database db = new Database();
db = srv.Databases[dbName];
Scripter scr = new Scripter(srv);
Scripter scrFullScript = new Scripter(srv);
srv.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject");
/* Create Options for the scr Scripter */
ScriptingOptions options = new ScriptingOptions();
options.IncludeHeaders = true;
options.AppendToFile = false;
options.ToFileOnly = true;
options.DriAll = true;
options.IncludeDatabaseContext = true;
//options.ScriptDrops = true;
scr.Options = options; //Assign options to scr
/* Create options for the scrFullScript Scripter */
ScriptingOptions scopFull = new ScriptingOptions();
scopFull.IncludeHeaders = true;
scopFull.AppendToFile = true;
scopFull.ToFileOnly = true;
scopFull.DriAll = true;
scopFull.IncludeDatabaseContext = true;
scopFull.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\"
+ dbName + "_FULL.sql";
scrFullScript.Options = scopFull; //Assign options to scrFullScript
/* ******************* */
/* CREATE SCRIPT FILES */
/* ******************* */
List<string> lstErrors = new List<string>();
//SCRIPT DATABASE
Microsoft.SqlServer.Management.Smo.Database[] dbs = new Microsoft.SqlServer.Management.Smo.Database[1];
tbxOutput.Text += "Scripting Database: " + db + "\r\n";
dbs[0] = db;
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\"
+ dbName + ".sql";
scr.Script(dbs);
scrFullScript.Script(dbs);
tbxOutput.Text += "Scripting Database Complete.\r\n";
//SCRIPT TABLES
Microsoft.SqlServer.Management.Smo.Table[] tbl = new Microsoft.SqlServer.Management.Smo.Table[1];
tbxOutput.Text += "Scripting Tables...\r\n";
for (int idx = 0; idx < db.Tables.Count; idx++)
{
if (!db.Tables[idx].IsSystemObject)
{
tbxOutput.Text += "Scripting Table: " + db.Tables[idx] + "\r\n";
tbl[0] = db.Tables[idx];
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\"
+ tbl[0].Name + ".sql";
scr.Script(tbl);
scrFullScript.Script(tbl);
}
}
tbxOutput.Text += "Scripting Tables Complete.\r\n";
//SCRIPT VIEWS
Microsoft.SqlServer.Management.Smo.View[] vw = new Microsoft.SqlServer.Management.Smo.View[1];
tbxOutput.Text += "Scripting Views...\r\n";
for (int idx = 0; idx < db.Views.Count; idx++)
{
if (!db.Views[idx].IsSystemObject)
{
tbxOutput.Text += "Scripting View: " + db.Views[idx] + "\r\n";
vw[0] = db.Views[idx];
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\"
+ vw[0].Name + ".sql";
scr.Script(vw);
scrFullScript.Script(vw);
}
}
tbxOutput.Text += "Scripting Views Complete.\r\n";
//SCRIPT STORED PROCEDURES
Microsoft.SqlServer.Management.Smo.StoredProcedure[] proc = new Microsoft.SqlServer.Management.Smo.StoredProcedure[1];
tbxOutput.Text += "Scripting Stored Procedures...\r\n";
for (int idx = 0; idx < db.StoredProcedures.Count; idx++)
{
if (!db.StoredProcedures[idx].IsSystemObject)
{
tbxOutput.Text += "Scripting Stored Procedure: " + db.StoredProcedures[idx] + "\r\n";
proc[0] = db.StoredProcedures[idx];
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\"
+ proc[0].Name + ".sql";
scr.Script(proc);
scrFullScript.Script(proc);
}
}
tbxOutput.Text += "Scripting Stored Procedures Complete.\r\n";
//SCRIPT FUNCTIONS
Microsoft.SqlServer.Management.Smo.UserDefinedFunction[] udf = new Microsoft.SqlServer.Management.Smo.UserDefinedFunction[1];
tbxOutput.Text += "Scripting User Defined Functions...\r\n";
for (int idx = 0; idx < db.UserDefinedFunctions.Count; idx++)
{
if (!db.UserDefinedFunctions[idx].IsSystemObject)
{
tbxOutput.Text += "Scripting User Defined Function: " + db.UserDefinedFunctions[idx] + "\r\n";
udf[0] = db.UserDefinedFunctions[idx];
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\"
+ udf[0].Name + ".sql";
scr.Script(udf);
scrFullScript.Script(udf);
}
}
tbxOutput.Text += "Scripting User Defined Functions complete.\r\n";
tbxOutput.Text += "Scripting master file...\r\n";
try
{
String strFullScript = "";
String strFullOutput = "";
tbxOutput.Text += "Retrieving full script...\r\n";
using (StreamReader sr = new StreamReader(scopFull.FileName))
{
strFullScript = sr.ReadToEnd();
}
tbxOutput.Text += "Full script retrieved.\r\n";
//strFullOutput = strFullScript;//Temporary
string[] arrFullScript = Regex.Split(strFullScript, "GO");
foreach (string line in arrFullScript)
{
if(!line.StartsWith("\r\nALTER TABLE"))
strFullOutput += line + "GO\r\n";
}
foreach (string line in arrFullScript)
{
if (line.StartsWith("\r\nALTER TABLE"))
strFullOutput += line + "GO\r\n";
}
string strConditionalDrop = "\r\n\r\nIF DB_ID('" + dbName + "') IS NOT NULL\r\nBEGIN\r\n"
+ " ALTER DATABASE " + dbName + "\r\n"
+ " SET SINGLE_USER\r\n"
+ " WITH ROLLBACK IMMEDIATE;\r\n"
+ " DROP DATABASE " + dbName + ";\r\n"
+ "END\r\n";
strFullOutput = strFullOutput.Insert(strFullOutput.IndexOf("GO") + 2, strConditionalDrop);
tbxOutput.Text += "Writing corrected full script...\r\n";
using (StreamWriter sw = new StreamWriter(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\" + dbName + "_FULL.sql"))
{
sw.Write(strFullOutput);
}
tbxOutput.Text += "Full script successfully written.\r\n";
tbxOutput.Text += "Scripting master file complete.\r\n";
}
catch
{
tbxOutput.Text += "ERROR Scripting Master File Failed.\r\n";
lstErrors.Add("Scripting Master File Failed.");
}
tbxOutput.Text += "=================================\r\n";
if (lstErrors.Count == 0)
tbxOutput.Text += "SCRIPTING COMPLETED SUCCESSFULLY.\r\n";
else
{
tbxOutput.Text += "SCRIPTING COMPLETED WITH ERRORS.\r\n";
tbxOutput.Text += String.Format("The following {0} errors occurred:\r\n", lstErrors.Count);
foreach (string error in lstErrors)
{
tbxOutput.Text += error + "\r\n";
}
}
ActiveControl = btnClose;
}
catch (ConnectionFailureException) //Error type thrown by attempt to bind invalid server name
{
//throw new ConnectionFailureException("A connection to the specified server could not be made. Please check the supplied server name and try again.");
tbxOutput.Text += "Connection to server failed.\r\n";
RemoveFolders();//Clean out folders creating during this run
tbxOutput.Text += "A connection to the specified server could not be made. Please check the supplied server name and try again.\r\n";
}
catch //General Catch-All re-throws error without further handling
{
RemoveFolders();//Clean out folders creating during this run
throw;
}
}
public void generate(String ProcedurePath)
{
String name = string.Format("ssp_{0}", _table.Name);
String FullObjectName = this._database.Name + "." + this._table.Schema + "." + this._table.Name;
Match match;
TSQL.StoredProcedure storedProcedure = new TSQL.StoredProcedure(this._database, name);
storedProcedure.Schema = this._table.Schema;
storedProcedure.TextMode = false;
TSQL.StoredProcedureParameter storedProcedureParameter = new TSQL.StoredProcedureParameter(storedProcedure, "@Operation");
storedProcedureParameter.DataType = TSQL.DataType.Int;
storedProcedureParameter.DefaultValue = "0";
storedProcedure.Parameters.Add(storedProcedureParameter);
TSQL.StoredProcedureParameter parameter2 = new TSQL.StoredProcedureParameter(storedProcedure, "@User");
parameter2.DataType = TSQL.DataType.NVarChar(0x80);
parameter2.DefaultValue = "NULL";
storedProcedure.Parameters.Add(parameter2);
IDictionary <TSQL.Column, TSQL.StoredProcedureParameter> source = new SortedDictionary <TSQL.Column, TSQL.StoredProcedureParameter>(new ColumnComparer());
foreach (TSQL.Column column in this._table.Columns)
{
TSQL.StoredProcedureParameter parameter3 = new TSQL.StoredProcedureParameter(storedProcedure, string.Format("@{0}", column.Name));
parameter3.DataType = column.DataType;
parameter3.DefaultValue = string.IsNullOrEmpty(column.Default) ? "NULL" : column.Default;
source.Add(column, parameter3);
storedProcedure.Parameters.Add(parameter3);
}
String SqlSearchBody = @"";
String SqlSearchHeader = @"-- =============================================================================
-- Name: #PROCEDURENAME#
-- Created By: MGenerator.Database.Generation.CrudProcedureGenerator (Generated)
-- Created On: ##GENDATE##
-- Description: Provides enhanced Search to Table [##DATBASE##].[##SCHEMA##].[##TABLE##]
--
-- This procedure was generated by a tool.
--
-- Changes to this file may cause incorrect behavior and will be
-- lost if the procedure is regenerated.
-- ============================================================================= \n\n".Replace("#PROCEDURENAME#", name).Replace("##GENDATE##", DateTime.Now.ToLongDateString()).Replace("##DATBASE##", _database.Name).Replace("##SCHEMA##", _table.Schema).Replace("##TABLE##", _table.Name);
SqlSearchBody = (char)9 + "\n CREATE PROCEDURE ##NAME##" + "\n";
SqlSearchBody = SqlSearchBody + (char)9 + (char)9 + "##PARAMETERS## \n";
SqlSearchBody = SqlSearchBody + (char)9 + "AS \n";
SqlSearchBody = SqlSearchBody + "BEGIN \n";
SqlSearchBody = SqlSearchBody + (char)9 + (char)9 + "SELECT \n";
SqlSearchBody = SqlSearchBody + "\t\t ##COLUMNS## \n";
SqlSearchBody = SqlSearchBody + (char)9 + (char)9 + "FROM \n";
SqlSearchBody = SqlSearchBody + (char)9 + (char)9 + (char)9 + "##TABLE## \n";
SqlSearchBody = SqlSearchBody + (char)9 + (char)9 + "WHERE \n";
SqlSearchBody = SqlSearchBody + (char)9 + (char)9 + (char)9 + "\t ##CONTRAINTS## \n";
SqlSearchBody = SqlSearchBody + " END ";
String Constraints = @"";
String ConstraintLine = "\t\t\t(##COLUMN## LIKE ISNULL(##PARAMETER##,NULL))";
String ColumnList = @"";
int i = 0;
int ic = _table.Columns.Count - 1;
while (i <= ic)
{
if (i == ic)
{
ColumnList = ColumnList + "[" + _table.Columns[i].Name + "]";
Constraints = Constraints + (char)9 + (ConstraintLine.Replace("##COLUMN##", "[" + _table.Columns[i].Name + "]").Replace("##PARAMETER##", "@" + _table.Columns[i].Name) + "\n");
}
else
{
ColumnList = ColumnList + "[" + _table.Columns[i].Name + "],";
Constraints = Constraints + (char)9 + (ConstraintLine.Replace("##COLUMN##", "[" + _table.Columns[i].Name + "]").Replace("##PARAMETER##", "@" + _table.Columns[i].Name) + " OR \n");
}
i = i + 1;
}
// build paramter scritp list
String ParametersList = @"";
foreach (TSQL.StoredProcedureParameter spp in storedProcedure.Parameters)
{
if (!(spp.Name == "Operation"))
{
ParametersList = ParametersList + "," + spp.Name + " " + spp.DataType.ToString() + " = NULL " + "\n";
}
}
SqlSearchBody = SqlSearchBody.Replace("##PARAMETERS##", "");
SqlSearchBody = SqlSearchBody.Replace("##NAME##", name);
SqlSearchBody = SqlSearchBody.Replace("##TABLE##", _table.Name);
SqlSearchBody = SqlSearchBody.Replace("##COLUMNS##", ColumnList);
SqlSearchBody = SqlSearchBody.Replace("##CONTRAINTS##", Constraints);
storedProcedure.TextMode = true;
storedProcedure.TextHeader = SqlSearchHeader;
//storedProcedure.TextBody = SqlSearchBody;
String Output = @"";
storedProcedure.TextMode = false;
Output = Output + SqlSearchHeader + "\n";
Output = Output + SqlSearchBody;
Output = Output + "\n GO";
File.WriteAllText(ProcedurePath + @"\" + name + ".sql", Output);
}
public TSQL.StoredProcedure Generate(String ProcedureRepositoryPath)
{
TSQL.StoredProcedure procedure = null;
if (CheckRequiredObjects())
{
procedure = new Microsoft.SqlServer.Management.Smo.StoredProcedure(this._database, "ssp_" + this._table.Name, _table.Schema);
// Set the text mode to false and then set other properties;
procedure.TextMode = false;
#region [ Operation 1 - Search by Any Criteria ]
// Add Parameters;
System.Text.StringBuilder SelectSql = new StringBuilder();
SelectSql.AppendLine("SELECT ");
int Count = _table.Columns.Count - 1;
int Index = 0;
while (Index <= Count)
{
MemberGraph mGraph = new MemberGraph(_table.Columns[Index]);
TSQL.StoredProcedureParameter parameter = new Microsoft.SqlServer.Management.Smo.StoredProcedureParameter(procedure, "@" + mGraph.PropertyName(), _table.Columns[Index].DataType);
parameter.DefaultValue = "NULL";
procedure.Parameters.Add(parameter);
// ntext Data types cna not be searched
if (!(mGraph.SqlColumn.DataType.SqlDataType == TSQL.SqlDataType.NText))
{
if (Index == Count)
{
SelectSql.AppendLine("[" + _table.Columns[Index].Name + "]");
}
else
{
SelectSql.AppendLine("[" + _table.Columns[Index].Name + "],");
}
}
Index = Index + 1;
}
SelectSql.AppendLine("FROM " + _table.Name);
SelectSql.AppendLine("WHERE ");
Count = _table.Columns.Count - 1;
Index = 0;
while (Index <= Count)
{
MemberGraph mGraph = new MemberGraph(_table.Columns[Index]);
String ParameterName = "@" + mGraph.PropertyName();
if (!(mGraph.SqlColumn.DataType.SqlDataType == TSQL.SqlDataType.NText))
{
if (Index == 0)
{ // This is the last column
if (_table.Columns[Index].DataType.SqlDataType == Microsoft.SqlServer.Management.Smo.SqlDataType.Text)
{
SelectSql.AppendLine("(" + ParameterName + " IS NULL OR " + _table.Columns[Index].Name + " LIKE " + ParameterName + ")");
}
else
{
SelectSql.AppendLine("(" + ParameterName + " IS NULL OR " + _table.Columns[Index].Name + " =" + ParameterName + ")");
}
}
else
{
SelectSql.AppendLine("AND");
if (_table.Columns[Index].DataType.SqlDataType == Microsoft.SqlServer.Management.Smo.SqlDataType.Text)
{
SelectSql.AppendLine("(" + ParameterName + " IS NULL OR " + _table.Columns[Index].Name + " LIKE " + ParameterName + ")");
}
else
{
SelectSql.AppendLine("(" + ParameterName + " IS NULL OR " + _table.Columns[Index].Name + " = " + ParameterName + ")");
}
}
}
Index = Index + 1;
}
SelectSql.AppendLine("\n");
#endregion
#region [ Build Script ]
string totalScript = SelectSql.ToString();
procedure.TextBody = totalScript;
TSQL.ScriptingOptions Options = new Microsoft.SqlServer.Management.Smo.ScriptingOptions();
StringBuilder sbSql = new StringBuilder();
foreach (var s in procedure.Script())
{
sbSql.AppendLine(s);
}
#endregion
File.WriteAllText(ProcedureRepositoryPath + @"\" + procedure.Name + ".sql", sbSql.ToString());
}
return(procedure);
}
public void Script(string srvname, string dbName, string destination)
{
tbxOutput.Text = "Scripting the " + dbName + " database." + "\r\n";
if (destination == "")
{
destination = Environment.CurrentDirectory + "\\";
}
else
{
if (destination[destination.Length - 1] != Convert.ToChar("\\"))
{
destination += "\\";
}
}
tbxOutput.Text += "Output directory set to " + destination + "\r\n";
/* *************************** */
/* CHECK FOR VALID DESTINATION */
/* *************************** */
tbxOutput.Text += "Checking for valid destination directory...\r\n";
if (!Directory.Exists(destination))
{
throw new DirectoryNotFoundException("The specified destination directory does not exist.");
}
else
{
tbxOutput.Text += "Destination directory is valid.\r\n";
/* *********************** */
/* CREATE FOLDER STRUCTURE */
/* *********************** */
tbxOutput.Text += "Establishing folder structure...\r\n";
newFolders.Clear();
try
{
if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd")))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd"));
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd"));
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + "\r\n";
}
if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\"))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\");
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\");
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\" + "\r\n";
}
if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\"))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\");
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\");
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\" + "\r\n";
}
if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\"))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\");
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\");
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\" + "\r\n";
}
if (!Directory.Exists(destination + dbName + @"\Programmability\Functions\"))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\");
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\");
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\" + "\r\n";
}
}
catch
{
throw new UnauthorizedAccessException("The program failed to create the backup folders in the specified directory. Please check security settings.");
}
tbxOutput.Text += "Folder structure established \r\n";
}
/* *************** */
/* Generate Script */
/* *************** */
try //Wrap in try statement to catch incorrect server errors
{
tbxOutput.Text += "Connecting to server " + srvname + "...\r\n";
Server srv;
srv = new Server(srvname);
srv.ConnectionContext.LoginSecure = true;
if (!srv.Databases.Contains(dbName))
{
RemoveFolders();//Clean out folders creating during this run
throw new ArgumentException("The specified database could not be found.");
}
Database db = new Database();
db = srv.Databases[dbName];
Scripter scr = new Scripter(srv);
Scripter scrFullScript = new Scripter(srv);
srv.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject");
/* Create Options for the scr Scripter */
ScriptingOptions options = new ScriptingOptions();
options.IncludeHeaders = true;
options.AppendToFile = false;
options.ToFileOnly = true;
options.DriAll = true;
options.IncludeDatabaseContext = true;
//options.ScriptDrops = true;
scr.Options = options; //Assign options to scr
/* Create options for the scrFullScript Scripter */
ScriptingOptions scopFull = new ScriptingOptions();
scopFull.IncludeHeaders = true;
scopFull.AppendToFile = true;
scopFull.ToFileOnly = true;
scopFull.DriAll = true;
scopFull.IncludeDatabaseContext = true;
scopFull.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\"
+ dbName + "_FULL.sql";
scrFullScript.Options = scopFull; //Assign options to scrFullScript
/* ******************* */
/* CREATE SCRIPT FILES */
/* ******************* */
List <string> lstErrors = new List <string>();
//SCRIPT DATABASE
Microsoft.SqlServer.Management.Smo.Database[] dbs = new Microsoft.SqlServer.Management.Smo.Database[1];
tbxOutput.Text += "Scripting Database: " + db + "\r\n";
dbs[0] = db;
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\"
+ dbName + ".sql";
scr.Script(dbs);
scrFullScript.Script(dbs);
tbxOutput.Text += "Scripting Database Complete.\r\n";
//SCRIPT TABLES
Microsoft.SqlServer.Management.Smo.Table[] tbl = new Microsoft.SqlServer.Management.Smo.Table[1];
tbxOutput.Text += "Scripting Tables...\r\n";
for (int idx = 0; idx < db.Tables.Count; idx++)
{
if (!db.Tables[idx].IsSystemObject)
{
tbxOutput.Text += "Scripting Table: " + db.Tables[idx] + "\r\n";
tbl[0] = db.Tables[idx];
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\"
+ tbl[0].Name + ".sql";
scr.Script(tbl);
scrFullScript.Script(tbl);
}
}
tbxOutput.Text += "Scripting Tables Complete.\r\n";
//SCRIPT VIEWS
Microsoft.SqlServer.Management.Smo.View[] vw = new Microsoft.SqlServer.Management.Smo.View[1];
tbxOutput.Text += "Scripting Views...\r\n";
for (int idx = 0; idx < db.Views.Count; idx++)
{
if (!db.Views[idx].IsSystemObject)
{
tbxOutput.Text += "Scripting View: " + db.Views[idx] + "\r\n";
vw[0] = db.Views[idx];
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\"
+ vw[0].Name + ".sql";
scr.Script(vw);
scrFullScript.Script(vw);
}
}
tbxOutput.Text += "Scripting Views Complete.\r\n";
//SCRIPT STORED PROCEDURES
Microsoft.SqlServer.Management.Smo.StoredProcedure[] proc = new Microsoft.SqlServer.Management.Smo.StoredProcedure[1];
tbxOutput.Text += "Scripting Stored Procedures...\r\n";
for (int idx = 0; idx < db.StoredProcedures.Count; idx++)
{
if (!db.StoredProcedures[idx].IsSystemObject)
{
tbxOutput.Text += "Scripting Stored Procedure: " + db.StoredProcedures[idx] + "\r\n";
proc[0] = db.StoredProcedures[idx];
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\"
+ proc[0].Name + ".sql";
scr.Script(proc);
scrFullScript.Script(proc);
}
}
tbxOutput.Text += "Scripting Stored Procedures Complete.\r\n";
//SCRIPT FUNCTIONS
Microsoft.SqlServer.Management.Smo.UserDefinedFunction[] udf = new Microsoft.SqlServer.Management.Smo.UserDefinedFunction[1];
tbxOutput.Text += "Scripting User Defined Functions...\r\n";
for (int idx = 0; idx < db.UserDefinedFunctions.Count; idx++)
{
if (!db.UserDefinedFunctions[idx].IsSystemObject)
{
tbxOutput.Text += "Scripting User Defined Function: " + db.UserDefinedFunctions[idx] + "\r\n";
udf[0] = db.UserDefinedFunctions[idx];
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\"
+ udf[0].Name + ".sql";
scr.Script(udf);
scrFullScript.Script(udf);
}
}
tbxOutput.Text += "Scripting User Defined Functions complete.\r\n";
tbxOutput.Text += "Scripting master file...\r\n";
try
{
String strFullScript = "";
String strFullOutput = "";
tbxOutput.Text += "Retrieving full script...\r\n";
using (StreamReader sr = new StreamReader(scopFull.FileName))
{
strFullScript = sr.ReadToEnd();
}
tbxOutput.Text += "Full script retrieved.\r\n";
//strFullOutput = strFullScript;//Temporary
string[] arrFullScript = Regex.Split(strFullScript, "GO");
foreach (string line in arrFullScript)
{
if (!line.StartsWith("\r\nALTER TABLE"))
{
strFullOutput += line + "GO\r\n";
}
}
foreach (string line in arrFullScript)
{
if (line.StartsWith("\r\nALTER TABLE"))
{
strFullOutput += line + "GO\r\n";
}
}
string strConditionalDrop = "\r\n\r\nIF DB_ID('" + dbName + "') IS NOT NULL\r\nBEGIN\r\n"
+ " ALTER DATABASE " + dbName + "\r\n"
+ " SET SINGLE_USER\r\n"
+ " WITH ROLLBACK IMMEDIATE;\r\n"
+ " DROP DATABASE " + dbName + ";\r\n"
+ "END\r\n";
strFullOutput = strFullOutput.Insert(strFullOutput.IndexOf("GO") + 2, strConditionalDrop);
tbxOutput.Text += "Writing corrected full script...\r\n";
using (StreamWriter sw = new StreamWriter(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\" + dbName + "_FULL.sql"))
{
sw.Write(strFullOutput);
}
tbxOutput.Text += "Full script successfully written.\r\n";
tbxOutput.Text += "Scripting master file complete.\r\n";
}
catch
{
tbxOutput.Text += "ERROR Scripting Master File Failed.\r\n";
lstErrors.Add("Scripting Master File Failed.");
}
tbxOutput.Text += "=================================\r\n";
if (lstErrors.Count == 0)
{
tbxOutput.Text += "SCRIPTING COMPLETED SUCCESSFULLY.\r\n";
}
else
{
tbxOutput.Text += "SCRIPTING COMPLETED WITH ERRORS.\r\n";
tbxOutput.Text += String.Format("The following {0} errors occurred:\r\n", lstErrors.Count);
foreach (string error in lstErrors)
{
tbxOutput.Text += error + "\r\n";
}
}
ActiveControl = btnClose;
}
catch (ConnectionFailureException) //Error type thrown by attempt to bind invalid server name
{
//throw new ConnectionFailureException("A connection to the specified server could not be made. Please check the supplied server name and try again.");
tbxOutput.Text += "Connection to server failed.\r\n";
RemoveFolders();//Clean out folders creating during this run
tbxOutput.Text += "A connection to the specified server could not be made. Please check the supplied server name and try again.\r\n";
}
catch //General Catch-All re-throws error without further handling
{
RemoveFolders(); //Clean out folders creating during this run
throw;
}
}
