/// <summary>
/// Converts sql db objects of a connected SQL server database into an XML schema file.
/// </summary>
/// <param name="SQLServer">The string for the SQL server name to connect to.</param>
/// <param name="DBName">The string for the SQL server database name to connect to.</param>
/// <param name="UID">The string for the SQL server user login to use for the SqlConnections object.</param>
/// <param name="PWD">The string for the SQL server user password to use for the SqlConnections object.</param>
/// <param name="SQLfile">The SQL script file that will be translated from the create XML schema file.</param>
/// <param name="Translate">A flag to cause the translation method to be called, thus creating the SQL script file
/// specified with the SQLfile param.</param>
/// <param name="Primary">A flag to force the FileGroups to only have Primary, no matter what the actual one is in
/// the database.</param>
/// <param name="threaded">Object that passes in a null or as bool</param>
/// <param name="objectsToSerialize">bit mask of SQL objects to serialize - use the _nodetype enum</param>
/// <param name="CustomXSLT">the full path and name of a custom xslt to be applied to the final xml output</param>
/// <param name="delimTableNames">the comma delimited string of table names to be serialized</param>
/// <returns>Returns the delimited ',' XML + SQL output filename(s)</returns>
public static string SerializeDB(
string SQLServer,
string DBName,
string UID,
string PWD,
string SQLfile,
bool Translate,
bool Primary,
object threaded,
byte objectsToSerialize,
string CustomXSLT,
string delimTableNames)
{
_threaded = threaded;
string _serverDB = SQLServer + ":" + DBName;
string outputFile = string.Format(_OUTPUTFILE, SQLServer.Replace("\\", "_").Replace(":", "-"),
DBName.Replace("\\", "_").Replace(":", "-"));
try
{
// TODO: add threads if this takes a long while
SQLMethods.SQLConnections _connections;
if (UID != null && PWD != null)
{
_connections = new SQLMethods.SQLConnections(SQLServer, DBName, UID, PWD, false);
}
else
{
_connections = new SQLMethods.SQLConnections(SQLServer, DBName);
}
if (_connections != null && _connections.Count > 0)
{
DataSet _ds = new DataSet("DataBase_Schema");
_ds.EnforceConstraints = false;
DataTable dt = _ds.Tables.Add("Database");
dt.Columns.Add("Name");
dt.Columns.Add("Date");
dt.Columns.Add("Time");
DataRow dr = dt.NewRow();
dr.ItemArray =
new object[] { DBName, DateTime.Now.ToShortDateString(), DateTime.Now.ToShortTimeString() };
dt.Rows.Add(dr);
Sleep();
// get defaults, rules and UDDTs : in this order because of dependant behavior
if ((objectsToSerialize & Convert.ToByte(_NodeType.DEFAULT)) == (int)_NodeType.DEFAULT)
{
SQLObjects.Defaults _defaults = new SQLObjects.Defaults(DBName);
_defaults.GetObject<SQLObjects.Defaults>(_connections[0].sqlConnection);
_ds.Merge(_defaults);
}
Sleep();
if ((objectsToSerialize & Convert.ToByte(_NodeType.RULE)) == (int)_NodeType.RULE)
{
SQLObjects.Rules _rules = new SQLObjects.Rules(DBName);
_rules.GetObject<SQLObjects.Rules>(_connections[0].sqlConnection);
_ds.Merge(_rules);
}
Sleep();
if ((objectsToSerialize & Convert.ToByte(_NodeType.UDDT)) == (int)_NodeType.UDDT)
{
SQLObjects.UDDTs _uddts = new SQLObjects.UDDTs(DBName);
_uddts.GetObject<SQLObjects.UDDTs>(_connections[0].sqlConnection);
_ds.Merge(_uddts);
}
Sleep();
if ((objectsToSerialize & Convert.ToByte(_NodeType.TABLE)) == (int)_NodeType.TABLE)
{
SQLObjects.Tables _tables = new SQLObjects.Tables(DBName);
if (!string.IsNullOrEmpty(delimTableNames))
{
_tables.GetObject<SQLObjects.Tables>(_connections[0].sqlConnection, Primary, delimTableNames);
}
else
{
_tables.GetObject<SQLObjects.Tables>(_connections[0].sqlConnection, Primary);
}
// TODO: make work with DBs attached as MDF files to SQL 2005
_ds.Merge(_tables);
}
Sleep();
if ((objectsToSerialize & Convert.ToByte(_NodeType.VIEW)) == (int)_NodeType.VIEW)
{
SQLObjects.Views _views = new SQLObjects.Views(DBName);
_views.GetObject<SQLObjects.Views>(_connections[0].sqlConnection);
_ds.Merge(_views);
}
Sleep();
if ((objectsToSerialize & Convert.ToByte(_NodeType.SPROC)) == (int)_NodeType.SPROC)
{
SQLObjects.Sprocs _sprocs = new SQLObjects.Sprocs(DBName);
_sprocs.GetObject<SQLObjects.Sprocs>(_connections[0].sqlConnection);
_ds.Merge(_sprocs);
}
Sleep();
if ((objectsToSerialize & Convert.ToByte(_NodeType.FUNCTION)) == (int)_NodeType.FUNCTION)
{
SQLObjects.Funcs _funcs = new SQLObjects.Funcs(DBName);
_funcs.GetObject<SQLObjects.Funcs>(_connections[0].sqlConnection);
_ds.Merge(_funcs);
}
Sleep();
if ((objectsToSerialize & Convert.ToByte(_NodeType.TRIGGER)) == (int)_NodeType.TRIGGER)
{
SQLObjects.Triggers _triggers = new SQLObjects.Triggers(DBName);
_triggers.GetObject<SQLObjects.Triggers>(_connections[0].sqlConnection);
_ds.Merge(_triggers);
}
// TODO: add jobs, users, roles
Sleep();
// get rid of old files
if (File.Exists(outputFile)) File.Delete(outputFile);
// write out xml schema document
XmlDataDocument xmlData = new XmlDataDocument(_ds);
//xmlData.Save(outputFile);
// reload to xml schema to avoid the "deleted row" error when removing the dependant child nodes
XmlDocument xmlDoc = new XmlDocument();
//xmlDoc.Load(outputFile);
xmlDoc.LoadXml(xmlData.OuterXml);
Sleep();
// sort the dependencies for views, functions, and stored procedures
SortDependencies(_serverDB, VIEWPATH, VIEWDEP, ref xmlDoc);
SortDependencies(_serverDB, FUNCPATH, FUNCDEP, ref xmlDoc);
SortDependencies(_serverDB, SPROCPATH, SPROCDEP, ref xmlDoc);
foreach (Char c in Path.GetInvalidFileNameChars())
{
outputFile = outputFile.Replace(c, '_');
}
foreach (Char c in Path.GetInvalidPathChars())
{
outputFile = outputFile.Replace(c, '_');
}
xmlDoc.Save(outputFile);
// perform garbage collection to free up memory
GC.Collect();
if (Translate && outputFile != null && outputFile.Trim().Length > 0)
{
string createName = outputFile.ToLower().Replace(".xml", ".sql");
if (SQLfile != null && SQLfile.Length > 0)
{
createName = SQLfile.ToLower().Replace(".xml", ".sql");
}
if (!createName.EndsWith(".sql")) { createName += ".sql"; }
XsltHelper.SQLTransform(outputFile, XsltHelper.SQLCREATEXSLT, createName);
outputFile += "," + createName;
logger.Info("\nSQL Create Schema has been saved to " + createName + ".");
}
if (CustomXSLT != null && CustomXSLT.Trim().Length > 0)
{
FileInfo fi = new FileInfo(CustomXSLT);
File.WriteAllText("CustomOutput.XML", XsltHelper.Transform(xmlDoc.OuterXml, fi));
logger.Info("\nThe Custom XSLT {0}, has been applied and saved as CustomOutput.XML.", CustomXSLT);
}
}
}
catch (Exception ex)
{
if (ex is System.Data.SqlClient.SqlException)
{
logger.Error("\nSQL Error: {0}, DB Server {1}", ex.Message, _serverDB);
}
else
{
logger.Error(ERRORFORMAT, ex.Message, ex.Source, ex.StackTrace);
}
}
return outputFile;
}
public SQLSecuritySettings(SQLServerExplorer sse)
{
string[] SQLServers = sse.Servers;
SQLConnections sqlConnections = sse.SQLConnections;
//
// Required for Windows Form Designer support
//
InitializeComponent();
this.Height = 188;
lblWarning.Visible = true;
rdb_Integrated.Visible = false;
rdb_Mixed.Visible = false;
groupBox1.Visible = false;
label2.Visible = false;
label3.Visible = false;
txt_PWD.Visible = false;
txt_UID.Visible = false;
label4.Top = 30;
comboBox1.Top = 32;
comboBox1.Enabled = false;
btn_OK.Top = 88;
btn_Cancel.Top = 119;
lblWarning.Top = 88;
_SQLConnections = sqlConnections;
chkSavePwd.Visible = false;
rdb_Integrated.Checked = true;
rdb_Mixed.Checked = false;
txt_ServerName.SelectedIndexChanged += new EventHandler(txt_ServerName_SelectedIndexChanged);
txt_ServerName.Enabled = true;
if (SQLServers != null)
{
txt_ServerName.Items.AddRange(SQLServers);
}
else
{
txt_ServerName.Enabled = true;
txt_ServerName.Focus();
}
string servername = string.Empty;
if (typeof(ServerTreeNode).IsInstanceOfType(sse.SelectedTreeNode))
{
servername = sse.SelectedTreeNode.Text;
}
if (typeof(DBTreeNode).IsInstanceOfType(sse.SelectedTreeNode))
{
servername = sse.SelectedTreeNode.Parent.Text;
}
txt_ServerName.Text = servername;
}
