DP44/enriched-qwen3-coder-next/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-16T14:09:58.168174+00:00	zai-org/GLM-5-FP8	1	5cf7683c6017efd4

Documentation: DTS.Common.Storage

1. Purpose

This module serves as the Data Access Layer (DAL) and schema definition hub for the DTS application (likely a data acquisition system named DataPRO). It provides abstraction over raw ADO.NET operations, managing connections to both LocalDB instances and remote SQL Servers. The module defines strongly-typed mappings for database tables—specifically for Sensors, MME (Measurement and Monitoring Equipment), and Test Setups—via constants and enums. It also includes utilities for type conversion, database lifecycle management (backup/restore/setup), and specific data migration tasks.

2. Public Interface

DTS.Common.Storage.LocalOnlyOperations

A singleton class managing local database connections and command execution.

• static LocalOnlyOperations Connection (Property): Singleton instance accessor.
• static IDbCommand GetCommand(): Returns an IDbCommand (wrapper for GetSQLCommand).
• static SqlCommand GetSQLCommand(bool newCommand): Returns a SqlCommand. If newCommand is false, it reuses a static cmd instance. It ensures the connection is open before returning.
• static void CreateParam(IDbCommand icmd, string name, SqlDbType type, object value): Adds a parameter to the command. Handles DateTime min-value constraints and formatting.
• int ExecuteCommand(IDbCommand icmd): Executes a non-query command against a new connection instance.
• string GetLocalConnectionString(): Constructs and caches the connection string using Server and DbName properties with Trusted Connection.
• void ResetLocalConnectionString(): Clears the cached connection string.

DTS.Common.Storage.TestSetups.TestHistory

Represents a historical record of a test run.

• TestHistory(IDataReader reader): Constructor that populates the object from a database row, handling DBNull checks.
• static TestHistory[] GetTestHistory(string name): Retrieves test histories by calling the stored procedure sp_TestHistoryGet.
• static string SerializeToString(TestHistory[] histories): Serializes an array of histories to an XML string using TestHistoryCollection.

DTS.Common.Storage.TypeConvertor

A utility class for mapping between .NET types, DbType, and SqlDbType.

• static Type ToNetType(DbType dbType)
• static Type ToNetType(SqlDbType sqlDbType)
• static DbType ToDbType(Type type)
• static DbType ToDbType(SqlDbType sqlDbType)
• static SqlDbType ToSqlDbType(Type type)
• static SqlDbType ToSqlDbType(DbType dbType)

DTS.Common.Storage.DatabaseServices

Provides high-level database setup and maintenance operations.

• static void RestoreLocalDatabase(string defaultDbName): Stops the LocalDB instance, restores .mdf and .ldf files from .BAK files, and restarts the instance.
• static void BackupLocalDatabase(string defaultDbName): Stops the LocalDB instance, copies current .mdf/.ldf to .BAK files, and restarts the instance.
• static int SetupLocal(string defaultDbName): Configures DbOperations for a local connection using DbAPI and returns the DB version.
• static int SetupLocal2(string defaultDbName): Configures LocalOnlyOperations for a local connection.
• static int SetupForRemoteDb(...): Configures DbOperations for a remote SQL Server connection.
• static bool SimpleDbTest(): Executes a simple query SELECT TOP 1 [DASId] FROM [DAS] to verify connectivity.

DTS.Common.Storage.UserMigrationHelper

Assists in migrating user data between database versions (v52 to v53).

• UserMigrationHelper(DataRow row): Parses user data, permissions, and visibility settings from a DataRow.
• void Commit(Dictionary<string, long> IUIItemNameToID): Inserts the user into DataPROUsers, UserUIItemSettings, and TagAssignments tables. Handles both MSSQL and SQLite execution paths.

Schema Definitions (Abstract Classes)

These classes contain const string table names and enum field mappings. They are nested within DbOperations (except SensorDB which is nested but the file structure implies partial classes).

• DbOperations.SensorDB: Defines tblSensors, tblSensorModels, tblSensorCalibrations.
• DbOperations.MMETables: Defines MMEPossibleChannels, tblMMEDirections, tblMMEFilterClasses, etc. Includes legacy field name constants (MyType, CustomChannelType, Id).
• DbOperations.TestSetups: Defines tblTestSetups, tblTestSetupHardware, tblTestChannelSettings, etc.

3. Invariants

• Singleton Access: LocalOnlyOperations.Connection is thread-safe (uses lock) and ensures only one instance exists.
• DateTime Handling: LocalOnlyOperations.CreateParam enforces that DateTime values cannot be less than System.Data.SqlTypes.SqlDateTime.MinValue. If they are, they are clamped to the minimum.
• Connection State: LocalOnlyOperations.GetSQLCommand guarantees the returned command has an open connection.
• Schema Enums: The integer values for SensorDataFields start at 1 (explicitly defined), while other enums rely on default 0-based indexing.
• Migration Logic: UserMigrationHelper.Commit relies on the external state of DbOperations._usingMSSQL to determine the correct execution method (ExecuteCommand vs ExecuteSQLiteCommand).

4. Dependencies

Internal Dependencies

• DTS.Common.Utilities.Logging: Used for logging (APILogger.Log).
• DbAPI: Used for low-level connection logic (DbAPI.Connections.ConnectionDetails, DbAPI.Connections.ConnectToDb).
• DbAPI.Errors: Used for error code checking (ErrorCodes.ERROR_SUCCESS).
• Utils.Database: Used for LocalDB instance management (StartInstance, StopInstance, path retrieval).
• Utils.FileUtils: Used for file operations during backup/restore.

External Dependencies

• System.Data.SqlClient: Heavily used for SQL Server interaction.
• System.Xml.Serialization: Used in TestHistory for serialization.
• System.IO: Used for file handling in DatabaseServices.

Consumers

• Any business logic layer requiring database access, sensor definitions, or test setup configurations.
• Migration scripts or tools targeting database version 52/53.

5. Gotchas

• Static Command Reuse: LocalOnlyOperations.GetSQLCommand() (without arguments) reuses a static cmd field. If multiple operations call this simultaneously or hold onto the command, they may interfere with each other. Always call GetSQLCommand(true) if a fresh command instance is required to avoid race conditions or parameter pollution.
• Legacy Schema Names: MMETables defines constants MyType, CustomChannelType, and Id with comments indicating they were exported in older versions (up to 1.3.515) and replaced by newer fields. Code handling imports or legacy data must account for these renamed columns.
• DateTime Formatting: LocalOnlyOperations.CreateParam converts DateTime objects to a formatted string (yyyy-MM-dd HH:mm:ss) when creating parameters, rather than passing the DateTime object directly to the SqlParameter.Value. This strips milliseconds and might cause precision loss or parsing issues depending on the server culture settings, though it ensures the value fits within SQL Server's valid range.
• ArrayList in TypeConvertor: TypeConvertor uses System.Collections.ArrayList instead of generic collections, indicating this is legacy code pre-dating .NET 2.0 generics. It involves boxing/unboxing overhead.
• Hardcoded Paths: DatabaseServices relies on Environment.CurrentDirectory and hardcoded folder names (db, SQL Server Scripts). The application must be run from the correct working directory for these methods to succeed.
• Connection Management in ExecuteCommand: LocalOnlyOperations.ExecuteCommand creates a new SqlConnection and opens it, ignoring the connection attached to the passed IDbCommand. This means the connection state checked/created in GetSQLCommand is discarded/overwritten in this specific execution path.