using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
namespace DatabaseToXml
{
/// <summary>
/// Main class of program.
/// </summary>
class Program
{
/// <summary>
/// Start point of program.
/// </summary>
/// <param name="args">CLI arguments.</param>
static void Main(string[] args)
{
Arguments arguments = new Arguments(args);
WriteAppHeader();
InitArguments(arguments);
}
#region Control Resources
/// <summary>
/// Write application header title to CLI interface.
/// </summary>
private static void WriteAppHeader()
{
System.Reflection.Assembly assembly;
assembly = System.Reflection.Assembly.GetExecutingAssembly();
// Get application title.
System.Reflection.AssemblyTitleAttribute titleAttribute;
titleAttribute = (System.Reflection.AssemblyTitleAttribute)assembly.GetCustomAttributes(typeof(System.Reflection.AssemblyTitleAttribute), false)[0];
// Get application description.
System.Reflection.AssemblyDescriptionAttribute descriptionAttribute;
descriptionAttribute = (System.Reflection.AssemblyDescriptionAttribute)assembly.GetCustomAttributes(typeof(System.Reflection.AssemblyDescriptionAttribute), false)[0];
// Get application version.
string version = assembly.GetName().Version.ToString();
// Write title.
Console.WriteLine(titleAttribute.Title + " " + version);
Console.WriteLine(descriptionAttribute.Description);
}
/// <summary>
/// Write help text from help.txt file.
/// </summary>
private static void WriteAppHelp()
{
Console.Write(GetTextResource("Help.txt"));
}
private static string GetTextResource(string name)
{
System.Reflection.Assembly assembly;
assembly = System.Reflection.Assembly.GetExecutingAssembly();
System.IO.TextReader tr = new System.IO.StreamReader(assembly.GetManifestResourceStream(assembly.GetName().Name + ".Resources." + name));
return tr.ReadToEnd();
}
#endregion
/// <summary>
/// Initialize CLI arguments.
/// </summary>
/// <param name="arguments">Arguments.</param>
private static void InitArguments(Arguments arguments)
{
// Command line parsing
bool argumentsOk = true;
if (arguments.Count == 0 || arguments.ContainsKey("?"))
{
WriteAppHelp();
}
else
{
Console.WriteLine("");
if (String.IsNullOrEmpty(arguments["d"]) && String.IsNullOrEmpty(arguments["f"]))
{
Console.WriteLine("Please specify database name!");
argumentsOk = false;
}
if (!String.IsNullOrEmpty(arguments["r"]))
{
try
{
filterPattern = new System.Text.RegularExpressions.Regex(arguments["r"]);
}
catch (Exception ex)
{
Console.WriteLine("Pattern Error:");
Console.WriteLine(ex.Message);
argumentsOk = false;
}
}
if (argumentsOk)
{
bool cSuccess = InitConnection(arguments);
if (cSuccess)
{
if (!string.IsNullOrEmpty(arguments["c"]))
{
WriteCode(arguments);
}
if (!string.IsNullOrEmpty(arguments["g"]))
{
GenerateSqlScript(arguments, true, System.IO.FileMode.Create);
GenerateSqlScript(arguments, false, System.IO.FileMode.Append);
}
}
}
}
}
#region Database Connection
private static Microsoft.SqlServer.Management.Common.ServerConnection connection;
private static Microsoft.SqlServer.Management.Smo.Server server;
private static Microsoft.SqlServer.Management.Smo.Database database;
private static System.Text.RegularExpressions.Regex filterPattern;
private static bool InitConnection(Arguments arguments)
{
System.Data.SqlClient.SqlConnectionStringBuilder scb;
connection = new Microsoft.SqlServer.Management.Common.ServerConnection();
scb = new System.Data.SqlClient.SqlConnectionStringBuilder();
scb.DataSource = "localhost";
if (arguments.ContainsKey("t"))
{
scb.IntegratedSecurity = true;
}
if (arguments.ContainsKey("u")) scb.UserID = arguments["u"];
if (arguments.ContainsKey("p")) scb.Password = arguments["p"];
if (arguments.ContainsKey("s")) scb.DataSource = arguments["s"];
Console.WriteLine("Connection String: " + scb.ConnectionString);
connection.ConnectionString = scb.ConnectionString;
server = new Microsoft.SqlServer.Management.Smo.Server(connection);
// IMPORTANT.
// If you find that your SQL Server Management Object's code is running really slow,
// check SQL Profiler to see if you are accessing properties that are not returned by
// default and creating extra roundtrips to the database.
// To speed up things we will force IsSystemObject to be returned by default.
//server.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject", "Text" );
// Get all fields by default.
server.SetDefaultInitFields(typeof(StoredProcedure), true);
Console.Write("Connecting...");
try
{
server.ConnectionContext.Connect();
Console.Write(" Success!\r\n");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.WriteLine(ex.Message + ex.GetBaseException().Message);
}
if (server.ConnectionContext.IsOpen)
{
string databaseName = arguments["d"];
// Create temp database.
if (!string.IsNullOrEmpty(arguments["f"]))
{
databaseName = "TempDatabase";
string sqlFileName = arguments["f"];
// Remove previous database.
if (server.Databases.Contains(databaseName))
{
Console.WriteLine("Drop '" + databaseName + "' Database.");
database = server.Databases[databaseName];
try
{
database.Drop();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return false;
}
}
database = new Database(server, databaseName);
Console.WriteLine("Create '" + databaseName + "' Database.");
database.Create();
System.IO.FileInfo sqlFileInfo = new System.IO.FileInfo(sqlFileName);
if (sqlFileInfo.Exists)
{
Console.WriteLine("Import SQL Script: " + sqlFileName);
string sqlScript = System.IO.File.ReadAllText(sqlFileInfo.FullName);
try
{
database.ExecuteNonQuery(sqlScript, Microsoft.SqlServer.Management.Common.ExecutionTypes.ContinueOnError);
}
catch (Exception ex)
{
Console.WriteLine(ex.InnerException.Message);
return false;
}
}
//database.Drop();
}
else
{
database = server.Databases[databaseName];
}
}
return server.ConnectionContext.IsOpen;
}
#endregion
//private static bool WriteXml(Arguments arguments)
//{
// string xmlFile = arguments["x"];
// if (!String.IsNullOrEmpty(xmlFile))
// {
// System.IO.FileInfo xmlInfo = new System.IO.FileInfo(xmlFile);
// Console.WriteLine("Write to File:" + xmlInfo.FullName);
// if (xmlInfo.Exists) xmlInfo.Delete();
// System.Xml.XmlTextWriter writer = new System.Xml.XmlTextWriter(xmlInfo.FullName, System.Text.Encoding.UTF8);
// writer.Formatting = System.Xml.Formatting.Indented;
// writer.WriteStartDocument();
// writer.WriteComment("Comments: Database XML Comments");
// writer.WriteStartElement("doc", "");
// writer.WriteStartElement("assembly", "");
// writer.WriteElementString("name", "", System.IO.Path.GetFileNameWithoutExtension(xmlInfo.Name));
// writer.WriteEndElement(); //assembly
// writer.WriteStartElement("members", "");
// int length = database.StoredProcedures.Count;
// Microsoft.SqlServer.Management.Smo.StoredProcedure procedure;
// JocysCom.Sql.XmlDocumentation.XmlComments xmlComments;
// // Route thru user procedures
// for (int i = 0; i < length; i++)
// {
// if (!database.StoredProcedures[i].IsSystemObject && !database.StoredProcedures[i].IsEncrypted)
// {
// procedure = database.StoredProcedures[i];
// bool proceed = true;
// if (filterPattern != null)
// {
// proceed = filterPattern.IsMatch(procedure.Name);
// }
// if (proceed)
// {
// Console.WriteLine("Export: " + procedure.Name);
// xmlComments = new JocysCom.Sql.XmlDocumentation.XmlComments(procedure, true);
// //writer.WriteStartElement("member", "");
// //writer.WriteAttributeString("name", "M:" + procedure.Name);
// System.Xml.XmlDocument doc;
// doc = (System.Xml.XmlDocument)xmlComments.AllComments;
// doc.DocumentElement.WriteTo(writer);
// //writer.WriteEndElement(); // member
// }
// }
// }
// writer.WriteEndElement(); //members
// writer.WriteEndElement(); //doc
// writer.WriteEndDocument();
// writer.Flush();
// writer.Close();
// Console.WriteLine();
// Console.WriteLine("Export to XML was finished.");
// }
// return true;
//}
private static bool WriteCode(Arguments arguments)
{
string xmlFile = arguments["c"];
if (!String.IsNullOrEmpty(xmlFile))
{
System.IO.FileInfo csInfo = new System.IO.FileInfo(xmlFile);
Console.WriteLine("Write to File:" + csInfo.FullName);
if (csInfo.Exists) csInfo.Delete();
System.IO.StreamWriter writer = new System.IO.StreamWriter(csInfo.FullName, false);
//writer.WriteLine("using System;");
//writer.WriteLine("using System.Collections.Generic;");
writer.WriteLine("using JocysCom.Sql.XmlDocumentation.SqlDataTypes;");
writer.Write(GetTextResource("SqlDataType.txt"));
writer.WriteLine();
writer.WriteLine("namespace Database");
writer.WriteLine("{");
writer.WriteLine("\t///<summary></summary>");
writer.WriteLine("\tpublic class Procedures");
writer.WriteLine("\t{");
//=================================
// Route thru user procedures
//---------------------------------
StoredProcedureCollection procedures = database.StoredProcedures;
Microsoft.SqlServer.Management.Smo.StoredProcedure procedure;
int length = procedures.Count;
for (int i = 0; i < length; i++)
{
if (!procedures[i].IsSystemObject && !procedures[i].IsEncrypted)
{
procedure = procedures[i];
bool proceed = true;
if (filterPattern != null)
{
proceed = filterPattern.IsMatch(procedure.Name);
}
if (proceed)
{
Console.WriteLine("Export: " + procedure.Name);
JocysCom.Sql.XmlDocumentation.XmlComments xmlComments;
xmlComments = new JocysCom.Sql.XmlDocumentation.XmlComments(procedure, true);
//writer.WriteStartElement("member", "");
//writer.WriteAttributeString("name", "M:" + procedure.Name);
writer.WriteLine(xmlComments.ToComments("\t\t/// ", true));
writer.WriteLine("\t\tpublic int " + procedure.Name + "(");
for (int p = 0; p < procedure.Parameters.Count; p++)
{
writer.Write("\t\t\t" + procedure.Parameters[p].DataType.SqlDataType.ToString() + " " + procedure.Parameters[p].Name.Replace("@", ""));
if (p < procedure.Parameters.Count - 1) writer.Write(",");
writer.WriteLine();
}
writer.WriteLine("\t\t){");
writer.WriteLine("\t\t\treturn 0;");
writer.WriteLine("\t\t}");
writer.WriteLine();
ScriptingOptions options = new ScriptingOptions();
System.Collections.Specialized.StringCollection sc = procedure.Script(options);
}
}
}
writer.WriteLine("\t}");
writer.WriteLine("}");
writer.Flush();
writer.Close();
}
return true;
}
private static bool GenerateSqlScript(Arguments arguments, bool scriptDrops, System.IO.FileMode fileMode)
{
// Set SQL Script file.
string fileName = arguments["g"];
System.IO.FileInfo fileInfo = new System.IO.FileInfo(fileName);
string dirName = arguments["gd"];
System.IO.DirectoryInfo dirNfo = null;
bool expDir = false;
if (!String.IsNullOrEmpty(dirName)){
dirNfo = new System.IO.DirectoryInfo(dirName);
expDir = dirNfo.Exists & !String.IsNullOrEmpty(dirName);
}
//if (fileInfo.Exists) fileInfo.Delete();
// Declare script options.
ScriptingOptions options = new ScriptingOptions();
options.Permissions = true;
// If IncludeIfNotExists = true then procedure text will be generated
// through "EXEC dbo.sp_executesql @statement = N'".
options.IncludeIfNotExists = scriptDrops;
options.ScriptDrops = scriptDrops;
options.AppendToFile = true;
//options.FileName = fileInfo.FullName;
options.Permissions = true;
System.IO.FileStream fs = new System.IO.FileStream(fileInfo.FullName, fileMode);
System.IO.TextWriter tw = new System.IO.StreamWriter(fs);
System.IO.FileStream dirFs = null;
System.IO.TextWriter dirTw = null;
string scriptingMode = scriptDrops ? "Procedure Drops" : "Stored Procedures";
Console.WriteLine();
Console.WriteLine("Generate " + scriptingMode + ".");
Console.WriteLine("Scripting To:");
Console.WriteLine(fileInfo.FullName);
Console.WriteLine();
//=================================
// Route thru user procedures
//---------------------------------
StoredProcedureCollection procedures = database.StoredProcedures;
Microsoft.SqlServer.Management.Smo.StoredProcedure procedure;
int length = procedures.Count;
for (int i = 0; i < length; i++)
{
if (!procedures[i].IsSystemObject && !procedures[i].IsEncrypted)
{
procedure = procedures[i];
bool proceed = true;
if (filterPattern != null)
{
proceed = filterPattern.IsMatch(procedure.Name);
}
if (proceed)
{
if (expDir)
{
string procName = dirNfo.FullName + "\\" + procedure.Name + ".sql";
dirFs = new System.IO.FileStream(procName, fileMode);
dirTw = new System.IO.StreamWriter(dirFs);
}
Console.WriteLine(" " + procedure.Name);
System.Collections.Specialized.StringCollection sc = procedure.Script(options);
foreach (string line in sc)
{
// If this is procedure body then...
System.Text.RegularExpressions.Regex cpRx;
cpRx = new System.Text.RegularExpressions.Regex("\\s*CREATE\\s+PROC", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
if (cpRx.IsMatch(line))
{
// Extract and regenerate comments.
JocysCom.Sql.XmlDocumentation.XmlComments xmlComments;
// This is SQL script so we don't need details of param inside comments.
xmlComments = new JocysCom.Sql.XmlDocumentation.XmlComments(procedure, true, false);
// If old style argument was set then...
string procComm = string.Empty;
if (arguments.ContainsKey("a"))
{
procComm = OldTemplate(procedure, xmlComments, arguments);
}
else
{
procComm = xmlComments.ToComments("--- ", true, false);
}
// Get procedure text without comments;
string procText = JocysCom.Sql.XmlDocumentation.XmlComments.RemoveSqlComments(line);
// Replace NOLOCK with READUNCOMMITED
if (arguments.ContainsKey("ru"))
{
System.Text.RegularExpressions.Regex ruRegex;
ruRegex = new System.Text.RegularExpressions.Regex("NOLOCK", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
procText = ruRegex.Replace(procText, "READUNCOMMITTED");
}
if (arguments.ContainsKey("fn"))
{
// Fix procedure name.
System.Text.RegularExpressions.Regex creProcRx;
creProcRx = new System.Text.RegularExpressions.Regex("CREATE\\s+(PROC|PROCEDURE)\\s+([^\\s\\(]+)", System.Text.RegularExpressions.RegexOptions.Multiline);
procText = creProcRx.Replace(procText, "CREATE PROCEDURE [dbo].[" + procedure.Name + "]");
}
if (arguments.ContainsKey("fv"))
{
System.Text.RegularExpressions.Regex nmRx;
string[] names = new string[]{
"sysname", "NVarChar(128)",
"BigInt","BigInt",
"UniqueIdentifier","UniqueIdentifier",
"NVarChar","NVarChar",
"NText","NText",
"SmallInt","SmallInt",
"DateTime", "DateTime",
"TimeStamp", "TimeStamp",
"([\\s])Int([\\s,])","$1Int$2",
"([\\s])Bit([\\s,])","$1Bit$2",
};
// Fix variables.
int nlen = names.Length / 2;
for (int n = 0; n < nlen; n++)
{
nmRx = new System.Text.RegularExpressions.Regex(names[n * 2], System.Text.RegularExpressions.RegexOptions.IgnoreCase);
procText = nmRx.Replace(procText, names[n * 2 + 1]);
}
}
if (procComm.Length > 0)
{
tw.WriteLine(procComm);
if (expDir) dirTw.WriteLine(procComm);
}
tw.WriteLine(procText);
if (expDir) dirTw.WriteLine(procText);
}
else
{
tw.WriteLine(line);
if (expDir) dirTw.WriteLine(line);
}
tw.WriteLine("GO");
if (expDir) dirTw.WriteLine("GO");
}
if (expDir)
{
dirTw.Flush();
dirTw.Close();
}
}
}
}
tw.Flush();
tw.Close();
return true;
}
/// <summary>
/// Generate SQL header with old template style.
/// </summary>
/// <param name="procedure">Stored procedure.</param>
/// <param name="xmlComments">SQL XML Comments object.</param>
/// <param name="arguments">CLI arguments.</param>
/// <returns>SQL header.</returns>
public static string OldTemplate(Microsoft.SqlServer.Management.Smo.StoredProcedure procedure, JocysCom.Sql.XmlDocumentation.XmlComments xmlComments, Arguments arguments)
{
string summary = xmlComments.Summary.InnerText.Replace("\r", "").Replace("\n", "");
string template = GetTextResource("HeaderTemplate.txt");
// If template fiel was specified then...
if (!String.IsNullOrEmpty(arguments["a"]))
{
// Use specified file as template.
template = System.IO.File.ReadAllText(arguments["a"]);
}
for (int i = (int)'a'; i <= (int)'z'; i++)
{
template = template.Replace("$a" + (char)i, arguments["a" + (char)i]);
}
template = template.Replace("$ProcedureName", procedure.Schema + "." + procedure.Name);
template = template.Replace("$ProcedureFile", procedure.Schema + "." + procedure.Name + ".PRC");
template = template.Replace("$HistoryRecord", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + " Modified");
template = template.Replace("$DocSummary", summary);
return template;
}
}
}
Submit an article or tip