SqlCommand: EnableOptimizedParameterBinding throws error when used together with Prepare

[IAmTheCShark]

When calling Prepare together with EnableOptimizedParameterBinding, TdsExecuteRPC will throw an exception
SQL.ParameterDirectionInvalidForOptimizedBinding because it seems that the Prepare-Method injects addional parameters into the command, that seem to not set the direction correctly.
When debugging the exception i can see it sets the sqlRPC to sp_prepexec and uses 3 additional parameters.

To reproduce

Use the official Prepare-Example from here Link
and add EnableOptimizedParameterBinding = true;

Expected behavior

No exception or a more meaningful one.
I could not find any documentation regarding the issue.

Further technical details

Microsoft.Data.SqlClient 6.1.3 on a net 10 Visual Studio 2026

[mdaigle]

Hi @IAmTheCShark, thanks for your report. I've reproduced the issue and tracked down the root cause to an rpc system parameter added here with type InputOutput. This area of the code is new to me, so it'll take me a bit of time to understand this behavior and whether it's intentional.

[IAmTheCShark]

Hi @mdaigle,

I assume it is necessary in order to return a plan handle reference, so that makes sense.
Maybe it should exclude those injected parameters for this functionality or only enable once the handle was returned.

[mdaigle]

I was able to reproduce this issue using Microsoft.Data.SqlClient 4.0.0 (the version which introduced the EnableOptimizedParameterBinding option), so I'm going to categorize this as a feature request rather than a bug. The original intention of the feature was to be a narrowly scoped performance improvement for queries with huge numbers of input parameters: #1041

@IAmTheCShark, can you please tell me more about your use case? The type of query, scale of data, and number of parameters will all be helpful in determining the expected impact of this work. If possible, can you please also profile your query with and without EnableOptimizedParameterBinding and with and without using Prepare to help guide our expectations?

Based on the performance numbers we may decide to just update the documentation/exception to make clear that these features are incompatible.

[IAmTheCShark]

@mdaigle there was no specific use case i was just reading about what can be done on client side to improve performance and just came accross this issue while writing performance tests. I'd say it would be enough to make the exception more meaningful (can't have both) or improve the docs.

[mdaigle]

Thanks, I will mark this as a doc/exception message update.