MS SQL History Table Utilities

HistoryTableUtilities.cs

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;

namespace Utilities.Sql
{
    public class HistoryTableUtilities
    {
        /// <summary>
        /// Turns on history tables in a single transaction for a given table name
        /// </summary>
        /// <param name="configuration"></param>
        /// <param name="tableName"></param>

        public static void TurnOnHistoryTableTransaction(string connectionString, string tableName)
        {            
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // Start a local transaction.
                var sqlTransaction = connection.BeginTransaction();
                var command = connection.CreateCommand();
                command.Transaction = sqlTransaction;

                TurnOnHistoryTable(command, tableName);
                sqlTransaction.Commit();
            }
        }

        /// <summary>
        /// Turns off history tables in a single transaction for a given table name
        /// </summary>
        /// <param name="configuration"></param>
        /// <param name="tableName"></param>
        public static void TurnOffHistoryTableTransaction(string connectionString, string tableName)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // Start a local transaction.
                var sqlTransaction = connection.BeginTransaction();
                var command = connection.CreateCommand();
                command.Transaction = sqlTransaction;

                TurnOffHistoryTable(command, tableName);
                sqlTransaction.Commit();
            }
        }

        /// <summary>
        /// Given a command and table name turn on history table.  To be used inside an existing transaction
        /// </summary>
        /// <param name="command"></param>
        /// <param name="tableName"></param>
        public static void TurnOnHistoryTable(SqlCommand command, string tableName)
        {
            var sqlCommand = $@"ALTER TABLE {tableName} ADD 
 PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)

 ALTER TABLE dbo.{tableName} SET (SYSTEM_VERSIONING = ON (      
      HISTORY_TABLE = History.{tableName},
  DATA_CONSISTENCY_CHECK = ON
    ))
";
            command.CommandText = sqlCommand;
            command.ExecuteNonQuery();            
        }

        /// <summary>
        /// Given a command and table name turn off history table.  To be used inside an existing transaction
        /// </summary>
        /// <param name="command"></param>
        /// <param name="tableName"></param>
        public static void TurnOffHistoryTable(SqlCommand command, string tableName)
        {

            var sqlCommand = $@"ALTER TABLE dbo.{tableName}
SET(SYSTEM_VERSIONING = OFF)
ALTER TABLE dbo.{tableName}
DROP PERIOD FOR SYSTEM_TIME
";
            command.CommandText = sqlCommand;
            command.ExecuteNonQuery();
        }


        /// <summary>
        /// Update the start time of an entity inside a history table where history has already been turned off
        /// </summary>
        /// <param name="command"></param>
        /// <param name="tableName"></param>
        /// <param name="id"></param>
        /// <param name="startTime"></param>
        public static void UpdateStartTime(SqlCommand command, string tableName, Guid id, DateTime startTime)
        {
            var formatDateTime = "yyyy-MM-dd HH:mm:ss:fff";
            //update date time
            var sqlCommand = $@"update dbo.{tableName}
SET
SysStartTime=CAST(N'{startTime.ToString(formatDateTime)}' AS DateTime2 (7))
where dbo.{tableName}.Id = '{id}'
";
            command.CommandText = sqlCommand;
            command.ExecuteNonQuery();                   
        }
    }
}