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;

namespace JocysCom.Sql.ScriptsGenerator.Controls
{
	public partial class GenerateActionScripts : UserControl
	{
		public GenerateActionScripts()
		{
			InitializeComponent();
			dbaConnectionString = DbaConnectionStringTextBox.Text;
			if (dbaConnectionString.Length > 0)
			{
				CreateServer();
			}
		}

		public string CreateOrAlter
		{
			get
			{
				return AlterCheckBox.Checked ? "ALTER" : "CREATE";
			}
		}


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

		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;
                CreateServer();
			}
		}

	#endregion

        //System.Data.SqlClient.SqlConnectionStringBuilder connectionBuilder;
        Microsoft.SqlServer.Management.Common.ServerConnection connection;
        Microsoft.SqlServer.Management.Smo.Server server;
		Microsoft.SqlServer.Management.Smo.Database database;
		Microsoft.SqlServer.Management.Smo.Table table;

        public void CreateServer()
		{
            connection = new Microsoft.SqlServer.Management.Common.ServerConnection();
            connection.ConnectionString = this.dbaConnectionString;
            server = new Microsoft.SqlServer.Management.Smo.Server(connection);
            FillDatabases();
		}

		public bool FillDatabases()
		{
			DatabasesListBox.Items.Clear();
            InfoLabel.Text = "";
            if (!string.IsNullOrEmpty(connection.DatabaseName))
            {
                DatabasesListBox.Items.Add(connection.DatabaseName);
            }
            else
            {
                int length = 0;
                try
                {
                    length = server.Databases.Count;
                }
                catch (Exception ex)
                {
                    InfoLabel.Text = DateTime.Now.ToString("HH:mm:ss") + " Database: " + ex.InnerException.Message;
                }
                for (int i = 0; i < length; i++)
                {
                    DatabasesListBox.Items.Add(server.Databases[i].Name);
                }
            }
            return true;
		}

		private void DatabasesListBox_SelectedIndexChanged(object sender, EventArgs e)
		{
			database = server.Databases[DatabasesListBox.SelectedIndex];
			FillTables();
		}

		public void FillTables()
		{
			TablesListBox.Items.Clear();
            InfoLabel.Text = "";
            int length = 0;
            try
            {
                length = database.Tables.Count;
            }
            catch (Exception ex)
            {
                InfoLabel.Text = DateTime.Now.ToString("HH:mm:ss") + " Database: " + ex.InnerException.Message;
            }
			for (int i = 0; i < length; i++)
			{
				if (!database.Tables[i].IsSystemObject)
				{
					TablesListBox.Items.Add(database.Tables[i].Name);
				}
			}
		}

		private void TablesListBox_SelectedIndexChanged(object sender, EventArgs e)
		{
			table = database.Tables[TablesListBox.Text];
			RegenereateProcedures();
		}

		private void DropCheckBox_CheckedChanged(object sender, EventArgs e)
		{
			RegenereateProcedures();
		}

		private void AlterCheckBox_CheckedChanged(object sender, EventArgs e)
		{
			RegenereateProcedures();
		}

		public void RegenereateProcedures()
		{
			if (table != null)
			{
				InsertTextBox.Text = GetInsertProcedure();
				UpdateTextBox.Text = GetUpdateProcedure();
				SelectTextBox.Text = GetSelectProcedure();
				SelectAllTextBox.Text = GetSelectAllProcedure();
				SelectPagedRecordsTextBox.Text = GetSelectPagedRecordsProcedure();
				DeleteTextBox.Text = GetDeleteProcedure();
				TableTextBox.Text = GetParametersTableWithType(table, ParamListType.All);
				RefreshColumnsTextBox();

				AllTextBox.Clear();
				AllTextBox.AppendText(InsertTextBox.Text);
				AllTextBox.AppendText(UpdateTextBox.Text);
				AllTextBox.AppendText(SelectTextBox.Text);
				AllTextBox.AppendText(SelectAllTextBox.Text);
				AllTextBox.AppendText(SelectPagedRecordsTextBox.Text);
				AllTextBox.AppendText(DeleteTextBox.Text);
			}
		}
		
		#region Generate Scripts

		public string GetInsertProcedure()
		{
			StringBuilder procedure = new StringBuilder();
			string procedureName = GetProcedureName("InsertRecord");
			if (DropCheckBox.Checked) procedure.Append(GetDropProcedure(procedureName));
			if (XmlCommentsCheckBox.Checked)
			{
				procedure.Append("--- <summary>\r\n");
				procedure.Append("--- Insert new row into [" + table.Name + "] table.\r\n");
				procedure.Append("--- </summary>\r\n");
				procedure.Append(GetParameterXmlComments(table, ParamListType.All));
				procedure.Append("--- <returns>New row.</returns>\r\n");
				procedure.Append("--- <remarks>\r\n");
				procedure.Append("--- History:\r\n");
				procedure.Append("--- \t" + DateTime.Now.ToString("yyyy-MM-dd") + " - Created\r\n");
				procedure.Append("--- </remarks>\r\n");
			}
			procedure.Append(this.CreateOrAlter + " PROCEDURE [dbo].[" + procedureName + "]\r\n");
			procedure.Append(GetParametersWithType(table, ParamListType.All));
			procedure.Append("AS\r\n");
			procedure.Append("\r\n");
			Microsoft.SqlServer.Management.Smo.Column primaryKey = GetPrimaryKey(table);
			Microsoft.SqlServer.Management.Smo.Column identity = GetIdentity(table);
			bool dtComment = false;
			// Allow set set DateTome to null.
			for (int c = 0; c < table.Columns.Count; c++)
			{
				if (table.Columns[c].DataType.SqlDataType == Microsoft.SqlServer.Management.Smo.SqlDataType.DateTime
					&& table.Columns[c].Nullable)
				{
					if (!dtComment)
					{
						procedure.Append("-- Set null value for DateTime from .NET code: .ColumnName = DateTime.MaxValue.\r\n");
						dtComment = true;
					}
					procedure.Append("IF @" + table.Columns[c].Name + " = '" + DateTime.MaxValue.ToString("yyyy-MM-dd HH:mm:ss.997") + "' SET @" + table.Columns[c].Name + " = NULL\r\n");
				}
			}
			procedure.Append("\r\n");
			if (primaryKey.DataType.SqlDataType == Microsoft.SqlServer.Management.Smo.SqlDataType.UniqueIdentifier
				&& !primaryKey.Identity)
			{
				procedure.Append("IF @" + primaryKey.Name + " IS NULL\r\n");
				procedure.Append("BEGIN\r\n");
				procedure.Append("\tSET @" + primaryKey.Name + " = newid()\r\n");
				procedure.Append("END\r\n");
				procedure.Append("\r\n");
			}
			procedure.Append("INSERT INTO [dbo].[" + table.Name + "] (\r\n");
			procedure.Append(GetColumnsList(table, ParamListType.ExcludeIdentity));
			procedure.Append(") VALUES (\r\n");
			procedure.Append(GetValuesList(table, ParamListType.ExcludeIdentity));
			procedure.Append(")\r\n");
			if (identity != null)
			{
				procedure.Append("SET @" + identity.Name + " = SCOPE_IDENTITY()\r\n");
			}
			procedure.Append("\r\n");
			procedure.Append("-- Return new record.\r\n");
			if (EnumerateColumnsCheckBox.Checked)
			{
				procedure.Append("SELECT");
				procedure.Append(GetColumnsList(table, ParamListType.All));
			}
			else
			{
				procedure.Append("SELECT *\r\n");
			}
			procedure.Append("FROM [dbo].[" + table.Name + "] WITH (NOLOCK)\r\n");
			procedure.Append("WHERE [" + primaryKey.Name + "] = @" + primaryKey.Name + "\r\n");
			procedure.Append("GO\r\n");
			return procedure.ToString();
		}

		public string GetSelectAllProcedure()
		{
			StringBuilder procedure = new StringBuilder();
			string procedureName = GetProcedureName("SelectRecords");
			if (DropCheckBox.Checked) procedure.Append(GetDropProcedure(procedureName));
			if (XmlCommentsCheckBox.Checked)
			{
				procedure.Append("--- <summary>\r\n");
				procedure.Append("--- Select all rows from [" + table.Name + "] table.\r\n");
				procedure.Append("--- </summary>\r\n");
				procedure.Append(GetParameterXmlComments(table, ParamListType.All));
				procedure.Append("--- <returns>All rows from [" + table.Name + "] table.</returns>\r\n");
				procedure.Append("--- <remarks>\r\n");
				procedure.Append("--- History:\r\n");
				procedure.Append("--- \t" + DateTime.Now.ToString("yyyy-MM-dd") + " - Created\r\n");
				procedure.Append("--- </remarks>\r\n");
			}
			procedure.Append(this.CreateOrAlter + " PROCEDURE [dbo].[" + procedureName + "]\r\n");
			procedure.Append("AS\r\n");
			procedure.Append("\r\n");
			if (EnumerateColumnsCheckBox.Checked)
			{
				procedure.Append("SELECT");
				procedure.Append(GetColumnsList(table, ParamListType.All));
			}
			else
			{
				procedure.Append("SELECT *\r\n");
			}
			procedure.Append("FROM [dbo].[" + table.Name + "] WITH (NOLOCK)\r\n");
			procedure.Append("GO\r\n");
			return procedure.ToString();
		}


		public string GetSelectPagedRecordsProcedure()
		{
			StringBuilder procedure = new StringBuilder();
			Microsoft.SqlServer.Management.Smo.Column primaryKey = GetPrimaryKey(table);
			Microsoft.SqlServer.Management.Smo.Column identity = GetIdentity(table);
			if (primaryKey != null)
			{

				string procedureName = GetProcedureName("SelectPagedRecords");
				if (DropCheckBox.Checked) procedure.Append(GetDropProcedure(procedureName));
				if (XmlCommentsCheckBox.Checked)
				{
					procedure.Append("--- <summary>\r\n");
					procedure.Append("--- Select part of records from [" + table.Name + "] table.\r\n");
					procedure.Append("--- </summary>\r\n");
					procedure.Append(GetParameterXmlComments(table, ParamListType.PrimaryKey));
					if (table.Columns.Contains("RecordEnabled"))
					{
						procedure.Append("--- <param name=\"RecordEnabled\" ref=\"dbo." + table.Name + ".RecordEnabled\" />\r\n");
					}
					procedure.Append("--- <param name=\"Order\">Order records before paging. Values: ASC, DESC.</param>\r\n");
					procedure.Append("--- <param name=\"maximumRows\">Number of rows per page.</param>\r\n");
					procedure.Append("--- <param name=\"startRowIndex\">Index of first row in page.</param>\r\n");
					procedure.Append("--- <param name=\"totalRowsCount\">Total number of records available.</param>\r\n"); 
					procedure.Append("--- <returns>Page rows.</returns>\r\n");
					procedure.Append("--- <remarks>\r\n");
					procedure.Append("--- History:\r\n");
					procedure.Append("--- \t" + DateTime.Now.ToString("yyyy-MM-dd") + " - Created\r\n");
					procedure.Append("--- </remarks>\r\n");
				}
				procedure.Append(this.CreateOrAlter + " PROCEDURE [dbo].[" + procedureName + "]\r\n");
				procedure.Append("\t@" + primaryKey.Name + " " + primaryKey.DataType.SqlDataType.ToString() + ",\r\n");
				if (table.Columns.Contains("RecordEnabled")){
					procedure.Append("\t@RecordEnabled Bit,\r\n");
				}
				procedure.Append("\t@Order Sysname,\r\n");
				procedure.Append("\t@maximumRows Int,\r\n");
				procedure.Append("\t@startRowIndex Int,\r\n");
				procedure.Append("\t@totalRowsCount Int out\r\n");
				procedure.Append("AS\r\n");
				procedure.Append("\r\n");
				procedure.Append("-- Set the page bounds.\r\n");
				procedure.Append("DECLARE @pageLowerBound Int\r\n");
				procedure.Append("DECLARE @pageUpperBound Int\r\n");
				procedure.Append("SET @pageLowerBound = @startRowIndex\r\n");
				procedure.Append("SET @pageUpperBound = @startRowIndex + @maximumRows\r\n");
				procedure.Append("\r\n");
				procedure.Append("-- Declare empty parameter.\r\n");
				if (primaryKey.DataType.SqlDataType == Microsoft.SqlServer.Management.Smo.SqlDataType.UniqueIdentifier)
				{
					procedure.Append("DECLARE @empty uniqueidentifier\r\n");
					procedure.Append("SET @empty = '00000000-0000-0000-0000-000000000000'\r\n");
				}
				else
				{
					procedure.Append("DECLARE @empty " + primaryKey.DataType.SqlDataType.ToString() + "\r\n");
					procedure.Append("SET @empty = 0\r\n");
				}
				procedure.Append("\r\n");
				procedure.Append("-- Create a temp table to store the select results.\r\n");
				procedure.Append("DECLARE	@pageIndexTable TABLE (\r\n");
				procedure.Append("\tIndexId int IDENTITY (1, 1) NOT NULL,\r\n");
				procedure.Append("\t" + primaryKey.Name + " " + primaryKey.DataType.SqlDataType.ToString() + "\r\n");
				procedure.Append(")\r\n");
				procedure.Append("\r\n");
				procedure.Append("-- Select records into page index table.\r\n");
				procedure.Append("IF (@" + primaryKey.Name + " = @empty) OR (@Order = 'ASC')\r\n");
				procedure.Append("BEGIN\r\n");
				procedure.Append("\tINSERT\r\n");
				procedure.Append("\tINTO	@pageIndexTable ([" + primaryKey.Name + "])\r\n");
				procedure.Append("\tSELECT	[" + primaryKey.Name + "]\r\n");
				procedure.Append("\tFROM	[dbo].[" + table.Name + "] WITH (NOLOCK)\r\n");
				procedure.Append("\tORDER BY [" + primaryKey.Name + "] ASC\r\n");
				procedure.Append("END\r\n");
				procedure.Append("ELSE IF (@" + primaryKey.Name + " = @empty) OR (@Order = 'DESC')\r\n");
				procedure.Append("BEGIN\r\n");
				procedure.Append("\tINSERT\r\n");
				procedure.Append("\tINTO	@pageIndexTable ([" + primaryKey.Name + "])\r\n");
				procedure.Append("\tSELECT	[" + primaryKey.Name + "]\r\n");
				procedure.Append("\tFROM	[dbo].[" + table.Name + "] WITH (NOLOCK)\r\n");
				procedure.Append("\tORDER BY [" + primaryKey.Name + "] DESC\r\n");
				procedure.Append("END\r\n");
				procedure.Append("ELSE IF (@" + primaryKey.Name + " <> @empty)\r\n");
				procedure.Append("BEGIN\r\n");
				procedure.Append("\tINSERT\r\n");
				procedure.Append("\tINTO	@pageIndexTable ([" + primaryKey.Name + "])\r\n");
				procedure.Append("\tSELECT	[" + primaryKey.Name + "]\r\n");
				procedure.Append("\tFROM	[dbo].[" + table.Name + "] WITH (NOLOCK)\r\n");
				procedure.Append("\tWHERE	[" + primaryKey.Name + "] = @" + primaryKey.Name + "\r\n");
				procedure.Append("END\r\n");
				procedure.Append("ELSE\r\n");
				procedure.Append("BEGIN\r\n");
				procedure.Append("\tINSERT\r\n");
				procedure.Append("\tINTO	@pageIndexTable ([" + primaryKey.Name + "])\r\n");
				procedure.Append("\tSELECT	[" + primaryKey.Name + "]\r\n");
				procedure.Append("\tFROM	[dbo].[" + table.Name + "] WITH (NOLOCK)\r\n");
				procedure.Append("END\r\n");
				procedure.Append("\r\n");
				procedure.Append("-- Count number of selected records.\r\n");
				procedure.Append("SELECT @totalRowsCount = @@ROWCOUNT\r\n");
				procedure.Append("\r\n");
				procedure.Append("-- Select page.\r\n");
				procedure.Append("SELECT	[dbo].[" + table.Name + "].*\r\n");
				procedure.Append("FROM	[dbo].[" + table.Name + "] (NOLOCK), @pageIndexTable p\r\n");
				procedure.Append("WHERE	[dbo].[" + table.Name + "].[" + primaryKey.Name + "] = p.[" + primaryKey.Name + "]\r\n");
				procedure.Append("\tAND p.IndexId > @pageLowerBound\r\n");
				procedure.Append("\tAND p.IndexId <= @pageUpperBound\r\n");
				procedure.Append("\r\n");
				procedure.Append("-- Return number of records.\r\n");
				procedure.Append("RETURN @totalRowsCount\r\n");
				procedure.Append("GO\r\n");
			}
			return procedure.ToString();
		}

		public string GetSelectProcedure()
		{
			StringBuilder procedure = new StringBuilder();
			string procedureName = GetProcedureName("SelectRecord");
			if (DropCheckBox.Checked) procedure.Append(GetDropProcedure(procedureName));
			if (XmlCommentsCheckBox.Checked)
			{
				procedure.Append("--- <summary>\r\n");
				procedure.Append("--- Select row from [" + table.Name + "] table by primary key value.\r\n");
				procedure.Append("--- </summary>\r\n");
				procedure.Append(GetParameterXmlComments(table, ParamListType.PrimaryKey));
				procedure.Append("--- <returns>Row with specified primary key value.</returns>\r\n");
				procedure.Append("--- <remarks>\r\n");
				procedure.Append("--- History:\r\n");
				procedure.Append("--- \t" + DateTime.Now.ToString("yyyy-MM-dd") + " - Created\r\n");
				procedure.Append("--- </remarks>\r\n");
			}
			procedure.Append(this.CreateOrAlter + " PROCEDURE [dbo].[" + procedureName + "]\r\n");
			procedure.Append(GetParametersWithType(table, ParamListType.PrimaryKey));
			procedure.Append("AS\r\n");
			procedure.Append("\r\n");
			if (EnumerateColumnsCheckBox.Checked)
			{
				procedure.Append("SELECT");
				procedure.Append(GetColumnsList(table, ParamListType.All));
			}
			else
			{
				procedure.Append("SELECT *\r\n");
			}
			procedure.Append("FROM [dbo].[" + table.Name + "] WITH (NOLOCK)\r\n");
			procedure.Append("WHERE\r\n");
			procedure.Append(GetParametersToSet(table, ParamListType.PrimaryKey));
			procedure.Append("GO\r\n");
			return procedure.ToString();
		}

		/// <summary>
		/// Generate delete script.
		/// </summary>
		/// <returns>SQL script.</returns>
		public string GetDeleteProcedure()
		{
			StringBuilder procedure = new StringBuilder();
			string procedureName = GetProcedureName("DeleteRecord");
			if (DropCheckBox.Checked) procedure.Append(GetDropProcedure(procedureName));
			if (XmlCommentsCheckBox.Checked)
			{
				procedure.Append("--- <summary>\r\n");
				procedure.Append("--- Delete row from [" + table.Name + "] table by primary key value.\r\n");
				procedure.Append("--- </summary>\r\n");
				procedure.Append(GetParameterXmlComments(table, ParamListType.PrimaryKey));
				procedure.Append("--- <returns>0</returns>\r\n");
				procedure.Append("--- <remarks>\r\n");
				procedure.Append("--- History:\r\n");
				procedure.Append("--- \t" + DateTime.Now.ToString("yyyy-MM-dd") + " - Created\r\n");
				procedure.Append("--- </remarks>\r\n");
			}
			procedure.Append(this.CreateOrAlter + " PROCEDURE [dbo].[" + procedureName + "]\r\n");
			procedure.Append(GetParametersWithType(table, ParamListType.PrimaryKey));
			procedure.Append("AS\r\n");
			procedure.Append("\r\n");
			procedure.Append("DELETE [dbo].[" + table.Name + "]\r\n");
			procedure.Append("WHERE\r\n");
			procedure.Append(GetParametersToSet(table, ParamListType.PrimaryKey));
			procedure.Append("\r\n");
			procedure.Append("RETURN 0\r\n");
			procedure.Append("GO\r\n");
			return procedure.ToString();
		}

		public string GetUpdateProcedure()
		{
			StringBuilder procedure = new StringBuilder();
			string procedureName = GetProcedureName("UpdateRecord");
			if (DropCheckBox.Checked) procedure.Append(GetDropProcedure(procedureName));
			if (XmlCommentsCheckBox.Checked)
			{
				procedure.Append("--- <summary>\r\n");
				procedure.Append("--- Update row of [" + table.Name + "] table by primary key value.\r\n");
				procedure.Append("--- </summary>\r\n");
				procedure.Append(GetParameterXmlComments(table, ParamListType.All));
				procedure.Append("--- <returns>0</returns>\r\n");
				procedure.Append("--- <remarks>\r\n");
				procedure.Append("--- History:\r\n");
				procedure.Append("--- \t" + DateTime.Now.ToString("yyyy-MM-dd") + " - Created\r\n");
				procedure.Append("--- </remarks>\r\n");
			}
			procedure.Append(this.CreateOrAlter + " PROCEDURE [dbo].[" + procedureName + "]\r\n");
			procedure.Append(GetParametersWithType(table, ParamListType.All));
			procedure.Append("AS\r\n");
			procedure.Append("\r\n");
			bool dtComment = false;
			// Allow set set DateTome to null.
			for (int c = 0; c < table.Columns.Count; c++)
			{
				if (table.Columns[c].DataType.SqlDataType == Microsoft.SqlServer.Management.Smo.SqlDataType.DateTime
					&& table.Columns[c].Nullable)
				{
					if (!dtComment)
					{
						procedure.Append("-- Set null value for DateTime from .NET code: .ColumnName = DateTime.MaxValue.\r\n");
						dtComment = true;
					}
					procedure.Append("IF @" + table.Columns[c].Name + " = '" + DateTime.MaxValue.ToString("yyyy-MM-dd HH:mm:ss.997") + "' SET @" + table.Columns[c].Name + " = NULL\r\n");
				}
			}
			procedure.Append("\r\n");
			procedure.Append("UPDATE [dbo].[" + table.Name + "] SET\r\n");
			procedure.Append(GetParametersToSet(table, ParamListType.ExcludePrimaryKey));
			procedure.Append("WHERE\r\n");
			procedure.Append(GetParametersToSet(table, ParamListType.PrimaryKey));
			procedure.Append("\r\n");
			procedure.Append("RETURN 0\r\n");
			procedure.Append("GO\r\n");
			return procedure.ToString();
		}

		#endregion

		#region Helper

		#endregion

		#region Get Procedure Part

		public string GetColumnsList(Microsoft.SqlServer.Management.Smo.Table table, ParamListType listType)
		{
			return GetColumnsList(table, listType, "\t");
		}


		public string GetColumnsList(Microsoft.SqlServer.Management.Smo.Table table, ParamListType listType, string prefix)
		{
			List<Microsoft.SqlServer.Management.Smo.Column> columns;
			columns = GetColumns(table, listType);
			StringBuilder text = new StringBuilder();
			int length = columns.Count;
			for (int i = 0; i < length; i++)
			{
				text.Append(prefix);
				text.Append("[" + columns[i].Name + "]");
				if (i < length - 1) text.Append(",");
				text.Append("\r\n");
			}
			return text.ToString();
		}

		public string GetParameterXmlComments(Microsoft.SqlServer.Management.Smo.Table table, ParamListType listType)
		{
			List<Microsoft.SqlServer.Management.Smo.Column> columns;
			columns = GetColumns(table, listType);
			StringBuilder text = new StringBuilder();
			int length = columns.Count;
			for (int i = 0; i < length; i++)
			{
				text.Append("--- <param name=\"" + columns[i].Name + "\" ref=\"" + table.Schema + "." + table.Name + "." + columns[i].Name + "\" />\r\n");
			}
			return text.ToString();
		}


		public string GetValuesList(Microsoft.SqlServer.Management.Smo.Table table, ParamListType listType)
		{
			List<Microsoft.SqlServer.Management.Smo.Column> columns;
			columns = GetColumns(table, listType);
			StringBuilder text = new StringBuilder();
			int length = columns.Count;
			for (int i = 0; i < length; i++)
			{
				text.Append("\t");
				text.Append("@" + columns[i].Name);
				if (i < length - 1) text.Append(",");
				text.Append("\r\n");
			}
			return text.ToString();
		}

		public string GetParametersToSet(Microsoft.SqlServer.Management.Smo.Table table, ParamListType listType)
		{
			List<Microsoft.SqlServer.Management.Smo.Column> columns;
			columns = GetColumns(table, listType);
			StringBuilder text = new StringBuilder();
			int length = columns.Count;
			for (int i = 0; i < length; i++)
			{
				text.Append("\t");
				text.Append(GetParameterToSet(columns[i]));
				if (i < length - 1) text.Append(",");
				text.Append("\r\n");
			}
			return text.ToString();
		}

		public string GetParameterToSet(Microsoft.SqlServer.Management.Smo.Column column)
		{
			StringBuilder text = new StringBuilder();
			text.Append("[" + column.Name + "]");
			text.Append(" = @" + column.Name);
			return text.ToString();
		}

		public string GetParametersWithType(Microsoft.SqlServer.Management.Smo.Table table, ParamListType listType)
		{
			return GetParametersWithType(table, listType, false);
		}

		public string GetParametersWithType(Microsoft.SqlServer.Management.Smo.Table table, ParamListType listType, bool appendAllComas)
		{
			List<Microsoft.SqlServer.Management.Smo.Column> columns;
			columns = GetColumns(table, listType);
			StringBuilder text = new StringBuilder();
			int length = columns.Count;
			for (int i = 0; i < length; i++)
			{
				text.Append("\t");
				text.Append(SqlObjectsToString.GetParameterWithType(columns[i]));
				if (i < length - 1 || appendAllComas) text.Append(",");
				text.Append("\r\n");
			}
			return text.ToString();
		}

		

		public string GetParametersTableWithType(Microsoft.SqlServer.Management.Smo.Table table, ParamListType listType)
		{
			List<Microsoft.SqlServer.Management.Smo.Column> columns;
			columns = GetColumns(table, listType);
			StringBuilder text = new StringBuilder();
			int length = columns.Count;
			text.Append("<table class=\"ColumnListTable\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\">\r\n");
			for (int i = 0; i < length; i++)
			{
				text.Append("\t");
				text.Append(GetParameterRowWithType(columns[i]));
				text.Append("\r\n");
			}
			text.Append("</table>\r\n");
			return text.ToString();
		}


		public string GetParameterRowWithType(Microsoft.SqlServer.Management.Smo.Column column)
		{
			StringBuilder text = new StringBuilder();
			text.Append("<tr><td class=\"ColumnName\">" + column.Name + "</td>");
			text.Append("<td class=\"ColumnType\">" + column.DataType.SqlDataType.ToString());
			if (SqlObjectsToString.HaveSize(column.DataType))
			{
				text.Append("<span class=\"ColumnLength\">(" + column.DataType.MaximumLength.ToString() + ")</span>");
			}
			text.Append("</td><td class=\"ColumnDescription\"></td></tr>");
			return text.ToString();
		}

		public string GetProcedureName(string action)
		{
			return table.Name + "_" + action;
		}

		public string GetDropProcedure(string procedureName)
		{
			StringBuilder text = new StringBuilder();
			text.Append("IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[" + procedureName + "]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)\r\n");
			text.Append("DROP PROCEDURE [dbo].[" + procedureName + "]\r\n");
			text.Append("GO\r\n");
			return text.ToString();
		}

		#endregion

		#region Filter Columns

		public enum ParamListType
		{
			None = 0,
			All = 1,
			ExcludePrimaryKey = 2,
			PrimaryKey = 4,
			Identity = 8,
			ExcludeIdentity = 16
		}


		public List<Microsoft.SqlServer.Management.Smo.Column> GetColumns(Microsoft.SqlServer.Management.Smo.Table table, ParamListType listType)
		{

			List<Microsoft.SqlServer.Management.Smo.Column> columns;
			columns = new List<Microsoft.SqlServer.Management.Smo.Column>();
			Microsoft.SqlServer.Management.Smo.Column column;
			int length = table.Columns.Count;
			for (int i = 0; i < length; i++)
			{
				column = table.Columns[i];
				switch (listType)
				{
					case ParamListType.None:
						break;
					case ParamListType.All:
						columns.Add(column);
						break;
					case ParamListType.ExcludePrimaryKey:
						if (!column.InPrimaryKey) columns.Add(column);
						break;
					case ParamListType.Identity:
						if (column.Identity) columns.Add(column);
						break;
					case ParamListType.ExcludeIdentity:
						if (!column.Identity) columns.Add(column);
						break;
					case ParamListType.PrimaryKey:
						if (column.InPrimaryKey) columns.Add(column);
						break;
					default:
						break;
				}
			}
			return columns;
		}

		/// <summary>
		/// Get primary key column from table.
		/// </summary>
		/// <param name="table">Table to search for primary key.</param>
		/// <returns>Primary key column.</returns>
		public Microsoft.SqlServer.Management.Smo.Column GetPrimaryKey(Microsoft.SqlServer.Management.Smo.Table table)
		{
			Microsoft.SqlServer.Management.Smo.Column column = null;
			List<Microsoft.SqlServer.Management.Smo.Column> columns;
			columns = GetColumns(table, ParamListType.PrimaryKey);
			if (columns.Count > 0) column = columns[0];
			return column;
		}

		/// <summary>
		/// Get identity column from table.
		/// </summary>
		/// <param name="table">Table to search for identity column.</param>
		/// <returns>Identity column.</returns>
		public Microsoft.SqlServer.Management.Smo.Column GetIdentity(Microsoft.SqlServer.Management.Smo.Table table)
		{
			Microsoft.SqlServer.Management.Smo.Column column = null;
			List<Microsoft.SqlServer.Management.Smo.Column> columns;
			columns = GetColumns(table, ParamListType.Identity);
			if (columns.Count > 0) column = columns[0];
			return column;
		}

		#endregion

		private void CopyButton_Click(object sender, EventArgs e)
		{
			TextBox current = (TextBox)Controls.Find(ProceduresTabControl.SelectedTab.Text + "TextBox", true)[0];
			current.SelectAll();
			current.Copy();
		}

		private void GenerateActionScripts_Load(object sender, EventArgs e)
		{

		}

		private void label1_Click(object sender, EventArgs e)
		{

		}

		private void AlterCheckBox_CheckedChanged_1(object sender, EventArgs e)
		{

		}

		private void DropCheckBox_CheckedChanged_1(object sender, EventArgs e)
		{

		}

		private void ProceduresTabControl_SelectedIndexChanged(object sender, EventArgs e)
		{

		}

		private void label2_Click(object sender, EventArgs e)
		{

		}

		private void DbaConnectionStringTextBox_TextChanged(object sender, EventArgs e)
		{

		}

		private void label5_Click(object sender, EventArgs e)
		{

		}

		

	}
}