Best practices

This page distills practical recommendations for building reliable, secure, and high-performance applications with CaeriusNet. It complements the Quickstart, Reading data, Writing data, and Advanced usage guides.

Applies to: C# 14 / .NET 10, SQL Server 2019 +, Microsoft.Data.SqlClient.

Architecture and patterns

• Use the Repository pattern. Keep data access isolated behind interfaces — ICaeriusNetDbContext is the only external dependency repositories should know about.
• Inject ICaeriusNetDbContext via DI. Never instantiate it manually; the lifetime and connection management is handled by the framework.
• Favor sealed records. They are immutable, lightweight, value-equatable, and pair well with the source generators.
• Prefer source generators. The analyzer validates the contract and the generated code matches the mapper you would write by hand:

  [GenerateDto]
  public sealed partial record UserDto(int Id, string Name, byte? Age);
  
  [GenerateTvp(Schema = "dbo", TvpName = "tvp_int")]
  public sealed partial record UserIdTvp(int Id);

• Keep mapping out of services and controllers. Repositories own SQL; services orchestrate them.

DTO mapping

• Mapping is ordinal-based. The constructor parameter order must match the stored procedure SELECT column order. Aliases are cosmetic.
• Mark columns that may return NULL as nullable in the DTO; the source generator emits IsDBNull guards automatically.
• Keep DTOs purpose-specific. Avoid catch-all DTOs that grow with every screen.
• Manual mapping example for reference:

  public sealed record UserDto(int Id, string Name, byte? Age) : ISpMapper<UserDto>
  {
      public static UserDto MapFromDataReader(SqlDataReader reader)
          => new(
              reader.GetInt32(0),
              reader.GetString(1),
              reader.IsDBNull(2) ? null : reader.GetByte(2));
  }

Stored procedure conventions

• Use dedicated schemas (Users, Orders, Sales, …) instead of the default dbo for application code where feasible.
• Always start procedures with SET NOCOUNT ON; to avoid spurious result sets.
• Never SELECT *. Explicitly list columns in the order your DTO expects.
• Keep the result-set shape stable. Any change in column count, order, or type requires a matching DTO change.
• Prefer parameterized procedures for all inputs — avoid dynamic SQL unless absolutely required.
• Wrap multi-statement writes in BEGIN TRY / BEGIN CATCH with explicit COMMIT / ROLLBACK and THROW; for re-raise.
• Adopt a consistent name convention, e.g. Schema.sp_Action_Subject_By_Filter.

TVPs

• Define SQL types with the minimal columns needed.
• Use [GenerateTvp] for zero-boilerplate mappers.
• Ensure the .NET TVP record's primary-constructor parameters match the SQL column definition exactly (order, nullability, types).
• Pass TVPs READONLY (required by SQL Server).
• Validate non-empty input before calling — AddTvpParameter throws ArgumentException on empty collections.
• For very large sets, batch into reasonable chunks (e.g. 5 000–10 000 rows per call) to keep memory and CPU usage predictable.

Caching strategy

Pick the right tier per call via StoredProcedureParametersBuilder:

• Frozen — in-process, immutable, fastest. Only for data that truly never changes during the application lifetime (lookup tables, currency codes, permission definitions).
• InMemory — in-process with TTL. Good for hot paths where some staleness is acceptable. Always set a sensible expiration.
• Redis — distributed. Use in multi-instance deployments. Secure with TLS + auth in production.

Cache keys:

• Deterministic, short, descriptive (e.g. users:age:>=30).
• Include all parameters that affect the result.
• Lowercase, colon-separated.

Invalidation:

• Prefer time-based expiry for mutable data.
• For Frozen, only cache truly immutable data — there is no invalidation hook.

See Caching for the full guide.

Performance

• Set resultSetCapacity accurately. It pre-allocates the List<T> and avoids resize churn for large reads.
• Return only required columns. Less data = fewer allocations and faster TDS framing.
• Pick the right collection. ImmutableArray<T> for frozen / shared data; ReadOnlyCollection<T> for public APIs; IEnumerable<T> for LINQ pipelines.
• Combine related result sets with the QueryMultiple*Async families rather than chaining separate stored-procedure calls.
• Benchmark critical flows. CaeriusNet's own BenchmarkDotNet suites are reproducible (Random(42)). Use them as a baseline before tuning application code.
• Keep hot paths direct. Avoid adding extra mapping layers around CaeriusNet calls unless they remove real duplication.
• Use ValueTask naturally. Await CaeriusNet calls directly. Convert to Task only when another API explicitly requires it.

Transactions

• Use await using so DisposeAsync runs on every code path — including exceptions.
• Pass CancellationToken everywhere; it cancels in-flight SQL commands.
• Keep transactions short. They hold locks; long transactions block other readers and writers.
• Don't perform non-database I/O (HTTP calls, file writes, log shipping) inside a transaction scope.
• Use the lowest isolation level that meets your consistency requirements (ReadCommitted is the default).
• Retry the entire transaction at the caller level if the scope poisons — never attempt partial recovery.

// ✅ Short, focused, cancellable
await using var tx = await dbContext
    .BeginTransactionAsync(IsolationLevel.ReadCommitted, ct);

await tx.ExecuteNonQueryAsync(spDebit,  ct);
await tx.ExecuteNonQueryAsync(spCredit, ct);
await tx.CommitAsync(ct);

Logging

• Configure ILoggerFactory before CaeriusNetBuilder.Build() — DI then wires the logger automatically.
• Filter by event ID category to control verbosity per subsystem:
  • CaeriusNet.Cache → Warning in production
  • CaeriusNet.Commands → Information to keep timing visible
• Use structured sinks (Seq, Elasticsearch, OTLP) to leverage CaeriusNet's named placeholders ({ProcedureName}, {Duration}, {RowCount}).
• Set up alerts on event ID 5003 (slow execution) and 5004 (command failure) for production monitoring.

builder.Services.AddLogging(logging =>
{
    logging.AddFilter("CaeriusNet.Cache",    LogLevel.Warning);
    logging.AddFilter("CaeriusNet.Commands", LogLevel.Information);
});

See Logging and observability for the complete event ID reference.

Async, cancellation, reliability

• All APIs are asynchronous by design — never block on async calls (.Result, .Wait()).
• Propagate CancellationToken from the request boundary down to every database call.
• Configure reasonable command and connection timeouts in your connection string.
• The library opens, uses, and disposes connections automatically — do not manage SqlConnection instances manually.

Error handling

Issue	Likely cause	Fix
InvalidCastException at runtime	Reader method or DTO type doesn't match SQL column type	Align the reader call or DTO field type with the actual SQL type
IndexOutOfRangeException	DTO has more parameters than the stored procedure returns columns	Recheck the SELECT column count and DTO parameter count
Aspire connection failure	WithAspireSqlServer("name") does not match AppHost name	Cross-check the resource name in the AppHost
TVP type mismatch	Schema/TvpName diverge between SQL and .NET, or column order is wrong	Re-align [GenerateTvp] arguments and constructor params
Cache miss when hit was expected	Different keys per call	Build keys deterministically from inputs
Memory growth (Frozen)	Frozen used for non-static data	Switch to InMemory with TTL

Security

• Use stored procedures with parameters — no string concatenation, no dynamic SQL.
• Never cache sensitive data (passwords, tokens, raw PII) without explicit threat modeling.
• Secure Redis with TLS and authentication; restrict network access.
• Grant the application user the minimum required SQL permissions (EXECUTE on the stored procedure schema, no dbo).

Versioning and migrations

• Treat stored procedures and DTOs as a contract. Version them when breaking changes are needed, for example sp_GetUsers to sp_GetUsers_v2.
• Add new procedures alongside existing ones; deprecate old ones once consumers migrate.

Quick reference

// Read with caching
var sp = new StoredProcedureParametersBuilder("Users", "usp_Get_All_Users", 250)
    .AddInMemoryCache("users:all", TimeSpan.FromMinutes(2))
    .Build();

var users = await dbContext.QueryAsReadOnlyCollectionAsync<UserDto>(sp, ct);

// Update with affected rows
var sp = new StoredProcedureParametersBuilder("dbo", "sp_UpdateUserAge_By_Guid")
    .AddParameter("Guid", guid, SqlDbType.UniqueIdentifier)
    .AddParameter("Age",  age,  SqlDbType.TinyInt)
    .Build();

var rows = await dbContext.ExecuteNonQueryAsync(sp, ct);

// TVP read
var tvp = userIds.Select(id => new UserIdTvp(id));
var sp  = new StoredProcedureParametersBuilder("dbo", "sp_GetUsers_By_Tvp_Ids", 256)
    .AddTvpParameter("Ids", tvp)
    .Build();

var users = await dbContext.QueryAsIEnumerableAsync<UserDto>(sp, ct);

---

Use this page as a checklist when designing new queries or auditing existing ones. For deeper APIs and patterns, see API reference, Advanced usage, and the Examples.