# Streaming JSON and XML Serialization from DbDataReader

> A provider-independent C# design that writes DbDataReader results directly to JSON or XML using one-time schema compilation, typed getters, Utf8JsonWriter, and XmlWriter without materializing a second row graph.

- Author: Muhammet Ali Köker
- Language: en
- Canonical: https://alikoker.com.tr/en/streaming-json-xml-serialization-from-dbdatareader
- Translation: https://alikoker.com.tr/dbdatareader-akiskan-json-xml-serilestirme
- Published: 2020-07-03T15:27:00+03:00
- Modified: 2026-08-26T23:04:00+03:00
- Verified: 2026-08-26T23:04:00+03:00
- Type: article

This implementation grew out of an older C# utility where the performance decision was made before JSON or XML entered the picture. The query result already arrived through a `DbDataReader`; materializing it into a `DataTable`, a DTO list, or one dictionary per row meant paying for a second representation before the first byte of output was written.

That extra representation affects more than peak memory. It adds row allocations, dictionary traversal, repeated name resolution and another complete walk over the data when the serializer finally sees the intermediate graph. Those structures are useful when an application actually needs them. For an export path whose only job is to move an executing query to a stream, they are often unnecessary work.

My historical `DbRow` type took a narrower route. It exposed whatever row the reader currently pointed at through an `IDictionary<string, object>`-like view, captured column names once, and advanced with `yield return` while reusing that view. The same rule later appeared in my enumerable CSV code: **prepare stable metadata once; keep the repeated row path limited to row-specific work.**

The publication version keeps that rule but makes two execution paths explicit:

1. `DbReaderEnumerable` provides a reusable `DbRowView` when callers actually need row-wise enumeration.
2. `DbReaderSerializer` skips that view for direct export and runs `Read() -> typed getter -> writer`.

This is intentionally not an ORM, a reflection mapper, or a general serialization framework. Its contract is much smaller: **accept an existing `DbDataReader`, preserve its forward-only nature, and write to the destination `Stream` with as little intermediate state as the format permits.**

## DataReader is already the right starting point

ADO.NET `DataReader` exposes a forward-only, read-only stream of database results. Microsoft documentation notes that results become available as the query executes and that, by default, only one row needs to be retained at a time on the client side.

For many export workloads, therefore, the following shape is unnecessarily indirect:

```text
DbDataReader
    -> DataTable
    -> List<Row>
    -> JSON/XML serializer
```

The path I prefer is:

```text
DbDataReader
    -> schema cache
    -> current row
    -> JSON/XML writer
```

As the result set grows, the important property of the second path becomes clear: auxiliary memory does not have to grow with the complete row count.

## The historical Db Enumerable idea

In the first version, `DbRow` captured column names in its constructor. The enumerator did not allocate a new `Dictionary` for each row. Instead, it returned the same `DbRow` instance while the underlying reader advanced.

The idea was useful, but the historical source was too provider-specific. `DbRow` directly stored an `OleDbDataReader` even though the ODBC, MySQL, and Oracle enumerable classes attempted to use the same row type. The `IDictionary` contract also exposed mutation members that were meaningless for a forward-only reader, so many of them naturally threw `NotSupportedException`.

The publication version makes the contract explicit:

- it uses the provider-independent `DbDataReader` base class;
- the row view implements `IReadOnlyDictionary<string, object?>`;
- the enumerable is explicitly **single-pass**;
- one `DbRowView` object is reused across rows;
- the column-name-to-ordinal lookup is built once;
- duplicate column names are rejected instead of being overwritten silently, with SQL aliases required to disambiguate them.

This removes SQL Server, Oracle, MySQL, ODBC, and Ole DB client dependencies from the core. If the calling provider exposes a `DbDataReader`, the same serialization path can be used.

## Compile the schema once

After the reader is open, the number of columns, names, and CLR field types do not change between rows. They should not be rediscovered for every record.

Initialization prepares the following metadata for each ordinal:

```text
ordinal
column name
pre-encoded JSON property name
XML-safe element name
CLR field type
value-writer category
```

On the JSON path, property names are encoded once as `JsonEncodedText`. This is a small but meaningful optimization: Microsoft explicitly recommends pre-encoding known property names with `JsonEncodedText` when using `Utf8JsonWriter` for best performance.

On the XML path, the column name is converted once through `XmlConvert.EncodeLocalName`, so names containing spaces or other characters outside XML name rules are not normalized repeatedly for every row.

Schema preparation is approximately:

```text
O(C)
```

where `C` is the number of columns.

The repeated path becomes:

```text
while (reader.Read())
    for each column
        null?
        typed getter
        writer
```

## JSON without an intermediate object graph

`Utf8JsonWriter` is .NET's forward-only, non-cached writer for UTF-8 JSON. `JsonSerializer` itself uses this low-level writer internally, but when the source is a database result whose schema is already known, an intermediate object graph is often unnecessary.

The current path is direct:

```csharp
using DbDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
DbReaderSerializer.WriteJson(reader, outputStream);
```

The output is a normal JSON array:

```json
[
  {"id":1,"name":"..."},
  {"id":2,"name":"..."}
]
```

There is no `Dictionary<string, object>` allocation per row. There is no DTO reflection. A complete JSON `string` is not built and later converted to UTF-8. The writer targets the destination `Stream` directly.

### Preserve JSON types

Calling `ToString()` for every database value is easy but destroys JSON type semantics. Numbers, booleans, and null values become strings.

The schema therefore classifies common CLR types once:

- `string`
- `bool`
- signed and unsigned integer types
- `float`, `double`, `decimal`
- `DateTime`, `DateTimeOffset`
- `DateOnly`, `TimeOnly`, `TimeSpan`
- `Guid`
- `byte[]`

The hot loop switches on a precomputed small enum rather than comparing `Type` objects for every cell. Numbers remain JSON numbers, booleans remain booleans, `DBNull` becomes `null`, and `byte[]` is written as Base64.

If a provider exposes an unknown provider-specific CLR type, only that field falls back to an invariant string representation. This is a narrow compatibility path rather than converting the entire result set to strings.

### Contract details visible in the implementation

A few behaviors are deliberately explicit in code rather than hidden behind convenience defaults:

- `WriteJson` and `WriteXml` return the number of rows actually written.
- `leaveReaderOpen` defaults to `true`; the caller keeps ownership of the reader unless it explicitly delegates disposal.
- duplicate column names are rejected and the query must disambiguate them with aliases;
- missing/empty column names receive deterministic `column_<ordinal>` names;
- JSON property names and XML element names are compiled once from the schema rather than rebuilt per row.

These are small details, but they matter in long-running export code because ownership, ambiguous schemas and error behavior are part of performance reliability as much as allocation count.

## XML without building a DOM

The same principle applies to XML. Instead of constructing an `XmlDocument` or another DOM tree, the implementation uses `XmlWriter`. Microsoft describes `XmlWriter` as a fast, non-cached, forward-only API for producing XML streams or files.

```csharp
using DbDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
DbReaderSerializer.WriteXml(reader, outputStream);
```

The resulting shape is conceptually:

```xml
<rows xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
    <id>1</id>
    <name>...</name>
    <optional_note xsi:nil="true" />
  </row>
</rows>
```

A database `NULL` is not the same value as an empty string. XML output therefore represents database nulls explicitly with `xsi:nil="true"`.

## When the enumerable layer is useful

For direct JSON/XML export, `DbReaderSerializer` is the shortest path. The enumerable layer is still useful when callers need row-level logic:

```csharp
using var rows = reader.AsRows();
foreach (DbRowView row in rows)
{
    Console.WriteLine(row["id"]);
}
```

This is useful for:

- row filtering;
- statistics;
- custom formats;
- integration with existing `IEnumerable`-based code.

The important contract is that `DbRowView` is **not a snapshot**. The same object points at the new current reader row after each `MoveNext()`. It must not be stored in a list for later use. If a durable object is required, the caller should take an explicit snapshot at that boundary.

That behavior is the tradeoff for avoiding a row allocation and should remain visible in the API contract.

## Why a specialized writer can be faster than a generic serializer

A generic serializer has to discover what kind of object it receives. A dictionary requires key/value enumeration; a DTO requires property metadata; converters and naming policies can also participate.

The reader already exposes the information needed to write a field:

```text
column ordinal
column name
column CLR type
current value
```

For this source shape, I can turn the stable part of that information into a writer plan before entering the row loop instead of constructing another object model.

That is the direct connection to my earlier enumerable CSV implementation. CSV required a one-time header-to-property mapping and then a positional row path. Here there is no model property layer at all; the reader schema itself becomes the JSON/XML field plan.

## Complexity and memory behavior

Let `N` be the number of rows and `C` the number of columns.

Schema preparation:

```text
O(C)
```

Row serialization:

```text
O(N * C)
```

Every serializer must eventually visit the exported fields, so the objective is not to change the asymptotic complexity. The objective is to reduce repeated work and allocation inside the `N * C` hot path.

Auxiliary memory is approximately:

```text
O(C + writer buffer + current reader row)
```

The following structures do not grow with the full result set:

- `DataTable`;
- `List<T>`;
- dictionary-per-row collections;
- XML DOM;
- a complete in-memory JSON/XML text buffer.

Actual memory consumption still depends on the ADO.NET provider, network buffering, and the size of individual LOB values.

## Large BLOB/CLOB boundary

The current implementation is aimed at tabular scalar data and normally sized `byte[]` fields. Binary values are obtained from the provider and written as Base64.

For very large BLOB/CLOB fields, a separate streaming path is preferable:

```text
DbDataReader.GetStream / GetTextReader
    -> bounded buffer
    -> output writer
```

I did not automatically fold this into the generic core. A small binary field and a multi-gigabyte LOB have different operational contracts and should not be hidden behind the same convenience behavior.

## Database security remains a separate concern

The historical provider-specific wrappers carried both connection strings and SQL text. The publication version deliberately removes that responsibility. The library accepts an **already created `DbDataReader`**.

That decision has two benefits:

1. provider packages and connection lifecycle remain outside the serialization core;
2. query construction does not become a serializer concern.

Parameterized SQL, least privilege, credential storage, transaction policy, and authorization still belong to the calling application. A fast serializer is not a substitute for safe data access.

## Why there are no invented benchmark numbers

The source code makes several removed costs observable without inventing a benchmark result: it does not materialize the complete result set, does not allocate a dictionary for every row, does not discover DTO properties, and does not re-encode stable property names in the hot loop.

A numerical claim such as “X times faster,” on the other hand, would require a reproducible benchmark using the same:

- ADO.NET provider;
- query;
- network conditions;
- column types;
- result size;
- JSON/XML representation;
- runtime and GC settings.

I therefore leave throughput numbers out of the article. The architectural reductions can be inspected directly in the code; an actual speedup belongs to a benchmark tied to a specific provider, schema, data distribution, runtime and GC configuration.

## Relationship to my Enumerable CSV work

This design applies the same performance principle as my earlier **An Enumerable CSV Processing Algorithm** to a different data source.

CSV side:

```text
property metadata -> once
header mapping     -> once
rows               -> positional hot path
```

This database side:

```text
reader schema          -> once
JSON/XML field plan    -> once
rows                   -> typed writer hot path
```

The common idea is not a large framework. It is simply to avoid recomputing information that does not change from one record to the next.

## Conclusion

In this implementation the central optimization is a data-flow decision rather than a claim about one serializer being universally faster than another. If the reader can be consumed directly, I do not create a second object graph merely to hand it to a higher-level API.

`DbDataReader` is consumed forward-only; `Utf8JsonWriter` and `XmlWriter` produce output forward-only. The middle layer only prepares the schema once and routes each field to the appropriate writer:

```text
DB -> reader -> writer -> stream
```

The useful property is not allocation reduction in isolation. The design makes ownership and failure boundaries explicit: the caller controls reader lifetime by default, ambiguous duplicate column names are rejected instead of overwritten, the return value reports the number of rows written, and auxiliary application memory does not have to track the total result count. The same core remains usable for any provider that exposes `DbDataReader`.

## References

1. Microsoft. *DataAdapters and DataReaders - ADO.NET*. https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/dataadapters-and-datareaders
2. Microsoft. *How to use Utf8JsonWriter in System.Text.Json*. https://learn.microsoft.com/en-us/dotnet/standard/serialization/system-text-json/use-utf8jsonwriter
3. Microsoft. *XmlWriter Class*. https://learn.microsoft.com/en-us/dotnet/api/system.xml.xmlwriter
4. Muhammet Ali Köker. *An Enumerable CSV Processing Algorithm*. https://alikoker.com.tr/en/enumerable-csv-processing-algorithm
5. Muhammet Ali Köker. *db-reader-streaming-serialization-csharp*. GitHub. [db-reader-streaming-serialization-csharp](https://github.com/alikoker/db-reader-streaming-serialization-csharp)

## Open Source Code

The implementation analyzed in this article is now published as open source on GitHub:

**Source code:** [db-reader-streaming-serialization-csharp](https://github.com/alikoker/db-reader-streaming-serialization-csharp)

The repository contains `DbReaderEnumerable`, `DbReaderSerializer`, schema planning, type-specific writer paths, examples, and tests. This article remains the design narrative; the GitHub repository is the executable implementation. The source archive intentionally does not embed duplicate article files, and repository metadata points to the canonical English article slug `streaming-json-xml-serialization-from-dbdatareader`.

## Software Record

**GitHub:** [db-reader-streaming-serialization-csharp](https://github.com/alikoker/db-reader-streaming-serialization-csharp)

**Archived software release (Zenodo DOI):** [10.5281/zenodo.22117281](https://doi.org/10.5281/zenodo.22117281)

## Cite This Work

Köker, M. A. (2020). Streaming JSON and XML Serialization from DbDataReader. alikoker.com.tr. https://alikoker.com.tr/en/streaming-json-xml-serialization-from-dbdatareader

- BibTeX: https://alikoker.com.tr/en/streaming-json-xml-serialization-from-dbdatareader.bib
- RIS: https://alikoker.com.tr/en/streaming-json-xml-serialization-from-dbdatareader.ris
- CSL-JSON: https://alikoker.com.tr/en/streaming-json-xml-serialization-from-dbdatareader.csl.json