DP44/enriched-qwen3-coder-next/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-16T03:51:39.334243+00:00	Qwen/Qwen3-Coder-Next-FP8	1	f699b3e38a2dc576

Database Unit Testing Module Documentation

1. Purpose

This module provides a structured framework for database unit testing within the DataPRO system, using NUnit to validate database interactions via stored procedures and the DbAPI interface. It ensures data integrity and correct behavior of database operations (insert, update, delete, get) for core entities—such as DAS, DAS channels, laboratory details, customer details, calculated channels, and regions of interest—by leveraging database snapshots and transactional rollbacks to maintain test isolation and repeatability. The module supports two distinct testing approaches: low-level stored procedure testing (via ResultSetTester and DatabaseModificationTester) and high-level API testing (via DbAPI methods), enabling comprehensive coverage of database functionality.

2. Public Interface

TestSetups (Base Class for Test Fixtures)

• [OneTimeSetUp] TFSetup()
  Initializes a SqlConnection, opens it, and instantiates DatabaseModificationTester, which creates a database snapshot for test isolation.
• [OneTimeTearDown] TFTearDown()
  Disposes DatabaseModificationTester (dropping the snapshot) and closes the connection; does not delete test data.
• [SetUp] SetUp()
  Begins a new transaction via DatabaseModificationTester.BeginTestTransaction(), sets up a SqlCommand bound to the transaction.
• [TearDown] TearDown()
  Rolls back the current test transaction via DatabaseModificationTester.EndTestTransaction().
• Properties
  • TestResultPath: Returns path to test results directory (..\..\TestResults\ relative to app base directory).
  • UnitTests: Exposes the DatabaseModificationTester instance.
  • BSETUPMODE: Always false (hardcoded constant).
  • Command: Returns the SqlCommand bound to the current transaction.

ResultSetTester

• ResultSetTester(SqlConnection connection, string procedureName)
  Constructs a SqlCommand for the given stored procedure name, with CommandType.StoredProcedure.
• ResultSetTester(SqlCommand command)
  Wraps an existing SqlCommand.
• SetInputParameter(string parameterName, object parameterValue)
  Adds or replaces an input parameter on the command.
• SetOutputParameter(string parameterName, SqlDbType type, int size)
  Adds or replaces an output parameter on the command, setting its direction to Output.
• PrintOuputParameterValues()
  Returns an IEnumerable<KeyValuePair<string, object>> of all output parameter names and values.
• CompareToFile(string filename)
  Parses the stored procedure result set using ResultSetParser, reads expected results from XML file via XmlFileAdapter, and returns true if equal.
• OutputToFile(string filename)
  Parses the stored procedure result set and writes it to an XML file via XmlFileAdapter.

DatabaseModificationTester

• DatabaseModificationTester(SqlConnection connection, string databaseName, string snapshotName)
  Initializes a snapshot-based testing environment; creates a database snapshot on construction.
• BeginTestTransaction()
  Begins a new transaction on the connection; throws if a transaction already exists (rolls back first).
• EndTestTransaction()
  Rolls back and disposes the current transaction; calls CleanUp() internally.
• WriteDiffsToXml(string filename)
  Generates differences between current transaction and the snapshot, writes to XML file.
• CompareDiffsToXml(string filename)
  Compares current transaction differences to expected XML file; returns true if equal.
• AreEqual()
  Returns true if no differences exist between current transaction and snapshot.
• AddColumnToIgnore(string schemaName, string objectName, string columnName)
  Adds a single column to the ignore list for comparisons.
• AddColumnsToIgnore(string schema1, string name1, List<string> columnNames)
  Adds multiple columns to the ignore list for a given table.
• AddObjectComparison(string schemaName, string tableName)
  Registers a table for comparison (likely default behavior).
• Dispose()
  Drops the database snapshot if active.

DbAPI Tests (Partial Classes)

The DbAPITests partial class and its companions (DbAPITestsRegionsOfInterest, DbAPITestsChannels, DbAPITestsSensorsAnalog, DbAPITestsGroupHardware, DbAPITestsCustomerDetails) contain integration tests for the DbAPI layer. Key methods include:

• TestConnect() / TestLogin()
  Verify database connectivity and user authentication.
• RegionsOfInterest()
  Tests versioned ROI functionality (sp_TestSetupsUpdateInsert_92, sp_TestSetupROIsInsert, sp_ROIPeriodChannelsInsert, etc.), including graceful handling of pre-Version 92 databases.
• TestChannelsInsert() / TestChannelsUpdate() / TestChannelsGet() / TestChannelsDelete()
  Validate channel CRUD operations via DbAPI.Channels.* methods.
• TestSensorsDeleteAll() / TestSensorsAnalogInsertAndDelete() / TestSensorAnalogInsertAndDeleteShouldFail() / TestSensorsAnalogBridgeResistanceGet()
  Validate analog sensor operations, including error handling for null user/connection and special test sensors that persist after bulk delete.
• TestGroupHardwareInsertGetAndDelete()
  Validates group-hardware associations (GroupHardware table) including multi-DAS/group scenarios.
• CustomerDetails()
  Validates customer details CRUD, including null-name delete (delete all) and name-based lookup.

3. Invariants

• Test Isolation: Each test runs inside a transaction that is rolled back after the test (BeginTestTransaction/EndTestTransaction), ensuring no persistent side effects.
• Snapshot Consistency: A database snapshot is created once per test fixture ([OneTimeSetUp]) and dropped at teardown ([OneTimeTearDown]), providing a known baseline for all tests in the fixture.
• No Test Data Cleanup: The teardown explicitly skips deletion of test data; only the transaction is rolled back and snapshot dropped.
• Transaction Lifecycle: A transaction must exist before calling EndTestTransaction(); otherwise, InvalidOperationException is thrown.
• Output Parameter Handling: ResultSetTester.SetOutputParameter() removes any existing parameter with the same name before adding a new one.
• Versioned ROI Support: ROI tests handle both pre-Version 92 (string-based) and Version 92+ (normalized tables) databases; RegionsOfInterestInsert is expected to fail on Version ≤91.

4. Dependencies

Internal Dependencies

• DatabaseUnitTesting.Utilities namespace:
  • DatabaseComparer, DatabaseAdapter, ResultSetParser, XmlFileAdapter (used by DatabaseModificationTester and ResultSetTester).
• DatabaseUnitTesting.Utilities.Results namespace:
  • Database (used by ResultSetParser and XmlFileAdapter).
• DTS.Common.* namespaces:
  • DTS.Common.Interface.Database, DTS.Common.Interface.TestSetups, DTS.Common.Interface.Channels, DTS.Common.Interface.Sensors, DTS.Common.Interface.Groups, DTS.Common.Interface.TestMetaData, DTS.Common.Classes.*, DTS.Common.Enums.*, DTS.Common.Storage (used by DbAPITests* classes).
• Properties.Settings.Default:
  • Used for ConnectionString, DBName, DBSnapshot in TestSetups.

External Dependencies

• NUnit: Testing framework ([TestFixture], [Test], [SetUp], [TearDown], [OneTimeSetUp], [OneTimeTearDown], Assert.*).
• System.Data.SqlClient: SqlConnection, SqlCommand, SqlTransaction, SqlDbType, ParameterDirection.
• System.Data: DataTable, IDataReader, DbDataAdapter (used via ResultSetParser).
• LocalDB / SQL Server: Target database engine; connection via LocalDb ((localdb)\DataPROInstance).
• External Scripts: AttachDBs.bat, db folder (used by DbAPI.Connections.ConnectToDb).

Inferred Usage

• All test fixture classes (DefaultPropertiesTests, LaboratoryDetailsTests, DASTests, etc.) inherit from TestSetups.
• ResultSetTester is used by tests to execute stored procedures and compare result sets.
• DatabaseModificationTester is used by tests to assert database state changes (via CompareDiffsToXml, AreEqual, etc.).
• DbAPI is used for integration tests against the high-level API layer.

5. Gotchas

• BSETUPMODE is Always false: The constant _BSETUPMODE = false is hardcoded and never configurable; tests cannot switch to a setup mode.
• Transaction Reuse Risk: BeginTestTransaction() throws if a transaction already exists; tests must not call BeginTestTransaction() multiple times per test.
• Snapshot Cleanup: The snapshot is dropped only in DatabaseModificationTester.Dispose(), which is called in [OneTimeTearDown]. If tests are run in parallel, snapshot conflicts may occur.
• Output Parameter Overwrite: ResultSetTester.SetOutputParameter() removes existing parameters with the same name before adding a new one—this may mask bugs if parameter names are reused unintentionally.
• WriteDiffsToXml Generates Manual Verification Files: The XML files produced by WriteDiffsToXml must be manually reviewed and accepted as expected results; they are not auto-validated.
• CustomerDetails.Name is Required: Tests and comments indicate Name is the identifying field for insert/update; null may cause errors.
• DAS Requires SerialNumber: Tests and comments indicate SerialNumber is mandatory for DAS operations.
• SensorsDeleteAll Preserves Test Sensors: TestSensorsDeleteAll expects that specific test sensors (SensorConstants.TEST_SPECIFIC_ANALOG_SERIAL, SensorConstants.TEST_SPECIFIC_CLOCK_SERIAL) persist after a bulk delete.
• ROI Version Handling: RegionsOfInterestInsert is expected to fail (non-zero hr) on databases with version ≤91; tests must check version before invoking.
• DbAPI Error Codes: Return codes (hr) are used for success/failure (0 = success); tests rely on this convention rather than exceptions.
• Path Resolution Complexity: DbAPITests.Setup() includes complex logic to locate AttachDBs.bat and db folder; behavior may vary depending on build configuration (e.g., Debug/Release, x86/x64).