DP44/enriched-qwen3-coder-next/DataPRO/UnitTest/DatabaseUnitTesting/Utilities.md

source_files, generated_at, model, schema_version, sha256

source_files	generated_at	model	schema_version	sha256
DataPRO/UnitTest/DatabaseUnitTesting/Utilities/ObjectsToCompare.cs DataPRO/UnitTest/DatabaseUnitTesting/Utilities/DatabaseAdapter.cs DataPRO/UnitTest/DatabaseUnitTesting/Utilities/DatabaseComparer.cs	2026-04-16T03:52:03.248634+00:00	Qwen/Qwen3-Coder-Next-FP8	1	b192c520f6919014

Utilities

Documentation: DatabaseUnitTesting.Utilities Module

---

1. Purpose

This module provides core infrastructure for comparing database schemas and data between two SQL Server databases as part of unit testing workflows. It enables programmatic definition of object pairs (e.g., tables or views) to compare, dynamic column exclusion, and execution of set-based data difference queries using SQL EXCEPT. The module is internal to the unit testing framework and is not intended for general-purpose database access; its primary consumers are test runners that validate expected data consistency across environments (e.g., baseline vs. modified database states).

---

2. Public Interface

Note

: All types and members are internal, not public. They are accessible only within the DatabaseUnitTesting assembly.

ObjectsToCompare class

Represents a pair of database objects (e.g., tables) to compare, with optional column exclusions.

• Constructor

  internal ObjectsToCompare(string schema1Name, string object1Name, string schema2Name, string object2Name)
  

  Initializes a comparison pair. All arguments are non-null strings identifying the schema and object name for each side of the comparison.

• Properties

  internal string Schema1Name { get; }
  internal string Object1Name { get; }
  internal string Schema2Name { get; }
  internal string Object2Name { get; }
  internal string Qualified1 { get; }   // Returns "schema1.object1"
  internal string Qualified2 { get; }   // Returns "schema2.object2"
  internal IEnumerable<string> ColumnsToIgnore { get; }
  

• Methods

  internal void AddColumnToIgnore(string columnName)
  

  Adds a column name (case-insensitive) to the list of columns to exclude from comparison.

---

DatabaseAdapter class

Encapsulates low-level SQL Server operations for snapshot management and database switching.

• Constructor

  internal DatabaseAdapter(SqlConnection connection)
  

  Wraps an existing SqlConnection. Automatically opens the connection if closed.

• Methods

  internal bool IsSnapshot(string name)
  

  Returns true if name refers to a database snapshot (checks sys.databases.source_database_id); false otherwise.

  internal void UseDatabase(string databaseName)
  

  Executes USE [databaseName] on the connection.

  internal void CreateSnapshot(string databaseName, string snapshotName)
  

  Creates a snapshot of databaseName at a fixed path: C:\Temp\{snapshotName}.
  ⚠️ Hardcoded path: Assumes C:\Temp\ exists and is writable.

  internal void DropSnapshot(string snapshotName)
  

  Drops the snapshot snapshotName. Throws ArgumentException if no such snapshot exists.

---

DatabaseComparer class

Orchestrates comparison of database objects between two databases.

• Constructor

  internal DatabaseComparer(SqlConnection connection, string databaseOneName, string databaseTwoName)
  

  Initializes the comparer. Switches the connection context to databaseOneName via DatabaseAdapter.UseDatabase.

• Properties & Fields

  • _connection: Underlying SqlConnection.
  • _databaseAdapter: Internal DatabaseAdapter instance.
  • _databaseOne, _databaseTwo: Names of the two databases being compared.

• Methods

  internal void CleanUp()
  

  Clears the list of registered object comparisons (_objectsToCompare).

  internal void AddObjectComparison(string schema1, string object1, string schema2, string object2)
  

  Registers a new object pair for comparison by creating and adding an ObjectsToCompare instance.

  internal void AddColumnToIgnore(string schemaName, string objectName, string columnName)
  

  Adds a column to ignore for the first object matching (schemaName, objectName).
  ⚠️ Assumes uniqueness: Uses List.Find, so only the first match is updated.

  internal void AddColumnsToIgnore(string schemaName, string tableName, List<string> columnNames)
  

  Adds multiple columns to ignore for the same object.

  internal string GetAllColumns(SqlTransaction transaction, ObjectsToCompare objects)
  

  Queries INFORMATION_SCHEMA.COLUMNS for objects.Qualified1 in _databaseOne.

  • Validates that the object exists (throws ArgumentException if not).
  • Removes columns listed in objects.ColumnsToIgnore.
  • Throws ArgumentException if a specified column is not found or if all columns are ignored.

  internal Table RunCompare(SqlTransaction transaction, ObjectsToCompare objectComparison)
  

  Executes a data comparison between objectComparison.Qualified1 and objectComparison.Qualified2.

  • Uses SELECT ... EXCEPT SELECT ... to find rows present in one side but not the other.
  • Adds a TempRowNumber column (via ROW_NUMBER() OVER(PARTITION BY ... ORDER BY @@SPID)) to handle duplicates.
  • Iterates over result sets (first = "In First", second = "In Second") to populate a Table object.
  • Column names are normalized to lowercase in the resulting Column objects.

  internal Database GenerateDifferences(SqlTransaction transaction)
  

  Compares all registered objects and returns a Database object containing non-empty Table results.

  • Skips tables with zero rows in the result set.

---

3. Invariants

• Object existence: GetAllColumns requires that the first object (objects.Qualified1) exists in _databaseOne; otherwise, it throws ArgumentException.
• Column validation: All columns passed to AddColumnToIgnore must exist in the target object’s column list; otherwise, GetAllColumns throws ArgumentException.
• Non-empty comparison: At least one column must remain after ignoring columns; otherwise, GetAllColumns throws ArgumentException.
• Snapshot path: CreateSnapshot assumes C:\Temp\ is a valid, writable directory.
• Transaction scope: All comparison queries (RunCompare, GetAllColumns) require an active SqlTransaction passed explicitly.
• Case sensitivity: Column names in Column objects are stored in lowercase, regardless of source casing.

---

4. Dependencies

• Internal dependencies:

  • System.Data, System.Data.SqlClient: For SqlConnection, SqlCommand, SqlTransaction, SqlDataReader.
  • DatabaseUnitTesting.Utilities.Results: Uses Database, Table, Row, and Column types (not included in source).

• External dependencies:

  • SQL Server (tested against versions supporting sys.databases.source_database_id, INFORMATION_SCHEMA.COLUMNS, and EXCEPT).
  • File system access to C:\Temp\ for snapshot creation.

• Consumers (inferred):

  • Unit test classes (e.g., Microsoft.VisualStudio.TestTools.UnitTesting-based tests) that use DatabaseComparer to assert data equality.

---

5. Gotchas

• Hardcoded snapshot path: CreateSnapshot uses C:\Temp\ unconditionally. This will fail on non-Windows systems or if the directory is missing/readonly.
• Ambiguous object matching: AddColumnToIgnore and AddColumnsToIgnore use List.Find, which only updates the first matching ObjectsToCompare entry. If multiple entries share the same (schema1, object1), only the first will be modified.
• Case normalization: Column names are lowercased in Row.AddColumn(...), which may cause mismatches if downstream consumers expect original casing.
• @@SPID ordering: The ORDER BY @@SPID in ROW_NUMBER() is arbitrary and non-deterministic. It is used solely to generate distinct TempRowNumber values for duplicate rows; it does not imply stable ordering.
• No schema validation: DatabaseComparer does not verify that object2 exists in _databaseTwo; it assumes the object exists and will fail at query time if not.
• Transaction dependency: All public comparison methods require a SqlTransaction. Using them outside a transaction context will cause runtime errors.
• No cleanup of snapshots: DatabaseAdapter provides DropSnapshot, but no automatic cleanup is performed by DatabaseComparer or ObjectsToCompare. Tests must explicitly manage snapshot lifecycle.

None identified beyond the above.