--- 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-16T14:09:58.168174+00:00" model: "zai-org/GLM-5-FP8" schema_version: 1 sha256: "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 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.