Code Generate Alter Table Statement

Code Generation

I have developed a script for use in LINQPad, that will auto-generate the statement above from a few parameters. It is written using the Fluent Syntax so multiple changes can be generated at once with a single output.

Class : Fluent Alter Table

There are two methods defined in this fluent class.

Method : AddChangeItem()

This method takes three parameters in order to generate the Sql Alter Table Add Column Statement

• Schema Name
• Column Name
• Column Definition

Method : GetDefinition()

This method returns a string containing the Sql Statements for altering one or more tables and one or more columns in tables.

using System
using System.Collections.Generic
using System.Linq
using System.Text
using System.Windows.Forms

public class FluentAlterTable
{
	private List<TableChangeItem> _changes = new List<TableChangeItem>();
	private List<ForeignKeyItem> _foreignKeyItems = new List<FluentAlterTable.ForeignKeyItem>();

	public FluentAlterTable AddChangeItem (string ns, string table, string fieldName, string columnDefiniton)
	{
		if (string.IsNullOrWhiteSpace(ns))
			throw new ArgumentNullException(nameof(ns));
		if (string.IsNullOrWhiteSpace(table))
			throw new ArgumentNullException(nameof(table));
		if (string.IsNullOrWhiteSpace(columnDefiniton))
			throw new ArgumentNullException(nameof(columnDefiniton));

		if (columnDefiniton.Contains("BIT", StringComparison.Ordinal))
		{
			if (!columnDefiniton.Contains("NOT NULL", StringComparison.Ordinal))
				throw new InvalidOperationException("A Bit field must have NOT NULL");
			if (!columnDefiniton.Contains("DEFAULT", StringComparison.Ordinal))
				throw new InvalidOperationException("A Bit field must have A Default");
		}

		_changes.Add(new TableChangeItem
		{
			FullTableName = $"{ns}.{table}",
			ColumnName = fieldName,
			ColumnDetails = columnDefiniton
		});
		return this;
	}

	public string GetDefinition()
	{
		string GetTemplate(string tn, string cn, string cd)
			=> $"IF COL_LENGTH('{tn}', '{cn}') IS NULL\nBEGIN\n\tALTER TABLE {tn}\n\tADD {cn} {cd}\nEND\n";

		var sb = new StringBuilder();
		sb.AppendLine(GetUseStatements());
		sb.AppendLine("GO");
		sb.AppendLine();
		sb.AppendLine("BEGIN TRANSACTION");

		foreach (var ci in _changes)
			sb.AppendLine(GetTemplate(ci.FullTableName, ci.ColumnName, ci.ColumnDetails));

		sb.AppendLine(@"--ROLLBACK TRANSACTION");
		sb.AppendLine("COMMIT TRANSACTION");

		return sb.ToString();
	}

	private string GetUseStatements()
	{
		var currentMonth = DateTime.Now.Month;
		var currentYear = DateTime.Now.Year;
		var dbNames = new List<string> {"YourDatabaseName"};
		return string.Join("\n", dbNames.Select(c => $"-- USE [{c}]\t\t -- .{currentMonth}.{currentYear}."));
	}

	internal class TableChangeItem
	{
		public string FullTableName { get; set; }
		public string ColumnName { get; set; }
		public string ColumnDetails { get; set; }

	}
}

Usage

To demonstrate the output of the code above I have created a LINQPad script that uses the FluentAlterTable class. The example below creates two columns on two tables.

First the class is created, then using the fluent syntax the change items for each table are added and finally a call to GetDefinition() returns the Sql output which can be saved to a file for use on your database

You can add as many change items as you like before calling GetDefinition().

void Main()
{
	var fluentAlterTable = new FluentAlterTable()
		.AddChangeItem("dbo", "TableA","PriceOnDemand", "DECIMAL(18,2) NULL")
		.AddChangeItem("dbo", "TableB","IsEnabled", "BIT DEFAULT(0) NOT NULL")
    	 .GetDefinition();
}

Output

The output of the code above is shown below :

-- USE [YourDatabaseName]     -- .4.2020.
GO

BEGIN TRANSACTION
IF COL_LENGTH('dbo.TableA', 'PriceOnDemand') IS NULL
BEGIN
  ALTER TABLE dbo.TableA
  ADD PriceOnDemand DECIMAL(18,2) NULL
END

IF COL_LENGTH('dbo.TableB', 'IsEnabled') IS NULL
BEGIN
  ALTER TABLE dbo.TableB
  ADD IsEnabled BIT DEFAULT(0) NOT NULL
END

--ROLLBACK TRANSACTION
COMMIT TRANSACTION