dotnet / dotnet/SqlClient

Support Memory<byte>/ReadOnlyMemory<byte> as SqlParameter.Value for binary/varbinary/image parameters

Open
#4,491 1 comment 1 reaction 0 assignees View on GitHub
Approval Needed Design Review Needed Partner Approval Needed :handshake: Public API :new:
Dominant language
C#
Stars
989
Forks
340
Avg merge
4d 19h
Merged PRs (30d)
72

Description

### Is your feature request related to a problem? Please describe.

`SqlParameter.Value` only accepts a handful of hardcoded CLR representations for
`Binary`/`VarBinary`/`Image` parameters. Tracing `SqlParameter.CoerceValue` (decompiled from
`Microsoft.Data.SqlClient` 6.0.2): when the destination `MetaType.ClassType` is `typeof(byte[])`,
the method special-cases exactly `byte[]`, `Stream` (via `StreamDataFeed`), `SqlBytes`, and
`SqlBinary`. Anything else falls through to the generic fallback:

```csharp
value = Convert.ChangeType(value, destinationType.ClassType, null);
```

`Memory` and `ReadOnlyMemory` don't implement `IConvertible`, so this throws
`InvalidCastException("Object must implement IConvertible.")`, which `ADP.ParameterConversionFailed`
re-wraps as:

```
InvalidCastException: Failed to convert parameter value from a ReadOnlyMemory`1 to a Byte[].
```

This forces any caller already holding binary data as `Memory`/`ReadOnlyMemory` — e.g.
a slice of a larger buffer, or a rented `ArrayPool` segment — to call `.ToArray()` first,
which is exactly the extra allocation + copy that `Memory` exists to avoid. There's effectively
no way to bind a byte-slice parameter today without either allocating a full new array or holding
the whole surrounding buffer as a full-size `byte[]` from the start.

The read side has the same shape of gap: `SqlDataReader` always materializes and returns binary
columns as a freshly allocated `byte[]` (via `GetValue`/`GetFieldValue`); there's no way to
read a `varbinary`/`binary`/`image` column directly into a caller-owned buffer without an extra copy.

### Describe the solution you'd like

**Write side:** recognize `Memory` and `ReadOnlyMemory` in `SqlParameter.CoerceValue`
(and the `MetaType`/`GetMetaTypeFromValue` dispatch) as additional carriers for
`Binary`/`VarBinary`/`Image`, alongside the existing `byte[]`/`Stream`/`SqlBytes`/`SqlBinary`
special-cases — mirroring how `Stream` is already handled instead of going through
`Convert.ChangeType`. (Note: `Span` is a `ref struct` and can never be boxed into an
`object`-typed property, so it structurally cannot participate in `SqlParameter.Value` — this
request is scoped to `Memory`/`ReadOnlyMemory` for the write side.)

**Read side:** add a `Span`-based fill API for binary columns (a `ref struct` parameter is
fine here, since it's a method argument, not a stored property) — something like:

```csharp
int SqlDataReader.GetBytes(int ordinal, long dataIndex, Span buffer);
```

analogous to `Stream.Read(Span)`, so a caller can read directly into a pooled/stack buffer
without forcing a fresh `byte[]` allocation per row.

### Describe alternatives you've considered

- **Calling `.ToArray()`/`.Span.ToArray()` before assigning `SqlParameter.Value`.** Works today,
but is exactly the per-call allocation + copy that holding data as `Memory` (from an
`ArrayPool` rental or a slice of a larger buffer) was meant to avoid.
- **Using `Stream` instead** (already supported via `StreamDataFeed`). Reasonable for genuinely
large values, but is heavyweight and async-oriented for small, already-in-memory buffers that
just happen to be `Memory` rather than `byte[]`.
- **Passing the underlying `byte[]` plus a separate offset/length pair.** Defeats the point of a
self-describing `Memory` slice and reintroduces manual bookkeeping the `Memory` API
exists to eliminate.

### Additional context

- A closely related request already exists for the SQLite provider:
[dotnet/efcore#37484](https://github.com/dotnet/efcore/issues/37484) — "Support Memory and
ReadOnlyMemory parameter binding in Microsoft.Data.Sqlite," motivated by the same
array-pool/slicing scenario ("what if the desired blob to bind is not at the beginning of the
array... you want to slice a byte array into multiple columns"). Parity across ADO.NET providers
would help code that targets more than one backend.
- Minimal repro of the current failure:
```csharp
byte[] backing = [1, 2, 3, 4, 5];
ReadOnlyMemory slice = backing.AsMemory(1, 3);

using var command = connection.CreateCommand();
command.CommandText = "INSERT INTO T (Col) VALUES (@p)";
command.Parameters.Add(new SqlParameter("@p", SqlDbType.VarBinary) { Value = slice });
command.ExecuteNonQuery(); // InvalidCastException: Failed to convert parameter value from a ReadOnlyMemory`1 to a Byte[].
```
- Even just the write-side change (`Memory`/`ReadOnlyMemory` as `SqlParameter.Value`)
would already remove the most common pain point; the read-side `Span` fill API is a
separate, larger ask and could be tracked independently if that's preferred.

Contributor guide

Open the contributing guide

Research direction

Start by reading SqlParameter.CoerceValue and the MetaType/GetMetaTypeFromValue dispatch for the existing byte[]/Stream handling, then inspect SqlDataReader's GetBytes and GetFieldValue behavior. Done means the write-side Memory/ReadOnlyMemory request is supported without the reported conversion failure and the separate Span-based read API is evaluated or split into its own issue.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp, sql
Domain
database
Issue type
Feature
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
58/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.