340 lines
11 KiB
Markdown
340 lines
11 KiB
Markdown
# Data Access Pattern
|
|
|
|
## When to Use
|
|
- Retrieving or storing data in SQL Server database
|
|
- Implementing data layer for business entities
|
|
- Creating CRUD operations for domain objects
|
|
|
|
## Architecture Overview
|
|
|
|
```
|
|
┌─────────────────┐ ┌─────────────────┐ ┌─────────────────┐
|
|
│ ViewModel │────>│ DbAPI │────>│ SQL Server │
|
|
│ (UI Layer) │ │ (Data Layer) │ │ (Database) │
|
|
└─────────────────┘ └─────────────────┘ └─────────────────┘
|
|
│
|
|
├── Connections/
|
|
├── Sensors/
|
|
├── Groups/
|
|
├── TestSetups/
|
|
└── Channels/
|
|
```
|
|
|
|
## Files to Create/Modify
|
|
|
|
### Structure
|
|
```
|
|
DbAPI/
|
|
├── DbAPI.cs (Main facade class)
|
|
├── Connections/
|
|
│ └── ConnectionManager.cs (Connection handling)
|
|
├── {Entity}/
|
|
│ └── {Entity}.cs (Data access implementation)
|
|
└── Errors/
|
|
└── ErrorCodes.cs (Error code constants)
|
|
```
|
|
|
|
## Code Templates
|
|
|
|
### 1. Interface Definition
|
|
**File:** `DTS.Common/Interface/{Entity}/I{Entity}.cs`
|
|
```csharp
|
|
using DTS.Common.Interface.Database;
|
|
|
|
namespace DTS.Common.Interface.{Entity}
|
|
{
|
|
public interface I{Entity}
|
|
{
|
|
/// <summary>
|
|
/// Gets all {entity} records
|
|
/// </summary>
|
|
/// <param name="user">User making the request</param>
|
|
/// <param name="connection">Database connection details</param>
|
|
/// <param name="records">Output array of records</param>
|
|
/// <returns>Error code (0 = success)</returns>
|
|
ulong {Entity}Get(IUserDbRecord user, IConnectionDetails connection,
|
|
out I{Entity}Record[] records);
|
|
|
|
/// <summary>
|
|
/// Adds a new {entity} record
|
|
/// </summary>
|
|
ulong {Entity}Add(IUserDbRecord user, IConnectionDetails connection,
|
|
I{Entity}Record record, out int newId);
|
|
|
|
/// <summary>
|
|
/// Updates an existing {entity} record
|
|
/// </summary>
|
|
ulong {Entity}Update(IUserDbRecord user, IConnectionDetails connection,
|
|
I{Entity}Record record);
|
|
|
|
/// <summary>
|
|
/// Deletes a {entity} record
|
|
/// </summary>
|
|
ulong {Entity}Delete(IUserDbRecord user, IConnectionDetails connection, int id);
|
|
}
|
|
}
|
|
```
|
|
|
|
### 2. Data Access Implementation
|
|
**File:** `DbAPI/{Entity}/{Entity}.cs`
|
|
```csharp
|
|
using DbAPI.Connections;
|
|
using DbAPI.Errors;
|
|
using DbAPI.Logging;
|
|
using DTS.Common.Interface.Database;
|
|
using DTS.Common.Interface.{Entity};
|
|
using DTS.Common.Utilities.Logging;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Data.SqlClient;
|
|
using System.Diagnostics;
|
|
|
|
namespace DbAPI.{Entity}
|
|
{
|
|
internal class {Entity} : I{Entity}
|
|
{
|
|
private const int DB_VERSION_{FEATURE} = 100;
|
|
|
|
public ulong {Entity}Get(IUserDbRecord user, IConnectionDetails connection,
|
|
out I{Entity}Record[] records)
|
|
{
|
|
records = new I{Entity}Record[0];
|
|
var list = new List<I{Entity}Record>();
|
|
|
|
if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
|
|
{
|
|
return ErrorCodes.ERROR_ACCESS_DENIED;
|
|
}
|
|
|
|
var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_{Entity}Get");
|
|
if (ret != ErrorCodes.ERROR_SUCCESS) return ret;
|
|
|
|
try
|
|
{
|
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
var reader = cmd.ExecuteReader();
|
|
|
|
while (reader.Read())
|
|
{
|
|
list.Add(new {Entity}Record(reader));
|
|
}
|
|
|
|
records = list.ToArray();
|
|
return ErrorCodes.ERROR_SUCCESS;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogManager.Log(TraceEventType.Error, LogManager.LogEvents.{Entity},
|
|
$"sp_{Entity}Get failed: {ex.Message}");
|
|
return ErrorCodes.ERROR_UNKNOWN;
|
|
}
|
|
finally
|
|
{
|
|
cmd.Connection.Dispose();
|
|
}
|
|
}
|
|
|
|
public ulong {Entity}Add(IUserDbRecord user, IConnectionDetails connection,
|
|
I{Entity}Record record, out int newId)
|
|
{
|
|
newId = 0;
|
|
|
|
if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
|
|
return ErrorCodes.ERROR_ACCESS_DENIED;
|
|
|
|
var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_{Entity}Add");
|
|
if (ret != ErrorCodes.ERROR_SUCCESS) return ret;
|
|
|
|
try
|
|
{
|
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
|
|
cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar) { Value = record.Name });
|
|
|
|
var idParam = new SqlParameter("@Id", SqlDbType.Int)
|
|
{
|
|
Direction = ParameterDirection.Output
|
|
};
|
|
cmd.Parameters.Add(idParam);
|
|
|
|
cmd.ExecuteNonQuery();
|
|
newId = (int)idParam.Value;
|
|
|
|
return ErrorCodes.ERROR_SUCCESS;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogManager.Log(TraceEventType.Error, LogManager.LogEvents.{Entity},
|
|
$"sp_{Entity}Add failed: {ex.Message}");
|
|
return ErrorCodes.ERROR_UNKNOWN;
|
|
}
|
|
finally
|
|
{
|
|
cmd.Connection.Dispose();
|
|
}
|
|
}
|
|
|
|
protected void AddNullableIntParameter(SqlCommand cmd, string paramName, int? value)
|
|
{
|
|
cmd.Parameters.Add(new SqlParameter(paramName, SqlDbType.Int)
|
|
{
|
|
Value = value.HasValue ? (object)value.Value : DBNull.Value
|
|
});
|
|
}
|
|
|
|
protected void AddNullableStringParameter(SqlCommand cmd, string paramName, string value)
|
|
{
|
|
cmd.Parameters.Add(new SqlParameter(paramName, SqlDbType.NVarChar)
|
|
{
|
|
Value = string.IsNullOrEmpty(value) ? (object)DBNull.Value : value
|
|
});
|
|
}
|
|
}
|
|
}
|
|
```
|
|
|
|
### 3. Register in DbAPI.cs Facade
|
|
**File:** `DbAPI/DbAPI.cs`
|
|
```csharp
|
|
private readonly {Entity}.{Entity} _{entity} = new {Entity}.{Entity}();
|
|
|
|
public static I{Entity} {Entity}
|
|
{
|
|
get => _instance._{entity};
|
|
}
|
|
```
|
|
|
|
## Examples from Codebase
|
|
|
|
### Example 1: DbAPI Facade
|
|
**File:** `DataPRO/DbAPI/DbAPI.cs:23`
|
|
```csharp
|
|
public class DbAPI
|
|
{
|
|
private static readonly DbAPI _instance = new DbAPI();
|
|
private readonly ConnectionManager _connectionManager = new ConnectionManager();
|
|
|
|
public static IConnections Connections => _instance._connectionManager;
|
|
|
|
private readonly Sensors.Sensors _sensors = new Sensors.Sensors();
|
|
public static ISensors Sensors => _instance._sensors;
|
|
|
|
private readonly TestSetups.TestSetups _testSetups = new TestSetups.TestSetups();
|
|
public static ITestSetups TestSetups => _instance._testSetups;
|
|
|
|
private readonly Groups.Groups _groups = new Groups.Groups();
|
|
public static IGroups Groups => _instance._groups;
|
|
}
|
|
```
|
|
|
|
### Example 2: Sensor Data Access
|
|
**File:** `DataPRO/DbAPI/Sensors/Sensors.cs:26`
|
|
```csharp
|
|
internal class Sensors : ISensors
|
|
{
|
|
public ulong SensorsAnalogDiagnosticsGet(IUserDbRecord user, IConnectionDetails connection,
|
|
long? Id, long? diagnosticRunId, int? sensorId, string sensorSerialNumber,
|
|
out IDiagnosticEntry[] records)
|
|
{
|
|
records = new IDiagnosticEntry[0];
|
|
var list = new List<IDiagnosticEntry>();
|
|
|
|
if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
|
|
return ErrorCodes.ERROR_ACCESS_DENIED;
|
|
|
|
var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_AnalogDiagnosticsGet");
|
|
if (ret != ErrorCodes.ERROR_SUCCESS) return ret;
|
|
|
|
try
|
|
{
|
|
AddNullableBigIntParameter(cmd, "@Id", Id);
|
|
AddNullableIntParameter(cmd, "@SensorId", sensorId);
|
|
|
|
var reader = cmd.ExecuteReader();
|
|
while (reader.Read())
|
|
{
|
|
list.Add(new DiagnosticEntry(reader));
|
|
}
|
|
records = list.ToArray();
|
|
return ErrorCodes.ERROR_SUCCESS;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors,
|
|
$"sp_AnalogDiagnosticsGet failed, {ex.Message}");
|
|
return ErrorCodes.ERROR_UNKNOWN;
|
|
}
|
|
finally
|
|
{
|
|
cmd.Connection.Dispose();
|
|
}
|
|
}
|
|
}
|
|
```
|
|
|
|
### Example 3: Connection Manager Usage
|
|
**File:** `DataPRO/DbAPI/DbAPI.cs:104`
|
|
```csharp
|
|
public static ulong GetDatabaseVersion(IConnectionDetails connection, out int serverDbVersion)
|
|
{
|
|
serverDbVersion = 0;
|
|
var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_DbVersionGet");
|
|
if (ret != ErrorCodes.ERROR_SUCCESS) return ret;
|
|
|
|
try
|
|
{
|
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
var reader = cmd.ExecuteReader();
|
|
var dbVersionsList = new List<int>();
|
|
while (reader.Read())
|
|
{
|
|
var version = Convert.ToInt32(reader["Version"]);
|
|
dbVersionsList.Add(version);
|
|
}
|
|
serverDbVersion = dbVersionsList.Max();
|
|
return ErrorCodes.ERROR_SUCCESS;
|
|
}
|
|
finally
|
|
{
|
|
cmd.Connection.Dispose();
|
|
}
|
|
}
|
|
```
|
|
|
|
### Example 4: Stored Procedure Version Check
|
|
**File:** `DataPRO/DbAPI/DbAPI.cs:57`
|
|
```csharp
|
|
public static ulong GetStoredProcedureToUseCached(IConnectionDetails connection,
|
|
string storedProcedure, int clientDbVersion, out int storedProcedureVersionToUse)
|
|
{
|
|
lock (StoredProcedureLock)
|
|
{
|
|
if (_spLookup.ContainsKey(storedProcedure))
|
|
{
|
|
var match = _spLookup[storedProcedure].Find(sp =>
|
|
sp.ClientVersion == clientDbVersion && sp.DbVersion == connection.ConnectionDbVersion);
|
|
if (null != match)
|
|
{
|
|
storedProcedureVersionToUse = match.StoredProcedureVersion;
|
|
return ErrorCodes.ERROR_SUCCESS;
|
|
}
|
|
}
|
|
}
|
|
// ... determine version and cache it
|
|
}
|
|
```
|
|
|
|
## Common Mistakes to Avoid
|
|
|
|
1. **Not disposing SqlCommand connection** - Memory leak; always use `finally { cmd.Connection.Dispose(); }`
|
|
2. **Missing authentication check** - Always verify `IsUserLoggedIn(user, connection)` first
|
|
3. **Hardcoded stored procedure names** - Consider versioning for DB migrations
|
|
4. **Not handling DBNull** - Use nullable parameters with DBNull.Value conversion
|
|
5. **Swallowing exceptions** - Always log errors before returning error code
|
|
6. **Using dynamic SQL** - Always use parameterized stored procedures
|
|
7. **Not checking DB version** - Some features require minimum DB version
|
|
8. **Returning null instead of empty array** - Initialize arrays as `new T[0]`
|
|
9. **Missing error output parameter** - Some SPs require `@ErrorNumber` output
|
|
10. **Not caching SP version lookups** - Use `GetStoredProcedureToUseCached()` for performance
|