DP44/docs/ai/Common/DTS.Common.Storage.md

---
source_files:
  - Common/DTS.Common.Storage/SensorDB.cs
  - Common/DTS.Common.Storage/LocalOnlyOperations.cs
  - Common/DTS.Common.Storage/TestHistory.cs
  - Common/DTS.Common.Storage/TypeConverter.cs
  - Common/DTS.Common.Storage/MMETables.cs
  - Common/DTS.Common.Storage/DatabaseServices.cs
  - Common/DTS.Common.Storage/TestSetups.cs
  - Common/DTS.Common.Storage/UserMigrationHelper.cs
generated_at: "2026-04-17T15:29:48.477859+00:00"
model: "zai-org/GLM-5-FP8"
schema_version: 1
sha256: "f8c80457dce3204b"
---

# DTS.Common.Storage Module Documentation

## 1. Purpose

The `DTS.Common.Storage` module provides data access infrastructure for a test and measurement system (DataPRO). It defines database schema constants (table names and field enumerations), manages connections to both local SQL Server LocalDB instances and remote databases, handles type conversion between .NET and SQL types, and provides utilities for test history management, database backup/restore, and user migration. The module serves as the persistence layer for sensor configurations, test setups, MME (Measurement/Monitoring Equipment) metadata, and user data.

---

## 2. Public Interface

### `DbOperations.SensorDB` (abstract class)
Nested within `DbOperations` partial class. Contains schema constants and field enumerations for sensor-related tables.

**Constants:**
- `SensorCalibrationTable` = `"tblSensorCalibrations"`
- `SensorDataTable` = `"tblSensors"`
- `SensorModelsTable` = `"tblSensorModels"`

**Enums:**
- `SensorDataFields` — 55 fields including `SerialNumber`, `UserSerialNumber`, `Model`, `Manufacturer`, `Status`, `Id`, `Capacity`, `BridgeType`, `CalInterval`, `LatestCalibrationId`, etc.
- `SensorModelFields` — 32 fields including `Model`, `Manufacturer`, `Capacity`, `BridgeResistance`, `SupportedExcitation`, etc.
- `SensorCalibrationFields` — 13 fields including `SerialNumber`, `CalibrationDate`, `Username`, `CalibrationRecords`, `SensorCalibrationId`, etc.
- `SensorCalibrationRecordFields` — 7 fields including `Sensitivity`, `Poly`, `AtCapacity`, `EngineeringUnits`, etc.

---

### `DbOperations.MMETables` (abstract class)
Nested within `DbOperations` partial class. Contains schema constants and field enumerations for MME reference tables.

**Constants:**
- `MyType` = `"MyType"` (legacy, replaced in v1.3.496)
- `CustomChannelType` = `"CustomChannelType"` (legacy, replaced in v1.3.515)
- `Id` = `"Id"` (legacy, replaced in v1.3.515)
- `MMEPossibleChannelsTable`, `MMEDirectionsTable`, `MMEFilterClassesTable`, `MMEFineLocations1Table`, `MMEFineLocations2Table`, `MMEFineLocations3Table`, `MMEPhysicalDimensions`, `MMEPositionsTable`, `MMETestObjectsTable`, `MMEMainLocationTable`

**Enums:**
- `MMEPossibleChannelsFields` — 21 fields with `CustomChannelFieldSizeAttribute` decorations
- `MMEDirectionsFields`, `MMEFilterClassesFields`, `MMEFineLocations1Fields`, `MMEFineLocations2Fields`, `MMEFineLocations3Fields`, `MMEPhysicalDimensionFields`, `MMEPositionsFields`, `MMETestObjectsFields`, `MMEMainLocationsFields`

---

### `DbOperations.TestSetups` (abstract class)
Nested within `DbOperations` partial class. Contains schema constants and field enumerations for test configuration tables.

**Constants:**
- `HardwareTable` = `"tblTestSetupHardware"`
- `DASSettingsTable` = `"tblTestSetupDASSettings"`
- `ChannelSettingsTable` = `"tblTestChannelSettings"`
- `TestSetupsTable` = `"tblTestSetups"`
- `TestSetupObjectsTable` = `"tblTestSetupObjects"`
- `TestObjectMetaDataTable` = `"tblTestSetupObjectMetaData"`
- `TestObjectTemplatesTable` = `"tblTestObjectTemplates"`
- `TestObjectsTable` = `"tblTestObjects"`
- `TestGraphsTable` = `"tblTestGraphs"`

**Enums:**
- `HardwareFields`, `ChannelSettingFields`, `ChannelFields`, `SettingsFields`, `Fields`, `TestSetupObjectFields`, `TestSetupDASHardwareFields`, `TestSetupGroupFields`, `TestObjectMetaDataFields`, `TestObjectTemplatesFields`, `TestObjectsFields`, `GroupsGroupFields`, `GroupsGroupChannelFields`, `GroupsGroupHardwareListFields`, `GraphFields`

---

### `LocalOnlyOperations` (class)
Singleton class for local database access operations.

**Constants:**
- `BeginStatement` = `"BEGIN TRAN;"`
- `CommitStatement` = `"COMMIT TRAN;"`
- `UsingNtlmAuthentication` = `true`
- `NoConnection` = `"NoConnection"`

**Static Properties:**
- `Connection` — Singleton instance getter (thread-safe via lock on `DbLock`)
- `cmd` — Static `SqlCommand` cache

**Instance Properties:**
- `Server` — Database server address
- `DbName` — Database name
- `Username` — Authentication username
- `Password` — Authentication password

**Methods:**
- `static void CreateParam(IDbCommand icmd, string name, SqlDbType type, object value)` — Creates and adds a `SqlParameter` to the command. Handles `DateTime` conversion to string format for dates below `SqlDateTime.MinValue`.
- `static IDbCommand GetCommand()` — Returns `GetSQLCommand()`.
- `static SqlCommand GetSQLCommand()` — Returns `GetSQLCommand(false)`.
- `static SqlCommand GetSQLCommand(bool newCommand)` — Returns a cached or new `SqlCommand` with an open connection. Clears parameters before returning.
- `static IDbCommand GetSQLOnlyCommand()` — Returns a new `SqlCommand` without connection setup.
- `void ResetLocalConnectionString()` — Clears cached connection string (thread-safe).
- `string GetLocalConnectionString()` — Builds and caches connection string using `Trusted_Connection=TRUE`.
- `int ExecuteCommand(IDbCommand icmd)` — Executes a non-query command. Throws `SystemException(NoConnection, ex)` on connection failure.

---

### `DatabaseServices` (abstract class)
Static methods for database lifecycle management.

**Constants:**
- `INSTANCE_NAME` = `"DataPROInstance"`
- `LOCAL_SERVER` = `@"(localdb)\DataPROInstance"`
- `SCRIPTS_FOLDER` = `"SQL Server Scripts"`
- `LOCAL_DB_FOLDER` = `"db"`
- `ATTACH_DBS_BAT` = `"AttachDBs.bat"`

**Methods:**
- `static void RestoreLocalDatabase(string defaultDbName)` — Stops LocalDB instance, restores `.mdf` and `_log.ldf` from `.BAK` files, restarts instance.
- `static void BackupLocalDatabase(string defaultDbName)` — Stops LocalDB instance, copies `.mdf` and `_log.ldf` to `.BAK` files, restarts instance.
- `static int SetupLocal(string defaultDbName)` — Configures `DbOperations.Connection` for local DB, returns connection DB version. Throws `SystemException` on connection failure.
- `static int SetupLocal2(string defaultDbName)` — Configures `LocalOnlyOperations.Connection` for local DB, returns connection DB version.
- `static int SetupForRemoteDb(string dbHost, bool userNTLMAuthentication, string user, string password, string defaultDbName)` — Configures `DbOperations.Connection` for remote centralized DB.
- `static bool SimpleDbTest()` — Tests database connectivity by querying `[DAS]` table.

---

### `TestHistory` (class)
Entity class representing test history records.

**Properties:**
- `TestHistoryId` (long) — Primary key
- `TestSetupId` (int) — Foreign key to test setup
- `TestSetupName`, `TestSetupDescription`, `TestId` (string)
- `Destructive` (bool)
- `ArmTime` (DateTime)
- `TestSetup` (byte[]) — Gzip-compressed XML

**Constructors:**
- `TestHistory()` — Default constructor.
- `TestHistory(IDataReader reader)` — Populates instance from data reader, handling `DBNull` checks.

**Static Methods:**
- `static TestHistory[] GetTestHistory(string name)` — Retrieves test histories via stored procedure `sp_TestHistoryGet`.
- `static string SerializeToString(TestHistory[] histories)` — Serializes array to XML using `XmlSerializer`.

**Nested Class:**
- `TestHistoryCollection` — Helper class for XML serialization with `Histories` property.

---

### `TypeConvertor` (sealed class)
Type conversion utility between .NET, `DbType`, and `SqlDbType`.

**Supported Types:** `bool`, `byte`, `byte[]`, `DateTime`, `decimal`, `double`, `Guid`, `short`, `int`, `long`, `object`, `string`, `TimeSpan`

**Static Methods:**
- `static Type ToNetType(DbType dbType)` — Converts `DbType` to .NET `Type`.
- `static Type ToNetType(SqlDbType sqlDbType)` — Converts `SqlDbType` to .NET `Type`.
- `static DbType ToDbType(Type type)` — Converts .NET `Type` to `DbType`.
- `static DbType ToDbType(SqlDbType sqlDbType)` — Converts `SqlDbType` to `DbType`.
- `static SqlDbType ToSqlDbType(Type type)` — Converts .NET `Type` to `SqlDbType`.
- `static SqlDbType ToSqlDbType(DbType dbType)` — Converts `DbType` to `SqlDbType`.

---

### `UserMigrationHelper` (class)
Migration utility for database version 52→53 user data migration.

**Properties:**
- `UserName`, `DisplayName`, `Password` (string)
- `Role` (short)
- `LastModified` (DateTime)
- `LastModifiedBy` (string)
- `LocalOnly` (bool)
- `Permissions` — `Dictionary<string, short>`
- `Visibility` — `Dictionary<string, bool>`
- `TagIds` — `int[]`

**Constructor:**
- `UserMigrationHelper(DataRow row)` — Parses user data from `DataRow`, including tag deserialization from byte array.

**Methods:**
- `void Commit(Dictionary<string, long> IUIItemNameToID)` — Inserts user into `DataPROUsers` table, then inserts permissions into `UserUIItemSettings` and tags into `TagAssignments`. Batches inserts in transactions of 25.

---

## 3. Invariants

- **Singleton Pattern:** `LocalOnlyOperations.Connection` is a singleton; the getter uses a lock (`DbLock`) to ensure thread-safe initialization.
- **Connection String Caching:** `_localConnection` in `LocalOnlyOperations` is cached after first access; `ResetLocalConnectionString()` must be called to refresh it.
- **DateTime Handling:** `LocalOnlyOperations.CreateParam` converts `DateTime` values below `SqlDateTime.MinValue` to the minimum value, then formats as `"yyyy-MM-dd HH:mm:ss"` string.
- **Command Reuse:** `GetSQLCommand(false)` reuses a static `SqlCommand` instance; parameters are cleared before return.
- **Field Enum Values:** `SensorDataFields.SerialNumber` explicitly starts at `1`; subsequent values auto-increment.
- **Nullable Type Support:** `TypeConvertor.Find(Type)` handles nullable types via `Nullable.GetUnderlyingType`.
- **Transaction Batching:** `UserMigrationHelper.Commit` batches permission inserts in groups of 25 with explicit `BEGIN TRAN`/`COMMIT TRAN`.

---

## 4. Dependencies

### This module depends on:
- `System.Data` and `System.Data.SqlClient` — ADO.NET SQL Server client
- `System.Xml.Serialization` — XML serialization for `TestHistory`
- `System.IO` — File operations for database backup/restore
- `DTS.Common.Utilities.Logging` — `APILogger` for logging
- `DbAPI.Errors` — `ErrorCodes` for connection result checking
- `DbAPI.Connections` — `ConnectionDetails` class

### External dependencies inferred:
- `DbOperations` (partial class) — Referenced by `LocalOnlyOperations`, `TestHistory`, `UserMigrationHelper`, `DatabaseServices` but not fully defined in provided sources
- `Utils.Database` — `StopInstance`, `StartInstance`, `GetSqlServerLocalDbPath`, `GetODBCToolsPath`
- `Utils.FileUtils` — `DeleteFileOrMove`
- `DbAPI.DbAPI.Connections` — `ConnectToDb` method

---

## 5. Gotchas

1. **Static Command Cache:** `LocalOnlyOperations.cmd` is a static field. Concurrent access to `GetSQLCommand()` without `newCommand: true` could lead to race conditions where one thread's connection is swapped mid-operation.

2. **DateTime Conversion Side Effect:** `CreateParam` converts `DateTime` to a formatted string for `SqlDbType.DateTime`, which changes the parameter value type unexpectedly. Null `DateTime` values become `SqlDateTime.MinValue`.

3. **Legacy Field Name Constants:** `MMETables` contains deprecated constants (`MyType`, `CustomChannelType`, `Id`) that were replaced in specific versions (1.3.496, 1.3.515). Code referencing these may be interacting with outdated schema expectations.

4. **Dual Database Execution Paths:** `UserMigrationHelper.Commit` checks `DbOperations._usingMSSQL` to choose between `ExecuteCommand` and `ExecuteSQLiteCommand`. This suggests the system supports both SQL Server and SQLite, but `LocalOnlyOperations` only implements SQL Server-specific logic.

5. **Connection Disposal Pattern:** Multiple methods (e.g., `TestHistory.GetTestHistory`, `UserMigrationHelper.Commit`) use `finally { cmd.Connection.Dispose(); }` but the `cmd` object itself is not disposed, potentially leaving resources unreleased.

6. **Hardcoded Stored Procedure:** `TestHistory.GetTestHistory` references `sp_TestHistoryGet` stored procedure which is not defined in these sources.

7. **Incomplete Type Mapping:** `TypeConvertor` maps `byte[]` to `SqlDbType.Image` (deprecated) rather than `VarBinary`.