Design a Robust Domain Mapping Between Application Types and SQL Server Types

Learning outcomes

ServiceHub’s database schema is now strongly typed, but the application can still erase those guarantees at the boundary. A .NET string inferred as nvarchar is compared to a varchar indexed key, decimal precision is left to inference, timestamps lose offset policy, and optional values are sent as language null instead of database NULL. Production correctness therefore requires a domain mapping between application types, parameter metadata, SQL Server types, and serialization rules.

1. A domain type is more than a language primitive

A business amount is not merely a C# decimal or Python Decimal; it has an allowed range, precision, scale, currency policy, and rounding rule. A customer code is not merely a string; it can be ASCII-only varchar(16), Unicode nvarchar(16), case-sensitive or case-insensitive depending on collation, and possibly fixed-format. A timestamp needs a UTC/offset/time-zone contract.

2. Parameter metadata can change semantics and plans

Microsoft’s ADO.NET documentation explains that providers can infer parameter types from application values. For SQL Server, a .NET String is commonly inferred as nvarchar. That may be correct for a Unicode column, but Chapter 3 Lesson 3 showed why an inferred Unicode parameter against an intentional varchar key can introduce a conversion.

Do not make “never infer anything” another superstition; instead, explicitly specify metadata where the domain contract matters. Bounded strings should have a deliberate size. Decimal parameters should have deliberate precision/scale where the provider exposes them. Temporal parameters should use the corresponding SQL Server type and precision policy.

USE ServiceHubLab;GODECLARE @sql nvarchar(max) = N'SELECT work_order_id, statusFROM ops.WorkOrderWHERE customer_code = @customer_code  AND opened_at >= @from_utc;';EXEC sys.sp_executesql    @sql,    N'@customer_code varchar(16), @from_utc datetime2(0)',    @customer_code = 'CUST-001',    @from_utc = '2026-08-01T00:00:00';GO

This does not replace application-side binding; it demonstrates that parameter definitions are part of the compiled statement contract. The driver should send corresponding metadata rather than asking the server to repair a mismatch repeatedly.

3. Parameterize values; keep secrets out of source

Parameterized commands separate query text from value data, improve validation/type handling, and prevent a user value from becoming executable SQL text. They are also the normal route to plan reuse. Identifiers such as table/column names cannot generally be parameterized the same way; dynamic identifier SQL requires a separate allowlist/quoting design.

using Microsoft.Data.SqlClient;using System.Data;string cs = Environment.GetEnvironmentVariable("SERVICEHUB_SQL")    ?? throw new InvalidOperationException("SERVICEHUB_SQL is not set");await using var cn = new SqlConnection(cs);await cn.OpenAsync();const string sql = @"SELECT work_order_id, statusFROM ops.WorkOrderWHERE customer_code = @customer_code  AND opened_at >= @from_utc;";await using var cmd = new SqlCommand(sql, cn);cmd.Parameters.Add("@customer_code", SqlDbType.VarChar, 16).Value = "CUST-001";cmd.Parameters.Add("@from_utc", SqlDbType.DateTime2).Value =    new DateTime(2026, 8, 1, 0, 0, 0, DateTimeKind.Utc);await using var reader = await cmd.ExecuteReaderAsync();

The example reads its connection string from an environment variable rather than embedding a password. A production deployment should use its platform’s secret/configuration mechanism and current TLS/certificate validation settings. The lesson does not require .NET to complete the SQL lab; it uses C# only to make the client metadata boundary concrete.

4. Null, empty, zero and default are four different states

Application languages have their own null/optional abstractions. SQL Server has NULL. In ADO.NET, sending a database null uses DBNull.Value; a C# null reference is not automatically interchangeable in every API. The schema decides whether the column allows NULL. The business model decides whether empty string, zero, false, or a generated default means something different.

USE ServiceHubLab;GOSELECT    s.name AS schema_name,    t.name AS table_name,    c.name AS column_name,    TYPE_NAME(c.user_type_id) AS type_name,    c.max_length, c.precision, c.scale, c.is_nullableFROM sys.columns AS cJOIN sys.tables AS t ON t.object_id = c.object_idJOIN sys.schemas AS s ON s.schema_id = t.schema_idWHERE s.name = N'ops' AND t.name IN (N'Technician', N'WorkOrder')ORDER BY t.name, c.column_id;GO

For example, ops.WorkOrder.technician_id is nullable because an unassigned work order is meaningful. priority is not nullable because “no priority value” is not part of the established model. That difference should appear in the application type model too.

5. Time-zone and string policies must survive a round trip

If the application represents an instant in UTC, decide whether it will send datetime2 with a documented UTC convention or datetimeoffset with +00:00. If the user’s original offset matters, do not throw it away accidentally. If future scheduling depends on named zones and daylight-saving rules, store the zone identifier separately because datetimeoffset stores an offset, not the whole rule set.

For text, choose Unicode based on domain needs. Do not “optimize” a human name from nvarchar to varchar without encoding/collation analysis. Conversely, an intentional ASCII-style machine code can stay varchar if the application binds it as such.

6. JSON mapping is a feature-status and driver contract

On the stable course path, JSON payloads are strings stored in validated nvarchar(max). SQL Server 2025’s native json type is currently preview for SQL Server 2025, and Microsoft.Data.SqlClient documentation already describes JSON-specific client support such as SqlJson/SqlDbType.Json in supported .NET versions. That is exactly why application mapping must record server feature status and driver capability.

7. Deliberately wrong approach: AddWithValue/inference defines the schema contract

A developer passes every string using a convenience inference API, every decimal without fixed precision/scale, and every time value as the language’s default date-time object. The code passes tests, but production sees parameter-length/type variation, implicit conversions, rounded decimals, or time-zone ambiguity. The problem is not the API name itself; it is allowing incidental runtime values to decide persistent domain metadata.

8. Hands-on lab: expose a stored-procedure parameter contract

The mandatory lab stays entirely in SQL Server so every learner can complete it. It creates a disposable procedure whose parameters match the domain exactly, inspects that metadata, invokes it with typed values, and then removes it. Applications can use the same metadata as their binding specification.

USE ServiceHubLab;GOIF SCHEMA_ID(N'lab03') IS NULL EXEC(N'CREATE SCHEMA lab03 AUTHORIZATION dbo;');GOCREATE OR ALTER PROCEDURE lab03.FindWorkOrders    @customer_code varchar(16),    @from_utc datetime2(0),    @minimum_priority tinyint = 1ASBEGIN    SET NOCOUNT ON;    SELECT work_order_id, customer_code, status, priority, opened_at    FROM ops.WorkOrder    WHERE customer_code = @customer_code      AND opened_at >= @from_utc      AND priority >= @minimum_priority    ORDER BY opened_at, work_order_id;END;GOSELECT    p.parameter_id,    p.name,    TYPE_NAME(p.user_type_id) AS type_name,    p.max_length,    p.precision,    p.scale,    p.is_output,    p.has_default_valueFROM sys.parameters AS pWHERE p.object_id = OBJECT_ID(N'lab03.FindWorkOrders')ORDER BY p.parameter_id;GOEXEC lab03.FindWorkOrders    @customer_code = 'CUST-001',    @from_utc = '2026-08-01T00:00:00',    @minimum_priority = 1;GO

Verification checklist

• The server reports @customer_code as varchar(16), not an inferred Unicode/max string.
• The temporal parameter has the intended datetime2 scale.
• The application example uses parameterized values and an external connection-string source.
• You can distinguish SQL NULL from empty string/zero/default.
• You record that the native SQL Server 2025 JSON type is not a mandatory production mapping while it remains preview.

USE ServiceHubLab;GODROP PROCEDURE IF EXISTS lab03.FindWorkOrders;GO

9. Production judgment: version the boundary contract

Treat database/app type mappings as versioned interface definitions. A schema review should compare SQL type/length/precision/scale/nullability/collation to stored-procedure parameters, ORM model annotations, serialization formats, and driver bindings. Integration tests should include maximum lengths, decimal boundaries, Unicode, NULL, invalid values, UTC/offset round trips, and representative indexed predicates.

Chapter 03 closes with one principle: a SQL value is never “just a value.” It has type, nullability, collation/encoding where textual, precision/scale where numeric, temporal semantics where time-based, and client metadata when crossing a protocol. Chapter 04 builds on that foundation with schema ownership, keys, constraints, identity/sequence strategies, computed columns, and temporal tables.

Authoritative references

• Configuring parameters in Microsoft.Data.SqlClient — parameter naming, type inference and explicit SqlDbType metadata
• SQL Server data type mappings for SqlClient — application-to-provider type mappings
• Date and time data with SqlClient — DateTime/DateTimeOffset parameter behavior
• JSON data type support in SqlClient — current client support for SQL Server JSON
• JSON data type — current SQL Server 2025 server feature status
• Data type precedence — why mismatched parameter types can cause implicit conversions