--- source_files: - DataPRO/UnitTest/DatabaseUnitTesting/Utilities/ObjectsToCompare.cs - DataPRO/UnitTest/DatabaseUnitTesting/Utilities/DatabaseAdapter.cs - DataPRO/UnitTest/DatabaseUnitTesting/Utilities/DatabaseComparer.cs generated_at: "2026-04-16T03:52:03.248634+00:00" model: "Qwen/Qwen3-Coder-Next-FP8" schema_version: 1 sha256: "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** ```csharp 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** ```csharp 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 ColumnsToIgnore { get; } ``` - **Methods** ```csharp 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** ```csharp internal DatabaseAdapter(SqlConnection connection) ``` Wraps an existing `SqlConnection`. Automatically opens the connection if closed. - **Methods** ```csharp internal bool IsSnapshot(string name) ``` Returns `true` if `name` refers to a database snapshot (checks `sys.databases.source_database_id`); `false` otherwise. ```csharp internal void UseDatabase(string databaseName) ``` Executes `USE [databaseName]` on the connection. ```csharp 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. ```csharp 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** ```csharp 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** ```csharp internal void CleanUp() ``` Clears the list of registered object comparisons (`_objectsToCompare`). ```csharp 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. ```csharp 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. ```csharp internal void AddColumnsToIgnore(string schemaName, string tableName, List columnNames) ``` Adds multiple columns to ignore for the same object. ```csharp 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. ```csharp 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. ```csharp 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.