187 lines
11 KiB
Markdown
187 lines
11 KiB
Markdown
---
|
||
source_files:
|
||
- DataPRO/DbAPI/Connections/IConnectionDetails.cs
|
||
- DataPRO/DbAPI/Connections/ConnectionDetails.cs
|
||
- DataPRO/DbAPI/Connections/IConnections.cs
|
||
- DataPRO/DbAPI/Connections/ConnectionManager.cs
|
||
generated_at: "2026-04-16T04:26:39.468072+00:00"
|
||
model: "Qwen/Qwen3-Coder-Next-FP8"
|
||
schema_version: 1
|
||
sha256: "ce0c80cfa1afa7c7"
|
||
---
|
||
|
||
# Documentation: Connection Management Module
|
||
|
||
## 1. Purpose
|
||
|
||
This module provides core database connection and user authentication functionality for the DataPRO system, abstracting both remote SQL Server connections and local SQL Server LocalDB instance management. It defines interfaces and implementations for establishing database connections (including dynamic LocalDB instance lifecycle management), authenticating users via password or pre-hashed credentials, and tracking active connections and logged-in users. The module serves as the foundational layer for all database interactions in the system, ensuring consistent connection handling, user session tracking, and error reporting.
|
||
|
||
## 2. Public Interface
|
||
|
||
### `IConnectionDetails` Interface
|
||
|
||
- **`bool UseNTLMAuthentication { get; set; }`**
|
||
Indicates whether to use Windows Authentication (NTLM) instead of SQL authentication. Defaults to `true`.
|
||
|
||
- **`string DbUser { get; set; }`**
|
||
Username for SQL authentication. Defaults to `"DataPROUser"`.
|
||
|
||
- **`string DbUserPassword { get; set; }`**
|
||
Password for SQL authentication. Defaults to `"DTSSealBeachHQ"`.
|
||
|
||
- **`string InstanceName { get; set; }`**
|
||
Name of the LocalDB instance to use. Defaults to `"DataPROInstance"`.
|
||
|
||
- **`string DbServer { get; set; }`**
|
||
Server name for remote DB connections. Defaults to `@"(localdb)\DataPROInstance"`.
|
||
|
||
- **`string DbName { get; set; }`**
|
||
Name of the database to connect to. Defaults to `"DataPRO"`.
|
||
|
||
- **`bool UsingCentralizedDb { get; set; }`**
|
||
`true` if connecting to a centralized (remote) database; `false` for LocalDB.
|
||
|
||
- **`string DbFolderPath { get; set; }`**
|
||
Path to directory containing `.mdf` and `.ldf` files for LocalDB attachment.
|
||
|
||
- **`string AttachDbsBatPath { get; set; }`**
|
||
Full path to `attach.bat` script used to attach databases via `sqlcmd.exe`.
|
||
|
||
- **`string ODBCToolsPath { get; set; }`**
|
||
Path to directory containing `sqlcmd.exe`.
|
||
|
||
- **`string SqlDbPath { get; set; }`**
|
||
Path to directory containing `SqlLocalDB.exe`.
|
||
|
||
- **`int ClientDbVersion { get; set; }`**
|
||
Database version expected by the client (code-defined). Must be initialized by clients.
|
||
|
||
- **`int ConnectionDbVersion { get; set; }`**
|
||
Database version actually in use (e.g., from metadata). Must be initialized by clients.
|
||
|
||
- **`string GetConnectionString()`**
|
||
Returns a connection string for `SqlClient`. Uses Windows Auth if `UseNTLMAuthentication` is `true`; otherwise uses SQL auth with `DbUser`/`DbUserPassword`. Caches result in internal `_Connection` field.
|
||
|
||
- **`IConnectionDetails Clone()`**
|
||
Returns a deep copy of the current instance.
|
||
|
||
### `ConnectionDetails` Class
|
||
|
||
- **`ConnectionDetails()`**
|
||
Default constructor. Initializes all properties to their default values.
|
||
|
||
- **`ConnectionDetails(ConnectionDetails details)`**
|
||
Copy constructor. Performs deep copy of all fields, including `_Connection`.
|
||
|
||
- **`string GetConnectionString()`**
|
||
Overrides interface method. Returns cached or newly generated connection string.
|
||
|
||
- **`string ToString()`**
|
||
Returns `"Server\DbName"` if `UsingCentralizedDb` is `true`; otherwise `"Local\DbName"`.
|
||
|
||
### `IConnections` Interface
|
||
|
||
- **`IConnectionDetails[] GetActiveConnections()`**
|
||
Returns array of all currently active `IConnectionDetails` instances.
|
||
|
||
- **`ulong LoginUserHash(IConnectionDetails connection, string user, string hash, out IUserDbRecord userObject)`**
|
||
Authenticates user using a pre-hashed password (SHA256 base64-encoded). Returns `ERROR_SUCCESS` on success, `ERROR_LOGINFAILED` if hash mismatch, `ERROR_UNKNOWN` on exception.
|
||
|
||
- **`ulong LoginUser(IConnectionDetails connection, string user, string password, out IUserDbRecord userObject)`**
|
||
Authenticates user using plaintext password. Computes SHA256 hash of `"{password}_{user}"`, base64-encodes it, and compares to stored hash. Returns same error codes as `LoginUserHash`.
|
||
|
||
- **`ulong ConnectToDb(IConnectionDetails details)`**
|
||
Establishes database connection. For LocalDB (`UsingCentralizedDb == false`), manages instance lifecycle: stops/deletes/creates/starts instance, then attaches databases. Returns `ERROR_SUCCESS` on success, `ERROR_UNKNOWN` on failure.
|
||
|
||
- **`bool IsUserLoggedIn(IUserDbRecord user, IConnectionDetails connection)`**
|
||
Returns `true` if `user` is logged in to `connection`.
|
||
|
||
- **`Tuple<IUserDbRecord, IConnectionDetails>[] GetLoggedInUsers()`**
|
||
Returns array of all logged-in user/connection pairs.
|
||
|
||
- **`void ClearConnections()`**
|
||
Removes all logged-in users and all active connections.
|
||
|
||
### `ConnectionManager` Class *(Internal)*
|
||
|
||
- **`ulong ConnectToDb(IConnectionDetails details)`**
|
||
Public entry point for `IConnections.ConnectToDb`. Wraps `Connect()` in logging and exception handling.
|
||
|
||
- **`IConnectionDetails[] GetActiveConnections()`**
|
||
Returns snapshot of active connections.
|
||
|
||
- **`ulong LoginUserHash(...)` / `ulong LoginUser(...)`**
|
||
Implement `IConnections` methods. `LoginUser` computes SHA256 hash of `"{password}_{user}"` (UTF-8 encoded) and compares to `foundUser.Password`.
|
||
|
||
- **`bool IsUserLoggedIn(...)`**
|
||
Checks `_loggedInUsers` list for matching user/connection pair.
|
||
|
||
- **`Tuple<IUserDbRecord, IConnectionDetails>[] GetLoggedInUsers()`**
|
||
Returns snapshot of `_loggedInUsers`.
|
||
|
||
- **`void ClearConnections()`**
|
||
Clears `_loggedInUsers` and `_connections` lists.
|
||
|
||
- **`private ulong Connect(IConnectionDetails details)`**
|
||
Core connection logic: routes to `ConnectRemote` or `ConnectLocal`, clones details, and adds to `_connections` on success.
|
||
|
||
- **`private ulong ConnectLocal(IConnectionDetails details)`**
|
||
Manages LocalDB instance lifecycle:
|
||
1. Stops existing instance (ignores "instance doesn’t exist" errors)
|
||
2. Deletes existing instance (ignores "instance doesn’t exist" errors)
|
||
3. Creates new instance
|
||
4. Starts instance
|
||
5. Attaches databases (`DataPRO` and `ISO`)
|
||
Returns `ERROR_SUCCESS` or `ERROR_UNKNOWN`.
|
||
|
||
- **`private void AttachDatabases(IConnectionDetails details, LogDelegate log)`**
|
||
Calls `AttachDatabase` for `details.DbName` and `"ISO"`.
|
||
|
||
- **`private static void AttachDatabase(...)`**
|
||
Executes `attach.bat` script with parameters: `dbName`, `dbFileName`, `logFileName`, `fullSqlcmdPath`. Throws `Exception` on non-empty result.
|
||
|
||
- **`internal static ulong GetSqlCommand(IConnectionDetails con, out SqlCommand cmd, string commandText = "")`**
|
||
Helper to create/open `SqlCommand` using `con.GetConnectionString()`. Returns `ERROR_SUCCESS` or `ERROR_UNKNOWN`.
|
||
|
||
## 3. Invariants
|
||
|
||
- **Connection String Caching**: `GetConnectionString()` caches the result in `_Connection` and reuses it on subsequent calls.
|
||
- **LocalDB Instance Lifecycle**: For LocalDB connections, `ConnectLocal` *always* stops, deletes, creates, and starts the instance—regardless of prior state—except when "instance doesn’t exist" errors occur (which are silently ignored).
|
||
- **User Authentication Hashing**: Passwords are hashed as `SHA256(UTF8("{password}_{user}"))` and stored/compared as base64 strings.
|
||
- **Thread Safety**: `_connections` and `_loggedInUsers` are accessed under `CONNECT_LOCK` and `LOGIN_LOCK` respectively. `ProcessSqlLocalDbCommand` and `BatchCommandProcessor` use `PROCESS_LOCK`.
|
||
- **Database Attachment**: Only two databases are attached: `details.DbName` and `"ISO"`.
|
||
- **Error Codes**:
|
||
- `ERROR_SUCCESS` (0) = success
|
||
- `ERROR_LOGINFAILED` = authentication failure
|
||
- `ERROR_UNKNOWN` = unexpected error
|
||
- `ERROR_MISSING_PARAMETER` = null/empty `user` or `connection` in `LoginUser`
|
||
|
||
## 4. Dependencies
|
||
|
||
### Dependencies *on* this module:
|
||
- **`DbAPI.User.User`**: Used in `LoginUser`/`LoginUserHash` to retrieve user records (`User.GetUser`).
|
||
- **`DTS.Common.Interface.Database.IUserDbRecord`**: Interface for user records used in login/session tracking.
|
||
- **`DTS.Common.Utils.Database.SqlServerLocalDbException`**: Custom exception type used to signal LocalDB-specific errors (e.g., instance not found).
|
||
- **`DTS.Common.Utils.Database.LogDelegate`**: Logging delegate used in LocalDB command execution.
|
||
- **`DbAPI.Errors.ErrorCodes`**: Defines error constants (`ERROR_SUCCESS`, `ERROR_LOGINFAILED`, etc.).
|
||
- **`DbAPI.Logging.LogManager`**: Used for logging connection/login events.
|
||
|
||
### Dependencies *of* this module:
|
||
- **`System.Data.SqlClient`**: Used for `SqlConnection`/`SqlCommand`.
|
||
- **`System.Diagnostics`**: Used for `Process` management (LocalDB/attach.bat).
|
||
- **`System.IO`**: Used for file operations (`Path.Combine`, `FileInfo`).
|
||
- **`System.Security.Cryptography`**: Used for `SHA256` hashing in `LoginUser`.
|
||
- **`DTS.Common.Utilities.Logging.APILogger`**: Used as logging target in `ConnectLocal`.
|
||
|
||
## 5. Gotchas
|
||
|
||
- **Hardcoded Credentials**: Default `DbUser` and `DbUserPassword` are hardcoded (`"DataPROUser"` / `"DTSSealBeachHQ"`). These should be overridden in production.
|
||
- **SHA256 Hash Format**: Password hashing uses `SHA256("{password}_{user}")`—*not* standard salted hashing. The user is appended to the salted password string, which is non-standard and may be a security risk.
|
||
- **LocalDB Instance Destruction**: `ConnectLocal` *destroys* the LocalDB instance on every connection attempt. This may cause data loss if `.mdf`/`.ldf` files are not persisted or backed up.
|
||
- **Missing Validation**: `ConnectionDetails` does not validate required fields (e.g., `DbFolderPath`, `AttachDbsBatPath`, `ODBCToolsPath`, `SqlDbPath`) before attempting LocalDB operations. `ConnectLocal` may fail with cryptic errors if these are empty.
|
||
- **`GetSqlCommand` Opens Connection**: `GetSqlCommand` opens the SQL connection but does *not* dispose it. Callers must manage connection lifetime.
|
||
- **`Clone()` Shallow Copy of `_Connection`**: The copy constructor copies `_Connection` directly, but since it’s a string (immutable), this is safe. However, if `_Connection` were mutable, this could be a bug.
|
||
- **`ConnectRemote` Does Nothing**: `ConnectRemote` currently returns `ERROR_SUCCESS` without performing any actual connection logic. Remote DB connections are effectively unimplemented.
|
||
- **`ProcessSqlLocalDbCommand` Uses Global `StringBuilder`**: Static `sb`/`sbErrors` are used in `SqlCommandProcessor` and `BatchCommandProcessor`. While protected by `PROCESS_LOCK`, this design is fragile and not thread-safe across *other* callers of these methods.
|
||
- **`AttachDatabase` Ignores Missing `sqlcmd.exe`**: Logs a warning if `sqlcmd.exe` is missing but proceeds to execute the batch file anyway, likely causing failure.
|
||
- **No Timeout Handling**: No explicit timeout configuration for `Process.WaitForExit()` or SQL connections—could hang indefinitely on hung operations. |