104 lines
8.4 KiB
Markdown
104 lines
8.4 KiB
Markdown
---
|
|
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<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. |