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

source_files, generated_at, model, schema_version, sha256

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