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