Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
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;
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment