DP44/docs/ai/DataPRO/UnitTest/DatabaseUnitTesting.md

---
source_files:
  - DataPRO/UnitTest/DatabaseUnitTesting/DefaultPropertiesTests.cs
  - DataPRO/UnitTest/DatabaseUnitTesting/LaboratoryDetailsTests.cs
  - DataPRO/UnitTest/DatabaseUnitTesting/TestSetups.cs
  - DataPRO/UnitTest/DatabaseUnitTesting/ResultSetTester.cs
  - DataPRO/UnitTest/DatabaseUnitTesting/DASTests.cs
  - DataPRO/UnitTest/DatabaseUnitTesting/CustomerDetailsTests.cs
  - DataPRO/UnitTest/DatabaseUnitTesting/DASChannelsTests.cs
  - DataPRO/UnitTest/DatabaseUnitTesting/CalculatedChannelsTests.cs
  - DataPRO/UnitTest/DatabaseUnitTesting/DBAPITests.cs
  - DataPRO/UnitTest/DatabaseUnitTesting/DbAPITestsRegionsOfInterest.cs
  - DataPRO/UnitTest/DatabaseUnitTesting/DatabaseModificationTester.cs
  - DataPRO/UnitTest/DatabaseUnitTesting/DbAPITestsChannels.cs
  - DataPRO/UnitTest/DatabaseUnitTesting/DbAPITestsSensorsAnalog.cs
  - DataPRO/UnitTest/DatabaseUnitTesting/DbAPITestsGroupHardware.cs
  - DataPRO/UnitTest/DatabaseUnitTesting/DbAPITestsCustomerDetails.cs
generated_at: "2026-04-17T15:44:53.502112+00:00"
model: "zai-org/GLM-5-FP8"
schema_version: 1
sha256: "89b12019c46b65c4"
---

# Database Unit Testing Module Documentation

## 1. Purpose

This module provides a unit testing framework for validating database operations in the DataPRO system. It enables testing of stored procedures, database modifications, and API operations against a SQL Server database. The framework uses database snapshots for test isolation, transaction rollback for cleanup, and XML-based result comparison for verifying database state changes. It tests various domain entities including DAS (Data Acquisition Systems), channels, sensors, customer details, laboratory details, and regions of interest.

---

## 2. Public Interface

### TestSetups (Base Class)
**File:** `TestSetups.cs`

Base class providing test infrastructure for database unit tests.

| Member | Signature | Description |
|--------|-----------|-------------|
| `TFSetup` | `void TFSetup()` | One-time setup marked with `[OneTimeSetUp]`. Creates database snapshot via `DatabaseModificationTester` constructor. |
| `TFTearDown` | `void TFTearDown()` | One-time teardown marked with `[OneTimeTearDown]`. Disposes `DatabaseModificationTester` (drops snapshot) and closes connection. |
| `SetUp` | `void SetUp()` | Per-test setup marked with `[SetUp]`. Begins a test transaction via `_unitTests.BeginTestTransaction()`. |
| `TearDown` | `void TearDown()` | Per-test teardown marked with `[TearDown]`. Ends test transaction via `_unitTests.EndTestTransaction()`. |
| `TestResultPath` | `String TestResultPath { get; }` | Returns `_testResultsInitialPath` pointing to `TestResults\` directory. |
| `UnitTests` | `DatabaseModificationTester UnitTests { get; }` | Returns the `DatabaseModificationTester` instance. |
| `BSETUPMODE` | `bool BSETUPMODE { get; }` | Returns constant `_BSETUPMODE` (always `false`). |
| `Command` | `SqlCommand Command { get; }` | Returns the current test's `SqlCommand` associated with the transaction. |

### DatabaseModificationTester
**File:** `DatabaseModificationTester.cs`

Core class for managing database snapshots and transaction-based testing. Implements `IDisposable`.

| Member | Signature | Description |
|--------|-----------|-------------|
| Constructor | `DatabaseModificationTester(SqlConnection connection, string databaseName, string snapshotName)` | Creates a database snapshot via `DatabaseAdapter.CreateSnapshot()`. Sets `_activeSnapshot = true`. |
| `BeginTestTransaction` | `SqlTransaction BeginTestTransaction()` | Begins a new SQL transaction. Ends any existing transaction first. Returns the transaction. |
| `EndTestTransaction` | `void EndTestTransaction()` | Calls `_dataComparer.CleanUp()`, rolls back and disposes the transaction. Throws `InvalidOperationException` if no transaction exists. |
| `WriteDiffsToXml` | `void WriteDiffsToXml(string filename)` | Writes database differences between current state and snapshot to XML file via `XmlFileAdapter.Write()`. |
| `CompareDiffsToXml` | `bool CompareDiffsToXml(string filename)` | Compares current differences against stored XML file. Returns `true` if equal. |
| `AreEqual` | `bool AreEqual()` | Returns `true` if `GenerateDifferences().TableCount == 0`. |
| `AddColumnToIgnore` | `void AddColumnToIgnore(string schemaName, string objectName, string columnName)` | Adds a single column to ignore during comparison. |
| `AddColumnsToIgnore` | `void AddColumnsToIgnore(string schema1, string name1, List<string> columnNames)` | Adds multiple columns to ignore during comparison. |
| `AddObjectComparison` | `void AddObjectComparison(string schemaName, string tableName)` | Adds an object for comparison. |
| `Dispose` | `void Dispose()` | Drops the database snapshot if `_activeSnapshot` is true. |

### ResultSetTester
**File:** `ResultSetTester.cs`

Utility for testing stored procedure result sets.

| Member | Signature | Description |
|--------|-----------|-------------|
| Constructor | `ResultSetTester(SqlConnection connection, string procedureName)` | Creates `SqlCommand` with `CommandType.StoredProcedure`. |
| Constructor | `ResultSetTester(SqlCommand command)` | Wraps an existing `SqlCommand`. |
| `PrintOuputParameterValues` | `IEnumerable<KeyValuePair<string, object>> PrintOuputParameterValues()` | Returns output parameters as key-value pairs. |
| `CompareToFile` | `bool CompareToFile(string filename)` | Parses command results via `ResultSetParser.Parse()`, reads expected from XML via `XmlFileAdapter.Read()`, compares for equality. |
| `OutputToFile` | `void OutputToFile(string filename)` | Parses results and writes to XML file. |
| `SetInputParameter` | `void SetInputParameter(string parameterName, object parameterValue)` | Sets or adds an input parameter. Uses `Parameters.AddWithValue()` if not present. |
| `SetOutputParameter` | `void SetOutputParameter(string parameterName, SqlDbType type, int size)` | Configures an output parameter. Removes existing parameter if present. Sets `Size` if non-zero. |

### DBAPITests (Partial Class)
**File:** `DBAPITests.cs`, `DbAPITestsChannels.cs`, `DbAPITestsCustomerDetails.cs`, `DbAPITestsGroupHardware.cs`, `DbAPITestsRegionsOfInterest.cs`, `DbAPITestsSensorsAnalog.cs`

Main test fixture for database API operations.

| Member | Signature | Description |
|--------|-----------|-------------|
| `Setup` | `void Setup()` | `[OneTimeSetUp]` Initializes logger, configures `ConnectionDetails`, calls `DbAPI.DbAPI.Connections.ConnectToDb()`. |
| `TestConnect` | `void TestConnect()` | Verifies active connections exist via `GetActiveConnections().Any()`. |
| `TestLogin` | `void TestLogin()` | Logs in as "Admin" with password "DTSAdmin" via `LoginUser()`. |
| `Login` | `void Login()` | Private helper that performs login and sets `_user` and `clientDbVersion`. |
| `RegionsOfInterest` | `void RegionsOfInterest()` | Tests ROI operations for database version 92+. Tests `TestSetupsUpdateInsert`, `RegionsOfInterestInsert`, `RegionsOfInterestGet`, `RegionsOfInterestDelete`. |
| `TestChannelsInsert/Update/Get/Delete` | `void TestChannels*()` | Tests channel CRUD operations via `DbAPI.DbAPI.Channels.*` methods. |
| `TestSensorsDeleteAll` | `void TestSensorsDeleteAll()` | Tests `SensorsDeleteAll` preserves test-specific sensors (`TEST_SPECIFIC_ANALOG_SERIAL`, `TEST_SPECIFIC_CLOCK_SERIAL`). |
| `TestSensorsAnalogInsertAndDelete` | `void TestSensorsAnalogInsertAndDelete()` | Tests analog sensor CRUD via `SensorsAnalogUpdateInsert`, `SensorsAnalogGet`, `SensorsDelete`. |
| `TestSensorAnalogInsertAndDeleteShouldFail` | `void TestSensorAnalogInsertAndDeleteShouldFail()` | Verifies operations fail with null user or connection. |
| `TestSensorsAnalogBridgeResistanceGet` | `void TestSensorsAnalogBridgeResistanceGet()` | Tests `SensorsAnalogBridgeResistanceGet` returns correct resistance value. |
| `TestGroupHardwareInsertGetAndDelete` | `void TestGroupHardwareInsertGetAndDelete()` | Tests `GroupHardwareInsert`, `GroupHardwareGet`, `GroupHardwareDelete`. |
| `CustomerDetails` | `void CustomerDetails()` | Tests `CustomerDetailsInsert`, `CustomerDetailsGet`, `CustomerDetailsUpdate`, `CustomerDetailsDelete`. |
| `CreateFakeChannel` | `IChannelDbRecord CreateFakeChannel()` | Creates a test `ChannelDbRecord`. |
| `CreateFakeSensor` | `IAnalogDbRecord CreateFakeSensor()` | Creates a test `AnalogDbRecord` with `BridgeResistance = 350`. |
| `CreateFakeGroup` | `IGroupDbRecord CreateFakeGroup(string displayName, int? staticGroupId)` | Creates a test `GroupDbRecord`. |
| `CreateGroupHardwareDbRecord` | `GroupHardwareDbRecord CreateGroupHardwareDbRecord(int groupId, int dasId)` | Creates a test `GroupHardwareDbRecord`. |
| `CreateFakeCustomerDetails` | `CustomerDetailsDbRecord CreateFakeCustomerDetails(string name)` | Creates a test `CustomerDetailsDbRecord`. |
| `CreateFakeTestSetupWithROIs` | *(referenced but implementation not in provided files)* | Creates test setup with ROIs. |

### Test Fixture Classes (Commented-Out Tests)
**Files:** `DefaultPropertiesTests.cs`, `LaboratoryDetailsTests.cs`, `DASTests.cs`, `CustomerDetailsTests.cs`, `DASChannelsTests.cs`, `CalculatedChannelsTests.cs`

These classes inherit from `TestSetups` and contain commented-out test stubs documenting intended test cases for various stored procedures:

- `sp_LabratoryDetailsUpdateInsert`
- `sp_DASDelete`, `sp_DASGet`, `sp_DASCUpdateInsert`, `sp_DASInUpdateInsert`
- `sp_CustomerDetailsDelete`, `sp_CustomerDetailsGet`, `sp_CustomerDetailsUpdateInsert`, `sp_CustomerDetailsInUpdateInsert`
- `sp_DASChannelsDelete`, `sp_DASChannelsGet`, `sp_DASChannelsUpdateInsert`, `sp_DASChannelsInUpdateInsert`
- `sp_CalculatedChannelsDelete`, `sp_CalculatedChannelsGet`, `sp_CalculatedChannelsUpdateInsert`, `sp_CalculatedChannelsInUpdateInsert`

---

## 3. Invariants

1. **Transaction Isolation**: Every test runs within a transaction that is rolled back in `TearDown`. This ensures no permanent database modifications from tests.

2. **Snapshot Lifecycle**: A database snapshot is created in `TFSetup` (via `DatabaseModificationTester` constructor) and must be dropped in `TFTearDown` (via `Dispose()`). The `_activeSnapshot` flag prevents double-drop attempts.

3. **Single Active Transaction**: `BeginTestTransaction()` will end any existing transaction before creating a new one.

4. **EndTestTransaction Requires Active Transaction**: Calling `EndTestTransaction()` when `_transaction` is null throws `InvalidOperationException`.

5. **BSETUPMODE is Always False**: The property returns a constant `false` value.

6. **CustomerDetails Name Constraint**: Per comments in `CustomerDetailsTests.cs`, "Name cannot be null as that is the identifying field for insert or update."

7. **DAS Serial Number Requirement**: Per comments in `DASTests.cs`, "DAS must have serial number."

8. **SensorsDeleteAll Preserves Test Sensors**: `TestSensorsDeleteAll` verifies that `TEST_SPECIFIC_ANALOG_SERIAL` and `TEST_SPECIFIC_CLOCK_SERIAL` sensors remain after delete all.

9. **ROI Operations Version Gating**: `RegionsOfInterestInsert` should fail (return non-zero HR) for database version ≤ 91.

---

## 4. Dependencies

### This Module Depends On:
- **NUnit.Framework** - Test framework (`[TestFixture]`, `[Test]`, `[SetUp]`, `[TearDown]`, `[OneTimeSetUp]`, `[OneTimeTearDown]`, `Assert`)
- **System.Data.SqlClient** - SQL Server client (`SqlConnection`, `SqlCommand`, `SqlParameter`, `SqlTransaction`)
- **System.Data** - ADO.NET types (`CommandType`, `ParameterDirection`, `SqlDbType`)
- **DTS.Common.Interface.Database** - `IUserDbRecord`
- **DTS.Common.Interface.Channels** - `IChannelDbRecord`
- **DTS.Common.Interface.Sensors** - `IAnalogDbRecord`
- **DTS.Common.Interface.Groups** - `IGroupDbRecord`
- **DTS.Common.Interface.TestMetaData** - `ICustomerDetailsDbRecord`
- **DTS.Common.Interface.TestSetups.TestSetupsList** - Test setup interfaces
- **DTS.Common.Classes.Channels** - `ChannelDbRecord`
- **DTS.Common.Classes.Sensors** - `AnalogDbRecord`
- **DTS.Common.Classes.Groups** - `GroupDbRecord`, `GroupHardwareDbRecord`
- **DTS.Common.Classes.Hardware** - Hardware-related classes
- **DTS.Common.Classes.CustomerDetails** - `CustomerDetailsDbRecord`
- **DTS.Common.Classes.TestSetups** - Test setup classes
- **DTS.Common.Enums.Sensors** - Sensor enums
- **DTS.Common.Storage** - `DbOperations` (includes `CURRENT_DB_VERSION`, `MINIMUM_LTS_DB_VERSION`)
- **DTS.Common.Utils.Database** - `GetSqlServerLocalDbPath()`, `GetODBCToolsPath()`
- **DbAPI.DbAPI** - Main API class with nested `Connections`, `TestSetups`, `RegionsOfInterest`, `Channels`, `Sensors`, `DAS`, `Groups`, `GroupHardware`, `CustomerDetails`
- **DatabaseUnitTesting.Utilities** - `DatabaseAdapter`, `DatabaseComparer`
- **DatabaseUnitTesting.Utilities.Results** - `Database`, `ResultSetParser`
- **Properties.Settings.Default** - Application settings (`ConnectionString`, `DBName`, `DBSnapshot`)

### Internal Dependencies:
- `TestSetups` is the base class for: `DefaultPropertiesTests`, `LaboratoryDetailsTests`, `DASTests`, `CustomerDetailsTests`, `DASChannelsTests`, `CalculatedChannelsTests`
- `DBAPITests` is a partial class spread across multiple files

---

## 5. Gotchas

1. **Typo in Stored Procedure Names**: Comments reference `sp_LabratoryDetailsUpdateInsert` and `LabratoryDetails` table (misspelled "Laboratory" as "Labratory"). This may reflect actual database object names or be a documentation error.

2. **Inconsistent Stored Procedure Names in Comments**: `DASTests.cs` references `sp_CalculatedChannelsUpdateInsert` in an INSERT DAS test comment, and `sp_DASInUpdateInsert` in UPDATE comments. This appears to be copy-paste error in comments.

3. **Hardcoded Credentials**: `Login()` method uses hardcoded credentials "Admin" / "DTSAdmin".

4. **Hardcoded Database Instance**: `LOCAL_SERVER` is hardcoded as `@"(localdb)\DataPROInstance"`.

5. **Commented-Out Tests**: Multiple test files contain only commented-out test stubs. These document intended tests but are not executable.

6. **CreateFakeTestSetupWithROIs Implementation Missing**: Referenced in `RegionsOfInterest()` test but implementation not present in provided source files.

7. **Channel Tests Have Commented-Out Insert/Delete Calls**: In `DbAPITestsChannels.cs`, the actual `ChannelsInsert()`, `ChannelsUpdate()`, `ChannelsDelete()` calls are commented out, making tests incomplete.

8. **Path Construction Relies on Directory Structure**: `Setup()` method makes assumptions about build output directory structure (`bin\{architecture}\{debugOrRelease}`).

9. **Test Result Path Uses Relative Navigation**: `_testResultsInitialPath` uses `..\..\TestResults\` relative to `AppDomain.CurrentDomain.BaseDirectory`.

10. **Bridge Resistance Constant**: `FAKE_BRIDGE_RESISTANCE_OMS` is set to 350 (presumably ohms, though "OMS" may be a typo).