Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » SQL » Downloads
 
Add your own
alternative version
Go to top

SQL XML Documentation

, 29 Feb 2008
How to create and compile SQL XML Documentation comments
JocysComSqlScriptsGenerator.zip
DatabaseToXml
bin
Debug
JocysCom.Sql.DatabaseToXml.exe
JocysCom.Sql.DatabaseToXml.vshost.exe
JocysCom.Sql.DatabaseToXml.vshost.exe.manifest
JocysCom.Sql.XmlDocumentation.dll
JocysCom.Sql.DatabaseToXml.csproj.user
Properties
Resources
WinApp
bin
Debug
Interop.ADODB.dll
Interop.CDO.dll
JocysCom.ClassLibrary.dll
JocysCom.Sql.ScriptsGenerator.exe
JocysCom.Sql.ScriptsGenerator.vshost.exe
JocysCom.Sql.ScriptsGenerator.vshost.exe.manifest
JocysCom.Sql.XmlDocumentation.dll
Microsoft.Data.ConnectionUI.Dialog.dll
Microsoft.Data.ConnectionUI.dll
Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.SqlEnum.dll
Controls
GenerateActionScripts
Jocys.com SQL Scripts Generator.csproj.user
Properties
Settings.settings
XmlDocumentation
bin
Debug
JocysCom.Sql.XmlDocumentation.dll
JocysCom.Sql.XmlDocumentation.csproj.user
Properties
SQL Scripts Generator.suo
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;
		}

	}
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

EJocys
Software Developer (Senior)
United Kingdom United Kingdom
No Biography provided

| Advertise | Privacy | Mobile
Web03 | 2.8.140916.1 | Last Updated 29 Feb 2008
Article Copyright 2007 by EJocys
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid