SQL XML Documentation

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;
			}

		}

	}
}