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

source_files, generated_at, model, schema_version, sha256

source_files	generated_at	model	schema_version	sha256
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	2026-04-17T15:44:53.502112+00:00	zai-org/GLM-5-FP8	1	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).