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

SQL XML Documentation

, 29 Feb 2008 CPOL
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.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Smo;

namespace JocysCom.Sql.ScriptsGenerator.Controls
{
	public partial class GenerateDataBaseScripts : UserControl
	{
		public GenerateDataBaseScripts()
		{
			InitializeComponent();
		}

		#region Database Connection Strings

		private string filterConnectionString(string text)
		{
			System.Text.RegularExpressions.Regex regex;
			regex = new System.Text.RegularExpressions.Regex("(Password|PWD)\\s*=([^;]*)([;]*)", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
			return regex.Replace(text, "$1=<hidden>$3");
		}

		// <summary>
		/// Database Administrative Connection String.
		/// </summary>
		private string dbaConnectionString
		{
			get { return m_dbaConnectionString; }
			set
			{
				this.m_dbaConnectionString = value;
				DbaConnectionStringTextBox.Text = filterConnectionString(value);
			}
		}
		private string m_dbaConnectionString;

		#endregion

		#region Log Functions

		// We will use lock to avoid crazy show insite textbox when
		// different threads will try to update it at same time.
		private System.Threading.ReaderWriterLock LogLock;
		private TextBox LogTarget;

		public void AddLog(string text)
		{
			AddLog(text, true);
		}

		public void AddLog(string text, bool addReturn)
		{
			LogLock.AcquireWriterLock(120000);
			this.LogTarget.AppendText(text);
			if (addReturn) this.LogTarget.AppendText("\r\n");
			LogLock.ReleaseWriterLock();
		}

		private void InitLog()
		{
			LogLock = new System.Threading.ReaderWriterLock();
			this.LogTarget = LogTextBox;
		}

		#endregion

		#region Events

		public event EventHandler<LogEventArgs> Progress;

		protected virtual void OnProgress(string message)
		{
			if (this.Progress != null)
			{
				LogEventArgs e = new LogEventArgs(message);
				this.Progress(this, e);
			}
		}

		#endregion

		private void DbaConnectionButton_Click(object sender, EventArgs e)
		{
			Microsoft.Data.ConnectionUI.DataConnectionDialog dcd;
			dcd = new Microsoft.Data.ConnectionUI.DataConnectionDialog();
			//Adds all the standard supported databases
			//DataSource.AddStandardDataSources(dcd);
			//allows you to add datasources, if you want to specify which will be supported 
			dcd.DataSources.Add(Microsoft.Data.ConnectionUI.DataSource.SqlDataSource);
			dcd.SetSelectedDataProvider(Microsoft.Data.ConnectionUI.DataSource.SqlDataSource, Microsoft.Data.ConnectionUI.DataProvider.SqlDataProvider);
			dcd.ConnectionString = this.dbaConnectionString;
			Microsoft.Data.ConnectionUI.DataConnectionDialog.Show(dcd);
			if (dcd.DialogResult == DialogResult.OK)
			{
				this.dbaConnectionString = dcd.ConnectionString;
			}
		}

		private void GenerateSqlScripts_Load(object sender, EventArgs e)
		{
			InitLog();
			this.m_dbaConnectionString = DbaConnectionStringTextBox.Text;
			FileNameTextBox.Text = new System.IO.FileInfo(Application.ExecutablePath).Directory.FullName + "\\Resources\\SqlScript.sql";
		}

		private void CreateSqlProceduresScript(bool scriptDrops)
		{
			System.Data.SqlClient.SqlConnectionStringBuilder scb = new System.Data.SqlClient.SqlConnectionStringBuilder(this.dbaConnectionString);
			Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(scb.DataSource);
			Microsoft.SqlServer.Management.Smo.Database myDB = server.Databases["Northwind"];
			//System.Data.DataTable dt = myDB.EnumObjects(Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes.StoredProcedure);
			Microsoft.SqlServer.Management.Smo.ScriptingOptions options = new Microsoft.SqlServer.Management.Smo.ScriptingOptions();
			options.Permissions = true;
			options.IncludeIfNotExists = scriptDrops;
			options.ScriptDrops = scriptDrops;
			options.AppendToFile = true;
			options.FileName = "C:\\temp\\procedures.sql";
			options.Permissions = true;
			Microsoft.SqlServer.Management.Smo.StoredProcedureCollection procedures = myDB.StoredProcedures;
			int length = procedures.Count;
			Microsoft.SqlServer.Management.Smo.StoredProcedure sp = null;
			for (int i = 0; i < length; i++)
			{
				sp = procedures[i];
				if (!sp.IsSystemObject && !sp.IsEncrypted)
				{
					OnProgress(sp.Name);
					System.Collections.Specialized.StringCollection sc = sp.Script(options);
				}
			}
		}

		bool IndexContainsAllPrimary(Table table, Index index)
		{
			// Count all primary keys of table.
			int tpk = 0;
			for (int i = 0; i < table.Columns.Count; i++)
			{
				if (table.Columns[i].InPrimaryKey) tpk += 1;
			}
			// Count primary keys inside index.
			int ipk = 0;
			for (int i = 0; i < index.IndexedColumns.Count; i++)
			{
				if (table.Columns[index.IndexedColumns[i].Name].InPrimaryKey) ipk += 1;
			}
			return (tpk == ipk && tpk > 0);
		}

		

		public string GetIndexName(Index index)
		{
			string name = index.Name;
			if (index.Parent.GetType() == typeof(Table))
			{
				Table table = (Table)index.Parent;
				string autoName = "IX";
				bool allPrimary = IndexContainsAllPrimary(table, index);
				if (index.IsUnique) autoName = "UQ";
				if (allPrimary) autoName = "PK";
				autoName += "_" + table.Name;
				if (!allPrimary)
				{
					for (int i = 0; i < index.IndexedColumns.Count; i++)
					{
						autoName += "_" + index.IndexedColumns[i].Name;
					}
				}
				name = autoName;
			}
			return name;
		}

		

		private void GenerateScripts()
		{
			JocysCom.ClassLibrary.Data.SqlScripter ss = new JocysCom.ClassLibrary.Data.SqlScripter();
			JocysCom.ClassLibrary.Data.SqlScripterArgument wa = new JocysCom.ClassLibrary.Data.SqlScripterArgument();
			System.Data.SqlClient.SqlConnectionStringBuilder scb = new System.Data.SqlClient.SqlConnectionStringBuilder(this.dbaConnectionString);
			wa.Server = scb.DataSource;
			wa.Database = "Northwind";
			wa.GenerateDrop = true;
			wa.GenerateCreate = true;
			wa.IncludeForeignKeyConstraints = true;
			ss.ProgressChanged += new ProgressChangedEventHandler(ss_ProgressChanged);
			ss.Script(wa);
		}

		void ss_ProgressChanged(object sender, ProgressChangedEventArgs e)
		{
			JocysCom.ClassLibrary.Data.SqlScripterState state;
			state = (JocysCom.ClassLibrary.Data.SqlScripterState)e.UserState;
			AddLog(state.ObjectName + "["+state.ObjectAutoname+"]");
		}

		public void Script()
		{
			//=================================================
			// Archive procedures.
			//-------------------------------------------------
			//System.IO.FileInfo pFile = new System.IO.FileInfo("C:\\temp\\procedures.sql");
			//if (pFile.Exists) pFile.Delete();
			//OnProgress("Generate DROP scripts for procedures.");
			//CreateSqlProceduresScript(true, pFile.FullName);
			//OnProgress("Generate CREATE scripts for procedures.");
			//CreateSqlProceduresScript(false, pFile.FullName);
			//=================================================
			// Archive tables.
			//-------------------------------------------------
			System.IO.FileInfo tFile = new System.IO.FileInfo(FileNameTextBox.Text);
			if (tFile.Exists) tFile.Delete();
			GenerateScripts();
			//=================================================
			// Drop Scripts
			//-------------------------------------------------
			//OnProgress("Generate DROP scripts for defaults.");
			//AddHead(tFile.FullName, "Drop Defaults");
			//CreateSqlTablesScript(true, tFile.FullName, "DF");
			//-----------------
			//OnProgress("Generate DROP for Check Constraints.");
			//AddHead(tFile.FullName, "Drop Check Constraints");
			//CreateSqlTablesScript(true, tFile.FullName, "CK");
			//-----------------
			//OnProgress("Generate DROP for Indexes.");
			//AddHead(tFile.FullName, "Drop Indexes");
			//CreateSqlTablesScript(true, tFile.FullName, "IX,PK,UQ");
			//=================================================
			// Create Scripts
			//-------------------------------------------------
			//OnProgress("Generate CREATE for Indexes.");
			//AddHead(tFile.FullName, "Create Indexes");
			//CreateSqlTablesScript(false, tFile.FullName, "IX,PK,UQ");
			////-----------------
			//OnProgress("Generate CREATE for Check Constraints.");
			//AddHead(tFile.FullName, "Create Check Constraints");
			//CreateSqlTablesScript(false, tFile.FullName, "CK");
			////-----------------
			//OnProgress("Generate CREATE scripts for defaults.");
			//AddHead(tFile.FullName, "Create Defaults");
			//CreateSqlTablesScript(false, tFile.FullName, "DF");
		}


		private void ScriptButton_Click(object sender, EventArgs e)
		{
			Script();
		}

		private void SaveAsButton_Click(object sender, EventArgs e)
		{
			SaveSqlFileDialog.FileName = FileNameTextBox.Text;
			DialogResult results = SaveSqlFileDialog.ShowDialog();
			if (results == DialogResult.OK)
			{
				FileNameTextBox.Text = SaveSqlFileDialog.FileName;
			}

		}

	}
}

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
Web04 | 2.8.141022.2 | Last Updated 29 Feb 2008
Article Copyright 2007 by EJocys
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid