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
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
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)
		{

		}

		

	}
}

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)

About the Author

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

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