UnitTestsVerifyingDbChangesAffectingSystemX.cs

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

using NUnit.Framework;

namespace YourSystem.Tests
{
    [TestFixture]
    public class UnitTestsVerifyingDbChangesAffectingSystemX
    {
        [TestCase(123456, "Table1", "Column1", "Column3")]
        [TestCase(987654, "Table55", "Column1", "Column3", "Column5")]
        public void VerifyNoChangesAffectingSystemX(int expectedCheckSum, string tableName, params string[] columnNames)
        {
            var actualCheckSum = CalculateCheckSum(tableName, columnNames);
            var message = string.Format("Calculated checksum {1} for the table [{0}] is not equal to expected {2}. " + 
                "Please verify recent changes to the table [{0}] as this might affect SystemX.",
                tableName, actualCheckSum, expectedCheckSum);
            Assert.AreEqual(expectedCheckSum, actualCheckSum, message);
        }

        public int CalculateCheckSum(string tableName, string[] columnNames)
        {
            var connectionString = ConfigurationManager.ConnectionStrings["YourTestDatabase"].ConnectionString;
            var checkSum = 0;
            const string sql = @"
                -- This is a bit crazy but very quick and effective way of splitting a string into a temp table
                IF OBJECT_ID('tempdb..#ColumnNames') IS NOT NULL
                DROP TABLE #ColumnNames
                DECLARE @xml xml
                SET @xml = cast(('<X>'+replace(@ColumnNamesList, ',', '</X><X>')+'</X>') as xml)
                SELECT C.value('.', 'varchar(500)') as ColumnName
                INTO #ColumnNames
                FROM @xml.nodes('X') as X(C)

                -- Calculating checksum on column details for provided parameters
                SELECT CHECKSUM_AGG(BINARY_CHECKSUM(TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT,
                                                    IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
                                                    NUMERIC_PRECISION, NUMERIC_SCALE, DATETIME_PRECISION))
                    FROM INFORMATION_SCHEMA.COLUMNS WITH (NOLOCK)
                    WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = @TableName AND
                    COLUMN_NAME IN (SELECT ColumnName FROM #ColumnNames)";
            using (var conn = new SqlConnection(connectionString))
            {
                var cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@TableName", tableName);
                cmd.Parameters.AddWithValue("@ColumnNamesList", string.Join(",", columnNames));
                conn.Open();
                checkSum = (Int32)cmd.ExecuteScalar();
            }
            return checkSum;
        }
    }
}