SQL XML Documentation

using System;
using System.IO;
using System.Xml;
using System.Text;
using System.Reflection;
using System.Diagnostics;
using System.Collections;
using System.Runtime.InteropServices;

namespace JocysCom.Sql.XmlDocumentation
{
	public partial class XmlComments
	{

		/// <summary>Initializes the XML comments for the specified procedure.</summary>
		/// <param name="mi">The procedure for which we want to retrieve the XML comments.</param>
		public XmlComments(Microsoft.SqlServer.Management.Smo.StoredProcedure procedure)
		{
			// Get the comments.  If we got any, parse out the important stuff.
			_comments = GetComments(procedure, false, true);
			InitComments();
		}

		/// <summary>Initializes the XML comments for the specified procedure.</summary>
		/// <param name="procedure">The procedure for which we want to retrieve the XML comments.</param>
		/// <param name="recreate">Recreate XML comments.</param>
		public XmlComments(Microsoft.SqlServer.Management.Smo.StoredProcedure procedure, bool recreate)
		{
			// Get the comments.  If we got any, parse out the important stuff.
			_comments = GetComments(procedure, recreate, true);
			InitComments();
		}

		/// <summary>Initializes the XML comments for the specified procedure.</summary>
		/// <param name="procedure">The procedure for which we want to retrieve the XML comments.</param>
		/// <param name="recreate">Recreate XML comments.</param>
		/// <param name="detailParam">Add parameter type and size to XML comment.</param>
		public XmlComments(Microsoft.SqlServer.Management.Smo.StoredProcedure procedure, bool recreate, bool detailParam)
		{
			// Get the comments.  If we got any, parse out the important stuff.
			_comments = GetComments(procedure, recreate, detailParam);
			InitComments();
		}

		public static XmlNode GetComments(Microsoft.SqlServer.Management.Smo.StoredProcedure procedure, bool recreate, bool detailParam)
		{
			string xml = ExtractSqlComments(procedure.Properties["Text"].Value.ToString());
			XmlDocument doc = new XmlDocument();
			string rootNode = string.Empty;
			//rootNode += "<?xml version=\"1.0\"?><doc><assembly><name>JocysCom.Sql.ScriptsGenerator</name></assembly>";
			//rootNode += "<members>";
			rootNode += "<member name=\"M:DataBase.Procedures." + procedure.Name + "(";
			for (int i = 0; i < procedure.Parameters.Count; i++)
			{
				if (i > 0) rootNode += ",";
				rootNode += procedure.Parameters[i].DataType.SqlDataType.ToString();
			}
			rootNode += ")\">" + xml + "</member>";
			//rootNode += "</members></doc>";
			//System.Windows.Forms.MessageBox.Show(rootNode);
			doc.LoadXml(rootNode);
			// Get the appropriate node from the document
			//string xpath = "//member[@name='M:" + procedure.Name + "']";
			string xpath = "member";
			// Recreate missing nodes
			if (recreate)
			{
				// Replace summary node.
				XmlNode summaryNew = doc.CreateNode(XmlNodeType.Element, "summary", "");
				XmlNode summaryOld = doc.SelectSingleNode("//" + XmlDocumentation.Tags.Summary);
				summaryNew.InnerText = "";
				if (summaryOld != null)
				{
					summaryNew.InnerText = summaryOld.InnerText;
					summaryOld.ParentNode.RemoveChild(summaryOld);
				}
				doc.DocumentElement.InsertAfter(summaryNew, null);

				// Remove old summary node. 

				XmlNode[] aNodes = new XmlNode[procedure.Parameters.Count];
				for (int i = 0; i < procedure.Parameters.Count; i++)
				{
					XmlNode paramNode = doc.CreateNode(XmlNodeType.Element, "param", "");
					string name = procedure.Parameters[i].Name;
					XmlAttribute att = doc.CreateAttribute("name");
					att.Value = name.Replace("@", "");
					paramNode.Attributes.Append(att);
					if (detailParam)
					{
						att = doc.CreateAttribute("type");
						att.Value = procedure.Parameters[i].DataType.SqlDataType.ToString();
						//Microsoft.SqlServer.Management.Smo.DataType.NVarChar
						paramNode.Attributes.Append(att);
						att = doc.CreateAttribute("size");
						att.Value = procedure.Parameters[i].DataType.MaximumLength.ToString();
						paramNode.Attributes.Append(att);
					}
					aNodes[i] = paramNode;
					XmlNode param = doc.SelectSingleNode("//" + XmlDocumentation.Tags.Param + "[@name='" + name + "']");
					if (param != null)
					{
						paramNode.InnerText = param.InnerText;
						// We can remove old node.
						param.ParentNode.RemoveChild(param);
					}
					else
					{
						paramNode.InnerText = string.Empty;
					}
				}

				// Append new nodes.
				for (int i = 0; i < aNodes.Length; i++)
				{
					doc.DocumentElement.InsertAfter(aNodes[aNodes.Length - 1 - i], summaryNew);
				}
			}
			XmlNode node = doc.SelectSingleNode(xpath);
			return node;
		}

		private static string commentsBlockRegex = "(?<Content>---.*?<\\w+[^>]*>.*?)\\r\\n\\s*[^- ]";
		private static string commentsLineRegex = "^\\s*---\\s*(?<Content>.*)\\s*$";

		/// <summary>
		/// Return SQL procedure witout XML comments.
		/// </summary>
		/// <param name="s">Stored procedure text.</param>
		/// <returns>Stored procedure without comments.</returns>
		public static string RemoveSqlComments(string s)
		{
			System.Text.RegularExpressions.Regex regex;
			System.Text.RegularExpressions.MatchCollection mc;
			// Extract comments block.
			regex = new System.Text.RegularExpressions.Regex(commentsBlockRegex, System.Text.RegularExpressions.RegexOptions.Singleline);
			mc = regex.Matches(s);
			if (mc.Count > 0)
			{
				System.Text.RegularExpressions.Group g;
				g = mc[0].Groups["Content"];
				// Combine procedure without XML comments.
				s = s.Substring(0, g.Index) + s.Substring(g.Index + g.Length);
			}
			return s.Trim(new char[] { '\r', '\n', '\t', ' ' });
		}

		/// <summary>
		/// Extract and return XML Comments from SQL stored procedure text.
		/// </summary>
		/// <param name="s">Stored procedure text.</param>
		/// <returns>XML comments for stored procedure.</returns>
		public static string ExtractSqlComments(string s)
		{
			System.Text.RegularExpressions.Regex regex;
			System.Text.RegularExpressions.MatchCollection mc;
			// Extract comments block.
			regex = new System.Text.RegularExpressions.Regex(commentsBlockRegex, System.Text.RegularExpressions.RegexOptions.Singleline);
			mc = regex.Matches(s);
			if (mc.Count > 0) s = mc[0].Groups["Content"].Value.Trim(new char[] { '\r', '\n', '\t', ' ' });
			// Remove comment prefix.
			regex = new System.Text.RegularExpressions.Regex(commentsLineRegex, System.Text.RegularExpressions.RegexOptions.Multiline);
			StringBuilder lines = new StringBuilder();
			string comments = string.Empty;
			mc = regex.Matches(s);
			// We need to remove line breaks and ends and recreate them.
			System.Text.RegularExpressions.Regex rn;
			rn = new System.Text.RegularExpressions.Regex("[\r\n]", System.Text.RegularExpressions.RegexOptions.Singleline);
			for (int i = 0; i < mc.Count; i++)
			{
				lines.AppendLine(rn.Replace(mc[i].Groups["Content"].Value, ""));
			}
			return lines.ToString();
		}

	}
}